Gurus,
Here is what I ma trying to do. I have numeric expression stored in a
table column. for e.g. @a + @b + @c. I supply values to the variables
at run time and want them to be computed at run time as per the
expression in the column.
the stored procedure works fine but it gives a silly error.
Any help greatly appreciated. Below is the code.
--drop procedure proc_bkr
create procedure proc_bkr AS
declare @expr nvarchar(2000)
declare @sql nvarchar(2000)
declare @temp_exp nvarchar(3000)
declare @ans integer
declare @QFAAPAC02_1 integer
declare @QFAAPAC02_2 integer
declare @QFAAPAC02_3 integer
declare @QFAAPAC02_4 integer
-- Assigning values to variables -- Start
set @QFAAPAC02_1 = (Select QFAAPAC02_1 from fa_ap_stage where recordid
= 3)
set @QFAAPAC02_2 = (Select QFAAPAC02_2 from fa_ap_stage where recordid
= 3)
set @QFAAPAC02_3 = (Select QFAAPAC02_3 from fa_ap_stage where recordid
= 3)
set @QFAAPAC02_4 = (Select QFAAPAC02_4 from fa_ap_stage where recordid
= 3)
-- Assigning values to variables -- End
set @temp_exp = (select num from translation where processid = 'AP' and
label = 'C1')
-- This is how num looks: @QFAAPAC02_1 + @QFAAPAC02_2 + @QFAAPAC02_3 +
@QFAAPAC02_4
--select @expr = '@QFAAPAC02_1 + @QFAAPAC02_2 + @QFAAPAC02_3 +
@QFAAPAC02_4'
-- Above line works fine but below one does not. though both are same.
select @expr = @temp_exp
select @sql = 'select @ans = ' + @expr
exec sp_executesql @sql, N'@QFAAPAC02_1 integer, @QFAAPAC02_2 integer,
@QFAAPAC02_3 integer, @QFAAPAC02_4 integer, @ans integer OUTPUT',
@QFAAPAC02_1,@QFAAPAC02_2,@QFAAPAC02_3,@QFAAPAC02_ 4,@ans OUTPUT
set @cc = @ans
Error Message: Server: Msg 137, Level 15, State 2, Line 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Must declare the
variable '@QFAAPAC02_'.
Thanks in Advance!
Bkr
