Solved

Store procedure explanation

Posted on 2014-10-20
3
173 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
3 Comments
 
LVL 65

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 48

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
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.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

828 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