Assigning variable to multiple rows?

  • 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

  • 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

  • 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.

  • 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

  • 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

  •  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