以下oracle 存储过程如何改为sql server的
CREATE OR REPLACE
PROCEDURE query_tmlId_proc ( areacity IN VARCHAR2, areacounty IN VARCHAR2, p_cursor OUT testpackage.test_cursor )
AS
V_SQL varchar2(30000):= ' select ter.tml_id from ixterminal ter where ter.GROUP_ID in
( SELECT CG.ID FROM CINEMA_GROUP CG,CINEMA_GROUP_AREA CGA WHERE CG.ID = CGA.CINEMA_GROUP_ID AND CG.CATEGORY =1 AND AREA_PROVINCE='''||'湖南省'|| '''';beginif areacity is not null then V_SQL:=V_SQL || ' AND CGA.AREA_CITY='''||areacity||'''';
end if;
if areacounty is not null then V_SQL:=V_SQL || 'AND CGA.AREA_COUNTY='''||areacounty||'''';
end if;V_SQL:=V_SQL || ')';
open p_cursor for v_sql;
end query_tmlId_proc;
CREATE OR REPLACE
PROCEDURE query_tmlId_proc ( areacity IN VARCHAR2, areacounty IN VARCHAR2, p_cursor OUT testpackage.test_cursor )
AS
V_SQL varchar2(30000):= ' select ter.tml_id from ixterminal ter where ter.GROUP_ID in
( SELECT CG.ID FROM CINEMA_GROUP CG,CINEMA_GROUP_AREA CGA WHERE CG.ID = CGA.CINEMA_GROUP_ID AND CG.CATEGORY =1 AND AREA_PROVINCE='''||'湖南省'|| '''';beginif areacity is not null then V_SQL:=V_SQL || ' AND CGA.AREA_CITY='''||areacity||'''';
end if;
if areacounty is not null then V_SQL:=V_SQL || 'AND CGA.AREA_COUNTY='''||areacounty||'''';
end if;V_SQL:=V_SQL || ')';
open p_cursor for v_sql;
end query_tmlId_proc;
IF OBJECT_ID('query_tmlId_proc','P') IS NOT NULL
DROP PROC query_tmlId_proc
GOCREATE PROC query_tmlId_proc(@areacity VARCHAR(1000), @areacounty VARCHAR(1000))
AS
BEGIN
DECLARE @V_SQL VARCHAR(MAX)
SET @V_SQL = ' select ter.tml_id from ixterminal ter where ter.GROUP_ID in
( SELECT CG.ID FROM CINEMA_GROUP CG,CINEMA_GROUP_AREA CGA WHERE CG.ID = CGA.CINEMA_GROUP_ID AND CG.CATEGORY =1 AND AREA_PROVINCE='''+'湖南省'+ '''';
if @areacity is not null
SET @V_SQL = @V_SQL + ' AND CGA.AREA_CITY='''+@areacity+''''
if @areacounty is not null
SET @V_SQL = @V_SQL + ' AND CGA.AREA_COUNTY='''+@areacounty+'''' EXEC(@V_SQL)
END