September 21, 2008 at 11:23 pm
hai to all,
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".
How I can pass the procedure parameter value to this formule
any one have the idea,
Please help me,
Regards,
Ramya.S
September 22, 2008 at 2:28 am
Hi there,
Hope this helps
DECLARE
@taxCode varchar(100),
@Qty numeric(19,6),
@Price numeric(19,6),
@dis numeric(19,6),
@AssessableValue numeric(19,6)
SELECT
@taxCode = '123',
@Qty = '123'),
@Price = '123',
@dis = '123',
@AssessableValue = '123'
EXEC tax
@taxCode varchar(100),
@Qty numeric(19,6),
@Price numeric(19,6),
@dis numeric(19,6),
@AssessableValue numeric(19,6)
-- OR
EXEC tax
@taxCode = '123',
@Qty = '123'),
@Price = '123',
@dis = '123',
@AssessableValue = '123'
--OR SIMPLY
EXEC tax '123','123','123','123'
Now if you want your sproc to have default values just fdo soething like this
CREATE PROC Sample_SProc
@val1 INT = 123,
@val2 VARCHAR(8) =1234
AS
...
Please Tell me if this was helpful or if you need some modifications
_____________________________________________
[font="Comic Sans MS"]Quatrei Quorizawa[/font]
:):D:P;):w00t::cool::hehe:
MABUHAY PHILIPPINES!
September 22, 2008 at 2:29 am
:DLOL
:PI forgot to explain...
:)The Stored procedure expects inputs for ALL its parameters unless they have DEFAULT values...
:w00t:By the way, it's best to use my first example, It easier to do the 2nd example but aviod the third unless you only have VERY FEW parameters
_____________________________________________
[font="Comic Sans MS"]Quatrei Quorizawa[/font]
:):D:P;):w00t::cool::hehe:
MABUHAY PHILIPPINES!
September 22, 2008 at 3:12 am
Hi Ramya
You are getting this error message because the batch executed by the EXEC statement cannot reference variables declared in the batch from which it was called.
You could use sp_executesql, which is parameter-friendly.
Can you please post the string which is executed, for a few examples?
Put a PRINT @STR
before exec(@str)
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 22, 2008 at 3:35 am
hai,
Thanks for your reply.
the @STR is
if (@AssessableValue > 0) Begin @BED_BaseAmt = @AssessableValue*@Qty End else Begin @BED_BaseAmt = @Price*@Qty End @BED_TaxAmt=@BED_BaseAmt*@BED_Rate
can u please say how to use the sp_executesql statement?
I have one more doubt .It is possible using the dynamic Sql.
Regrads,
Ramya.S
September 22, 2008 at 3:54 am
Hi Ramya
Can't you run the statement as it is? I can't see a need for EXEC:
[font="Courier New"]IF @AssessableValue > 0
SET @BED_BaseAmt = @AssessableValue * @Qty
ELSE
SET @BED_BaseAmt = @Price * @Qty
SET @BED_TaxAmt = @BED_BaseAmt * @BED_Rate
[/font]
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 22, 2008 at 4:01 am
hai,
I want retrive the the value from the formula
@BED_BaseAmt,@BED_TaxAmt .
If i execute the if statement then only i got that amount.
I don't know the formula .at the runtime only the formula is come.
Regards,
Ramya.S
September 22, 2008 at 4:19 am
Hi Ramya
If you set up your formula expression as a string and EXECute it, then any variables defined in the calling batch will be out of scope to the batch EXECuted, and likewise any variables defined within the batch EXECuted will be out of scope to the calling batch.
Executing dynamic SQL like this, using variables, requires you to use sp_executesql:
[font="Courier New"]DECLARE @Today DATETIME
SET @Today = GETDATE()
DECLARE @SQLString NVARCHAR(200)
SET @SQLString = 'SELECT @Thisday'
PRINT @SQLString
--EXEC(@SQLString) -- fails, variable is out of scope
EXECUTE sp_executesql @SQLString,
N'@Thisday DATETIME',
@Thisday = @Today -- SUCCESS
[/font]
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply