July 15, 2013 at 1:21 pm
I am creating a stored procedure to send an email when the status field in a table changes from Pending to Approved and eventid is the unique id for the events.
I am using update command to change the status and capturing oldstatus and newstatus.
But when I run the procedure, It is not asking for the parameters and when I execute it I am getting no result as it is not asking for any parameter
Please let me know what I am doing wrong.
Thanks,
Blyzzard
Below is the code:
declare @EventId int
declare @EventName nvarchar(500)
declare @EventDate datetime
declare @sstatus nvarchar(50)
declare @oldstatus nvarchar(50)
declare @newstatus nvarchar(50)
select @oldstatus=sstatus from tblEvents where EventID=@Eventid
UPDATE [tblEvents]
SET [EventName] = @EventName, [EventDate] = @EventDate,
[sstatus]= @sstatus, [Updateuser]=@Updateuser,UpdateDate=GetDate()
WHERE [EventID] = @Eventid
select @newstatus=sstatus from tblEvents where EventID=@eventid
--if oldstatus doesnot matches with newstatus then execute rest
if @oldstatus <> @newstatus
begin
*****rest of the code********
July 15, 2013 at 1:24 pm
That's not the code for a Stored Procedure. If you have defaults assigned to the parameters, then the SP won't forrce you to input any parameter.
July 15, 2013 at 1:31 pm
How do I do this? I want to update the status to newstatus and comapre oldstatus and newstatus for a particular eventid and keep rest of the values same as stored.
July 15, 2013 at 2:00 pm
Could you post the complete definition of the stored procedure?
try to use the IFCode located in the left when you write the post [ code="sql"][/code]
July 15, 2013 at 2:10 pm
Basically tblevents has a field name sstatus and I am capturing oldstatus and then in the update statement if the status changes to newstatus and then comparing them
USE [databasename]
GO
/****** Object: StoredProcedure [dbo].[sp_Mail_UpdateEvents] Script Date: 07/15/2013 14:36:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo].[sp_MailUpdateEvents]
AS
SET nocount ON
BEGIN TRY
declare @EventId int
declare @EventName nvarchar(500)
declare @EventDate datetime
declare @sstatus nvarchar(50)
declare @oldstatus nvarchar(50)
declare @newstatus nvarchar(50)
select @oldstatus=sstatus from tblEvents where EventID=@Eventid
--create TABLE #StatusChanges ( Status1 Varchar(20),Status2 VARCHAR(20));
UPDATE [tblEvents]
SET [EventName] = @EventName, [EventDate] = @EventDate,
[sstatus]= @sstatus, [Updateuser]=@Updateuser,UpdateDate=GetDate()
WHERE [EventID] = @Eventid
select @newstatus=sstatus from tblEvents where EventID=@eventid
--if oldstatus doesnot matches with newstatus then execute rest
if @oldstatus <> @newstatus
July 15, 2013 at 2:32 pm
The problem is that you don't have any parameters declared. All you have are variables.
Without knowing more about your SP, this might help but you might want to remove parameters that should actually be variables (like @oldstatus).
Create PROCEDURE [dbo].[sp_MailUpdateEvents]
(
@EventId int,
@EventName nvarchar(500),
@EventDate datetime,
@sstatus nvarchar(50)
)
AS
SET nocount ON
BEGIN TRY
declare @oldstatus nvarchar(50),
@newstatus nvarchar(50)
select @oldstatus=sstatus from tblEvents where EventID=@Eventid
--create TABLE #StatusChanges ( Status1 Varchar(20),Status2 VARCHAR(20));
UPDATE [tblEvents]
SET [EventName] = @EventName, [EventDate] = @EventDate,
[sstatus]= @sstatus, [Updateuser]=@Updateuser,UpdateDate=GetDate()
WHERE [EventID] = @Eventid
select @newstatus=sstatus from tblEvents where EventID=@eventid
--if oldstatus doesnot matches with newstatus then execute rest
if @oldstatus <> @newstatus
July 15, 2013 at 2:37 pm
As I read this a few times I keep asking myself...is this person trying to implement a trigger? You said you use this when a certain value changes, how do you call this procedure? Why do you not pass it any parameters? Would this make sense to have in a trigger instead of a proc?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 15, 2013 at 8:38 pm
Thanks a lot .. It worked
July 15, 2013 at 9:08 pm
Sean, do you mean same code but instead of calling sp just fore trigger when the value changes?
Please bear with me as I am a newbie.
Thanks
July 16, 2013 at 6:20 am
Your stored procedure was executing as you didn't specify any parameters in that sp...
Please refer Luis post...
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 16, 2013 at 7:03 am
amar_kaur16 (7/15/2013)
Sean, do you mean same code but instead of calling sp just fore trigger when the value changes?Please bear with me as I am a newbie.
Thanks
This will explain what a trigger is and how they operate. http://msdn.microsoft.com/en-us/library/ms189799.aspx
Just make sure that if you do use a trigger that it can handle multiple row updates.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply