SQL Server Performance

Can sp_executesql return results into variables?

Discussion in 'General DBA Questions' started by DBADave, Apr 4, 2006.

  1. DBADave New Member

    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
  2. smy New Member

  3. FrankKalis Moderator

    USE Pubs
    DECLARE @stmt nvarchar(4000)
    DECLARE @rowcount bigint
    DECLARE @table nvarchar(255)
    SET @table = 'authors'
    SELECT @stmt = 'SELECT @count = COUNT(*) FROM ' + @table
    EXEC sp_executesql @stmt, N' @count bigint output', @rowcount OUTPUT
    IF @rowcount > 0
    SELECT @rowcount AS Anzahl

    Frank Kalis
    Microsoft SQL Server MVP
    Heute schon gebloggt?http://www.insidesql.de/blogs
    Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)
  4. DBADave New Member

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

  5. Adriaan New Member

    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))
    <... processing ...>
    SELECT 'value1', 'value2'

    ... 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'
  6. DBADave New Member

    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
    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
  7. Adriaan New Member

    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.
  8. DBADave New Member

    Thanks again
  9. gk_sezhian New Member

    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

Share This Page