Using variable assignment within a query

Question

Hi, Can anyone help me out with this query:

  DECLARE @descrip varchar(500)
  SELECT    b.custNo,
            b.soldToName,
            SUBSTRING(b.salesOrderNo, 1, 1) + '-' + 
            SUBSTRING(b.salesOrderNo, 2, 4) + '-' + 
            SUBSTRING(b.salesOrderNo, 6, 5),
            SUBSTRING(d.reqDate, 1, 2) + '/' + 
            SUBSTRING(d.reqDate, 3, 2) + '/' + 
            SUBSTRING(d.reqDate, 5, 2),
            d.reqQty,
            (d.reqQty * b.unitPrice),
            creditLimit,
            accreceivable,
            @descrip,
            b.sequenceNo,
            salesmanCode,
            (SELECT @descrip = ISNULL(@descrip + '', '') + descrip
             FROM   BACK
             WHERE  salesOrderNo = b.salesOrderNo
                    AND recordType = '3')
            SELECT @descrip)
  FROM      BACK b
  FULL OUTER JOIN DELB d ON b.salesorderNo = d.salesOrderNo
                            AND b.sequenceNo = d.sequenceNo
  INNER JOIN CUST c ON c.custNo = b.custNo
  FULL OUTER JOIN SHIPVIA sh ON d.shipViaCode = sh.code
  WHERE     b.partNo = 'A 6A16-015DF2506'
            AND qtyBackordered > 0
  ORDER BY  dbo.ConvertDeliveryDate(reqDate, GETDATE())

The error I get is > Server: Msg 170, Level 15, State 1, > Line 5 Line 5: Incorrect syntax near > '='.

The line of query where I'm assigning the descrip column to @descrip is basically to collect ALL descriptions available for a certain order in the BACK table and put it in one field separated by <BR> to display it back to the user. If I remove this SELECT statement, the query works fine, but I need this data in order not to make extra trips to my DB. Thanks in advance.

Answer 1

0

You cannot select data and assign variables at the same time. If you need your results to include the list of all descrip values for the specified salesOrderNo then you cannot assign the value to the variable per returned record and expect it to somehow return correct values for each record. You can restate your query like this to get the data you need:

SELECT 
    b.custNo, b.soldToName, 
    SUBSTRING(b.salesOrderNo,1,1)+'-'+SUBSTRING(b.salesOrderNo,2,4)+
        '-'+SUBSTRING(b.salesOrderNo,6,5),
    SUBSTRING(d.reqDate,1,2)+'/'+SUBSTRING(d.reqDate,3,2)+
        '/'+SUBSTRING(d.reqDate,5,2),
    d.reqQty,(d.reqQty*b.unitPrice), creditLimit,accreceivable,
    stuff(cast(tvf.descrip as varchar(max)), 1, 5, '') descrip,
    b.sequenceNo,salesmanCode
    FROM BACK b cross apply
    (
        select 
            '<br/>'+ descrip 
        FROM BACK 
        WHERE salesOrderNo = b.salesOrderNo and recordType='3'
        for xml path(''), type
    ) tvf (descrip)
    FULL OUTER JOIN DELB d 
        ON b.salesorderNo=d.salesOrderNo AND b.sequenceNo=d.sequenceNo 
    INNER JOIN CUST c on c.custNo=b.custNo 
    FULL OUTER JOIN SHIPVIA sh 
        ON d.shipViaCode=sh.code 
    WHERE b.partNo='A 6A16-015DF2506' AND qtyBackordered>0 
    ORDER BY dbo.ConvertDeliveryDate(reqDate,getDate())

Oleg

answered Feb 10 '11 at 10:43 AM

Oleg
15.4k ● 1 ● 4 ● 24

I am on SQL SERVER 2000. Running your solutions yeields a few errors:

Server: Msg 170, Level 15, State 1, Line 10
Line 10: Incorrect syntax near 'apply'.
Server: Msg 156, Level 15, State 1, Line 16
Incorrect syntax near the keyword 'for'.

Feb 10 '11 at 11:17 AM peacemaker1820

Sorry about that. This is true that the script in my answer will not run on SQL Server 2000 because the latter does not have APPLY and does not support xml like 2005 does.

Feb 10 '11 at 11:24 AM Oleg

So, do I have to resort to a function?

Feb 10 '11 at 12:09 PM peacemaker1820

SQL Server 2000 supports user defined scalar functions, so probably yes. If you opt to go this route then you have to write a function taking salesOrderNo as a parameter and returning the list of br-delimited values. The body of the function is pretty much what you had in your original select except you should probably use

SELECT @descrip = ISNULL(@descrip + '<br/>', '') + descrip

-- rather than

SELECT @descrip = ISNULL(@descrip + '', '') + descrip

Then of course you will replace the ocurence of @descrip in your select list with this function call.

This appears to be a much better option than to revert to using a cursor. Too bad that you still have SQL Server 2000, the life is much easier when working with later versions. It is amazing that 2000 is still there when support for SQL Server 2005 ends in 2 months from now (on April 12th of this year).

Feb 10 '11 at 12:56 PM Oleg

(comments are locked)

Answer 2

0

My Solution and it works...thanks all.

CREATE FUNCTION dbo.GetBackorderNotes (@soNo varchar(10))

RETURNS varchar(500)

BEGIN
    DECLARE @descrip varchar(500)
    SELECT @descrip=ISNULL(@descrip+'<BR/>','')+descrip from BACK WHERE salesOrderNo = @soNo and recordType='3' order by sequenceNo 

RETURN @descrip
END

answered Feb 10 '11 at 01:02 PM

peacemaker1820
31 ● 1 ● 1

I am glad to hear it works. When you upgrade to a newer version, you might consider switching to the soluition using cross apply with a table-valued function (or inline SQL like in my answer) for performance reasons because the table valued functions appear to offer better performance. In any case, please come back to this site when you have a question or willing to offer your help with answers. As you can see, it does not take long to get a reply here.

Feb 10 '11 at 01:27 PM Oleg

True. I most definately will. Performance is my number one priority.

Feb 11 '11 at 04:59 AM peacemaker1820

(comments are locked)

Using variable assignment within a query

Follow this question

Related Questions