Dynamic Sql - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Dynamic Sql

Author

Topic

ruthika
Starting Member

2 Posts

Posted - 2008-09-22 : 05:26:25

hai,

I create one procedure and I want to pass that parameter value
of the procedure
to the following expersion

exec('if (@AssessableValue > 0) Begin @BED_BaseAmt = @AssessableValue*@Qty End else Begin @BED_BaseAmt = @Price*@Qty End @BED_TaxAmt=@BED_BaseAmt*@BED_Rate')

@AssessableValue,@Qty and @Price all are the numeric Parameter.

I what in the dynamic SQL.

Can any one have the idea?

Please help me.

Regards,
Ramya.S

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-22 : 05:29:23

Why do you use dynamic sql in this case?
What is wrong with static sql?

if (@AssessableValue > 0) 
Begin 
	@BED_BaseAmt = @AssessableValue*@Qty 
End 
else 
Begin 
	@BED_BaseAmt = @Price*@Qty 
End 
SET @BED_TaxAmt=@BED_BaseAmt*@BED_Rate

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-22 : 05:31:32

why use dynamic sql? i think what you want is this

SELECT @BED_BaseAmt = CASE WHEN @AssessableValue > 0 THEN @AssessableValue*@Qty ELSE @Price*@Qty End,
@BED_TaxAmt=@BED_BaseAmt*@BED_Rate

ruthika
Starting Member

2 Posts

Posted - 2008-09-22 : 05:44:02

hai,

Thanks for your reply.I retrive that if condition from one table.that table have many formula like this.so I want to use the dynamic sql.

My procedure is

alter procedure tax(@taxCode varchar(100),@Qty numeric(19,6),@Price numeric(19,6),@dis numeric(19,6),@AssessableValue numeric(19,6))
as
begin
declare vari cursor for Select VarName from OFML A,FML1 b where a.absid=b.FmlId and A.Code='bed'
declare vari1 cursor for Select VarName from OFML A,FML1 b where a.absid=b.FmlId and A.Code=@taxcode and VarName not in('Qty','Price','AssessableValue')
declare @str as varchar(1000),@str1 as varchar(1000),@str3 as varchar(1000),@var varchar(40),@var1 varchar(40),@qry varchar(3000)
set @str=(select fmlLang from ofml where code=@taxcode)
open vari
fetch next from vari into @var
WHILE @@FETCH_STATUS=0
begin
set @str=(select replace(@str,@var,'@'+@var))
fetch next from vari into @var
end
close vari
deallocate vari

open vari1
fetch next from vari1 into @var1
set @str1='Declare @'+@var +' numeric(19,6)'
WHILE @@FETCH_STATUS=0
begin
fetch next from vari1 into @var1
set @str1 = @str1+' ,@'+@var1+' numeric(19,6)'
end
close vari1
deallocate vari1

set @str1=(select replace(@str1,',@'+@var1+' numeric(19,6)',''))

exec(@str1)
set @str=(select replace(@str,'{',' Begin '))
set @str=(select replace(@str,'}',' End '))
--set @str= ''''+@str+''''
select @str,@str1,@var1
set @str=(select replace(@str,'@BED_Rate',1))
exec(@str)
end

in this Procedure

in the vari cursor

Select VarName from OFML A,FML1 b where a.absid=b.FmlId and A.Code='bed'

the result is

BED_TaxAmt
BED_BaseAmt
BED_Rate
Total
Qty
Price
AssessableValue

I declare these variable

In the Procedure

set @str=(select fmlLang from ofml where code=@taxcode)--(if taxcode='BED')

then the result is

if (AssessableValue > 0) { BED_BaseAmt = AssessableValue*Qty } else { BED_BaseAmt = Price*Qty } BED_TaxAmt=BED_BaseAmt*BED_Rate

these formula is come
I want to pass procedure parameter to this formul

so I use this statement
set @str=(select replace(@str,@var,'@'+@var))
so @str

I got this one

if (@AssessableValue > 0) Begin @BED_BaseAmt = @AssessableValue*@Qty End else Begin @BED_BaseAmt = @Price*@Qty End @BED_TaxAmt=@BED_BaseAmt*@BED_Rate

If i directly execute the
exec(@str)

then I got the error

Must declare the scalar variable "@AssessableValue".

have U have any idea?

Please help me.

Regards,
Ramya.S

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-22 : 05:52:38

You cant retrieve values of varaibles using EXEC(). use sp_executesql with OUTPUT parameter to receive the variable value by dynamic sql.
I also feel you can avoid cursor and do it in set based approachj. would you mind illustrating your requirement with some sample output in which case i will try rewriting logic in set based approach.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources