选择带有变量作为SQL存储过程参数的语句 [英] Select statement with variable as parameter for sql stored procedure

问题描述

我正在尝试将带有变量的SQL Select语句传递给存储过程.但是我遇到了以下错误:

必须声明标量变量"@date".

这是我的存储过程:

过程[dbo].[sp_ExecuteQuery]
@query nvarchar(150)，
@name nvarchar(10)
AS
EXEC(@query)

...这是我执行存储过程的方式:

EXEC sp_ExecuteQuery SELECT * FROM sqltable WHERE name = @ name，``john''

该存储过程具有两(2)个参数:

@query = SELECT * FROM sqltable WHERE名称= @名称
@name =``约翰''

请注意，sql语句具有名为 @name 的参数.此参数的值将从存储过程(为 john )中的变量 @name 中检索.

不幸的是，我遇到了以上错误.我只想知道这是否可能.任何帮助都将受到高度赞赏.谢谢！


[edit]已删除呼喊声-OriginalGriff [/edit]

解决方案

您可以将SP编写为

 更改 过程 [dbo].[sp_ExecuteQuery]
 @ query   nvarchar ( 150 )，
 @ name   nvarchar ( 10 )
 AS 
 EXEC ( @ query  +  @ name ) 

您的execute语句将像这样

  EXEC  sp_ExecuteQuery '  SELECT *从sqltable WHERE name ='，'  john' 

I''m trying to pass an SQL Select statement with a variable to a stored procedure. But I''m getting the following error:

Must declare the scalar variable "@date".

Here is my Stored Procedure:

PROCEDURE [dbo].[sp_ExecuteQuery]
@query nvarchar(150),
@name nvarchar(10)
AS
EXEC (@query)

...and here is how I execute the Stored Procedure:

EXEC sp_ExecuteQuery SELECT * FROM sqltable WHERE name=@name,''john''

The stored procedure has two (2) parameters:

@query = SELECT * FROM sqltable WHERE name=@name
@name = ''john''

Notice that the sql statement has a parameter named @name. The value of this parameter will be retrieved from the variable @name inside the stored procedure (which is john).

Unfortunately, I got the above error. I just want to know if this is possible. Any help is highly appreciated. Thanks!


[edit]SHOUTING removed - OriginalGriff[/edit]

解决方案

you can write your SP as

Alter PROCEDURE [dbo].[sp_ExecuteQuery]
@query nvarchar(150),
@name nvarchar(10)
AS
EXEC (@query + @name)

and your execute statement will be like this

EXEC sp_ExecuteQuery 'SELECT * FROM sqltable WHERE name=','john'

这篇关于选择带有变量作为SQL存储过程参数的语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！