拼接sql语句的问题

显示全部楼层 · 2008-5-31 01:02:30

下面这段sql代码，在多个线路的条件下无法查询得到想要的结果。
DECLARE @CircuitIDs AS VARCHAR(200)
DECLARE @STRSQL AS VARCHAR(500)
SET @CircuitIDs= 'BEJBEJ(CNC)LK00001,SHZ(CT)IA00509'--2条线路编号
SET @STRSQL ='SELECT DISTINCT * FROM

(SELECT CusID,CctName FROM T_IAPNCircuit WHERE CctName IN('''+ @CircuitIDs +''')

UNION ALL

SELECT CusID,LKName FROM T_LKInfo WHERE LKName IN('''+ @CircuitIDs +''')) AS Temp';
EXEC(@STRSQL );
select @STRSQL的时候发现，sql语句使这样的：SELECT DISTINCT * FROM (SELECT CusID,CctName FROM T_IAPNCircuit WHERE CctName IN('BEJBEJ(CNC)LK00001,SHZ(CT)IA00509')
UNION ALLSELECT CusID,LKName FROM T_LKInfo WHERE LKName IN('BEJBEJ(CNC)LK00001,SHZ(CT)IA00509')) AS Temp
很明显，上面sql语句括号内不是（'BEJBEJ(CNC)LK00001','SHZ(CT)IA00509'）这样的形式，请问这样才能变成这样形式的呢？

千问 · 2008-5-31 01:02:30

附上存储过程吧。上面是调试用的sql。
ALTER PROCEDURE [dbo].[P_RPOUMW_Sel_CusIDByCircuitIDs]

-- Add the parameters for the stored procedure here
(

@CircuitIDs AS VARCHAR(200)
)

AS
BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

-- SET NOCOUNT ON;
-- Insert statements for procedure here

BEGIN TRY

DECLARE @sql AS VARCHAR(500)

SET @sql='SELECT DISTINCT * FROM

(SELECT CusID,CctName FROM T_IAPNCircuit WHERE CctName IN('''+ @CircuitIDs +''')

UNION ALL

SELECT CusID,LKName FROM T_LKInfo WHERE LKName IN('''+ @CircuitIDs +''')) AS Temp';

EXEC(@sql);

END TRY

BEGIN CATCH

DECLARE @ErrMsg varchar(50)

SET@ErrMsg ='APPEND Error!'

RAISERROR(@ErrMsg, 14, 1)

END CATCH
END

千问 · 2008-5-31 01:02:30

从变量@CircuitIDs的形式入手：
DECLARE @CircuitIDs AS VARCHAR(200)
DECLARE @STRSQL AS VARCHAR(500)
SET @CircuitIDs= '''BEJBEJ(CNC)LK00001'',''SHZ(CT)IA00509'''--2条线路编号
SET @STRSQL ='SELECT DISTINCT * FROM

(SELECT CusID,CctName FROM T_IAPNCircuit WHERE CctName IN('+ @CircuitIDs +')

UNION ALL

SELECT CusID,LKName FROM T_LKInfo WHERE LKName IN('+ @CircuitIDs +')) AS Temp';
exec(@STRSQL)
go