Can sp_executesql save results to a variable? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Can sp_executesql save results to a variable?

Author

Topic

DBADave
Constraint Violating Yak Guru

366 Posts

Posted - 2006-04-04 : 23:51:37

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-05 : 00:11:35

sp_executesql allows you to pass variable in / out.

declare
    @JobEnabled       int,
    @ScheduledEnabled int
exec sp_executesql @SQL, N'@JobEnabled int OUTPUT, @ScheduledEnabled int OUTPUT', 
                   @JobEnabled OUTPUT, @ScheduledEnabled OUTPUT

Refer to BOL for more details

KH

DBADave
Constraint Violating Yak Guru

366 Posts

Posted - 2006-04-05 : 09:56:26

Thanks. I was not aware the output parameter could be used with sp_executesql. That's good news.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources