Help needed on concatination of data into variable problem
-
Yep, that's it. The WHERE SLPRSNID =@MinRep is used for looping. The section above the whole thing changes @MinRep so that the loop selects the next record.
I'll give it another try in the morning and confirm it works. At the moment it works with the loop in place so I'm very happy, but I always like to learn more efficient ways of doing things.
-
Heh... the "golden plate" was correct... somebody forgot to wash the glass though
JacekO is correct... the method being suggested doesn't use an explicit "loop" so no need for the loop control variable. What I am curious about is... how many rows might you be concatenating? This method (and the loop method) will only allow 8000 characters (if @Message is VARCHAR(8000))...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Did your mom tell you not to trust strangers?
_____________
Code for TallyGenerator -
The
SELECT @variable = @variable + myfield
FROM mytable
portion is this undocumented feature Sergiy wrote about.
It allows you to concatenate all myfield values selected by a query into a variable in a single pass. It works with different datatypes but the reults might be 'funny' or there are other functions already doing the same (like SUM for numerics) . One thing you have to be careful using this is the amount of data you are processing so you do not exceed the maximum size of your variable.
I kind of accidentally discovered it myself few years ago and have been using it since whenever I have problems similar to yours.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font] -
With the changes above it does work just fine without the loop.
The whole code is below, with the xp_sendmail part as well if anyone else is looking to send results by email.
Specifically, this is for SMS, so I still need to add some logic to break the message into 150 character chunks.
/*Description: Get sales data MTD by rep and send via SMS to reps mobile phones*/
SET NOCOUNT ON
--Get the data
DECLARE @Stage TABLE
(
SLPRSNID VARCHAR(15),
PHONE VARCHAR(21),
INFO VARCHAR(9)
)
INSERT@Stage
SELECT REP, MIN(PHONE) AS PHONE, CONVERT(VARCHAR(9),SUM(TOTAL)) AS INFO
FROM SOP10100 S INNER JOIN RM00301 R ON S.SLPRSNID = R.SLPRSNID
WHERE S.docdate BETWEEN convert(datetime, convert(varchar(4),YEAR(getdate()))+'/'+convert(varchar(4),MONTH(GETDATE()))+'/01') and getdate()
GROUP BY REP
-- Do the work
DECLARE @Details VARCHAR(4000),@Concat VARCHAR(15), @rc INT, @CRLF char(2),
@EmailAdd VARCHAR(200), @User Varchar(15), @PHONE VARCHAR(21),
@Start VARCHAR(15), @End VARCHAR(15)
set @CRLF = char(10)
select @Start = convert(varchar(15),convert(datetime,+convert(varchar(3),MONTH(GETDATE()))+'/1/'+convert(varchar(4),YEAR(getdate()))),3),
@End = Convert(varchar(15),GetDate(),3)
SELECT@Details = ISNULL(@Details +',','')+ Z.REP+':'+ Z.INFO,
@EmailAdd = ISNULL(@EmailAdd + ',', '')+ Z.PHONE + '@smscountry.net'
FROM(
SELECT LEFT(SLPRSNID,3) AS REP,INFO,PHONE
FROM @Stage
) Z
set @User = 'x'
exec @rc = master.dbo.xp_smtp_sendmail
@FROM= N'server@x.com',
@FROM_NAME= @User,
@TO= @EmailAdd,
@subject= @User,
@message= @Details,
@type= N'text/plain',
@server = N'mail.x.com'
select RC = @rc
Thanks for all the help!
Mark
-
I don't think you should split messages in SQL Server.
Module sending SMS should take care of it.
Otherwise recipient will receive many separate messages instead of multiple parts of the same message to be displayed as 1 by mobile.
_____________
Code for TallyGenerator -
Undocumented "feature"? So what would SET @Counter = @Counter+1 be?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
COUNT
If you inclined to scratch your left ear using your right hand going over top of your head.
And funny part is that if will do the count regardless if the @Counter datatype is numeric or string
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font] -
Jeff, I think you misunderstood my post.
The undocumented (and I don't think it is documented) feature is the usage of SELECT into varable when you do the query that processes multiple records. It does the concatenation trick.
Actually SET will do just the SET operation. (my previous reply just above was to the SELECT FROM not the SET)
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply