SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Create a string from temp table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kdeutsch
Yak Posting Veteran

USA
83 Posts

Posted - 10/03/2013 :  14:42:04  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 10/03/2013 :  14:54:17  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/04/2013 :  03:09:45  Show Profile  Reply with Quote
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
Go to Top of Page

kdeutsch
Yak Posting Veteran

USA
83 Posts

Posted - 10/04/2013 :  09:56:01  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/04/2013 :  10:17:54  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000