Post reply

Getting result of dynamic SQL into variable

  • December 28, 2004 at 12:28 am

    #60626

    Hi,

    How do we capture the value of a dynamic sql in a variable.

     
    For example,
    I have a dynamic SQl.
     
    select @SQLStmt= 'select min ( ' + @IdentityField + ') from ' + @TableName
    exec (@SQLStmt)
     
    This, when run, outputs the result on the screen.
    But if I want to capture the output ona variable, how can I do that ?
     
    I tried options like
    select @SourceRecCnt= exec (@SQLStmt).
     
    But it was giving error.
    Can somebody please help me ?

  • December 28, 2004 at 12:52 am

    #535102

    use a function instead, write that query in a function, by function u will be retrive the value

    <a href="http://www.websolsoftware.com"> For IT jobs click here</a>

    *Sukhoi*[font="Arial Narrow"][/font]

  • December 28, 2004 at 2:24 am

    #535106

    Try to avoid this dynamic SQL if you can. To answer your question, try:

    USE PUBS

    GO

    DECLARE @stmt nvarchar(4000)

    DECLARE @rowcount bigint

    DECLARE @table nvarchar(255)

    SET @table = 'authors'

    SELECT @stmt = 'SELECT @count = COUNT(*) FROM ' + @table

    EXEC sp_executesql @stmt, N' @count bigint output', @rowcount OUTPUT

    IF @rowcount > 0

         BEGIN

             SELECT @rowcount AS Anzahl

         END

    RETURN

    Anzahl              

    --------------------

    23

    (1 row(s) affected)

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • December 28, 2004 at 7:05 am

    #535137

    And, to help you in your decision to avoid dynamic SQL, please read this document, appropriately entitled  "The Curse and Blessings of Dynamic SQL"

    http://www.sommarskog.se/dynamic_sql.html

    /Kenneth (doing Frank's job)

  • December 28, 2004 at 7:07 am

    #535138

    Ouch, getting lazy...

    No, it's just to let you score your 300 posts

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • December 29, 2004 at 1:56 am

    #535216

     

    was my suggest useful, was it wrong, please advice me

    <a href="http://www.websolsoftware.com"> For IT jobs click here</a>

    *Sukhoi*[font="Arial Narrow"][/font]

  • December 29, 2004 at 2:00 am

    #535217

    You can't use dynamic SQL inside a function. So this part of your suggestion won't work.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • December 29, 2004 at 8:23 am

    #535267

    Hi,

    Thanks everyone for your kind suggestions.

    Franks dynamic SQL is doing the trick. But since it is advicible not to use dynamic SQL, I changed my logic to acheive the results.

    what Frank said about Calling dynamic SQL inside a function is found correct. If you try that it would give error,

    Server: Msg 557, Level 16, State 2, Procedure try_fun, Line 13

    Only functions and extended stored procedures can be executed from within a function.

    But if the use of dynamic sql inside a function is inevitable, then I think using a stored procedure can be used for acheiving the desired result.

    Once again thanks everyone for your kind suggestions and answers.

Viewing 8 posts - 1 through 8 (of 8 total)

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