Store Exec result in a variable

• Taggs

  Ten Centuries

  Points: 1184

  More actions

  March 14, 2011 at 11:47 am

  #73017

  Hi,

  I am wondering if anyone can help me please. As part of a bigger picture I am trying to store a Variable (the SQL server version) so I can choose which script to run against the server. Here is the extract of code to get the server version (which works) but I want to store the result in a variable "@results".

  Any help would be much appriciated.

  Thanks

  Taggs

  declare @cmd nvarchar(1000),

  @SQLCommand nvarchar(1000),

  @results nvarchar(100),

  @ServerName sysname

  Select @ServerName = 'sql-001'

  Select @SQLCommand = '''SELECT SERVERPROPERTY(''''ProductVersion'''') AS [Product Version]'')'

  print @SQLcommand

  set @cmd = N'SELECT * FROM openquery ([' + @ServerName + '],' + @SQLCommand

  exec sp_executesql @cmd

• spaghettidba

  SSC Guru

  Points: 105712

  More actions

  March 14, 2011 at 12:01 pm

  #1298503

  This should do:

  DECLARE @result nvarchar(4000)

  DECLARE @tResults TABLE (

  ProductVersion nvarchar(128)

  )

  INSERT @tResults

  EXEC('SELECT CAST(SERVERPROPERTY(''ProductVersion'') AS nvarchar(128)) AS product_version')

  SELECT @result = ProductVersion

  FROM @tResults

  SELECT @result

  Hope this helps,

  Gianluca

  -- Gianluca Sartori

• Taggs

  Ten Centuries

  Points: 1184

  More actions

  March 15, 2011 at 5:53 am

  #1298772

  Hi Gianluca,

  thanks for the post it has pointed me in the right direction.:-) I have amended the code slightly to fit in with the rest of my script.

  Many Thanks