January 10, 2013 at 7:38 am
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 ---------------------------------------------
January 10, 2013 at 8:34 am
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
January 10, 2013 at 11:18 am
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 🙂
In any case thank you for taking the time to help me out.
-Todd
January 10, 2013 at 2:50 pm
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'
January 10, 2013 at 2:59 pm
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
January 11, 2013 at 6:00 am
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
January 16, 2013 at 6:50 pm
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply