Concatenating columns and storing into one

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)

Concatenating columns and storing into one

Author

Topic

reddymade
Posting Yak Master

165 Posts

Posted - 2006-02-07 : 10:33:19

When executed the following 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 which is @body.
I am first filling data to cursor and then using that data and inserting into another table. I am looking at few records may 4 or 5 records each time. I am not overloading the cursor with hundreds of records.
I am having problem at SET @body='ActionNo: '+@actionno+char(13)+'Description: '+@Description+char(13)
I am using at two different places firsttime when it fetches firstrow from cursor and the again at fetchnext row.
It is not working can you please help.



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-07 : 12:14:19

Didn't you post this last week?

Look at my answer from then..

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61241

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-08 : 03:25:01

Explain what you are trying to do
Do you want to concatenate all the data of a column?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources