Must declare the scalar variable - help required

  • begin

    DECLARE @deldate varchar(10)='01/01/1960',

    @tblname varchar(50) = 'CACCOUNTSTMT',

    @ssql varchar(200)

    set @ssql = 'set @deldate = (select max(Convert(varchar(10) ,RowCreateDate,103)) from ' +@tblname +')'

    print @ssql

    --set @deldate = @ssql

    exec(@ssql)

    print 'deledate'

    print @deldate

    end

    gives the following error

    Must declare the scalar variable "@deldate".

    can any one help on this?

  • try

    exec sp_executesql @ssql,N'@deldate varchar(10) OUTPUT',@deldate OUTPUT

    instead of

    exec(@ssql)

    Far away is close at hand in the images of elsewhere.
    Anon.

  • isaac.a (1/24/2011)


    begin

    DECLARE @deldate varchar(10)='01/01/1960',

    @tblname varchar(50) = 'CACCOUNTSTMT',

    @ssql varchar(200)

    set @ssql = 'set @deldate = (select max(Convert(varchar(10) ,RowCreateDate,103)) from ' +@tblname +')'

    print @ssql

    --set @deldate = @ssql

    exec(@ssql)

    print 'deledate'

    print @deldate

    end

    gives the following error

    Must declare the scalar variable "@deldate".

    can any one help on this?

    In the dynamic sql, you are setting a variable that doesn't exist in the context of the dynamic sql connection.

    Use the solution David supplied to pass it in and get the results back out.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • Thank u so much. It works.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply