November 3, 2004 at 3:12 pm
I'm new to TSQL and I'm having trouble with this paticular situation. I have a table (see below). I want to assign variables to the messages and return the variable to a calling procedure ( I know how to return the variables). The problem is how to assign the variables to the messages when I query one client? Should I use a CURSOR ,WHILE loop or is there another solution?
SELECT Msg
FROM tblMSG
WHERE CLIENT = 'A10021'
This will return 3 rows. I need to assign @Msg1, @Msg2, @Msg3 varibles to the Msg columns.
In this example I want to return Message1,Message2,Messag3. To add to this, a client could have one to four messages hence the Seq column. What would be my best solution? I had read, try to avoid CURSORS. Thanks for your time.
Client Seq Msg
A10021 1 Message1
A10021 2 Message2
A10021 3 Message3
A10051 1 Message1
A10061 1 Message1
A10061 2 Message2
A10131 1 Message1
A10231 1 Message1
A10231 2 Message2
A10231 3 Message3
A10231 4 Message4
November 3, 2004 at 4:14 pm
Something like this should do it:
declare @msg1 as varchar(50), @msg2 as varchar(50), @msg3 as varchar(50), @msg4 as varchar(50)
declare @client as char(6)
set @client = 'a10051'
set @msg1 = (select msg from tblMsg where client = @client and seq = 1)
set @msg2 = (select msg from tblMsg where client = @client and seq = 2)
set @msg3 = (select msg from tblMsg where client = @client and seq = 3)
set @msg4 = (select msg from tblMsg where client = @client and seq = 4)
select @msg1, @msg2, @msg3, @msg4
You'll have to amend the bit where I set the client code to suit your purposes.
Regards
Phil
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 3, 2004 at 4:40 pm
Phil, Thanks. I was making more complicated then I needed to. I thought, I needed to count the number of seq for the client and then assign the number of variables to match the seq. I guess, if there is only one seq available the rest of the variables will be null.
November 3, 2004 at 5:16 pm
Exactly right. Of course, if there might be more than 4 messages, you might have to go dynamic ...
Phil
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 4, 2004 at 6:43 am
This will do the trick (I posted this on another thread and that's exactly what you need since it's not dynamic SQL and it won't be limited by any number of rows)
CREATE FUNCTION [dbo].[fnGetMsgs] (@CLIENT_id as varchar(?))
RETURNS varchar(6000) AS --can be more than that but the total row size cannot exceed 8060 (or 8076???) characters
BEGIN
Declare @Return as varchar(6000)
Declare @Length as smallint
set @Return = ''
Select @Return = @Return + Msg + ', ' from tblMSG where CLIENT = @CLIENT_id
set @Length = len(@Return)
if @Length > 0
begin
--remove the trailing space and comma
set @Return = left(@Return, @Length - 2)
end
Return @Return
END
Use : Select /*Distinct --pretty sure you're gonna need this*/ field1, fields2, dbo.fnGetMsgs(Client_id) as MsgClients from dbo.tblMSG
November 4, 2004 at 8:59 am
Both are very helpful. It's gets you think about the different solutions to a problem. I'll keep plugging away at TSQL until it sets in. Thanks for your time.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply