Stored Procedure failing, but why? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Stored Procedure failing, but why?

Author

Topic

Adam West
Constraint Violating Yak Guru

261 Posts

Posted - 2009-08-05 : 12:53:10

I am getting a failure when I try to create this as a stored proc. the failure is on the last select "Msg 102, Level 15, State 1, Procedure spu_CRSS_Quotes_Week_Suppl01, Line 27
Incorrect syntax near 'IV40600'." This table does exist along with the fields.

SELECT SOP10100.DOCDATE, SOP10200.XTNDPRCE, SOP10200.ITEMNMBR, SOP10200.QUANTITY, IV00101.ITEMDESC,
SOP10100.SOPNUMBE, SOP10200.SOPTYPE, SOP10200.SOPNUMBE, IV00101.ITMGEDSC, SOP10100.CUSTNMBR
FROM (SUPPL.dbo.SOP10200 SOP10200 INNER JOIN SUPPL.dbo.IV00101 IV00101 ON SOP10200.ITEMNMBR=IV00101.ITEMNMBR)
INNER JOIN SUPPL.dbo.SOP10100 SOP10100 ON (SOP10200.SOPTYPE=SOP10100.SOPTYPE) AND (SOP10200.SOPNUMBE=SOP10100.SOPNUMBE)

where sop10100.docdate between @Startdate and @enddate

SELECT SOP10106.USRDEF05, SOP10106.SOPNUMBE, SOP10106.SOPTYPE
FROM SUPPL.dbo.SOP10106 SOP10106

SELECT IV40600.UserCatLongDescr, RM00101.CUSTNAME, RM00101.CUSTNMBR, IV40600.USCATVAL
FROM SUPPL.dbo.RM00101 RM00101 CROSS JOIN SUPPL.dbo.IV40600 IV40600

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-08-05 : 13:05:13

no syntax error there. let's see the entire SP

X002548
Not Just a Number

15586 Posts

Posted - 2009-08-05 : 13:12:49

Can you post the whole sproc with code tags?

[ code]

[ /code]

With out the leading space?

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

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-08-05 : 13:22:41

Where is the procedure "spu_CRSS_Quotes_Week_Suppl01" located? Are you not showing the entire stored procedure or is that function in a view that you are referencing?

Adam West
Constraint Violating Yak Guru

261 Posts

Posted - 2009-08-05 : 13:28:39

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

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

--- SET STARTDATES

SELECT SOP10100.DOCDATE, SOP10200.XTNDPRCE, SOP10200.ITEMNMBR, SOP10200.QUANTITY, IV00101.ITEMDESC,
SOP10100.SOPNUMBE, SOP10200.SOPTYPE, SOP10200.SOPNUMBE, IV00101.ITMGEDSC, SOP10100.CUSTNMBR
FROM (SUPPL.dbo.SOP10200 SOP10200 INNER JOIN SUPPL.dbo.IV00101 IV00101 ON SOP10200.ITEMNMBR=IV00101.ITEMNMBR)
INNER JOIN SUPPL.dbo.SOP10100 SOP10100 ON (SOP10200.SOPTYPE=SOP10100.SOPTYPE) AND (SOP10200.SOPNUMBE=SOP10100.SOPNUMBE)

where sop10100.docdate between @Startdate and @enddate

SELECT SOP10106.USRDEF05, SOP10106.SOPNUMBE, SOP10106.SOPTYPE
FROM SUPPL.dbo.SOP10106 SOP10106

SELECT IV40600.UserCatLongDescr, RM00101.CUSTNAME, RM00101.CUSTNMBR, IV40600.USCATVAL
FROM SUPPL.dbo.RM00101 RM00101 CROSS JOIN SUPPL.dbo.IV40600 IV40600

X002548
Not Just a Number

15586 Posts

Posted - 2009-08-05 : 14:29:42

so you really want 3 different result sets?

Can you post the table DDL so we can try it?

What version are you on?

You can script it out from SSMS or EM

EDIT: Gotta tell ya...I'm not liking those column or table names

EDIT2: Is this code generated?

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

Adam West
Constraint Violating Yak Guru

261 Posts

Posted - 2009-08-05 : 15:47:49

I am not that sure about this SQL. I got it from a Crystal Reports that i have to convert to Reporting services. They have something there called ShowSQL, that I can take and put it into Report Manager. But this is not getting me an understanding of what the developer was doing really, as you rightfully point out.

So the three selects are based on what she was doing in the Crystal Reports.

It appears for now that the stored proc is not my main problem. OK we are version 2005.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources