if exists (select name from sysobjects where name = "sp_ForEach" and type =
"P") 
drop proc sp_ForEach
go

/*--------------------------------------------------------------------------
------------------------------------
Based on code created by Sumit Dhingra

This proc takes a string argument of fields delimited by the 3rd argument
character,
or a comma if the 3rd argument is missing, and applies each field to the
second
argument which may be either a SQL statement or, more usually, a stored
procedure.

Example: sp_ForEach "table1,table2,table3","sp_spaceused"

----------------------------------------------------------------------------
----------------------------------*/

CREATE PROCEDURE sp_ForEach
@ParamStr Varchar(1000), -- delimited string
@cmd Varchar(500), -- command to issue for each
substring
@delim Char(1) = "," -- default delimiter
AS


SET NOCOUNT ON

/* Declare Variables */
DECLARE @Field Varchar(100) 
DECLARE @ctr Int
DECLARE @posctr Int
DECLARE @commaPos Int
DECLARE @paramLen Int
DECLARE @findposinparamstr Varchar(200)


/* Initialize the Variables */
SET @paramLen = len(@ParamStr)
SET @findposinparamstr = @ParamStr
SET @posctr = 1
SET @ctr = 0


/* Loop through the Comma Delimited String and pick up the individual values
(Fields) */
WHILE @posctr < @paramLen
BEGIN
SET @commaPos = patindex('%'+@delim+'%',@findposinparamstr)

IF (@commaPos <> 0)
BEGIN
SET @Field = ltrim(substring(@ParamStr,@posctr,@commaPos-1))
exec (@cmd+" "+@Field)
END
ELSE /* @commaPos <> 0 */
Break

SET @posctr = @posctr + @commaPos -- Set this
to starting position for next element.
SET @findposinparamstr = substring(@findposinparamstr,(@commapos +
1),(len(@findposinparamstr)-@commapos))
SET @ctr = @ctr + 1
END /* End of While.... */


/* Get the last item from the string */
SET @Field = ltrim(@findposinparamstr)
exec (@cmd+" "+@Field)

SET NOCOUNT OFF
Return(0)
GO

/*--------------------------------------------------------------------------
------------------------------------
The following is a somewhat contrived example showing how this could be used
using a non-comma delimiter:

if exists (select name from sysobjects where name =
"sp_ExpensesForNameAndDeptByDate" and type = "P") 
drop proc sp_ExpensesForNameAndDeptByDate
go

create proc sp_ExpensesForNameAndDeptByDate @parms varchar(500)
as
/* 
This proc retrieves expense data for a name in a specific department for
a date range.  It enables the user to specify all arguments as a single
string delimited by embedded commas.  Multiple queries may be issued with
one call by separating separate argument strings by a semi-colon.

Example (whitespace is provided for readability only):
sp_ExpensesForNameAndDeptByDate 
"'Joe','466','20020101','20020131';
'Joe','591','20020201','20020228';
             'Sam','972','20020101','20020131'"
*//*

exec sp_ForEach @parms,"sp_ExpensesForNameAndDeptByDate2",";"


----------------------------------------------------------------------------
----------------------------------

if exists (select name from sysobjects where name =
"sp_ExpensesForNameAndDeptByDate2" and type = "P") 
drop proc sp_ExpensesForNameAndDeptByDate2
go

create proc sp_ExpensesForNameAndDeptByDate2 @name varchar(20), @dept
varchar(20), @d1 char(8), @d2 char(8)
as
declare @statement nvarchar(1000)
select @statement=
"select d.name, d.mgr, p.name, p.date, p.expenses"+
"from Departments d "+
"join Personnel p on d.name = p.dept "+
"where p.date between @lowdate and @highdate "+
"and p.name = @name "+
"and d.dept = @dept"

exec sp_executesql @statement,
N'@lowdate char(8),@highdate char(8),@name varchar(20),@dept
varchar(20)',
@d1,@d2,@name,@dept
*/