Stored procedure question - SQL Server Forums

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)

Stored procedure question

Author

Topic

reddymade
Posting Yak Master

165 Posts

Posted - 2007-02-15 : 22:41:50

I am trying to create the following stored procedure, getting an error message saying i cannot use the use database inside the stored proc:

*************************************************

CREATE PROCEDURE [dbo].[USP_sMailSum]
WITH ENCRYPTION
AS
DECLARE @ErrorCode int

SET NOCOUNT ON
SELECT @ErrorCode = @@Error
IF @ErrorCode = 0
BEGIN
use Database1
exec USP_sSendMail

use Database2
exec USP_sSendMail

SELECT @ErrorCode = @@Error
END
SET NOCOUNT OFF
RETURN @ErrorCode
***************************************************

Thank you very much for the information. i have a sql agent job there i would likle to call this stored proc.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-15 : 23:12:45

[code]
~~use Database1~~
exec Database1.dbo.USP_sSendMail

~~use Database2~~
exec Database2.dbo.USP_sSendMail
[/code]

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-02-15 : 23:13:39

The message seems clear to me. You can't have a USE DATABASE command in a stored procedure.

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources