Can sp_executesql return results into variables?

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

try refer to this link..might help you answer your doubt:http://www.sommarskog.se/dynamic_sql.html

All I need to do is specify OUTPUT. I like that.

Thanks

Original posting suggests another possible setup, but it depends on what you're calling ...

For instance, if you have a stored procedure like this ...

CREATE PROCEDURE dbo.test (@param1 VARCHAR(10))
AS
<... processing ...>
SELECT 'value1', 'value2'
GO

... then you can insert the results into two columns in an existing table, by calling the sproc after an INSERT INTO clause ...

INSERT INTO TargetTbl (Col1, Col2)
EXEC dbo.test 'parameter'

Here's what I'm trying to do.

(1) Loop through master..sysservers for all linked servers (assumes a linked server per supported database server)
(2) Connect to each server and retrieve all database names, except tempdb
(3) See if a corresponding backup job exists per database (our job naming convention is DB Name + ' Backup/DBCC'
(4) If job exists,
set JobExists = 1, JobEnabled = enabled column from sysjobs, ScheduleEnabled = enabled from sysjobschedules and
insert the record into a table, including server and database name
Else
set JobExists = 0, JobEnabled = 0, ScheduleEnabled = 0 and insert the record for the server/database
(5) Produce a report for all servers listing
(I) Databases with no backup jobs
(II) Databases with backup jobs, but job not enabled
(III)Databases with backup jobs and job enabled, but schedule not enabled

If using the stored proc approach where would the stored procedure fit into the process? I want to avoid creating a stored proc on all servers. The tricky part was Step 4, however using OUTPUT variables should resolve the problem.

Thanks, Dave

You can execute any T-SQL command as the source for an INSERT, as long as it returns data in a number of columns. So of course you can use sp_ExecuteSQL for this as well ...<br /><br />INSERT INTO MyTable (&lt;columnlist&gt<img src='/community/emoticons/emotion-5.gif' alt='' /><br />EXEC dbo.sp_ExecuteSQL N'&lt;your_statement&gt;'<br /><br />It may save you a few keystrokes in your script.<br /><br />But besides that, using the OUTPUT keyword with sp_ExecuteSQL is a really great feature that not everyone seems to know about.

Thanks again

Thanks Man Long Live It worked!...Gk.sezhian[]
For Multiple parameters
DECLARE @stmt nvarchar(4000)
DECLARE @rowcount varchar(10)
DECLARE @idop int
DECLARE @table nvarchar(255)
SET @table = 'TABLENAME'
SELECT @stmt = 'SELECT @val=name ,@id=id FROM ' + @table
EXEC sp_executesql @stmt, N' @val varchar(10) output,@id int output ', @valop OUTPUT,@idop OUTPUT

SELECT @valop AS valouptput
SELECT @idop AS idoutput