在SQL语句中传递参数 [英] Passing parameter in SQL statement

问题描述

您好我有以下选择声明。我如何在where子句中传递参数？我是这样做的，但是当我运行它时它会显示错误。

  SET   @ cursor  =  CURSOR   FOR  
  SELECT  P.DocStatus 
  FROM  vsCustomizationRequest.dbo.CRF_Project P，vsCustomizationRequest .dbo.CustomerList C 
  WHERE  C.CustomerName  IN （'  + @ name +'） AND  P.CustomerID = C.CustomerID 
 
 
  OPEN   @ cursor  
  FETCH  NEXT 
  FROM   @ cursor  进入  @ st atus  
  WHILE   @@ FETCH_STATUS  =  0  
  BEGIN  

错误在C.CustomerName IN（'+ @ name +'），我可以传递多个值。但是在我运行上面的SQL之后，结果返回null。我调试了它，跳过了生成结果的部分。以上只是我的代码的一部分，因为错误来自那里。谢谢。

解决方案

在（'+ @ name +'）中你不需要撇号;删除它们。

  SET   @ cursor  =  CURSOR   FOR  
  SELECT  P.DocStatus 
  FROM  vsCustomizationRequest.dbo.CRF_Project P，vsCustomizationRequest.dbo.CustomerList C 
  WHERE  C.CustomerName =  @ name   AND  P.CustomerID = C.CustomerID 
 
 
  OPEN   @ cursor  
  FETCH  NEXT 
  FROM   @ cursor  进入  @ status  
  WHILE   @@ FETCH_STATUS  =  0  
  BEGIN  

这里我把它作为一个解决方案按照相同的步骤。我为你创建了一个样本表。

   -   创建表格： 
 
 创建 表 [dbo]。[学生]（
 [STUD_UID] [ int ]  NULL ，
 [NAME] [ varchar ]（ 50 ） NULL  
） ON  [ PRIMARY ] 
 
   -   插入样本记录到表： 
 
  INSERT   INTO  [Sudents] 
 （[STUD_UID]，[NAME]）
  VALUES  
（ 1 ，< span class =code-string>' 测试'）
 
  INSERT   INTO  [Sudents] 
（[STUD_UID]，[NAME]）
  VALUES  
（ 2 ，'  Test2'）
 
  INSERT   INTO  [Sudents] 
（[STUD_UID]，[NAME]）
  VALUES  
（ 3  ，'  Test3'）
 
   -   现在这里是您将name作为参数传递给select in子句的示例查询： 
 
 声明  @ names   varchar （ 100 ）
 声明  @sql   nvarchar （最多）
  set  @ names = ' ''Test2''，''Test3''' 
 
 设置 @sql = ' 从名为（' +）的学生中选择* @ names + ' ）' 
  exec  sp_executesql  @sql  
 
  set  @ names = ' ''测试''，''测试3''' 
 
 设置 @sql = ' 从名为（' + @ names + ' ）' 
 
  exec  sp_executesql  @sql  
   -   为了您的回复，我显示了2个差异查询。 

Hi i have the following select statement. How can i pass a parameter in the where clause? I did it like this but when i run it it shows me error.

SET @cursor = CURSOR FOR
SELECT P.DocStatus
FROM vsCustomizationRequest.dbo.CRF_Project P, vsCustomizationRequest.dbo.CustomerList C
WHERE C.CustomerName IN ('+@name+') AND P.CustomerID = C.CustomerID
	
	
OPEN @cursor
FETCH NEXT
FROM @cursor into @status
WHILE @@FETCH_STATUS = 0
BEGIN

The error is at the C.CustomerName IN ('+@name+'), I may pass multiple value into it. But after i run the above SQL, the result return null. I have debug it, the part which is suppose to generate the result is skipped. The above is only part of my code as the error is come from there. Thank you.

解决方案

In ('+@name+') you don't need the apostrophes; remove them.

SET @cursor = CURSOR FOR
SELECT P.DocStatus
FROM vsCustomizationRequest.dbo.CRF_Project P, vsCustomizationRequest.dbo.CustomerList C
WHERE C.CustomerName = @name AND P.CustomerID = C.CustomerID
	
	
OPEN @cursor
FETCH NEXT
FROM @cursor into @status
WHILE @@FETCH_STATUS = 0
BEGIN

hi,
here i make it as a solution follow the same steps .i have created one sample table for you.

-- Create Table : 

CREATE TABLE [dbo].[Students](
	[STUD_UID] [int] NULL,
	[NAME] [varchar](50) NULL
) ON [PRIMARY]

-- Insert sample records to the Table :

INSERT INTO [Sudents]
           ([STUD_UID],[NAME])
     VALUES
           (1 ,'Test')

INSERT INTO [Sudents]
           ([STUD_UID],[NAME])
     VALUES
           (2,'Test2')

INSERT INTO [Sudents]
           ([STUD_UID],[NAME])
     VALUES
           (3,'Test3')

--Now here is the sample query for you to pass name as parameter to the select in clause:

Declare @names varchar(100)
declare @sql nvarchar(Max)
set @names=' ''Test2'',''Test3'''

Set @sql='select * from students where name in ('+@names+')'
exec sp_executesql @sql

set @names=' ''Test'',''Test3'''

Set @sql='select * from students where name in ('+@names+')'

exec sp_executesql @sql
-- For your referance i have displayed 2 differance query.

这篇关于在SQL语句中传递参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！