April 4, 2006 at 9:56 pm
Is it possible to run "EXEC sp_executesql @sql, ........" and return the query results into multiple variables?
For example, I want to check sysjobs and sysjobschedules on all servers using a script executed from a single server with the help of linked servers. I am only looking to return @JobEnabled and @ScheduleEnabled, which my program will insert into a table.
Thanks, Dave
April 4, 2006 at 10:24 pm
Yes, it's possible using OUTPUT parameters.
See BOL how to use parameters with sp_executesql and how to declare and use OUTPUT paramenters.
_____________
Code for TallyGenerator
April 5, 2006 at 8:11 am
I was unaware you could specify an OUTPUT parameter with sp_executesql and found nothing in BOL tying the two together. I checked again and only found documentation regarding the use of OUTPUT parameters with the creation of stored procedures. What's the title of the BOL doc?
Thanks, Dave
April 5, 2006 at 9:15 am
It is not well documented in BOL under sp_executesql. Here is a snippet of code I used in conjunction with a #TempTable [#Variables] to send the OUTPUT into a @Variable [@Code]. The variable @OutPut is declared within the string being executed:
@Code varchar(6000),
@Counter integer,
@sql nvarchar(1000)
SELECT @Counter = 2
WHILE @Counter <= 6
BEGIN
SET @sql = N'SELECT @OutPut = Code' + CONVERT( varchar, @Counter) + ' FROM #Variables'
EXEC sp_executesql @sql, N'@OutPut varchar(1000) OUTPUT', @OutPut = @Code OUTPUT
-- SELECT @Counter AS '@Counter', @Code AS '@Code' -- TESTING
SELECT @Counter = @Counter + 1
END
I wasn't born stupid - I had to study.
April 5, 2006 at 9:31 am
That should do the trick. Thanks
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply