Insert Into Stored Proc Problems

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

Insert Into Stored Proc Problems

Author

Topic

Jennifer_Ann_28
Starting Member

6 Posts

Posted - 2007-08-21 : 11:48:52

Insert into Stored Proc

--------------------------------------------------------------------------------

I can't seem to get this stored proc to run properly. It won't let me insert records into it. Any suggestions? Can someone tell me if something doesn't look right? I've been over this a million times!

CREATE PROCEDURE TEST_PROC

/*
My Name
*/

AS

BEGIN

SET NOCOUNT ON

DECLARE @MAVG2003 AS MONEY

SET @MAVG2003 = (SELECT AVG(PRICE) AS AVERAGE
FROM ASSESSMENT.DBO.CUSTOMER C WITH (NOLOCK)
LEFT JOIN ASSESSMENT.DBO.ORDER)HEADER OH WITH (NOLOCK)
ON OH.CUSTOMER_ID = C.CUSTOMER_ID
LEFT JOIN ASSESSMENT.DBO.ORDER_DETAIL OD WITH (NOLOCK)
ON OD.ORDER_HEADER_ID = OH.ORDER_HEADER_ID
LEFT JOIN ASSESSMENT.DBO.PRODUCTS P WITH (NOLOCK)
ON P.PRODUCT_ID = OD.PRODUCT_ID
WHERE DATEPART(YYYY, OH.ORDER_DATE) = '2003')

CREATE TABLE #FORECAST(
CUSTOMER_ID INT,
FIRST_NAME VARCHAR(20),
LAST_NAME VARCHAR(20),
TOTAL_2003 MONEY,
FORECAST_2004 MONEY,
FORECAST_2005 MONEY)

INSERT INTO #FORECAST
SELECT C.CUSTOMER_ID,
C.FIRST_NAME,
C.LAST_NAME,
TOTAL_2003 SUM(P.PRICE) AS TOTALYEAR,
0,
0
FROM ASSESSMENT.DBO.CUSTOMER C WITH (NOLOCK)
LEFT JOIN ASSESSMENT.DBO.ORDER)HEADER OH WITH (NOLOCK)
ON OH.CUSTOMER_ID = C.CUSTOMER_ID
LEFT JOIN ASSESSMENT.DBO.ORDER_DETAIL OD WITH (NOLOCK)
ON OD.ORDER_HEADER_ID = OH.ORDER_HEADER_ID
LEFT JOIN ASSESSMENT.DBO.PRODUCTS P WITH (NOLOCK)
ON P.PRODUCT_ID = OD.PRODUCT_ID
WHERE DATEPART(YYYY, OH.ORDER_DATE) = '2003'
GROUP BY C.CUSTOMER_ID

UPDATE #FORECAST
SET FORECAST_2004 = TOTAL_2003 * (TOTAL_2003 / @MAVG2003)

UPDATE #FORECAST
SET FORECAST_2005 = FORECAST_2004 + (FORECAST_2004 * .0105)

SELECT CUSTOMER_ID, TOTAL_2003, FORECAST_2004, FORECAST_2005
FROM #FORECAST
ORDER BY TOTAL_2003 DESC

DROP TABLE #FORECAST

RETURN 0
END

GO

X002548
Not Just a Number

15586 Posts

Posted - 2007-08-21 : 12:15:44

First, lose all of the nolock hints

Second, what's this

LEFT JOIN ASSESSMENT.DBO.ORDER)HEADER OH

?????

You also have an incosistent grouping on the insert

Also, there is no need to do everything you are doing, you can just do a single SELECT by combining all of those components...

For us to really help we will need DDL and sample data and expected results

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-08-21 : 12:15:54

Are you getting an error? Or is it just not returning what you expect?

How about running the code outside of the stored proc to make this easier to troubleshoot:

SET NOCOUNT ON

DECLARE @MAVG2003 AS MONEY

SET @MAVG2003 = (SELECT AVG(PRICE) AS AVERAGE
FROM ASSESSMENT.DBO.CUSTOMER C WITH (NOLOCK)
LEFT JOIN ASSESSMENT.DBO.ORDER)HEADER OH WITH (NOLOCK)
ON OH.CUSTOMER_ID = C.CUSTOMER_ID
LEFT JOIN ASSESSMENT.DBO.ORDER_DETAIL OD WITH (NOLOCK)
ON OD.ORDER_HEADER_ID = OH.ORDER_HEADER_ID
LEFT JOIN ASSESSMENT.DBO.PRODUCTS P WITH (NOLOCK)
ON P.PRODUCT_ID = OD.PRODUCT_ID
WHERE DATEPART(YYYY, OH.ORDER_DATE) = '2003')

CREATE TABLE #FORECAST(
CUSTOMER_ID INT,
FIRST_NAME VARCHAR(20),
LAST_NAME VARCHAR(20),
TOTAL_2003 MONEY,
FORECAST_2004 MONEY,
FORECAST_2005 MONEY)

INSERT INTO #FORECAST
SELECT C.CUSTOMER_ID,
C.FIRST_NAME,
C.LAST_NAME,
TOTAL_2003 SUM(P.PRICE) AS TOTALYEAR,
0,
0
FROM ASSESSMENT.DBO.CUSTOMER C WITH (NOLOCK)
LEFT JOIN ASSESSMENT.DBO.ORDER)HEADER OH WITH (NOLOCK)
ON OH.CUSTOMER_ID = C.CUSTOMER_ID
LEFT JOIN ASSESSMENT.DBO.ORDER_DETAIL OD WITH (NOLOCK)
ON OD.ORDER_HEADER_ID = OH.ORDER_HEADER_ID
LEFT JOIN ASSESSMENT.DBO.PRODUCTS P WITH (NOLOCK)
ON P.PRODUCT_ID = OD.PRODUCT_ID
WHERE DATEPART(YYYY, OH.ORDER_DATE) = '2003'
GROUP BY C.CUSTOMER_ID

UPDATE #FORECAST
SET FORECAST_2004 = TOTAL_2003 * (TOTAL_2003 / @MAVG2003)

UPDATE #FORECAST
SET FORECAST_2005 = FORECAST_2004 + (FORECAST_2004 * .0105)

SELECT CUSTOMER_ID, TOTAL_2003, FORECAST_2004, FORECAST_2005
FROM #FORECAST
ORDER BY TOTAL_2003 DESC

DROP TABLE #FORECAST

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Jennifer_Ann_28
Starting Member

6 Posts

Posted - 2007-08-21 : 12:18:29

I'm not getting an error, but it doesn't seem to be returning any results. Also, does the WITH(nolock) make a difference? I thought this was just good practice to have those.

Jennifer_Ann_28
Starting Member

6 Posts

Posted - 2007-08-21 : 12:19:44

AHHHH!!!! This

LEFT JOIN ASSESSMENT.DBO.ORDER)HEADER OH

should be

LEFT JOIN ASSESSMENT.DBO.ORDER_HEADER OH

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-08-21 : 12:21:57

Removing the hints won't solve your problem, but you probably shouldn't have them in there. You should only put them in there if dirty reads are okay. You need to understand what it does before using them.

Take each statement in your code one statement at a time to figure out what is going on.

1. Does this return the correct value:

SET @MAVG2003 = (SELECT AVG(PRICE) AS AVERAGE
FROM ASSESSMENT.DBO.CUSTOMER C WITH (NOLOCK)
LEFT JOIN ASSESSMENT.DBO.ORDER)HEADER OH WITH (NOLOCK)
ON OH.CUSTOMER_ID = C.CUSTOMER_ID
LEFT JOIN ASSESSMENT.DBO.ORDER_DETAIL OD WITH (NOLOCK)
ON OD.ORDER_HEADER_ID = OH.ORDER_HEADER_ID
LEFT JOIN ASSESSMENT.DBO.PRODUCTS P WITH (NOLOCK)
ON P.PRODUCT_ID = OD.PRODUCT_ID
WHERE DATEPART(YYYY, OH.ORDER_DATE) = '2003')

PRINT @MAVG2003

2. Does this insert the correct rows:

CREATE TABLE #FORECAST(
CUSTOMER_ID INT,
FIRST_NAME VARCHAR(20),
LAST_NAME VARCHAR(20),
TOTAL_2003 MONEY,
FORECAST_2004 MONEY,
FORECAST_2005 MONEY)

INSERT INTO #FORECAST
SELECT C.CUSTOMER_ID,
C.FIRST_NAME,
C.LAST_NAME,
TOTAL_2003 SUM(P.PRICE) AS TOTALYEAR,
0,
0
FROM ASSESSMENT.DBO.CUSTOMER C WITH (NOLOCK)
LEFT JOIN ASSESSMENT.DBO.ORDER)HEADER OH WITH (NOLOCK)
ON OH.CUSTOMER_ID = C.CUSTOMER_ID
LEFT JOIN ASSESSMENT.DBO.ORDER_DETAIL OD WITH (NOLOCK)
ON OD.ORDER_HEADER_ID = OH.ORDER_HEADER_ID
LEFT JOIN ASSESSMENT.DBO.PRODUCTS P WITH (NOLOCK)
ON P.PRODUCT_ID = OD.PRODUCT_ID
WHERE DATEPART(YYYY, OH.ORDER_DATE) = '2003'
GROUP BY C.CUSTOMER_ID

SELECT * FROM #FORECAST

3. ...

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-08-21 : 12:22:43

quote:
Originally posted by Jennifer_Ann_28

AHHHH!!!! This

LEFT JOIN ASSESSMENT.DBO.ORDER)HEADER OH

should be

LEFT JOIN ASSESSMENT.DBO.ORDER_HEADER OH

I don't understand why you weren't getting a syntax error then.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Jennifer_Ann_28
Starting Member

6 Posts

Posted - 2007-08-21 : 12:23:36

And I'm getting this error below when I run this section of the code on this line

TOTAL_2003 SUM(P.PRICE) AS TOTALYEAR,

Msg 170, Level 15, State 1, Line 32
Line 32: Incorrect syntax near 'P'.

INSERT INTO #FORECAST
SELECT C.CUSTOMER_ID,
C.FIRST_NAME,
C.LAST_NAME,
TOTAL_2003 SUM(P.PRICE) AS TOTALYEAR,
0,
0
FROM ASSESSMENT.DBO.CUSTOMER C WITH (NOLOCK)
LEFT JOIN ASSESSMENT.DBO.ORDER)HEADER OH WITH (NOLOCK)
ON OH.CUSTOMER_ID = C.CUSTOMER_ID
LEFT JOIN ASSESSMENT.DBO.ORDER_DETAIL OD WITH (NOLOCK)
ON OD.ORDER_HEADER_ID = OH.ORDER_HEADER_ID
LEFT JOIN ASSESSMENT.DBO.PRODUCTS P WITH (NOLOCK)
ON P.PRODUCT_ID = OD.PRODUCT_ID
WHERE DATEPART(YYYY, OH.ORDER_DATE) = '2003'
GROUP BY C.CUSTOMER_ID

Jennifer_Ann_28
Starting Member

6 Posts

Posted - 2007-08-21 : 12:36:22

Thank you both for taking the time to look at this. tkizer, I did just as you suggested, and it seems something is wrong with my insert statements. However, I don't understand what X00 means by the inserts being inconsistant.

X002548
Not Just a Number

15586 Posts

Posted - 2007-08-21 : 12:44:57

GROUPING, the GROUPING is inconsistent

Also, what is this suppose to be?

TOTAL_2003 SUM(P.PRICE) AS TOTALYEAR,

Do you know how to script the table DDL?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Jennifer_Ann_28
Starting Member

6 Posts

Posted - 2007-08-21 : 12:47:01

The total price of their orders for 2003. then 2004 and 2005 are the forecasts

X002548
Not Just a Number

15586 Posts

Posted - 2007-08-21 : 12:57:36

that's not a valid syntax, are you missing a comma?

Also the grouping must contain the names if they are part of the select

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

X002548
Not Just a Number

15586 Posts

Posted - 2007-08-21 : 13:04:44

This kind of what you really have...the part in red doesn't make sense because of the grouping..but see how you should be able to eliminate the temp table...now we just gotta make it make some sense


CREATE PROCEDURE TEST_PROC
	@error int OUTPUT, @rowcount int OUTPUT
AS

BEGIN
SET NOCOUNT ON

DECLARE @MAVG2003 AS MONEY, @error int, @rowcount int

SET @MAVG2003 = (
         SELECT AVG(PRICE) AS AVERAGE
	   FROM ASSESSMENT.DBO.CUSTOMER 		C
      LEFT JOIN ASSESSMENT.DBO.ORDER_HEADER 		OH
	     ON OH.CUSTOMER_ID = C.CUSTOMER_ID
      LEFT JOIN ASSESSMENT.DBO.ORDER_DETAIL 		OD
	     ON OD.ORDER_HEADER_ID = OH.ORDER_HEADER_ID 
      LEFT JOIN ASSESSMENT.DBO.PRODUCTS 		P
	     ON P.PRODUCT_ID = OD.PRODUCT_ID 
	  WHERE DATEPART(YYYY, OH.ORDER_DATE) = '2003')

   SELECT C.CUSTOMER_ID
	, C.FIRST_NAME
	, C.LAST_NAME
	, TOTAL_2003
	,  SUM(P.PRICE) AS TOTALYEAR
	, TOTAL_2003 * (TOTAL_2003 /@MAVG2003) AS FORECAST_2004 
	, TOTAL_2003 * (TOTAL_2003 /@MAVG2003) + (TOTAL_2003 * (TOTAL_2003 /@MAVG2003) * .0105) AS FORECAST_2005      
FROM ASSESSMENT.DBO.CUSTOMER C
 LEFT JOIN ASSESSMENT.DBO.ORDER_HEADER OH
	ON OH.CUSTOMER_ID = C.CUSTOMER_ID
 LEFT JOIN ASSESSMENT.DBO.ORDER_DETAIL OD
	ON OD.ORDER_HEADER_ID = OH.ORDER_HEADER_ID 
 LEFT JOIN ASSESSMENT.DBO.PRODUCTS P
	ON P.PRODUCT_ID = OD.PRODUCT_ID 
     WHERE DATEPART(YYYY, OH.ORDER_DATE) = '2003'
  GROUP BY C.CUSTOMER_ID
SELECT @error = @@Error, @rowcount = @@Rowcount

SET NOCOUNT OFF
RETURN
END
GO

DECLARE @error int, @rowcount int
EXEC TEST_PROC @error OUTPUT, @rowcount OUTPUT
SELECT @error, @rowcount
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources