SQL Server 2005 - 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

Transact-SQL (2000)

SQL Server 2005

Author

Topic

dand_dd
Starting Member

6 Posts

Posted - 2011-07-21 : 02:33:47

Hi,

I have a problem with a SP. I need to verify if a column value is changed and to send an email alert.

To identify if the change is maded or not I have the following SP:


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[TestID] 
	-- Add the parameters for the stored procedure here
	@mVerifyRQ nvarchar(10),
	@mTestID nvarchar(10)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	
if @mTestID is null 
 Begin
 if exists (Select RQ From IDValues where RQ = @mVerifyRQ and TestID is null)
 SELECT RQ FROM IDValues where RQ = @mVerifyRQ and TestID is null
 exec dbo.SEND_MAIL_RQ_IE
 END	
else @mTestID = 1 or @mTestID = 0
 Begin
 if exists (Select RQ From IDValues where RQ = @mVerifyRQ and TestID = @mTestID)
 SELECT RQ FROM IDValues where RQ = @mVerifyRQ and TestID = @mTestID
 exec dbo.SEND_MAIL_RQ_IE
 END
END

I become an error:

Msg 102, Level 15, State 1, Procedure TestID, Line 29
Incorrect syntax near '@mTestID'.

Or are other posibility?

Thank you

Dan

[/code]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-07-21 : 03:02:51

[code]
else if @mTestID = 1 or @mTestID = 0
[/code]

KH
[spoiler]Time is always against us[/spoiler]

dand_dd
Starting Member

6 Posts

Posted - 2011-07-21 : 03:44:13

I do not understand why the following SP do not works (no error).
Update works but i don't send any emails (email SP works).


ALTER PROCEDURE [dbo].[UpdateStatusID]
	-- Add the parameters for the stored procedure here
	  @mRQ int,
	  @mID nchar(5)
     
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

   -- Insert statements for procedure here
	
	UPDATE    
		StatusID
	SET
		ID = @mID 
	WHERE     
		RQ=@mRQ


if @mID IS NULL
	Begin
	if not exists (Select RQ From StatusID where RQ = @mRQ and ID is null)
	exec dbo.SEND_MAIL_RQ_IE
	end
else if @mID = 1
	begin
	if not exists (Select RQ From StatusID where RQ = @mRQ and ID = 1)
	exec dbo.SEND_MAIL_RQ_IE
	end

END

Thank's

Dan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources