Unable to update the record in the database

[venuchalla]

Hello,
I have created a table
CREATE TABLE [dbo].[Issues](
[CreatedByAlias] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Title] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Description] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ExpireAt] [datetime] NOT NULL,
[CreatedAt] [datetime] NOT NULL,
[Status] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CompleteTime] [int] NOT NULL,
[MailSent] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Issues_MailSent] DEFAULT (N'No'),
[NotifyAlias] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
)
----------------------------------------
Now I created a stored procedure to add data from the form
to the database

CREATE PROCEDURE [dbo].[CalculateIssueExpiryTime]
-- Add the parameters for the stored procedure here
@createdbyalias varchar(20),
@notifyalias varchar(20),
@title ntext,
@description ntext,
@completetime int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here

Declare @expireat datetime,
@createdat datetime,
@status nchar(10) ,
@mailsent nchar(10)

Set @createdat = GetDate();
Set @expireat = DateAdd(minute, @completetime * 60, GetDate())
Set @status = 'Open'
Set @mailsent = 'No'


Insert into Issues values
(@createdbyalias,@title,@description,@expireat,@createdat,@status,@completetime,@mailSent,@notifyalias)

END

------------------------------------------------

Now I want to update the inserted record in the table like this...

CREATE PROCEDURE [dbo].[UpdateIssues]
-- Add the parameters for the stored procedure here
@createdbyalias varchar(20),
@notifyalias varchar(20),
@title varchar(max),
@description varchar(max),
@completetime int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here

Declare @expireat datetime,
@createdat datetime,
@status nchar(10) ,
@mailsent nchar(10)



declare cur CURSOR for
Select CreatedByAlias,Title,Description,CreatedAt,ExpireAt,Status,CompleteTime,MailSent,NotifyAlias
From Issues
Where Status = 'Open' and
CreatedAt < getdate () and
MailSent = 'No' and
ExpireAt < @expireat

open cur
fetch next from cur into @CreatedByAlias,@Title,@Description,@CreatedAt,@ExpireAt,@Status,@CompleteTime,@MailSent,@NotifyAlias

while @@FETCH_STATUS = 0
BEGIN

Set @createdat = getdate()
Set @expireat = DateAdd(minute, @completetime * 60, GetDate())
Set @status = 'Open'
Set @mailsent = 'No'


Update Issues Set CreatedByAlias =@createdbyalias,
Title = @title,
Description = @description,
CompleteTime = @completetime,
NotifyAlias = @notifyalias,
ExpireAt = @expireat,
CreatedAt = @createdat
Where Status = 'Open' and
ExpireAt < getdate () and
MailSent = 'No'

fetch next from cur into @CreatedByAlias,@Title,@Description,@CreatedAt,@ExpireAt,@Status,@CompleteTime,@MailSent,@NotifyAlias

END

close cur
deallocate cur

END
--------------------------------------------------

MY problem is the fields in the record are not updated, Is there anything wrong in the update stored procedure.

Can anyone of you please correct the Update stored procedure so that I can update the record in the database.

Thanks in advance

 

[SQLSister]

Well your where clause is wrong. I think you mean to use the variables in it, otherwise what's the point in setting them?

Also, never ever use a cursor for an update. it will work yes, but it is slow and inefficient. Updates should be done in a set based fashion. Look in BOL to see the syntax for an update that joins to other tables to select the records to update.

In fact you should forget that you even know the syntax for a cursor as they are a very poor programming practice in SQL Server and should be reserved for the very few times they are absolutely needed.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief