Trying to insert data from cursor to a table

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)

Trying to insert data from cursor to a table

Author

Topic

reddymade
Posting Yak Master

165 Posts

Posted - 2006-02-03 : 11:44:36

When executed the folloowing stored proc. it seems to be going into loop, Can you please tell me i am doing something wrong with the @body
All i am trying to do is concatenate few fields values and store into field emailbody.


CREATE PROCEDURE dbo.USP_SendEmailActions
		(@ModuleID			int)
 AS
DECLARE @ErrorCode					int
DECLARE @TransactionCountOnEntry	 		int
DECLARE @actionno				nvarchar(50)
DECLARE @DueDate				datetime
DECLARE @Description				nvarchar(150)
DECLARE @body 				nvarchar(1000)


SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SELECT @ErrorCode = @@Error,  @TransactionCountOnEntry = @@TranCount
IF @ErrorCode <>0
BEGIN
	SET NOCOUNT OFF
	RETURN @ErrorCode
END

DECLARE @CUR_TEMP CURSOR
SET @CUR_TEMP = CURSOR SCROLL FOR (Select  actionno, CONVERT(varchar(10),DueDate,101) as DueDate,actiondescription from tab_ccsnetactions where modulerecordid = @ModuleID)


OPEN @CUR_TEMP



BEGIN TRANSACTION
FETCH FIRST FROM @CUR_TEMP INTO @actionno, @DueDate, @description
SET @body='ActionNo: '+@actionno+char(13)+'Description: '+@Description+char(13)

WHILE @@FETCH_STATUS = 0

BEGIN
	INSERT INTO TABLE_Emails
		(ActionNo,
		DueDate,
		EmailBody)
	VALUES
		(@actionno,
		@DueDate,
		@body)

	FETCH NEXT FROM @CUR_TEMP INTO @actionno, @DueDate, @Description
	SET @body='ActionNo: '+@actionno+char(13)+'Description: '+@Description+char(13)
END
SELECT @ErrorCode = @@Error
IF @ErrorCode  = ' 0'
BEGIN
	IF @@TranCount > @TransactionCountOnEntry
    		  COMMIT TRANSACTION
	CLOSE @CUR_TEMP
	DEALLOCATE @CUR_TEMP
	SET NOCOUNT OFF
	RETURN @ErrorCode
END
ELSE
BEGIN
	CLOSE @CUR_TEMP
	DEALLOCATE @CUR_TEMP
	ROLLBACK TRANSACTION
	SET NOCOUNT OFF
	RETURN @ErrorCode
END
GO

Thank you very much for the information.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-02-03 : 11:54:06

Why use the cursor?


INSERT INTO TABLE_Emails
		(ActionNo,
		DueDate,
		EmailBody)
Select  
	actionno, 
	CONVERT(varchar(10),DueDate,101) as DueDate,
	'ActionNo: ' + @actionno + char(13) + 'Description: ' + actiondescription + char(13) as ERmailBody
from tab_ccsnetactions 
where modulerecordid = @ModuleID

reddymade
Posting Yak Master

165 Posts

Posted - 2006-02-07 : 13:40:37

Helo Rick,
I am using the same way you have asked me to instead of using cursors, But i don't see the concatenated column getting populated in the table. after the insert. Please can you tell me what am i doing wrong.


CREATE PROCEDURE dbo.USP_SendEmailActions
		(@ModuleID			int)
 AS
DECLARE @ErrorCode					int
DECLARE @TransactionCountOnEntry	 		int
DECLARE @actionno				nvarchar(50)
DECLARE @DueDate				datetime
DECLARE @Description				nvarchar(150)
DECLARE @body 				nvarchar(1000)

Set @Body = 'ActionNo: ' + @actionno + char(13) + 'Description: ' + @Description + char(13) + ' as description'


INSERT INTO TABLE_Emails
		(ActionNo,
		DueDate,
		Description)
Select  
	actionno, 
	CONVERT(varchar(10),DueDate,101) as DueDate, @Body 
from tab_ccsnetactions 
where modulerecordid = @ModuleID
GO

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-02-07 : 13:58:18

reddymade,
Your variables @Description doesn't have data when it is used in this way.

What RickD has told was to have the table-field names in the query along with constant text. the variables used in the Insert statement should be derived before reaching the statement or should be taken from a table in the statement.

Thats why the following works

'ActionNo: ' + @actionno + char(13) + 'Description: ' + actiondescription + char(13) as ERmailBody

and the following doesn't work

Set @Body = 'ActionNo: ' + @actionno + char(13) + 'Description: ' + @Description + char(13) + ' as description'

..... CONVERT(varchar(10),DueDate,101) as DueDate, @Body

reddymade
Posting Yak Master

165 Posts

Posted - 2006-02-07 : 15:24:07

Hello Srinika,
I am using the following, still i don't see any data getting into description field under table_emails table.
table_emails fields (Id(identity column), actionno, duedate, description(text - 16))


CREATE PROCEDURE dbo.USP_SendEmailActions
		(@ModuleID			int)
 AS
DECLARE @ErrorCode					int
DECLARE @TransactionCountOnEntry	 		int
DECLARE @actionno				nvarchar(50)
DECLARE @DueDate				datetime
DECLARE @Description				nvarchar(150)
DECLARE @body 				nvarchar(1000)

--Set @Body = 'ActionNo: ' + @actionno + char(13) + 'Description: ' + actiondescription + char(13) as EmailBody

INSERT INTO TABLE_Emails
		(ActionNo,
		DueDate,
		Description)
Select  
	actionno, 
	CONVERT(varchar(10),DueDate,101) as DueDate, 'ActionNo: ' + @actionno + char(13) + 'Description: ' + actiondescription + char(13) as description
from tab_ccsnetactions 
where modulerecordid = @ModuleID
GO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-02-07 : 16:07:12

You are still using a variable in your SELECT statement. Since you aren't setting it, it is using NULL which depending on your setting could mean that whole column is being set to NULL. Get rid of @actionno from the SELECT. Use the ActionNo column from tab_ccsnetactions instead.

quote:

'ActionNo: ' + @actionno + char(13)

Tara Kizer
aka tduggan

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-02-08 : 05:25:48

Sorry, my bad, forgot to take out the @..


INSERT INTO TABLE_Emails
		(ActionNo,
		DueDate,
		EmailBody)
Select  
	actionno, 
	CONVERT(varchar(10),DueDate,101) as DueDate,
	'ActionNo: ' + actionno + char(13) + 'Description: ' + actiondescription + char(13) as ERmailBody
from tab_ccsnetactions 
where modulerecordid = @ModuleID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources