Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

EXECute SQL Commands over entire sets of parameters!

By RBarry Young,


The OVER_SET stored procedure allows you to repeatedly execute a string of text as a SQL command, using a row set returned from a SELECT statement to replace or "substitute" tags in your original command with column values from the rowset. In this sense, OVER_SET resembles the undocumented system procedures sp_MSForEachTable and sp_MSForEachDB, which allow you to execute a command for every database in your server, or every table in a database, however, it has features and abilites far beyond them. All this, plus it does it without any Curors or WHILE loops, anywhere.

Syntax:

Invoke the OVER_SET procedure with the following syntax:

EXECECUTE OVER_SET 
    @command AS NVARCHAR(MAX),   -- Your SQL command template
@from AS NVARCHAR(MAX), -- a FROM..WHERE clause

@subs1 AS NVARCHAR(MAX) = N'', -- Substitution parameters,
@subs2 AS NVARCHAR(MAX) = N'', -- (see below..)
@subs3 AS NVARCHAR(MAX) = N'', --
@print AS BIT = 1, -- controls PRINT of the SQL text
@catch AS VARCHAR(12) = 'continue',
-- TRY/CATCH options (see below)
@use_db AS NVARCHAR(255) = N'', -- USE [<db>] name @quote AS NVARCHAR(8) = N'' -- alternate quote character

Parameters:

@command The text of the SQL commands to be executed.
@from The text of a SQL FROM clause and a WHERE clause. This just gets appended to a "SELECT *" and then executed to get the row set that @command will be executed over. So @command will be executed as many times as there are rows in this query.
[@subs1,2,3] These are the substitution parameters that control how the returned rows will modify the the @command text. They're formatted as '<find>=<column>'. The <find> is whatever text comes before the first equals sign, and the <column> is whatever text comes after it. OVER_SET will search for the <find> text and replace it with the current row's column value from the column of the same name. Up to three substitution parameters can be used.
[@print] Specify 0 or 1 to control whether the resulting SQL text for each row is PRINTed before it is EXECuted. A setting of 1 (the default) causes it to print.
[@catch]

Controls the use of TRY..CATCH blocks around the resulting SQL text for each row returned, and the behavior if the CATCH block catches an error.

A setting of 'continue' will cause a caught error to be printed, and then it will attempt to continue with the next row. (this is the default)

'ignore' will cause all caught errors to be ignored.

'fail' will cause the error to be re-raised, aborting the execution.

'none', will result in no TRY.CATCH blocks being added. It's probably faster, ..?

[@use_db] If this parameter is specified, then a single USE [<db>] command will be executed just beofre all of the result rows are executed. Note that this is executed [i]after[/i] the rowset is generated with the @from parameter, so it will not affect it.
[@quote] This parameter allows you to specify a second, alternate, quotation character. The purpose of this is to reduce the amount of quote-doubling you need for some commands (especially nested and dyanimic ones). If specified, this charcacter is replaced in @command before the column substitutions occur. Note also that this option does not affect the @from text.

Example Usages:

1) INSERT..EXECute:

Demonstrates capturing the SELECT output from an EXECUTE OVER_SET that searches every database in the SQL Server Instance for routines with the word "cursor" in them.

CREATE TABLE #temp (DB sysname, [Schema] sysname, Routine sysname);
INSERT INTO #temp
EXECUTE OVER_SET '
SELECT ROUTINE_CATALOG, ROUTINE_SCHEMA, ROUTINE_NAME
FROM [{db}].INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION like "%cursor%" ',
@from = 'sys.sysdatabases WHERE dbid > 4',
@subs1 = '{db}=name',
@quote = '"'
;
SELECT * from #temp;
DROP TABLE #temp;

The @from argument returns the list of non-system databases in the server, and the @susbs1 argument "{db}=name" tells it to replace every instance of "{db}" in the command strings with the value of the [name] column (from sys.sysdatabases). Note also the @quote argument's value (") allows us to use a single quotation mark in the quoted command text instead of having to use double apostrophes (ie, ' "%cursor%" ', instead of ' ' '%cursor%' ' ').

2) Nested Execution:

Demonstrates, nesting OVER_SET execution to operate against the combination of to different sets, the second dependent on the first. Specifically, it searches every non-system database for every user that is a windows user or group, and then attempts to map them back to a server Login of the same name.

EXECUTE OVER_SET '
EXECUTE OVER_SET "
ALTER USER [{name}] WITH LOGIN = [{name}];
PRINT `USER {name} has been mapped to its Login.`;",
@from = "sys.database_principals
WHERE ( type_desc = ""WINDOWS_GROUP"" OR type_desc =""WINDOWS_USER"" )
AND name NOT like ""%dbo%"" AND name NOT LIKE ""%#%"" ",
@use_db = "{db}",
@subs1 = "{name}=name",
@catch = "continue",
@print = 1,
@quote = "`";
',
@from = 'sys.sysdatabases
WHERE dbid > 4',
@subs1 = '{db}=name',
@catch = 'continue',
@print = 0,
@quote = '"' ;

The outer OVER_SET uses the @from argument to return the list of all databases which the @subs1 argument "{db}=name", uses to modify the inner OVER_SET command's @use_db argument, causing the inner execution to USE [{db}] to each database in turn. The inner execution's @from argument returns the list of database users that are WINDOWS_* user or group, and the @subs1 ({name}=name) cause the "{name}" token to be replaced with the value of the [name] column from the database_principals table.

Note that two different @quote characters are used ( ("), then (`) ), removing the need for double or even quadruple apostrophes in the inner command text. (also note, that the @from argument text does not benefit from this, and can only use the outer command quote (") becuase it is part of the outer command
text argument.

-- RBarryYoung

Total article views: 2738 | Views in the last 30 days: 5
 
Related Articles
FORUM

passing argument to command prompt through Execute Process Task

passing argument to command prompt through Execute Process Task

BLOG

SQL Server – sp_help without arguments.

sp_help returns information about database objects and types. @object_name argument is optional for...

FORUM

SSIS Arguments Do not Pass to Executable

Package Calls execute process task to execute bcp. Arguments are enclosed in quotes and sent as one ...

FORUM

database diagram print on one page

database diagram print

FORUM

SSIS error while executing in command line

SSIS error while executing in command line

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones