Store result in Variable of dynamic query

  • Bhuvnesh

    SSC Guru

    Points: 59344

    hi

    declare @lstr varchar(200)

    declare @word varchar(20)

    set @lstr = 'select top 1 name from sysobjects'

    set @word = exec(@lstr)

    select @word

    i want ot store result comes from dynamic query into variable @word.(or above code is not working)

    Please help

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • winash

    SSChampion

    Points: 11386

    The problem here is that the variables are declared outside the scope of the dynamic SQL statement...either declare the variable within the dynamic SQL (this might not be of any use) or get the results of the dynamic into a table/temp table (this might be of more use)

    declare @lstr varchar(200)

    set @lstr = 'declare @word varchar(20) select top 1 @word = name from sysobjects select @word'

    exec(@lstr)

    or

    DECLARE @Results TABLE(result sysname)

    INSERT @Results(result)

    EXEC('select top 1 name from sysobjects')

    SELECT * FRM @Results

  • Kishore.P

    SSCrazy Eights

    Points: 8147

    bhuvnesh.dogra (11/11/2008)


    hi

    declare @lstr varchar(200)

    declare @word varchar(20)

    set @lstr = 'select top 1 name from sysobjects'

    set @word = exec(@lstr)

    select @word

    i want ot store result comes from dynamic query into variable @word.(or above code is not working)

    Please help

    you may try like:

    declare @word table (tableName varchar (50))

    declare @lstr varchar (200)

    set @lstr = 'select name from sysobjects where xtype = ''u'''

    insert @word

    exec (@lstr)

    select * from @word

  • Bhuvnesh

    SSC Guru

    Points: 59344

    thanks

    but i cant take table variable or temp table ? i need to use local variable .

    but if atlast i go with local variable will there be performance difference between local variable and table variable ?

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • Ken McKelvey

    SSCoach

    Points: 18229

    Use sp_executesql with an OUTPUT parameter.

    DECLARE @word sysname

    EXEC sp_executesql

    &nbsp&nbsp&nbsp&nbspN'SELECT TOP 1 @DynamicWord = [name] FROM sysobjects'

    &nbsp&nbsp&nbsp&nbsp,N'@DynamicWord sysname OUTPUT'

    &nbsp&nbsp&nbsp&nbsp,@word OUTPUT

    SELECT @word

  • Bhuvnesh

    SSC Guru

    Points: 59344

    hi ken,

    thanks it works:)

    but can u explain the whole scenario.

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • venu_ksheerasagaram

    SSCommitted

    Points: 1911

    Hi All,

    Here i am having a doubt regarding the Dynamic Query. My requirement is How to store the Dynamic Query resultset into a TempTable? Could it possibel?

    Because i need to fire a Search Query on the result set that was generated by the Dyamic query.

    Thank you all,

    Venu Gopal.K
    Software Engineer
    INDIA

  • pradipcjain

    SSC Enthusiast

    Points: 117

    with small correction...

    declare @lstr varchar(200)

    declare @word table (tableName varchar (50))

    set @lstr = 'select top 1 name from sysobjects'

    insert @word

    exec(@lstr)

    select * from @word

    🙂

  • Adam Gojdas

    SSCommitted

    Points: 1766

    Bhuvnesh (11/11/2008)


    hi ken,

    thanks it works:)

    but can u explain the whole scenario.

    Here this may help a little:

    DECLARE @word sysname

    EXEC sp_executesql

    N'SELECT TOP 1 @DynamicWord = [name] FROM sysobjects' --dynamic sql query to execute

    ,N'@DynamicWord sysname OUTPUT' --parameter definitions

    ,@DynamicWord=@word OUTPUT --assigning the caller procs local variable to the dynamic parameter

    SELECT @word

  • Adam Gojdas

    SSCommitted

    Points: 1766

    venu_ksheerasagaram (11/1/2009)


    Hi All,

    Here i am having a doubt regarding the Dynamic Query. My requirement is How to store the Dynamic Query resultset into a TempTable? Could it possibel?

    Because i need to fire a Search Query on the result set that was generated by the Dyamic query.

    Thank you all,

    Here is a possible way to do that:

    CREATE TABLE #TempLocal (MyName sysname);

    EXEC sp_executesql

    N'INSERT INTO #TempLocal (MyName) SELECT [name] FROM sysobjects'; --dynamic sql query to execute

    SELECT MyName FROM #TempLocal;

    DROP TABLE #TempLocal;

  • tarang.lute

    SSC Enthusiast

    Points: 129

    hi i was stuck in the similar situation and following code works for me...

    Server: Sql Server 2005

    Query :

    select top 1 name into #tmp from sysobjects

    declare @result varchar(50)

    select @result=name from #tmp

    if(@result is not null)

    -- u r code

    else

    --u r code

    To view result

    exec [procedurename][parameter1][parameter2]

    :-):-)

    http://imbeginner.blogspot.com

  • dumbre.akshay

    SSC Journeyman

    Points: 83

    Thanks dude

  • mike_damaj

    Valued Member

    Points: 73

    --you can declare, execute,select from within a variable but you need to include ';' between statements
    --this is what we call it programming in SQL!
    DECLARE @VEXE VARCHAR(100),@COUNTS INT,@TBL_NAME VARCHAR(100)
    SET @TBL_NAME = 'ALIAS_TBL'
    SET @VEXE = 'DECLARE @COUNTS INT ;SET @COUNTS = (SELECT COUNT(*) FROM '+ @TBL_NAME +') ;SELECT @COUNTS'
    EXEC (@VEXE)
    SELECT @COUNTS

  • mittal618

    Valued Member

    Points: 67

    Ken McKelvey - Tuesday, November 11, 2008 5:24 AM

    Use sp_executesql with an OUTPUT parameter.DECLARE @word sysnameEXEC sp_executesql    N'SELECT TOP 1 @DynamicWord = [name] FROM sysobjects'    ,N'@DynamicWord sysname OUTPUT'    ,@word OUTPUTSELECT @word

    Below SQL returning NULL, Am I doing anything wrong?

    DECLARE @countQuery NVARCHAR(MAX) =

    N'SELECT COUNT(1) FROM customer';

    EXECUTE dbo.sp_executesql

    @countQuery,

    N'@productsExist INT OUTPUT', -- here

    @productsExist = @productsExist OUTPUT; -- here

    SELECT @productsExist as ProductsExist;

  • Lowell

    SSC Guru

    Points: 323361

    i think the issue is you did not declare an outer variable to capture the results as an output , nor did you actually use the variable declared in the parameters.
    i changed the table custmers to  sys.procedures to prove it works.

    DECLARE @results int;
    DECLARE @countQuery NVARCHAR(MAX) =
    N'SELECT @productsExist = COUNT(1) FROM sys.procedures';
    EXECUTE dbo.sp_executesql
    --pass my query through as the first parameter
    @countQuery,
    --pass the declared inner variable, specifically as output
    N'@productsExist INT OUTPUT', -- here
    --assign the inner variable to my outer variable
    @productsExist = @results OUTPUT; -- here

    SELECT @results as ProductsExist;

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

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

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