Problems with OpenQuery and variables

• Tobias Boehler

  Old Hand

  Points: 375

  More actions

  October 25, 2005 at 8:20 am

  #188139

  Hello everybody,

  first problem i had was placing a variable in an OpenQuery, becaus normally it doesn't work. (I need it because of the date-range) I solved that problem using the following SQL-query:

  ------------------------------------------------------

  declare @cubeDateBegin nvarchar(10)

  declare @cubeDateEnd nvarchar(10)

  declare @mdx as varchar(1000)

  declare @select as varchar(1500)

  declare @fltSubjEinsch as float

  set @cubeDateBegin = '2005-01-04'

  set @cubeDateEnd = '2005-01-04'

  set @mdx = 'WITH

  MEMBER [Datum].[Range] AS

  ''''sum([Datum].[' + @cubeDateBegin + ']:[Datum].[' + @cubeDateEnd + '])''''

  SELECT

  Measures.members ON 0,

  non empty [intervall].[intervall].members on 1,

  non empty [stationskennung].[stationskennung].members on 2

  FROM IBIcube_LEP_temp_SB

  WHERE ( [Stations_Gruppe].[M Chirurgie], [Datum].[Range])

  '

  select @select = 'select

  fltSubjEinsch = cast((SUM (cast ("[Measures].[LSB_Value]" as decimal(9,2)))) /

  (SUM (cast ("[Measures].[LSB_Anzahl_Werte]" as decimal(9,2))))as decimal(9,2))

  from openquery

  (DWCARE, ''' + @mdx + ''')'

  exec(@select)

  ------------------------------------------------------

  The exec-function returns in this case one value "3.45" (later of course more).

  How can i put this value(s) in different variables?

  For example in "@fltSubjEinsch" . Is this possible?

  Thanks for any answer.

  Tobi

• Ian Yates

  SSCoach

  Points: 19738

  More actions

  October 25, 2005 at 9:03 am

  #599836

  Look at this simple example of dynamic SQL...

  create table #x(myCol int)

  declare @sql varchar(8000)
  set @sql = 'select 1 as mycol union select 2'

  insert into #x(myCol)
  exec(@sql)

  select * from #x

  drop table #x
  

  You can do an

  INSERT INTO ...

  EXEC(...)

  statement to capture the output. It has to be a temp table - doesn't work with table variables.

  Hope that helps

• Tobias Boehler

  Old Hand

  Points: 375

  More actions

  October 25, 2005 at 9:15 am

  #599842

  So easy.

  Great, thank you.

  Wish you a nice evening.

  Tobi

• Moyete

  SSC Veteran

  Points: 201

  More actions

  November 1, 2005 at 12:53 pm

  #601663

  You can use this script too.

  declare @vcCmd nvarchar(200), @inResult int

  set @vcCmd = N'select @inOutPar = Count(*) from master.dbo.sysdatabases'

  execute sp_executesql @vcCmd, N'@inOutPar int output', @inResult output

  select @inResult

  Moy

• Ian Yates

  SSCoach

  Points: 19738

  More actions

  November 1, 2005 at 9:10 pm

  #601728

  Good idea Moyete - might be more efficient