Dynamic SQL and Database Context Question
-
As a part of a bigger script I am writing which iterates through each database, I want to extract information from the sysfiles table and store for later use. For the life of me I cannot figure out how to set the current context, perform a select and return the value I want.
As a test I am using the following. I set the context initially to the master db which as it is in the larger script. The "exec sp_execute" statement does return the correct value but I want to store that value in a variable that I can then use later... @retVal is NULL.
use master
go
declare @cmd nvarchar(50)
declare @retType nvarchar(50)
declare @userdb nvarchar(50)
declare @retVal nvarchar(20)
set @userdb = 'Northwind'
set @retType = N'@filename nvarchar(50) OUTPUT'
set @cmd = N'use ' + @userdb + ' select file_name(1)'
exec sp_executesql @cmd, @retType, @retVal OUTPUT
select @retVal
This one has me stumped. Appreciate any help...
Mark
-
declare @cmd nvarchar(50)
declare @retType nvarchar(50)
declare @userdb nvarchar(50)
declare @retVal nvarchar(20)
set @userdb = 'Northwind'
set @retType = N'@filename nvarchar(50) OUTPUT'
set @cmd = N'use ' + @userdb + ' select @retVal = file_name(1)'
exec sp_executesql @cmd, N'@retVal nvarchar(20) OUTPUT', @retVal = @retVal OUTPUT
select @retVal
-
It is so nice to have all these people around that are smarter than I am... Took what you had given and modified slightly just to clear things in my mind. Thank you so much!!
use master
go
declare @cmd nvarchar(50)
declare @retType nvarchar(50)
declare @userdb nvarchar(50)
declare @retVal nvarchar(20)
set @userdb = 'Northwind'
set @retType = N'@filename nvarchar(50) OUTPUT'
set @cmd = N'use ' + @userdb + ' select @filename = file_name(1)'
exec sp_executesql @cmd, @retType, @filename = @retVal OUTPUT
select @retVal
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply