Store result in Variable of dynamic query

Question

Store result in Variable of dynamic query

Bhuvnesh

SSC Guru

Points: 59351
More actions
November 11, 2008 at 4:31 am

#103623

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;-)

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

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

winash SSChampion Points: 11446 More actions · Answer 1

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 More actions · Answer 2

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@wordtable (tableNamevarchar (50))

declare@lstrvarchar (200)

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

insert@word

exec(@lstr)

select* from @word

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 3

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: 18765 More actions · Answer 4

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: 59351 More actions · Answer 5

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 More actions · Answer 6

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 More actions · Answer 7

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 More actions · Answer 8

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 More actions · Answer 9

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 More actions · Answer 10

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 More actions · Answer 11

dumbre.akshay

SSC Journeyman

Points: 83

April 18, 2012 at 3:29 am

#1475216

Thanks dude

mike_damaj Valued Member Points: 73 More actions · Answer 12

--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 More actions · Answer 13

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: 323504 More actions · Answer 14

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!