Cari Blog Ini

28 Maret 2013

Warning Must Declare The Scalar Variable


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