How to create a Stored Procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to create a Stored Procedure

Author

Topic

Adam West
Constraint Violating Yak Guru

261 Posts

Posted - 2009-07-23 : 14:04:34

I am having trouble creating a Stored Procedure. I clicked on new stored procedure in Management Studio and it brings up a template. I added in my code which is an existing SP bec. I want to modify it, but first to get it to compile but I am getting these halts. Also, how do you know which line it's referring to?

Msg 102, Level 15, State 1, Line 6
Incorrect syntax near '<'.
Msg 102, Level 15, State 1, Line 19
Incorrect syntax near 'ON'.
Msg 102, Level 15, State 1, Procedure spu_CRSS_Sely, Line 193
Incorrect syntax near '<'.

mfemenel
Professor Frink

1421 Posts

Posted - 2009-07-23 : 14:23:30

YOu would need to post your stored procedure code for us to help you with this.

Mike
"oh, that monkey is going to pay"

Adam West
Constraint Violating Yak Guru

261 Posts

Posted - 2009-07-23 : 14:34:51

- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <ok>
-- Create date: <07,24,09>
-- Description: <test new report for last years sales>
-- =============================================
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>
-- Add the parameters for the stored procedure here
<@STARTDATE DATETIME,>
<@ENDDATE DATETIME,>
<@Customer VARCHAR(20)>,

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[spu_CRSS_Sectionly]
(
@STARTDATE DATETIME,
@ENDDATE DATETIME,
@Customer VARCHAR(20)
)
AS
BEGIN

DECLARE @STARTDATE1 DATETIME
DECLARE @STARTDATE2 DATETIME
DECLARE @STARTDATE3 DATETIME
DECLARE @STARTDATE4 DATETIME

--- SET STARTDATES

SET @STARTDATE1 = DateAdd (m , -1 , @EndDate +1)

SET @STARTDATE2 = DateAdd (m , -2 , @EndDate +1 )

SET @STARTDATE3 = DateAdd (m , -3 , @EndDate +1 )

SET @STARTDATE4 = DateAdd (m , -4 , @EndDate +1 )

SELECT IV00101.ITMGEDSC,
IV00101.ITEMDESC,
RM00101.CUSTNAME,
RM00101.CPRCSTNM,
IV40600.UserCatLongDescr,
CATS.UserCatLongDescr AS Expr1,
SOP10200.ITEMNMBR,
SOP10200.SOPNUMBE,
SOP10200.QUANTITY,
SOP10200.OXTNDPRC,
SOP10200.SOPTYPE,
SOP10100.DOCDATE,
'Current' AS source

INTO #temp

FROM sop10200 AS SOP10200 INNER JOIN
iv00101 AS IV00101 ON SOP10200.ITEMNMBR = IV00101.ITEMNMBR INNER JOIN
sop10100 AS SOP10100 ON SOP10200.SOPTYPE = SOP10100.SOPTYPE AND SOP10200.SOPNUMBE = SOP10100.SOPNUMBE INNER JOIN
iv40600 AS IV40600 ON IV00101.ITMGEDSC = IV40600.USCATVAL INNER JOIN
iv40600 AS CATS ON IV00101.USCATVLS_2 = CATS.USCATVAL INNER JOIN
rm00101 AS RM00101 ON SOP10100.CUSTNMBR = RM00101.CUSTNMBR
WHERE (DOCDATE BETWEEN @StartDate4 AND @EndDate) OR
(DOCDATE BETWEEN @StartDate AND @EndDate)
UNION ALL
SELECT IV00101.ITMGEDSC,
IV00101.ITEMDESC,
RM00101.CUSTNAME,
RM00101.CPRCSTNM,
IV40600.UserCatLongDescr,
CATS.UserCatLongDescr AS Expr1,

SOP30300.ITEMNMBR,
SOP30300.SOPNUMBE,
SOP30300.QUANTITY,
SOP30300.OXTNDPRC,
SOP30300.SOPTYPE,
SOP30200.DOCDATE,
'History' AS source

FROM sop30300 AS SOP30300 LEFT OUTER JOIN
iv00101 AS IV00101 ON SOP30300.ITEMNMBR = IV00101.ITEMNMBR INNER JOIN
sop30200 AS SOP30200 ON SOP30300.SOPTYPE = SOP30200.SOPTYPE AND SOP30300.SOPNUMBE = SOP30200.SOPNUMBE LEFT OUTER JOIN
iv40600 AS IV40600 ON IV00101.ITMGEDSC = IV40600.USCATVAL LEFT OUTER JOIN
iv40600 AS CATS ON IV00101.USCATVLS_2 = CATS.USCATVAL INNER JOIN
rm00101 AS RM00101 ON SOP30200.CUSTNMBR = RM00101.CUSTNMBR
WHERE (DOCDATE BETWEEN @StartDate4 AND @EndDate) OR
(DOCDATE BETWEEN @StartDate AND @EndDate)

SELECT
CustName,
ItemDesc,
CAST(OXTNDPRC AS FLOAT) AS OXTNDPRC,
CAST(Quantity AS FLOAT) AS QUANTITY,
DOCDATE,
SOURCE,
ITEMNMBR,
CPRCSTNM,
CAST(0.0 AS FLOAT) AS BILLED,
CAST(0.0 AS FLOAT) AS BILLED1,
CAST(0.0 AS FLOAT) AS BILLED2,
CAST(0.0 AS FLOAT) AS BILLED3,
CAST(0.0 AS FLOAT) AS BILLED4,
CAST(0.0 AS FLOAT) AS CENSUS,
CAST(0.0 AS FLOAT) AS CENSUS1,
CAST(0.0 AS FLOAT) AS CENSUS2,
CAST(0.0 AS FLOAT) AS CENSUS3,
CAST(0.0 AS FLOAT) AS CENSUS4,
CAST(0.0 AS FLOAT) AS PPD1,
CAST(0.0 AS FLOAT) AS PPD2,
CAST(0.0 AS FLOAT) AS PPD3,
CAST(0.0 AS FLOAT) AS PPD4,
CAST(0.0 AS FLOAT) AS QUOTED,
CAST(0.0 AS FLOAT) AS QUOTED1,
CAST(0.0 AS FLOAT) AS QUOTED2,
CAST(0.0 AS FLOAT) AS QUOTED3,
CAST(0.0 AS FLOAT) AS QUOTED4,
CAST(0.0 AS FLOAT) AS LTOT

INTO #FinalReport
FROM #temp
WHERE (CPRCSTNM = @Customer) OR (@Customer IS NULL) OR (@Customer='')

UPDATE #FinalReport
SET BILLED = CASE WHEN SOURCE = 'History' THEN OXTNDPRC ELSE 0 END

UPDATE #FinalReport
SET BILLED1 = CASE WHEN DOCDATE BETWEEN @STARTDATE1 AND @ENDDATE THEN BILLED ELSE 0 END,
BILLED2 = CASE WHEN DOCDATE >= @StartDate2 AND DOCDATE < @StartDate1 THEN BILLED ELSE 0 END,
BILLED3 = CASE WHEN DOCDATE >= @StartDate3 AND DOCDATE < @StartDate2 THEN BILLED ELSE 0 END,
BILLED4 = CASE WHEN DOCDATE >= @StartDate4 AND DOCDATE < @StartDate3 THEN BILLED ELSE 0 END

UPDATE #FinalReport
SET CENSUS = CASE WHEN SOURCE='History' THEN Quantity ELSE 0 END

UPDATE #FinalReport
SET CENSUS1 = CASE WHEN DOCDATE BETWEEN @StartDate1 AND @EndDate THEN CENSUS ELSE 0 END,
CENSUS2 = CASE WHEN DOCDATE >= @StartDate2 AND DOCDATE < @StartDate1 THEN CENSUS ELSE 0 END,
CENSUS3 = CASE WHEN DOCDATE >= @StartDate3 AND DOCDATE < @StartDate2 THEN CENSUS ELSE 0 END,
CENSUS4 = CASE WHEN DOCDATE >= @StartDate4 AND DOCDATE < @StartDate3 THEN CENSUS ELSE 0 END

UPDATE #FinalReport
SET QUOTED = CASE WHEN SOURCE = 'current' THEN OXTNDPRC ELSE 0 END

UPDATE #FinalReport
SET QUOTED1 = CASE WHEN DOCDATE BETWEEN @STARTDATE1 AND @ENDDATE THEN QUOTED ELSE 0 END,
QUOTED2 = CASE WHEN DOCDATE >= @StartDate2 AND DOCDATE < @StartDate1 THEN QUOTED ELSE 0 END,
QUOTED3 = CASE WHEN DOCDATE >= @StartDate3 AND DOCDATE < @StartDate2 THEN QUOTED ELSE 0 END,
QUOTED4 = CASE WHEN DOCDATE >= @StartDate4 AND DOCDATE < @StartDate3 THEN QUOTED ELSE 0 END

--- SUMMARY FRO PPD Calculation
SELECT CUSTNAME,
CPRCSTNM,
MAX(CENSUS1) AS CENSUS1,
MAX(BILLED1) AS BILLED1,
MAX(CENSUS2) AS CENSUS2,
MAX(BILLED2) AS BILLED2,
MAX(CENSUS3) AS CENSUS3,
MAX(BILLED3) AS BILLED3,
MAX(CENSUS4) AS CENSUS4,
MAX(BILLED4) AS BILLED4,
SUM(QUOTED1) AS QUOTED1,
SUM(QUOTED2) AS QUOTED2,
SUM(QUOTED3) AS QUOTED3,
SUM(QUOTED4) AS QUOTED4,
CAST(0 AS FLOAT) AS PPD1,
CAST(0 AS FLOAT) AS PPD2,
CAST(0 AS FLOAT) AS PPD3,
CAST(0 AS FLOAT) AS PPD4,
CAST(0 AS FLOAT) AS LTOT

INTO #SUMMARY
FROM #FinalReport
GROUP BY CUSTNAME,
CPRCSTNM

UPDATE #SUMMARY
SET PPD1 = CASE WHEN CENSUS1 = 0 THEN 0 ELSE BILLED1/CENSUS1 END

UPDATE #SUMMARY
SET PPD2 = CASE WHEN CENSUS2 = 0 THEN 0 ELSE BILLED2/CENSUS2 END

UPDATE #SUMMARY
SET PPD3 = CASE WHEN CENSUS3 = 0 THEN 0 ELSE BILLED3/CENSUS3 END

UPDATE #SUMMARY
SET PPD4 = CASE WHEN CENSUS4=0 THEN 0 ELSE BILLED4/CENSUS4 END

UPDATE #SUMMARY
SET LTOT = QUOTED1 + QUOTED2 + QUOTED3 + QUOTED4 + BILLED1 + BILLED2 + BILLED3 + BILLED4

SELECT * FROM #SUMMARY

END

SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
GO

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-23 : 15:31:04

Here's how to enable line numbers:

Select Tool | Options.
Expand Text Editor in the Options Tree and select All Languages.
In the property pane on the right, select the Line Numbers check box under the Display heading.
OK
If you have statements in your script like Begin...End or Go then line numbering will start counting from line 1.

Also you can double click on an error message to go to the part of the query

No, you're never too old to Yak'n'Roll if you're too young to die.

Adam West
Constraint Violating Yak Guru

261 Posts

Posted - 2009-07-23 : 16:16:41

I was doing ok until 'Property pane on the right' I cannot locate this.

craigwg
Posting Yak Master

154 Posts

Posted - 2009-07-23 : 16:20:22

Either hit F4 on your keyboard, or you can select "View >> Properties window".

Craig

Craig Greenwood

Adam West
Constraint Violating Yak Guru

261 Posts

Posted - 2009-07-23 : 16:49:40

When I get this property pane, all I see is this:

Current Window Query Opt
Status
Current Status Connected
Execution Time 00:00:00

Adam West
Constraint Violating Yak Guru

261 Posts

Posted - 2009-07-23 : 16:52:03

a bit more
Sorry bout hitting enter there.)

Rows Returned 0
Server Testserver
SPID 59
User Testserver/OK
Version 0900 4035

I don't see any line number checking box.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-07-23 : 16:55:18

remove the angle brackets <> that's your syntax error, those are just place holders.

for instance <@STARTDATE DATETIME,>
becomes
@STARTDATE DATETIME,

also, you can jump to any line # by hitting <ctrl>G

Adam West
Constraint Violating Yak Guru

261 Posts

Posted - 2009-07-24 : 10:06:45

I am getting these errors now. I took out alot.

REATE PROCEDURE spu_CRSS_Sectionly

go

ALTER PROCEDURE [dbo].[spu_CRSS_Sectionly]
(
@STARTDATE DATETIME,
@ENDDATE DATETIME,
@Customer VARCHAR(20)
)
Msg 102, Level 15, State 1, Procedure spu_CRSS_Sectionly, Line 6
Incorrect syntax near 'spu_CRSS_Sectionly'.
Msg 102, Level 15, State 1, Procedure spu_CRSS_Sectionly, Line 194
Incorrect syntax near 'END'.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-07-24 : 10:20:22

you took out too much for us to see what the error is. based on your earlier post this is a syntax error:
SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2> even without the angle brackets...and you have one BEGIN but two ENDs.

show the whole (modified sp) and we'll get another look

Adam West
Constraint Violating Yak Guru

261 Posts

Posted - 2009-07-24 : 10:33:01

So I am supposed to keep the generic params there ? the p1 for example, the word System?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-07-24 : 10:47:31

no, remove 'em. remove the whole line there.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-07-24 : 10:51:11

just remove the last 2 lines

--SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
--END

and see what happens

Adam West
Constraint Violating Yak Guru

261 Posts

Posted - 2009-07-24 : 10:58:37

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
CREATE PROCEDURE spu_CRSS_Sectionly, sysname, ProcedureName>
-- Add the parameters for the stored procedure here
@STARTDATE DATETIME,
@ENDDATE DATETIME,
@Customer VARCHAR(20),

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[spu_CRSS_Sectionly]
(
@STARTDATE DATETIME,
@ENDDATE DATETIME,
@Customer VARCHAR(20)
)
AS
BEGIN

DECLARE @STARTDATE1 DATETIME
DECLARE @STARTDATE2 DATETIME
DECLARE @STARTDATE3 DATETIME
DECLARE @STARTDATE4 DATETIME

--- SET STARTDATES

SET @STARTDATE1 = DateAdd (m , -1 , @EndDate +1)

SET @STARTDATE2 = DateAdd (m , -2 , @EndDate +1 )

SET @STARTDATE3 = DateAdd (m , -3 , @EndDate +1 )

SET @STARTDATE4 = DateAdd (m , -4 , @EndDate +1 )

SELECT IV00101.ITMGEDSC,
IV00101.ITEMDESC,
RM00101.CUSTNAME,
RM00101.CPRCSTNM,
IV40600.UserCatLongDescr,
CATS.UserCatLongDescr AS Expr1,
SOP10200.ITEMNMBR,
SOP10200.SOPNUMBE,
SOP10200.QUANTITY,
SOP10200.OXTNDPRC,
SOP10200.SOPTYPE,
SOP10100.DOCDATE,
'Current' AS source

INTO #temp

FROM sop10200 AS SOP10200 INNER JOIN
iv00101 AS IV00101 ON SOP10200.ITEMNMBR = IV00101.ITEMNMBR INNER JOIN
sop10100 AS SOP10100 ON SOP10200.SOPTYPE = SOP10100.SOPTYPE AND SOP10200.SOPNUMBE = SOP10100.SOPNUMBE INNER JOIN
iv40600 AS IV40600 ON IV00101.ITMGEDSC = IV40600.USCATVAL INNER JOIN
iv40600 AS CATS ON IV00101.USCATVLS_2 = CATS.USCATVAL INNER JOIN
rm00101 AS RM00101 ON SOP10100.CUSTNMBR = RM00101.CUSTNMBR
WHERE (DOCDATE BETWEEN @StartDate4 AND @EndDate) OR
(DOCDATE BETWEEN @StartDate AND @EndDate)
UNION ALL
SELECT IV00101.ITMGEDSC,
IV00101.ITEMDESC,
RM00101.CUSTNAME,
RM00101.CPRCSTNM,
IV40600.UserCatLongDescr,
CATS.UserCatLongDescr AS Expr1,

SOP30300.ITEMNMBR,
SOP30300.SOPNUMBE,
SOP30300.QUANTITY,
SOP30300.OXTNDPRC,
SOP30300.SOPTYPE,
SOP30200.DOCDATE,
'History' AS source

FROM sop30300 AS SOP30300 LEFT OUTER JOIN
iv00101 AS IV00101 ON SOP30300.ITEMNMBR = IV00101.ITEMNMBR INNER JOIN
sop30200 AS SOP30200 ON SOP30300.SOPTYPE = SOP30200.SOPTYPE AND SOP30300.SOPNUMBE = SOP30200.SOPNUMBE LEFT OUTER JOIN
iv40600 AS IV40600 ON IV00101.ITMGEDSC = IV40600.USCATVAL LEFT OUTER JOIN
iv40600 AS CATS ON IV00101.USCATVLS_2 = CATS.USCATVAL INNER JOIN
rm00101 AS RM00101 ON SOP30200.CUSTNMBR = RM00101.CUSTNMBR
WHERE (DOCDATE BETWEEN @StartDate4 AND @EndDate) OR
(DOCDATE BETWEEN @StartDate AND @EndDate)

SELECT
CustName,
ItemDesc,
CAST(OXTNDPRC AS FLOAT) AS OXTNDPRC,
CAST(Quantity AS FLOAT) AS QUANTITY,
DOCDATE,
SOURCE,
ITEMNMBR,
CPRCSTNM,
CAST(0.0 AS FLOAT) AS BILLED,
CAST(0.0 AS FLOAT) AS BILLED1,
CAST(0.0 AS FLOAT) AS BILLED2,
CAST(0.0 AS FLOAT) AS BILLED3,
CAST(0.0 AS FLOAT) AS BILLED4,
CAST(0.0 AS FLOAT) AS CENSUS,
CAST(0.0 AS FLOAT) AS CENSUS1,
CAST(0.0 AS FLOAT) AS CENSUS2,
CAST(0.0 AS FLOAT) AS CENSUS3,
CAST(0.0 AS FLOAT) AS CENSUS4,
CAST(0.0 AS FLOAT) AS PPD1,
CAST(0.0 AS FLOAT) AS PPD2,
CAST(0.0 AS FLOAT) AS PPD3,
CAST(0.0 AS FLOAT) AS PPD4,
CAST(0.0 AS FLOAT) AS QUOTED,
CAST(0.0 AS FLOAT) AS QUOTED1,
CAST(0.0 AS FLOAT) AS QUOTED2,
CAST(0.0 AS FLOAT) AS QUOTED3,
CAST(0.0 AS FLOAT) AS QUOTED4,
CAST(0.0 AS FLOAT) AS LTOT

INTO #FinalReport
FROM #temp
WHERE (CPRCSTNM = @Customer) OR (@Customer IS NULL) OR (@Customer='')

UPDATE #FinalReport
SET BILLED = CASE WHEN SOURCE = 'History' THEN OXTNDPRC ELSE 0 END

UPDATE #FinalReport
SET BILLED1 = CASE WHEN DOCDATE BETWEEN @STARTDATE1 AND @ENDDATE THEN BILLED ELSE 0 END,
BILLED2 = CASE WHEN DOCDATE >= @StartDate2 AND DOCDATE < @StartDate1 THEN BILLED ELSE 0 END,
BILLED3 = CASE WHEN DOCDATE >= @StartDate3 AND DOCDATE < @StartDate2 THEN BILLED ELSE 0 END,
BILLED4 = CASE WHEN DOCDATE >= @StartDate4 AND DOCDATE < @StartDate3 THEN BILLED ELSE 0 END

UPDATE #FinalReport
SET CENSUS = CASE WHEN SOURCE='History' THEN Quantity ELSE 0 END

UPDATE #FinalReport
SET CENSUS1 = CASE WHEN DOCDATE BETWEEN @StartDate1 AND @EndDate THEN CENSUS ELSE 0 END,
CENSUS2 = CASE WHEN DOCDATE >= @StartDate2 AND DOCDATE < @StartDate1 THEN CENSUS ELSE 0 END,
CENSUS3 = CASE WHEN DOCDATE >= @StartDate3 AND DOCDATE < @StartDate2 THEN CENSUS ELSE 0 END,
CENSUS4 = CASE WHEN DOCDATE >= @StartDate4 AND DOCDATE < @StartDate3 THEN CENSUS ELSE 0 END

UPDATE #FinalReport
SET QUOTED = CASE WHEN SOURCE = 'current' THEN OXTNDPRC ELSE 0 END

UPDATE #FinalReport
SET QUOTED1 = CASE WHEN DOCDATE BETWEEN @STARTDATE1 AND @ENDDATE THEN QUOTED ELSE 0 END,
QUOTED2 = CASE WHEN DOCDATE >= @StartDate2 AND DOCDATE < @StartDate1 THEN QUOTED ELSE 0 END,
QUOTED3 = CASE WHEN DOCDATE >= @StartDate3 AND DOCDATE < @StartDate2 THEN QUOTED ELSE 0 END,
QUOTED4 = CASE WHEN DOCDATE >= @StartDate4 AND DOCDATE < @StartDate3 THEN QUOTED ELSE 0 END

--- SUMMARY FRO PPD Calculation
SELECT CUSTNAME,
CPRCSTNM,
MAX(CENSUS1) AS CENSUS1,
MAX(BILLED1) AS BILLED1,
MAX(CENSUS2) AS CENSUS2,
MAX(BILLED2) AS BILLED2,
MAX(CENSUS3) AS CENSUS3,
MAX(BILLED3) AS BILLED3,
MAX(CENSUS4) AS CENSUS4,
MAX(BILLED4) AS BILLED4,
SUM(QUOTED1) AS QUOTED1,
SUM(QUOTED2) AS QUOTED2,
SUM(QUOTED3) AS QUOTED3,
SUM(QUOTED4) AS QUOTED4,
CAST(0 AS FLOAT) AS PPD1,
CAST(0 AS FLOAT) AS PPD2,
CAST(0 AS FLOAT) AS PPD3,
CAST(0 AS FLOAT) AS PPD4,
CAST(0 AS FLOAT) AS LTOT

INTO #SUMMARY
FROM #FinalReport
GROUP BY CUSTNAME,
CPRCSTNM

UPDATE #SUMMARY
SET PPD1 = CASE WHEN CENSUS1 = 0 THEN 0 ELSE BILLED1/CENSUS1 END

UPDATE #SUMMARY
SET PPD2 = CASE WHEN CENSUS2 = 0 THEN 0 ELSE BILLED2/CENSUS2 END

UPDATE #SUMMARY
SET PPD3 = CASE WHEN CENSUS3 = 0 THEN 0 ELSE BILLED3/CENSUS3 END

UPDATE #SUMMARY
SET PPD4 = CASE WHEN CENSUS4=0 THEN 0 ELSE BILLED4/CENSUS4 END

UPDATE #SUMMARY
SET LTOT = QUOTED1 + QUOTED2 + QUOTED3 + QUOTED4 + BILLED1 + BILLED2 + BILLED3 + BILLED4

SELECT * FROM #SUMMARY

END

GO

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-07-24 : 12:18:08

[code]
CREATE PROCEDURE [dbo].[spu_CRSS_Sectionly]
@STARTDATE DATETIME,
@ENDDATE DATETIME,
@Customer VARCHAR(20)
AS

SET NOCOUNT ON

DECLARE @STARTDATE1 DATETIME
DECLARE @STARTDATE2 DATETIME
DECLARE @STARTDATE3 DATETIME
DECLARE @STARTDATE4 DATETIME

--- SET STARTDATES
SET @STARTDATE1 = DateAdd (m , -1 , @EndDate +1)
SET @STARTDATE2 = DateAdd (m , -2 , @EndDate +1 )
SET @STARTDATE3 = DateAdd (m , -3 , @EndDate +1 )
SET @STARTDATE4 = DateAdd (m , -4 , @EndDate +1 )

SELECT IV00101.ITMGEDSC,
IV00101.ITEMDESC,
RM00101.CUSTNAME,
RM00101.CPRCSTNM,
IV40600.UserCatLongDescr,
CATS.UserCatLongDescr AS Expr1,
SOP10200.ITEMNMBR,
SOP10200.SOPNUMBE,
SOP10200.QUANTITY,
SOP10200.OXTNDPRC,
SOP10200.SOPTYPE,
SOP10100.DOCDATE,
'Current' AS source
INTO #temp

FROM sop10200 AS SOP10200
INNER JOIN
iv00101 AS IV00101 ON SOP10200.ITEMNMBR = IV00101.ITEMNMBR
INNER JOIN
sop10100 AS SOP10100
ON SOP10200.SOPTYPE = SOP10100.SOPTYPE
AND SOP10200.SOPNUMBE = SOP10100.SOPNUMBE
INNER JOIN
iv40600 AS IV40600
ON IV00101.ITMGEDSC = IV40600.USCATVAL
INNER JOIN
iv40600 AS CATS
ON IV00101.USCATVLS_2 = CATS.USCATVAL
INNER JOIN
rm00101 AS RM00101
ON SOP10100.CUSTNMBR = RM00101.CUSTNMBR
WHERE (DOCDATE BETWEEN @StartDate4 AND @EndDate)
OR
(DOCDATE BETWEEN @StartDate AND @EndDate)
UNION ALL
SELECT IV00101.ITMGEDSC,
IV00101.ITEMDESC,
RM00101.CUSTNAME,
RM00101.CPRCSTNM,
IV40600.UserCatLongDescr,
CATS.UserCatLongDescr AS Expr1,
SOP30300.ITEMNMBR,
SOP30300.SOPNUMBE,
SOP30300.QUANTITY,
SOP30300.OXTNDPRC,
SOP30300.SOPTYPE,
SOP30200.DOCDATE,
'History' AS source
FROM sop30300 AS SOP30300
LEFT OUTER JOIN
iv00101 AS IV00101
ON SOP30300.ITEMNMBR = IV00101.ITEMNMBR
INNER JOIN
sop30200 AS SOP30200
ON SOP30300.SOPTYPE = SOP30200.SOPTYPE
AND SOP30300.SOPNUMBE = SOP30200.SOPNUMBE
LEFT OUTER JOIN
iv40600 AS IV40600
ON IV00101.ITMGEDSC = IV40600.USCATVAL
LEFT OUTER JOIN
iv40600 AS CATS
ON IV00101.USCATVLS_2 = CATS.USCATVAL
INNER JOIN
rm00101 AS RM00101
ON SOP30200.CUSTNMBR = RM00101.CUSTNMBR
WHERE (DOCDATE BETWEEN @StartDate4 AND @EndDate)
OR
(DOCDATE BETWEEN @StartDate AND @EndDate)

SELECT
CustName,
ItemDesc,
CAST(OXTNDPRC AS FLOAT) AS OXTNDPRC,
CAST(Quantity AS FLOAT) AS QUANTITY,
DOCDATE,
SOURCE,
ITEMNMBR,
CPRCSTNM,
CAST(0.0 AS FLOAT) AS BILLED,
CAST(0.0 AS FLOAT) AS BILLED1,
CAST(0.0 AS FLOAT) AS BILLED2,
CAST(0.0 AS FLOAT) AS BILLED3,
CAST(0.0 AS FLOAT) AS BILLED4,
CAST(0.0 AS FLOAT) AS CENSUS,
CAST(0.0 AS FLOAT) AS CENSUS1,
CAST(0.0 AS FLOAT) AS CENSUS2,
CAST(0.0 AS FLOAT) AS CENSUS3,
CAST(0.0 AS FLOAT) AS CENSUS4,
CAST(0.0 AS FLOAT) AS PPD1,
CAST(0.0 AS FLOAT) AS PPD2,
CAST(0.0 AS FLOAT) AS PPD3,
CAST(0.0 AS FLOAT) AS PPD4,
CAST(0.0 AS FLOAT) AS QUOTED,
CAST(0.0 AS FLOAT) AS QUOTED1,
CAST(0.0 AS FLOAT) AS QUOTED2,
CAST(0.0 AS FLOAT) AS QUOTED3,
CAST(0.0 AS FLOAT) AS QUOTED4,
CAST(0.0 AS FLOAT) AS LTOT
INTO #FinalReport
FROM #temp
WHERE (CPRCSTNM = @Customer) OR (@Customer IS NULL) OR (@Customer='')

UPDATE #FinalReport
SET BILLED = CASE WHEN SOURCE = 'History' THEN OXTNDPRC ELSE 0 END

UPDATE #FinalReport
SET BILLED1 = CASE WHEN DOCDATE BETWEEN @STARTDATE1 AND @ENDDATE THEN BILLED ELSE 0 END,
BILLED2 = CASE WHEN DOCDATE >= @StartDate2 AND DOCDATE < @StartDate1 THEN BILLED ELSE 0 END,
BILLED3 = CASE WHEN DOCDATE >= @StartDate3 AND DOCDATE < @StartDate2 THEN BILLED ELSE 0 END,
BILLED4 = CASE WHEN DOCDATE >= @StartDate4 AND DOCDATE < @StartDate3 THEN BILLED ELSE 0 END

UPDATE #FinalReport
SET CENSUS = CASE WHEN SOURCE='History' THEN Quantity ELSE 0 END

UPDATE #FinalReport
SET CENSUS1 = CASE WHEN DOCDATE BETWEEN @StartDate1 AND @EndDate THEN CENSUS ELSE 0 END,
CENSUS2 = CASE WHEN DOCDATE >= @StartDate2 AND DOCDATE < @StartDate1 THEN CENSUS ELSE 0 END,
CENSUS3 = CASE WHEN DOCDATE >= @StartDate3 AND DOCDATE < @StartDate2 THEN CENSUS ELSE 0 END,
CENSUS4 = CASE WHEN DOCDATE >= @StartDate4 AND DOCDATE < @StartDate3 THEN CENSUS ELSE 0 END

UPDATE #FinalReport
SET QUOTED = CASE WHEN SOURCE = 'current' THEN OXTNDPRC ELSE 0 END

UPDATE #FinalReport
SET QUOTED1 = CASE WHEN DOCDATE BETWEEN @STARTDATE1 AND @ENDDATE THEN QUOTED ELSE 0 END,
QUOTED2 = CASE WHEN DOCDATE >= @StartDate2 AND DOCDATE < @StartDate1 THEN QUOTED ELSE 0 END,
QUOTED3 = CASE WHEN DOCDATE >= @StartDate3 AND DOCDATE < @StartDate2 THEN QUOTED ELSE 0 END,
QUOTED4 = CASE WHEN DOCDATE >= @StartDate4 AND DOCDATE < @StartDate3 THEN QUOTED ELSE 0 END

--- SUMMARY FRO PPD Calculation
SELECT CUSTNAME,
CPRCSTNM,
MAX(CENSUS1) AS CENSUS1,
MAX(BILLED1) AS BILLED1,
MAX(CENSUS2) AS CENSUS2,
MAX(BILLED2) AS BILLED2,
MAX(CENSUS3) AS CENSUS3,
MAX(BILLED3) AS BILLED3,
MAX(CENSUS4) AS CENSUS4,
MAX(BILLED4) AS BILLED4,
SUM(QUOTED1) AS QUOTED1,
SUM(QUOTED2) AS QUOTED2,
SUM(QUOTED3) AS QUOTED3,
SUM(QUOTED4) AS QUOTED4,
CAST(0 AS FLOAT) AS PPD1,
CAST(0 AS FLOAT) AS PPD2,
CAST(0 AS FLOAT) AS PPD3,
CAST(0 AS FLOAT) AS PPD4,
CAST(0 AS FLOAT) AS LTOT
INTO #SUMMARY
FROM #FinalReport
GROUP BY
CUSTNAME, CPRCSTNM

UPDATE #SUMMARY
SET PPD1 = CASE WHEN CENSUS1 = 0 THEN 0 ELSE BILLED1/CENSUS1 END

UPDATE #SUMMARY
SET PPD2 = CASE WHEN CENSUS2 = 0 THEN 0 ELSE BILLED2/CENSUS2 END

UPDATE #SUMMARY
SET PPD3 = CASE WHEN CENSUS3 = 0 THEN 0 ELSE BILLED3/CENSUS3 END

UPDATE #SUMMARY
SET PPD4 = CASE WHEN CENSUS4=0 THEN 0 ELSE BILLED4/CENSUS4 END

UPDATE #SUMMARY
SET LTOT = QUOTED1 + QUOTED2 + QUOTED3 + QUOTED4 + BILLED1 + BILLED2 + BILLED3 + BILLED4

SELECT * FROM #SUMMARY

DROP TABLE #temp
DROP TABLE #FinalReport
DROP TABLE #SUMMARY
GO

[/code]

Adam West
Constraint Violating Yak Guru

261 Posts

Posted - 2009-07-24 : 13:57:39

Thank you Russel! that compiles successfully.

I was looking for the result object from it after executing that code, but I do not see it.

Adam West
Constraint Violating Yak Guru

261 Posts

Posted - 2009-07-24 : 14:56:32

If i try to recompile it I get this error message:

Msg 2714, Level 16, State 3, Procedure spu_CRSS_Sectionly, Line 196
There is already an object named 'spu_CRSS_Sectionly' in the database.

So then, where it is?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-07-24 : 15:25:12

change CREATE PROCEDURE to ALTER PROCEDURE

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-07-24 : 15:27:11

quote:
Originally posted by Adam West

Thank you Russel! that compiles successfully.

I was looking for the result object from it after executing that code, but I do not see it.

all you did was created it. to execute it:

EXEC spu_CRSS_Sely @STARTDATE = 'date here', @ENDDATE 'date here', @Customer = 'customer here'

Adam West
Constraint Violating Yak Guru

261 Posts

Posted - 2009-07-24 : 16:27:42

This is true, but I was just expecting to see the source somewhere in the database. The source exists, in a file. I guess it doesn't matter as long as I can refer to this in a Report.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources