Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Please help me understand this SQL


Please help me understand this SQL

Author
Message
TC-416047
TC-416047
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 Visits: 213
The code below is from a SQL trace I did to find out how our ERP system generates a gross profit report by customer order.
I studied music and not computer science so my SQL chops are fairly limited and I have a few questions.
1. I do not understand what the parameters are at the beginning.
Is there something else I need to look for in the trace that impacts this?

2. I do not understand what the stored procedure is doing.
because the SQL was wrapped in N'sql code' It looks like the code was a parameter in the sp_cursorprepexec.

3. I do not understand what happens when you have three select statements in a row like this.
Should it produce one result set or three result sets?

4. Some of the parameters in the code are obvious such as currency_id =@P1 but can I reverse engineer the others for customer order for example? Is there possibly something in a trace where I would see the data values?

Our goal is to have a gross profit report by customer order showing line detail and putting it in excel.

Your help on this would be greatly appreciated.
--------------------- BEGIN ---------------------------
/*
declare @p1 int
set @p1=-1
declare @p2 int
set @p2=0
declare @p5 int
set @p5=4104
declare @p6 int
set @p6=8193
declare @p7 int
set @p7=0
exec sp_cursorprepexec @p1 output,@p2 output,N'@P1 varchar(255),@P2 varchar(255),@P3 varchar(255),@P4 varchar(255),@P5 datetime,@P6 datetime,@P7 varchar(255),@P8 varchar(255),@P9 varchar(255),@P10 varchar(255),@P11 datetime,@P12 datetime,@P13 datetime,@P14 varchar(255),@P15 varchar(255),@P16 datetime,@P17 datetime,@P18 varchar(255),@P19 varchar(255),@P20 varchar(255),@P21 varchar(255)',
*/
SELECT
SDET.CUST_ORDER_ID,
SDET.DIST_NO,
SDET.CUST_ORDER_LINE_NO,
SDET.PART_ID,
SDET.PRODUCT_CODE,
SDIST.POSTING_STATUS,
SDIST.POSTING_DATE,
CL.MISC_REFERENCE,
CL.LINE_STATUS,
CL.COMMODITY_CODE,
CO.CUSTOMER_ID,
C.NAME

FROM
SHIPMENT_DETAIL SDET,
SHIPMENT_DIST SDIST,
CUST_ORDER_LINE CL,
CUSTOMER_ORDER CO,
CUSTOMER C

WHERE
SDET.CUST_ORDER_ID = SDIST.CUST_ORDER_ID
AND SDET.DIST_NO = SDIST.DIST_NO
AND SDET.CUST_ORDER_ID = CL.CUST_ORDER_ID
AND SDET.CUST_ORDER_LINE_NO = CL.LINE_NO
AND SDET.CUST_ORDER_ID = CO.ID
AND SDIST.ENTRY_NO = ( SELECT MIN ( ENTRY_NO )
FROM SHIPMENT_DIST SD
WHERE
SD.CUST_ORDER_ID = SDIST.CUST_ORDER_ID
AND SD.DIST_NO = SDIST.DIST_NO
AND SD.CURRENCY_ID = 'USD' ) --Was @P1
AND CO.CUSTOMER_ID = C.ID
AND SDET.ACT_MATERIAL_COST + SDET.ACT_LABOR_COST + SDET.ACT_BURDEN_COST + SDET.ACT_SERVICE_COST <> 0
AND SDET.CUST_ORDER_ID BETWEEN @P2 AND @P3
AND SDIST.CURRENCY_ID = 'USD' --was @P4
AND SDIST.POSTING_DATE BETWEEN '12/1/2012' AND '12/31/2012' --was @P5 and @P6

UNION SELECT
RL.CUST_ORDER_ID,
RDIST.DIST_NO,
RL.CUST_ORDER_LINE_NO,
CL.PART_ID,
CL.PRODUCT_CODE,
RDIST.POSTING_STATUS,
RDIST.POSTING_DATE,
CL.MISC_REFERENCE,
CO.STATUS,
CL.COMMODITY_CODE,
CO.CUSTOMER_ID,
C.NAME

FROM
RECEIVABLE_LINE RL,
CUSTOMER_ORDER CO,
CUST_ORDER_LINE CL,
RECEIVABLE_DIST RDIST,
CUSTOMER C

WHERE
RL.CUST_ORDER_ID = CO.ID
AND CL.CUST_ORDER_ID = CO.ID
AND RL.CUST_ORDER_ID = CL.CUST_ORDER_ID
AND RL.INVOICE_ID = RDIST.INVOICE_ID
AND RDIST.ENTRY_NO = ( SELECT MIN ( ENTRY_NO )
FROM RECEIVABLE_DIST RD
WHERE RD.INVOICE_ID = RDIST.INVOICE_ID
AND RD.DIST_NO = RDIST.DIST_NO
AND RD.CURRENCY_ID = 'USD' ) -- was @P7
AND C.ID = CO.CUSTOMER_ID
AND CL.LINE_NO = RL.CUST_ORDER_LINE_NO
AND RL.CUST_ORDER_ID BETWEEN @P8 AND @P9
AND RDIST.CURRENCY_ID = 'USD' -- was @P10
AND RDIST.POSTING_DATE BETWEEN '12/1/2012' AND '12/31/2012' -- was @P11 and @P12

UNION SELECT
RL.CUST_ORDER_ID,
RDIST.DIST_NO,
RL.CUST_ORDER_LINE_NO,
CL.PART_ID,
CL.PRODUCT_CODE,
RDIST.POSTING_STATUS,
RDIST.POSTING_DATE,
CL.MISC_REFERENCE,
CO.STATUS,
CL.COMMODITY_CODE,
CO.CUSTOMER_ID,
C.NAME

FROM
RECEIVABLE_LINE RL,
CUSTOMER_ORDER CO,
CUST_ORDER_LINE CL,
RECEIVABLE_DIST RDIST,
CUSTOMER C

WHERE
RL.CUST_ORDER_ID = CO.ID
AND CL.CUST_ORDER_ID = CO.ID
AND RL.CUST_ORDER_ID = CL.CUST_ORDER_ID
AND RL.INVOICE_ID = RDIST.INVOICE_ID
AND RL.CUST_ORDER_ID IS NOT NULL
AND RL.QTY <> 0
AND RL.INVOICE_ID = RDIST.INVOICE_ID
AND RDIST.POSTING_DATE < @P13
AND RDIST.CURRENCY_ID = @P14
AND RL.CUST_ORDER_ID IN ( SELECT RL2.CUST_ORDER_ID
FROM RECEIVABLE_LINE RL2
WHERE RL2.INVOICE_ID IN ( SELECT RD.INVOICE_ID
FROM RECEIVABLE_DIST RD
WHERE RD.CURRENCY_ID = @P15
AND RD.POSTING_DATE BETWEEN @P16 AND @P17 ) )
AND RDIST.ENTRY_NO IN ( SELECT MIN ( RD2.ENTRY_NO )
FROM RECEIVABLE_DIST RD2
WHERE RD2.INVOICE_ID = RDIST.INVOICE_ID
AND RD2.DIST_NO = RDIST.DIST_NO
AND RD2.CURRENCY_ID = @P18 )
AND C.ID = CO.CUSTOMER_ID
AND CL.LINE_NO = RL.CUST_ORDER_LINE_NO
AND RL.CUST_ORDER_ID BETWEEN @P19 AND @P20
AND RDIST.CURRENCY_ID = @P21
ORDER BY
1,
3',
@p5 output,@p6 output,@p7 output,
'USD','100106C','100106C',
'USD','2012-12-01 00:00:00','2012-12-31 00:00:00',
'USD','100106C','100106C',
'USD','2012-12-01 00:00:00','2012-12-31 00:00:00','2012-12-01 00:00:00' ,'USD'
,'USD','2012-12-01 00:00:00','2012-12-31 00:00:00'
,'USD','100106C','100106C'
,'USD'



select @p1, @p2, @p5, @p6, @p7
------------------------------- END CODE ---------------------------------------------
Jason-299789
Jason-299789
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1134 Visits: 3229
1) The parameters at the beginning are probably there to allow some sort of debugging to take place or to be able to run it outside the confines of the SP, without the developer needing to reentere the declare and set up every time, I've never come across the sp_cursorprepexec statement before.

2) It looks like its getting a list of orders, shipments and also doing a match against invoices, but your guess is as good as mine as i dont know your system. Does the SP name give any hints?

3) it produces one result set, the key to this is the UNION statement, which joins the results together, read BOL for more information about UNION

_________________________________________________________________________
SSC Guide to Posting and Best Practices
TC-416047
TC-416047
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 Visits: 213
the sp name makes me think this is a generic routine for calling programs.
prep and execute something. In this case a gross profit report by customer order.

I would like to replace the parameters with hard coded values to test this.
But I'm not sure how to identify all the parm values.

Its trial and error time I guess.

I did take your suggestion and read the BOL on UNION.
A big thanks for that kick in the pants Smile

In any case thank you for taking the time to help me out.

-Todd
TC-416047
TC-416047
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 Visits: 213
I have determined that the values at the end of the script are the actual parameter values:
'USD','100106C','100106C',
'USD','2012-12-01 00:00:00','2012-12-31 00:00:00',
'USD','100106C','100106C',
'USD','2012-12-01 00:00:00','2012-12-31 00:00:00','2012-12-01 00:00:00' ,'USD'
,'USD','2012-12-01 00:00:00','2012-12-31 00:00:00'
,'USD','100106C','100106C'
,'USD'
GSquared
GSquared
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14385 Visits: 9729
sp_cursorprepexec is a built-in stored procedure that comes with SQL Server. Details here: http://msdn.microsoft.com/en-us/library/ff848775.aspx

But it looks like it's in a comment-block in the code you quoted. /* begins a comment, and */ ends it. This is called a "c-style comment block", because it comes from the C programming language. T-SQL uses it the same way. That means what you quoted is probably just being called into that. Not entirely sure, since I don't use sp_cursorprepexec in my own programs.

But it's definitely just a call to a cursor, either way, as per MSDN.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
TC-416047
TC-416047
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 Visits: 213
GSquared,
Thank you for the link. That helps alot.

The /*...*/ was put in by me because I copied that code out of a SQL trace file and did not have any input parameter values. So I commented them out so I could test the results in the sql studio.

-Todd
GSquared
GSquared
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14385 Visits: 9729
TC-416047 (1/11/2013)
GSquared,
Thank you for the link. That helps alot.

The /*...*/ was put in by me because I copied that code out of a SQL trace file and did not have any input parameter values. So I commented them out so I could test the results in the sql studio.

-Todd


Sorry for the late reply. That makes sense. Glad the link helped.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search