存储过程如下
- 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 ([email protected]+')')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 ([email protected]+')' 之后
我自己拼接了好几次了 一直出错请高手帮拼一下
------解决方案--------------------
- SQL code
EXEC(' SELECT [JO002] ,[JO003] ,[JO005] ,[JO015] ,[JO027] ,[JO013] ,[JO014] ,[JO017] ,[JO021] ,[JO028] ,[JO022] ,[JO023] ,[JO024]FROM [EAPS].[dbo].[APSJOB]where JO015 IN ([email protected]+')'AND JO002 LIKE CASE WHEN '+ @in_ph + ' = '' THEN JO002 ELSE '%' '+ @in_ph +''%' ENDAND JO003 LIKE CASE WHEN [email protected]_pm +' = '' THEN JO003 ELSE '%''+ @in_pm +''%' ENDAND JO005 LIKE CASE WHEN '+ @in_gxmc +' = '' THEN JO005 ELSE '%''+ @in_gxmc +''%' END')
------解决方案--------------------
错了，应该是:
- SQL code
EXEC(' SELECT [JO002] ,[JO003] ,[JO005] ,[JO015] ,[JO027] ,[JO013] ,[JO014] ,[JO017] ,[JO021] ,[JO028] ,[JO022] ,[JO023] ,[JO024]FROM [EAPS].[dbo].[APSJOB]where JO015 IN ([email protected]+') AND JO002 LIKE CASE WHEN '+ @in_ph + ' = '' THEN JO002 ELSE '%' '+ @in_ph +''%' ENDAND JO003 LIKE CASE WHEN [email protected]_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 ([email protected]+') 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 code
declare @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 ([email protected]+') '+'AND JO002 LIKE CASE WHEN '+char(39)[email protected]_ph +char(39)+'= '+char(39)+char(39)+' THEN JO002 ELSE '+char(39)+char(37)[email protected]_ph +char(37)+char(39)+' END '+'AND JO003 LIKE CASE WHEN '+char(39)[email protected]_ph +char(39)+'= '+char(39)+char(39)+' THEN JO003 ELSE '+char(39)+char(37)[email protected]_ph +char(37)+char(39)+' END '+'AND JO005 LIKE CASE WHEN '+char(39)[email protected]_ph +char(39)+'= '+char(39)+char(39)+' THEN JO005 ELSE '+char(39)+char(37)[email protected]_ph +char(37)+char(39)+' END 'print(@execsql) EXEC(@execsql)