Create a string from temp 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 2012 Forums

Transact-SQL (2012)

Create a string from temp table

Author

Topic

kdeutsch
Yak Posting Veteran

83 Posts

Posted - 2013-10-03 : 14:42:04

I need to create emails form sql server, this is no issue just background. What I am attempting to do is loop though atemp table that I am creating and create the string nessecary to insert into the email, but it doe snot seem to be working for me. It always comes out null on select. The following is my test to try to get working.

Create table #Testtab (id int not null identity(1,1), IssuedId int, reIssue int);

Declare @Counter int, @TempCount int, @Iss varchar(max), @Issu2 varchar(max), @Reiss varchar(max), @Reiss2 varchar(max);

Insert #Testtab
select intIssuedId, intReIssueId from Drat_ReIssueHist where intReIssueId in (40888,40889,40890,40891);

select * from #Testtab

select @TempCount = COUNT(id) from #Testtab

While @Counter <= @TempCount
BEGIN
Set @Iss = (select IssuedId from #Testtab where id = @Counter)

set @Issu2 = @Issu2 + @Iss + Char(13) + Char(10)

Set @Reiss = (select reIssue from #Testtab where id = @Counter)

set @Reiss2 = @Reiss2 + @Reiss + Char(13) + Char(10)

Set @Counter = @Counter + 1
END

select @Issu2, @Reiss2

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-10-03 : 14:54:17

the problem usually turns out to be that at least one of your variables resolved to NULL. When that happens the entire final value is also NULL. you just need to do something like this for any variable that could be null:
COALESCE(@var1, '') + COALESCE(@var2, '')

Be One with the Optimizer
TG

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-04 : 03:09:45

I cant see where you're initializing @Counter variable. That may be the issue you're facing!

Anyways I dont think you need loop either.

you could simply do the below


Declare @Issu2 varchar(max),  @Reiss2 varchar(max);
SELECT @Issu2 = STUFF((SELECT Char(13) + Char(10) + CAST(intIssuedId AS varchar(7)) FROM Drat_ReIssueHist where intReIssueId in (40888,40889,40890,40891) FOR XML PATH('')),1,2,''),
@Reiss2 = STUFF((SELECT Char(13) + Char(10) + CAST(intReIssueId AS varchar(7)) FROM Drat_ReIssueHist where intReIssueId in (40888,40889,40890,40891) FOR XML PATH('')),1,2,'')

select @Issu2, @Reiss2

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

kdeutsch
Yak Posting Veteran

83 Posts

Posted - 2013-10-04 : 09:56:01

Thank for the help guys don't know much about using XML in t-sql but gonna use it as it works great for what I need. thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-04 : 10:17:54

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources