Seems like I should know this already

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Seems like I should know this already

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2003-07-18 : 09:24:08

I've got a stored proc that'll queue an email message.

exec dbo.MyProc @UserID=1234

I've got to execute that proc against some history of users that are pretty easy to query...

SELECT UserID FROM Users WHERE condition

I'm drawing a blank on how to use the select rowset to execute the stored procedure once for each userid.

Arg !

Sam

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-18 : 09:48:24

No..No...you can't make me

Oh the humanity...

How about a cursor?

[Nigel riding in on white horse]
HELP
[/Nigel riding in on white horse]

Brett

8-)

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-07-18 : 09:48:40

(sigh!) Sam, you gonna need a cursor for this one....or at best a while loop like this:

WHILE (@LastUserID IS NOT NULL)
BEGIN
SET @UserID = MIN(UserID) FROM Users
WHERE blah blah AND UserID > @LastUserID

IF(@UserID IS NOT NULL)
BEGIN
EXEC dbo.MyProc @UserID
END

SET @LastUserID = @UserID
END

Ugly!

Owais

SamC
White Water Yakist

3467 Posts

Posted - 2003-07-18 : 10:04:39

I hope you all realize I've never even tested a cursor script much less used one for production purposes.

The While loop above may be tough to do without complicating the query to eliminate those that have been processed.

Would someone spell out a cursor script for me here?

Sam

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-18 : 10:27:43



DECLARE USER_MSG CURSOR READ_ONLY FOR 
	select distinct user_id from user_work_profile(NoLock) 
 		Where Work_Type = 'A'
--		Where User_Id = 'x057117'

OPEN USER_MSG

FETCH NEXT FROM USER_MSG
	INTO	@User_Id

WHILE @@FETCH_STATUS = 0

	BEGIN
		Select @Command_String = 'Exec master..xp_cmdshell "NET SEND ' + @User_Id + ' TaxReconDB Quarter Load Beginning", no_output '
		Select @Command_String
		Exec (@Command_String)

		FETCH NEXT FROM USER_MSG
			INTO	@User_Id
	END

CLOSE USER_MSG
DEALLOCATE USER_MSG

Brett

8-)

SamC
White Water Yakist

3467 Posts

Posted - 2003-07-18 : 10:33:52

Thanks Brett.

Sam

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-18 : 10:50:55

For you Sam...anything (well at least to the best of my abilities...which is not saying much)

Brett

8-)

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-07-18 : 13:52:09

BTW... from BOL:

Transact-SQL cursors are extremely efficient when contained in stored
procedures and triggers. This is because everything is compiled into one
execution plan on the server and there is no network traffic associated
with fetching rows.

I think that this 'No Any Cursor!!' fuss is exaggerated too much...
Somewhat like fashion...

- Vit

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-18 : 14:55:50

Cursors have their place...only when all other options are exhausted....

Brett

8-)

Edited by - x002548 on 07/18/2003 14:56:16

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-18 : 15:06:16

And in this case I guess I didn't explore all of the options...

I think with this method you could really limit your exposure to cursor a whole bunch...didn't think this would work...but it does.


USE Northwind

CREATE TABLE myTableUsers (myUserID char(8))
GO

INSERT INTO myTableUsers (myUserID)
SELECT 'x002548' UNION ALL
SELECT 'x002548'
GO

DECLARE @x varchar(8000)

SELECT @x = ISNULL(@x,'') + 'Exec master..xp_cmdshell "NET SEND ' + myUserId + ' TEST", no_output '+CHAR(13)+CHAR(10)
FROM myTableUsers

SELECT @x

EXEC(@x)
GO

DROP TABLE myTableUsers 
GO

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources