Here's the WRONG way to do what I want. I need a way to populate a variable from the output of a dynamic query.
declare @.TableName sysname
set @.TableName = 'Customers'
delcare @.Output bigint
declare @.SQL varchar(max)
set @.SQL = 'select top 1 RowID from ' + @.TableName
select @.Output =
EXEC (@.SQL)
create function udf_GetDatabaseFingerPrint(
@.DBID bigint
)
begin
returns bigint
as
declare @.dbname sysname
, @.iBig bigint
, @.tSQL varchar(2000)
select @.dbName = Name from Master.Dbo.Sysdatabases where DBID = @.DBID
set @.tSQL = 'select sum(Rows) from ' + @.dbName + '.dbo.sysindexes'
set @.iBig = exec(@.tSQL)
return @.iBig
end
Number one, you cannot do this in a T-SQL function. Functions will not allow such things. You can use sp_executeSQL. In this case, something like this (and old example I had
declare @.objectId int,
exec sp_executeSQL
N'select @.objectId = max(object_id) from sys.objects',
N'@.objectId int output', @.objectId=@.objectId output
select @.objectId
