Procedure

  • 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

  • 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!

    "Press any key...
    Where the heck is the any key?
    hmmm... Let's see... there's ESC, CTRL, Page Up...
    but no any key"
    - Homer Simpson
  • :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!

    "Press any key...
    Where the heck is the any key?
    hmmm... Let's see... there's ESC, CTRL, Page Up...
    but no any key"
    - Homer Simpson
  • 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

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    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

  • 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

  • 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

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    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

  • 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

  • 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

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    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