Post reply

HOw to get value from Execute(@str) to Local variable in Sql server 2000

  • April 17, 2008 at 4:13 am

    #71837

    Hi, All

    I m using following code.

    EXECUTE(' declare @a as numeric(6) select top 1 @a= bp_id from bug_posts where bp_bug=4648 print @a' )

    i got a value in @a, but how can i use it outside Execute() statement.

    i want to use variable @a in next line after EXECUTE()

    Thanks in Advance,,

  • April 17, 2008 at 4:33 am

    #803572

    Use sp_executesql insead of EXECUTE. It allows you to pass variables in and out of dynamic SQL

    books online has plenty examples

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • April 17, 2008 at 5:27 am

    #803587

    [font="Verdana"]

    EXECUTE(' declare @a as numeric(6) select top 1 @a= bp_id from bug_posts where bp_bug=4648 select @a ' )

    Mahesh

    [/font]

    MH-09-AM-8694

  • April 17, 2008 at 6:12 am

    #803620

    Mahesh, i want to use variable @a in next statement after EXECUTE.

    How can i use it?

  • April 17, 2008 at 6:32 am

    #803644

    sp_executesql

    DECLARE @SQLString nvarchar(500);

    DECLARE @a int;

    SET @SQLString = N'SELECT @OutVariable = bp_id from bug_posts where bp_bug=4648 ';

    EXECUTE sp_executesql @SQLString, N'@OutVariable int OUTPUT', @OutVariable=@a OUTPUT;

    PRINT @a

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

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

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