必须声明标量变量错误执行存储过程 [英] must declare scalar variable error- executing stored proc

问题描述

我有以下结构的存储过程： -

Hi,

I have a stored procedure in the following structure:-

Alter PROCEDURE [dbo].[billno]
@billnumfrom int

AS
BEGIN

    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

declare @SQL nvarchar(300)
set @SQL='select * from Bill_header where Bill_no=@billnumfrom'
EXEC(@SQL)
END

Whenever I am executing the store procedure ,it is giving an error:-
Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@billnumfrom"

请帮助





问候



Sirshak

Please help


Regards

Sirshak

推荐答案

是的，它会的。

Well yes, it will.

set @SQL='select * from Bill_header where Bill_no=@billnumfrom'

将@SQL变量设置为引号之间的字符串。如果你想包含@billnumfrom变量的值，那么你想说

Sets the @SQL variable to exactly the string between the quotes. If you want to include the value of the @billnumfrom variable, then you want to say

set @SQL='select * from Bill_header where Bill_no=' + CONVERT(nvarchar, @billnumfrom)

现在看看您的SP：

Have a look at your SP now:

Alter PROCEDURE [dbo].[billno]
@billnumfrom int
 
AS
BEGIN
 
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
 
SELECT *
FROM Bill_header
WHERE Bill_no=@billnumfrom
END

如你所见，你可以在没有任何内部变量的情况下实现这一点（ @sql ）。

这篇关于必须声明标量变量错误执行存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！