SQL Server Stored Procedure

[olibh]

I want to create a stored procedure that will select records meeting the required criteria, then loop though and run another stored procedure on each record individually before proceeding to the next - is this possible ?

Any help would be greatly appreciated

e.g.

CREATE PROCEDURE sp_test

as

DECLARE @sql nvarchar(4000)

SET @sql = "select * from dbo.communications where Received = 'Initiated'"

While Not @sql.EOF

exec dbo.sp_genxml
Rs.MoveNext


GO

 

[AWithers]

You need to loop at sql loops or cursors...see Books On Line

Andy

 

[JamesLean]

You need a cursor something like this:

DECLARE @col varchar(50)
DECLARE exec_cur CURSOR FOR
  SELECT col1 FROM tablename WHERE col2 = 'xxx'

OPEN exec_cur

FETCH NEXT FROM exec_cur INTO @col

WHILE @@FETCH_STATUS <> -1
BEGIN
  EXEC spname @col
  FETCH NEXT FROM exec_cur INTO @col
END

You just need to declare your variables with the right data type, plus you can add more if you need to send more parameters to the SP.

 

[olibh]

I want to out the contents of a field in a record to a file and then update the contents of another field called STATUS to "Exported"