Solved

Store procedure explanation

Posted on 2014-10-20
3
180 Views
Last Modified: 2014-10-22
Could someone tell me what does this do?


-- GET JT NUMBER AND SALES ORDER STATUS
UPDATE #ForecastSolution
SET JTNumber = SDURAB
,OrderStatus = SDNXTR
FROM F42119
WHERE OrderNum = SDDOCO
AND SDURCD = 'JT'
AND SDURAB <> 0

ALTER PROCEDURE [dbo].[Acct_ForecastSolutionEvaluation]



AS
BEGIN
SET NOCOUNT ON;
--SET STATISTICS IO ON;
--SET STATISTICS TIME ON;


IF OBJECT_ID('TEMPDB..#ForecastSolution') IS NOT NULL
BEGIN
	DROP TABLE #ForecastSolution;
END

CREATE TABLE #ForecastSolution
(
	 OrderNum		INT
	,OrderType		VARCHAR(2)
	,SoldTo			VARCHAR(8)	
	,CustomerName	VARCHAR(40)
	,BusinessUnit	VARCHAR(12)
	,ShortItem		INT
	,LongItem		VARCHAR(25)
	,OrderStatus	VARCHAR(3)
	,JTNumber		INT
	,PubTypeCode	VARCHAR(4)
	,ProdPubType	VARCHAR(1975)
	,ConfigType		VARCHAR(8)
	,Description2	VARCHAR(1975)
	,Brand			VARCHAR(1975)
	,[PageCount]	VARCHAR(1975)
	,Qty			VARCHAR(1975)
	,ShippedQTY		DECIMAL(11,2)
	,RevenueJT		VARCHAR(1975)
	,ExtendedAmt	DECIMAL(11,2)
);


INSERT INTO #ForecastSolution(OrderNum,OrderType,SoldTo,CustomerName,BusinessUnit,ShortItem,ShippedQTY,Description2,ExtendedAmt,PubTypeCode)
SELECT
OrderNum
,OrderType
,AddressNum
,cName
,BusinessUnit
,ShortItemNum
,QuantityShipped
,DescriptionLine2
,ExtendedPrice
,ProdPubType
FROM dbo.Acct_SalesStatementRD
WHERE GL_Date >= '20140101'

-- ADD OPEN ORDER
-- FROM F4211



-- GET JT NUMBER AND SALES ORDER STATUS
UPDATE #ForecastSolution
SET JTNumber = SDURAB
,OrderStatus = SDNXTR
FROM F42119
WHERE OrderNum = SDDOCO
AND SDURCD = 'JT'
AND SDURAB <> 0

-- GET JT NUMBER AND SALES ORDER STATUS
UPDATE #ForecastSolution
SET JTNumber = SDURAB
,OrderStatus = SDNXTR
FROM F4211
WHERE OrderNum = SDDOCO
AND SDURCD = 'JT'
AND SDURAB <> 0

Open in new window

0
Comment
Question by:yguyon28
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 166 total points
ID: 40391938
Changes (UPDATE) the value in temp table #ForecastSolution, column JTNumber and OrderStatus, to the values in tableF42119, columns  SDURAB and SDNXTR (respectively), only for the rows WHERE the value in column OrderNum = the value in column SDDOCO, SDURCD = 'JT', and SDURAB <> (does not equal) 0.

Looks like the SP you posted creates this temp table based on the SELECT you see after the INSERT statement, and then makes two updates, but then doesn't do anything with the table before the proc ends.
0
 
LVL 50

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 167 total points
ID: 40391940
The piece of code that you posted it's updating two fields (JTNumber  and OrderStatus ) from a temporary table (#ForecastSolution) with the values of two other fields (SDURAB and SDNXTR respectively) from a table named F42119 and only for the records that meet the criteria (OrderNum = SDDOCO  AND SDURCD = 'JT' AND SDURAB <> 0).
0
 
LVL 11

Accepted Solution

by:
John_Vidmar earned 167 total points
ID: 40393220
Temporary-table #ForecastSolution is joined to table F42119 (via OrderNum = SDDOCO), this result-set is further filtered by the where-clause (SDURCD = 'JT' AND SDURAB <> 0).  If the filtered result-set has any rows remaining then you will get 2 fields updated in #ForecastSolution (JTNumber and OrderStatus) by values from F42119 (SDURAB and SDNXTR, respectively).

When more than one table is present in a query (select, insert, update, delete) then I like to use table-aliases, for readability (I also like sql-92 syntax):
UPDATE	a
SET	JTNumber		=	b.SDURAB
,	OrderStatus		=	b.SDNXTR
FROM	#ForecastSolution	a
JOIN	F42119			b	ON	a.OrderNum = b.SDDOCO
WHERE	b.SDURCD = 'JT'
AND	b.SDURAB <> 0

Open in new window

0

Featured Post

Get Database Help Now w/ Support & Database Audit

Keeping your database environment tuned, optimized and high-performance is key to achieving business goals. If your database goes down, so does your business. Percona experts have a long history of helping enterprises ensure their databases are running smoothly.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …

690 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question