查询语句拼接解决办法

2012-06-03

查询语句拼接存储过程如下SQL codeALTER PROCEDURE [dbo].[PROC_EAPS_GetJobReport]@in_phNvarchar(20),@i

查询语句拼接
存储过程如下

SQL code

ALTER PROCEDURE [dbo].[PROC_EAPS_GetJobReport]  @in_ph    Nvarchar(20),@in_pm    Nvarchar(60),@in_gxmc    Nvarchar(60),@in_usID nvarchar(10)AS  BEGIN declare @sql nvarchar(4000)DECLARE @STR VARCHAR(3000)SET @STR=''SELECT @STR=EM007 FROM [EAPS].[dbo].APSEMP WHERE EM001 = @in_usIDEXEC('        SELECT        [JO002]      ,[JO003]      ,[JO005]      ,[JO015]      ,[JO027]      ,[JO013]      ,[JO014]      ,[JO017]      ,[JO021]      ,[JO028]      ,[JO022]      ,[JO023]      ,[JO024]FROM     [EAPS].[dbo].[APSJOB]where    JO015 IN ('+@STR+')')END

我现在想把

SQL code

AND    JO002 LIKE CASE WHEN @in_ph = '' THEN JO002                ELSE '%'+ @in_ph +'%' ENDAND    JO003 LIKE CASE WHEN @in_pm = '' THEN JO003                ELSE '%'+ @in_pm +'%' ENDAND    JO005 LIKE CASE WHEN @in_gxmc = '' THEN JO005                ELSE '%'+ @in_gxmc +'%' END

拼接到JO015 IN ('+@STR+')' 之后
我自己拼接了好几次了一直出错请高手帮拼一下

[解决办法]

SQL code

EXEC('        SELECT        [JO002]      ,[JO003]      ,[JO005]      ,[JO015]      ,[JO027]      ,[JO013]      ,[JO014]      ,[JO017]      ,[JO021]      ,[JO028]      ,[JO022]      ,[JO023]      ,[JO024]FROM     [EAPS].[dbo].[APSJOB]where    JO015 IN ('+@STR+')'AND    JO002 LIKE CASE WHEN '+ @in_ph + ' = '' THEN JO002                ELSE '%' '+ @in_ph +''%' ENDAND    JO003 LIKE CASE WHEN '+@in_pm +' = '' THEN JO003                ELSE '%''+ @in_pm +''%' ENDAND    JO005 LIKE CASE WHEN '+ @in_gxmc +' = '' THEN JO005                ELSE '%''+ @in_gxmc +''%' END')
[解决办法]
错了，应该是:
SQL codeEXEC('        SELECT        [JO002]      ,[JO003]      ,[JO005]      ,[JO015]      ,[JO027]      ,[JO013]      ,[JO014]      ,[JO017]      ,[JO021]      ,[JO028]      ,[JO022]      ,[JO023]      ,[JO024]FROM     [EAPS].[dbo].[APSJOB]where    JO015 IN ('+@STR+') AND JO002 LIKE CASE WHEN '+ @in_ph + ' = '' THEN JO002                ELSE '%' '+ @in_ph +''%' ENDAND    JO003 LIKE CASE WHEN '+@in_pm +' = '' THEN JO003                ELSE '%''+ @in_pm +''%' ENDAND    JO005 LIKE CASE WHEN '+ @in_gxmc +' = '' THEN JO005                ELSE '%''+ @in_gxmc +''%' END')
[解决办法]
SQL code'        SELECT        [JO002]      ,[JO003]      ,[JO005]      ,[JO015]      ,[JO027]      ,[JO013]      ,[JO014]      ,[JO017]      ,[JO021]      ,[JO028]      ,[JO022]      ,[JO023]      ,[JO024]FROM     [EAPS].[dbo].[APSJOB]where    JO015 IN ('+@STR+') AND    JO002 LIKE CASE WHEN ' + @in_ph + ' = '''' THEN JO002                ELSE ''%' + @in_ph + '%'' END AND    JO003 LIKE CASE WHEN ' + @in_pm + ' = '''' THEN JO003                ELSE ''%' + @in_pm + '%'' END AND    JO005 LIKE CASE WHEN ' + @in_gxmc + ' = '''' THEN JO005                ELSE ''%' + @in_gxmc +'%'' END'
[解决办法]
SQL codedeclare @execsql nvarchar(4000)DECLARE @STR VARCHAR(3000)='1'DECLARE @in_ph Nvarchar(20)=''       SET @execsql='       SELECT        [JO002]      ,[JO003]      ,[JO005]      ,[JO015]      ,[JO027]      ,[JO013]      ,[JO014]      ,[JO017]      ,[JO021]      ,[JO028]      ,[JO022]      ,[JO023]      ,[JO024]FROM     [EAPS].[dbo].[APSJOB]where    JO015 IN ('+@STR+') '+'AND    JO002 LIKE CASE WHEN '+char(39)+@in_ph +char(39)+'= '+char(39)+char(39)+' THEN JO002                ELSE '+char(39)+char(37)+@in_ph +char(37)+char(39)+' END '+'AND    JO003 LIKE CASE WHEN '+char(39)+@in_ph +char(39)+'= '+char(39)+char(39)+' THEN JO003                ELSE '+char(39)+char(37)+@in_ph +char(37)+char(39)+' END '+'AND    JO005 LIKE CASE WHEN '+char(39)+@in_ph +char(39)+'= '+char(39)+char(39)+' THEN JO005                ELSE '+char(39)+char(37)+@in_ph +char(37)+char(39)+' END 'print(@execsql)              EXEC(@execsql)

热点排行

Bad Request.

SQL Server

查询语句拼接解决办法