Post reply

declaring variable

  • October 25, 2002 at 6:42 am

    #80250

    Hi everybody:

    I have this dynamic select statement:

    declare @comp_failed as int

    SET @strSQL = 'SELECT @comp_failed=SUM(FAILED) FROM TIME_VW WHERE ' + @STR

    exec(@strSQL)

    and is telling me variable @comp_failed is not declared, which is not true.

    all others variable from the @strSQL are declared.

    Any saving ideas?

    Thanks a lot,

    Durug

  • October 25, 2002 at 6:52 am

    #439446

    Try initializing your variable before calling it.

    Eg.

    declare @comp_failed as int

    @comp_failed = SUM(FAILED) from TIME_VW

    set @strSQL = 'SELECT @comp_failed

    where' + @STR

    Emma Smyth


    ESmyth

  • October 25, 2002 at 7:03 am

    #439447

    Hi Emma,

    sorry but it doesn't work. if I use your syntax i'm receiving

    'Incorrect syntax near '@comp_failed' probably because of the set keyword missing.

    if I add the set I'm getting error next to from and so on.

    Any other ideas?

    Thanks a lot.

  • October 25, 2002 at 7:45 am

    #439451

    Your variable @comp_failed is out of scope when referenced from the dynamic SQL. Dynamic SQL statements run in a separate batch and cannot therefore see local variables.

    Try concatenating the value of the variable into the @strSQL statement before execution.

    declare @comp_failed int

    set @strSQL = 'SELECT ' + convert(varchar(15), @comp_failed) + '=SUM(Failed) FROM TIME_VW WHERE ' + @STR

    exec (@strSQL)

  • October 25, 2002 at 8:05 am

    #439455

    Thanks. I'm not getting any error, but now after executing the SQL statement

    and trying to print the @comp_failed I have no value in it.

    Is it becasue the conversion, or what??? 🙂

    Thanks a lot,

    Durug

  • October 25, 2002 at 8:15 am

    #439457

    Now I see the problem, I missed the fact that you were trying to return the result into the declared variable.

    The only way I can think of doing this is with temporary tables. You can use temporary tables within dynamic SQL because their scope is against the connection and not the individual procedure.

    declare @comp_failed int

    declare table #tmpResult (CompFailed int)

    set @strSQL = 'SELECT Sum(Failed) as CompFailed FROM Time_VW WHERE ' + @STR

    insert into #tmpResult (CompFailed)

    exec (@strSQL)

    set @comp_failed = (SELECT TOP 1 CompFailed FROM #tmpResult)

    drop table #tmpResult

  • October 25, 2002 at 8:21 am

    #439458

    Just for testing purposes, have you tried it without the variable?

    SET @strSQL = 'SELECT SUM(FAILED) FROM TIME_VW WHERE ' + @STR

    exec(@strSQL)

    I know you need to use the variable, probably for additional script, but if you test just the above line you can tell if the problem is with the variable portion.

    -Bill

  • October 25, 2002 at 8:46 am

    #439465

    BillSQL: it works if I eliminate the variable from the @strSQL

    Paul: this is the solution that I used before, but I was trying to avoid the temp tables.

    Thanks a lot all of you.

    Durug

  • October 25, 2002 at 10:33 am

    #439488

    Have you tried this?

    declare @comp_failed as int

    SET @strSQL = 'SET @comp_failed = SELECT SUM(FAILED) FROM TIME_VW WHERE ' + @STR

    exec(@strSQL)

    -Bill

  • October 25, 2002 at 10:37 am

    #439490

    OOOPPS, I made a mistake....try this (note parenthesis' around the select statement):

    declare @comp_failed as int

    SET @strSQL = 'SET @comp_failed = (SELECT SUM(FAILED) FROM TIME_VW WHERE ' + @STR)

    exec(@strSQL)

    -Bill

  • October 27, 2002 at 12:16 pm

    #439546

    EXEC performs the SQL in a different scope to the calling TSQL, you can therefore not use variables declared outside of it.

    You need to use sp_executesql

    declare @comp_failed as int

    SET @strSQL = 'SELECT @comp_failed = SUM(FAILED) FROM TIME_VW WHERE ' + @STR

    exec sp_executesql @strSQL, N'@comp_failed int OUTPUT', @comp_failed OUTPUT

    Simon Sabin

    Co-author of SQL Server 2000 XML Distilled

    http://www.amazon.co.uk/exec/obidos/ASIN/1904347088


    Simon Sabin
    SQL Server MVP

    http://sqlblogcasts.com/blogs/simons

Viewing 11 posts - 1 through 10 (of 10 total)

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