T-SQL selecting into variable

• CTracy

  SSC Eights!

  Points: 903

  More actions

  October 18, 2005 at 8:49 am

  #91833

  I'm having the hardest time trying to take a select statement and dumping the result into a variable.  The only way I can get this to work is to dump the result into a temp table.  This is what I have working:

  DECLARE @SQLStatement varchar(255)

  create table #t1 (bytesperpage int, pagesperMB int)

  select @SQLStatement = 'select low FROM [' + @servername + '].master.dbo.spt_values WHERE number = 1 and type = ''E'''

  insert into #t1 (bytesperpage) EXEC(@SQLStatement)

   

  But this is what I'd like to use:

  DECLARE @SQLStatement varchar(255)

  DECLARE @myvar int

  select @SQLStatement = 'select low FROM [' + @servername + '].master.dbo.spt_values WHERE number = 1 and type = ''E'''

  set @myvar = EXEC(@SQLStatement)

  When I do this I get the error Incorrect syntax near the keyword 'EXEC'

  Any help would be much appreciated.

  Thanks!

• Ray M

  SSC-Insane

  Points: 21093

  More actions

  October 18, 2005 at 9:03 am

  #597957

  you cannot put a recordset into a parameter.

  try sp_execute_Sql

  select @SQLStatement = 'select @Myvar = low FROM [' + @servername + '].master.dbo.spt_values WHERE number = 1 and type = ''E'''

  exec sp_executeSql @SqlStatement, N'@Myvar int output', @Myvar OUTPUT

• noeld

  SSC Guru

  Points: 96590

  More actions

  October 18, 2005 at 9:03 am

  #597956

  The statement you are trying to run returns a recordset and... you are trying to save a bunch of records in an integer variable?

  
  * Noel

• CTracy

  SSC Eights!

  Points: 903

  More actions

  October 18, 2005 at 9:26 am

  #597971

  Thanks for your help.  I've never used sp_executeSql.  I no longer receive errors, but my variable comes back null.  This is what I have:

  DECLARE @SQLStatement2 NVARCHAR(255)

  DECLARE @Myvar int

  select @SQLStatement2 = 'select low FROM [' + @servername + '].master.dbo.spt_values WHERE number = 1 and type = ''E'''

  exec sp_executeSql @SQLStatement2, N'@Myvar int output', @Myvar OUTPUT

  select @Myvar as MYVAR

  It does display the recordset, which is just one row with one field...but won't put that into @Myvar.

  Thanks again!

   

• noeld

  SSC Guru

  Points: 96590

  More actions

  October 18, 2005 at 9:42 am

  #597979

  DECLARE @SQLStatement2 NVARCHAR(255)

  DECLARE @Myvar int

  select @SQLStatement2 = 'select @Myvar = low FROM [' + @servername + '].master.dbo.spt_values WHERE number = 1 and type = ''E'''

  exec sp_executeSql @SQLStatement2, N'@Myvar int output', @Myvar OUTPUT

  select @Myvar as MYVAR

   

  
  * Noel

• CTracy

  SSC Eights!

  Points: 903

  More actions

  October 18, 2005 at 9:52 am

  #597984

  That did it.  Thanks so much for your help!

  -Chris