Using variable assignment within a query

Hi, Can anyone help me out with this query:

   DECLARE @descrip varchar(500)
   SELECT    b.custNo,
             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 * b.unitPrice),
             (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.

more ▼

asked Feb 10, 2011 at 10:14 AM in Default

avatar image

31 1 1 1

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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:

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


more ▼

answered Feb 10, 2011 at 10:43 AM

avatar image

18.9k 3 7 28

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, 2011 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, 2011 at 11:24 AM Oleg

So, do I have to resort to a function?

Feb 10, 2011 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, 2011 at 12:56 PM Oleg
(comments are locked)
10|1200 characters needed characters left

My Solution and it works...thanks all.

 CREATE FUNCTION dbo.GetBackorderNotes (@soNo varchar(10))
 RETURNS varchar(500)
     DECLARE @descrip varchar(500)
     SELECT @descrip=ISNULL(@descrip+'<BR/>','')+descrip from BACK WHERE salesOrderNo = @soNo and recordType='3' order by sequenceNo 
 RETURN @descrip
more ▼

answered Feb 10, 2011 at 01:02 PM

avatar image

31 1 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, 2011 at 01:27 PM Oleg

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

Feb 11, 2011 at 04:59 AM peacemaker1820
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Feb 10, 2011 at 10:14 AM

Seen: 781 times

Last Updated: Feb 10, 2011 at 11:34 AM

Copyright 2017 Redgate Software. Privacy Policy