set and exec 2

[mimi2]

Hi,

what i would like to do is:
set @maxfound = (select max(date) from table1)
then work with @maxfound

but since i am crossing linkservers:
SET @query_max = "select max(date) from " + @link_server +".database1.dbo.table1"
EXEC (@query_max)

how can i assign the result to a variable like @maxfound.

Thanks

 

[cheyney]

you'll have to read the result into a temp table and then select from the temp table:


create table #tempMaxDate (MaxDate as datetime)

SET @query_max = "select max(date) from " + @link_server +".database1.dbo.table1"

INSERT INTO #tempMaxDate
exec(@query_max)

SELECT @maxfound = MaxDate
FROM #tempMaxDate

DROP TABLE #tempMaxDate

Print @maxfound


Cheyney

 

[JamesLean]

DECLARE @sql nvarchar(200),
  @max datetime

SET @sql = N'SELECT @m = MAX(date) FROM ' + @linkserver + '.db1.dbo.t1'

EXEC sp_executesql @sql, N'@m datetime OUTPUT', @max OUTPUT

SELECT @max AS MaxDate

--James

 

[mimi2]

thanks. sp_executesql this is what i am looking for.

 

[mimi2]

Thanks again.
I get errors with the above code.
Cannot use the OUTPUT option when passing a constant to a stored procedure.
what's wrong ?

 

[HyperRon]

Use this:

Syntax was wrong in the post above for create table
create table #tempMaxDate (MaxDate datetime)

SET @query_max = "select max(date) from " + @link_server +".database1.dbo.table1"

INSERT INTO #tempMaxDate
exec(@query_max)

SELECT @maxfound = MaxDate
FROM #tempMaxDate

DROP TABLE #tempMaxDate

Print @maxfound