Apply To :
Microsoft SQL Server 2008 R2
Warning:
Must declare the scalar variable "@sql_1".
Detail:
1. Example, Table "Vouchers"
2.
First Query,
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
create procedure SP_Vouchers_1
@Voucher as varchar(20)
as
Declare @sql_1 as varchar(8000)
@sql_1 = ' '
@sql_1 = @sql_1 + ' select Voucher,Nominal_CC,Payment_CC from Voucher '
@sql_1 = @sql_1 + ' where Voucher= ''' + @Voucher + ''' '
--print (@sql_1)
exec (@sql_1)
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
3.
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
Results Messages:
Msg 102, Level 15, State 1, Procedure SP_Vouchers_1, Line 9
Incorrect syntax near '@sql_1'.
Msg 137, Level 15, State 2, Procedure SP_Vouchers_1, Line 15
Must declare the scalar variable "@sql_1".
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
4. Solution:
Add syntax "SET", before word "@sql_1"
Become like this
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
create procedure SP_Vouchers_1
@Voucher as varchar(20)
as
Declare @sql_1 as varchar(8000)
set @sql_1 = ' '
set @sql_1 = @sql_1 + ' select Voucher,Nominal_CC,Payment_CC from Voucher '
set @sql_1 = @sql_1 + ' where Voucher= ''' + @Voucher + ''' '
--print (@sql_1)
exec (@sql_1)
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
Results Messages:
Command(s) completed successfully.
5.
Then, try to run Stored Procedure,
example:
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
exec SP_Vouchers_1 '000016/DN/10/01/12'
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
Results Messages:
6.
Optional, to drop the Stored Procedure,
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
drop procedure SP_Vouchers_1
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
Results Messages:
Command(s) completed successfully.
Source:
Andoko Chandra