|
|
|
SSCrazy
      
Group: General Forum Members
Last Login: Tuesday, March 26, 2013 8:41 AM
Points: 2,562,
Visits: 3,451
|
|
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---------- While 1 = 1 (Learning SQL....) Click to get fast response of your post
|
|
|
|
|
Ten 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
|
|
|
|
|
Ten Centuries
      
Group: General Forum Members
Last Login: Monday, November 26, 2012 3:25 AM
Points: 1,389,
Visits: 596
|
|
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
|
|
|
|
|
SSCrazy
      
Group: General Forum Members
Last Login: Tuesday, March 26, 2013 8:41 AM
Points: 2,562,
Visits: 3,451
|
|
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---------- While 1 = 1 (Learning SQL....) Click to get fast response of your post
|
|
|
|
|
SSC Eights!
      
Group: General Forum Members
Last Login: 2 days ago @ 5:19 AM
Points: 822,
Visits: 5,103
|
|
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
|
|
|
|
|
SSCrazy
      
Group: General Forum Members
Last Login: Tuesday, March 26, 2013 8:41 AM
Points: 2,562,
Visits: 3,451
|
|
|
|
|
|
SSC 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
|
|
|
|
|
Forum Newbie
      
Group: General Forum Members
Last Login: Monday, November 02, 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
|
|
|
|
|
SSC Journeyman
      
Group: General Forum Members
Last Login: Friday, March 22, 2013 3:37 PM
Points: 79,
Visits: 1,317
|
|
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
|
|
|
|
|
SSC Journeyman
      
Group: General Forum Members
Last Login: Friday, March 22, 2013 3:37 PM
Points: 79,
Visits: 1,317
|
|
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;
|
|
|
|