CODEWITHSUNDEEP
sql-serversql-server-2005cursor
black sensei
black sensei

Reputation: 6678

little problem with mssql cursor, insert in temp table within the cursor

i'm having a little trouble with my stored procedure. I've written a stored procedure where i'm using a cursor. Every thing works fine till the place where i'm inserting values from the cursor to the temp table. here is the error

Msg 156, Level 15, State 1, Procedure ors_DailyReportMessageStatus, Line 36 Incorrect syntax near the keyword 'where'.

and here is the code 

ALTER PROCEDURE [dbo].[ors_DailyReportMessageStatus]
-- Add the parameters for the stored procedure here
@startDate datetime, @endDate datetime
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
DECLARE @num int;
DECLARE @stat varchar(20);
DECLARE @statusCursor CURSOR 
SET @statusCursor = CURSOR FOR
select count(ms.status) as message, ms.status from message_status ms JOIN [message] m on  m.id=ms.message_id
where (m.ADDED_ON >= @startDate AND m.ADDED_ON < @endDate) GROUP BY ms.status

SET NOCOUNT ON;
if object_id('tempdb..#tempdailystatus') is not null
begin 
   drop table #tempdailystatus
end

CREATE TABLE #tempdailystatus(id int identity, total int , [status] varchar(20));
insert into #tempdailystatus ([status])
 select distinct([status]) from message_status;


 open @statusCursor 
 fetch next from @statusCursor into @num, @stat;
 while @@FETCH_STATUS = 0

    begin 
    -- this is where the error is
    insert into #tempdailystatus (total) values (@num) where id = (select id from   #tempdailystatus where [status] = @stat)
    -- this were just to see whether the cursor is ok. and it is
    --print  @stat
    --print  @num ;
    fetch next from @statusCursor into @num,@stat
 end

 close @statusCursor
 deallocate @statusCursor


 -- Insert statements for procedure here
 -- SELECT * from #tempdailystatus
 drop table #tempdailystatus
END

Am I possibly ignoring something? it seems like i'm forgetting something.

Thanks you for reading this and for giving suggestion .will appreciate it ^_^

Upvotes: 0

Views: 3835

Answers (1)

George Mastros
George Mastros

Reputation: 24498

try replacing:

insert into #tempdailystatus (total) values (@num) where id = (select id from   #tempdailystatus where [status] = @stat)

with:

If Exists(Select 1 From #tempdailystatus where [status] = @stat)
  Begin
    insert into #tempdailystatus (total) Values(@num)
  End

I made some assumptions about your logic, so you may need to adjust accordingly.

Upvotes: 1

Related Questions