Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 194
  • Last Modified:

Store procedure explanation

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
yguyon28
Asked:
yguyon28
3 Solutions
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
John_VidmarCommented:
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

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now