在SQL语句中传递参数 [英] Passing parameter in SQL statement
本文介绍了在SQL语句中传递参数的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
您好我有以下选择声明。我如何在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屋!
查看全文