Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Store result in Variable of dynamic query Expand / Collapse
Author
Message
Posted Tuesday, November 11, 2008 4:31 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:15 AM
Points: 2,837, Visits: 3,957
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
Post #600555
Posted Tuesday, November 11, 2008 5:01 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, February 18, 2013 3:46 AM
Points: 1,422, Visits: 1,883
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



Post #600568
Posted Tuesday, November 11, 2008 5:02 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, March 13, 2014 5:49 AM
Points: 1,391, Visits: 608
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




Post #600569
Posted Tuesday, November 11, 2008 5:10 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:15 AM
Points: 2,837, Visits: 3,957
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
Post #600575
Posted Tuesday, November 11, 2008 5:24 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 6:36 AM
Points: 845, Visits: 5,455
Use sp_executesql with an OUTPUT parameter.

DECLARE @word sysname

EXEC sp_executesql
    N'SELECT TOP 1 @DynamicWord = [name] FROM sysobjects'
    ,N'@DynamicWord sysname OUTPUT'
    ,@word OUTPUT

SELECT @word

Post #600579
Posted Tuesday, November 11, 2008 5:33 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:15 AM
Points: 2,837, Visits: 3,957
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
Post #600583
Posted Sunday, November 1, 2009 9:15 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, April 20, 2011 6:43 AM
Points: 99, Visits: 243
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
Post #812182
Posted Monday, November 2, 2009 12:10 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 2, 2009 2:52 AM
Points: 3, Visits: 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

Post #812202
Posted Tuesday, November 3, 2009 8:59 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, July 28, 2014 9:43 AM
Points: 79, Visits: 1,329
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

Post #812996
Posted Tuesday, November 3, 2009 9:05 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, July 28, 2014 9:43 AM
Points: 79, Visits: 1,329
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;

Post #813002
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse