查询语句拼接解决办法

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

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_usID
EXEC('
        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 +'%' 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

拼接到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 +''%' 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

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 +''%' 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


'
        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 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 ('+@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)

查询语句拼接解决办法

相关推荐