question

peacemaker1820 avatar image
peacemaker1820 asked

Using variable assignment within a query

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 `
` 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.
t-sqlsql-server-2000
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Oleg avatar image
Oleg answered
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 '
'+ 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
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

peacemaker1820 avatar image peacemaker1820 commented ·
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'.
0 Likes 0 ·
Oleg avatar image Oleg commented ·
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.
0 Likes 0 ·
peacemaker1820 avatar image peacemaker1820 commented ·
So, do I have to resort to a function?
0 Likes 0 ·
Oleg avatar image Oleg commented ·
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).
0 Likes 0 ·
peacemaker1820 avatar image
peacemaker1820 answered
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+'
','')+descrip from BACK WHERE salesOrderNo = @soNo and recordType='3' order by sequenceNo RETURN @descrip END
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Oleg avatar image Oleg commented ·
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.
0 Likes 0 ·
peacemaker1820 avatar image peacemaker1820 commented ·
True. I most definately will. Performance is my number one priority.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.