Help Required of Stored Procedure  
Author Message
ðÁÛÁ





PostPosted: Tue Jul 06 06:55:32 CDT 2004 Top

SQL Server Developer >> Help Required of Stored Procedure

Hi,

I'm facing a strange kind of a problem. I'm generating an SQL query consist on many sub-queries in my

stored procedure from the supplied parameters. It compiles successfully but when I try to excute it

using Query Analyzer, I get the following error:

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'M'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'F'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'O'.
Stored Procedure: library_management_system.dbo.RptSbjtStat
Return Code = 0

But if I get the query as an output in a string as given below:

SELECT DISTINCT s.SubjectTitle AS "Subject Title",
(SELECT COUNT(*) FROM (SELECT DISTINCT sm.SubjectTitle, lim.LibraryItemID, lim.LibraryItemTitle,

litm.LibraryItemIssueDate FROM LibraryItemTransactions litm INNER JOIN LibraryItems lim ON

litm.LibraryItemID = lim.LibraryItemID INNER JOIN LibraryItemsubHeadings lishm ON lim.LibraryItemID =

lishm.LibraryItemID INNER JOIN SubSubjects ssm ON lishm.subsubjectid = ssm.subsubjectid INNER JOIN

Subjects sm ON ssm.subjectid = sm.subjectid WHERE sm.SubjectTitle = s.SubjectTitle AND

litm.LibraryItemReceivedByGender = 'Male' AND litm.LibraryItemIssueDate BETWEEN '01/01/2000' AND

'01/01/2005') AS "M") AS "Male",
(SELECT COUNT(*) FROM (SELECT DISTINCT sm.SubjectTitle, lim.LibraryItemID, lim.LibraryItemTitle,

litm.LibraryItemIssueDate FROM LibraryItemTransactions litm INNER JOIN LibraryItems lim ON

litm.LibraryItemID = lim.LibraryItemID INNER JOIN LibraryItemsubHeadings lishm ON lim.LibraryItemID =

lishm.LibraryItemID INNER JOIN SubSubjects ssm ON lishm.subsubjectid = ssm.subsubjectid INNER JOIN

Subjects sm ON ssm.subjectid = sm.subjectid WHERE sm.SubjectTitle = s.SubjectTitle AND

litm.LibraryItemReceivedByGender = 'Female' AND litm.LibraryItemIssueDate BETWEEN '01/01/2000' AND

'01/01/2005')AS "F") AS "Female",
(SELECT COUNT(*) FROM (SELECT DISTINCT sm.SubjectTitle, lim.LibraryItemID, lim.LibraryItemTitle,

litm.LibraryItemIssueDate FROM LibraryItemTransactions litm INNER JOIN LibraryItems lim ON

litm.LibraryItemID = lim.LibraryItemID INNER JOIN LibraryItemsubHeadings lishm ON lim.LibraryItemID =

lishm.LibraryItemID INNER JOIN SubSubjects ssm ON lishm.subsubjectid = ssm.subsubjectid INNER JOIN

Subjects sm ON ssm.subjectid = sm.subjectid WHERE sm.SubjectTitle = s.SubjectTitle AND

litm.LibraryItemIssueDate BETWEEN '01/01/2000' AND '01/01/2005') AS "O") AS "Overall"
FROM LibraryItemTransactions lit INNER JOIN LibraryItems li ON lit.LibraryItemID = li.LibraryItemID

INNER JOIN LibraryItemsubHeadings lish ON li.LibraryItemID = lish.LibraryItemID INNER JOIN

SubSubjects ss ON lish.subsubjectid = ss.subsubjectid INNER JOIN Subjects s ON ss.subjectid =

s.subjectid

When I excute it, it runs perfectly and give the desired output that is:

Information Technology 2 1 3
Languages 0 1 1
Maths 1 0 1

Here is the code of my Stored Procedure:

CREATE PROCEDURE RptSbjtStat
(@IssueFromDate [varchar](50),
@IssueToDate [varchar](50))
AS

------------------------------------------------------------------------
-- Generating Query for Total Number of Males
------------------------------------------------------------------------



lim.LibraryItemTitle, litm.LibraryItemIssueDate FROM LibraryItemTransactions litm INNER JOIN

LibraryItems lim ON litm.LibraryItemID = lim.LibraryItemID INNER JOIN LibraryItemsubHeadings lishm ON

lim.LibraryItemID = lishm.LibraryItemID INNER JOIN SubSubjects ssm ON lishm.subsubjectid =

ssm.subsubjectid INNER JOIN Subjects sm ON ssm.subjectid = sm.subjectid WHERE sm.SubjectTitle =

s.SubjectTitle AND litm.LibraryItemReceivedByGender = ''Male'' AND litm.LibraryItemIssueDate BETWEEN



--------------------------------------------------------------------------
-- Generating Query for Total Number of Female
--------------------------------------------------------------------------



lim.LibraryItemID, lim.LibraryItemTitle, litm.LibraryItemIssueDate FROM LibraryItemTransactions litm

INNER JOIN LibraryItems lim ON litm.LibraryItemID = lim.LibraryItemID INNER JOIN

LibraryItemsubHeadings lishm ON lim.LibraryItemID = lishm.LibraryItemID INNER JOIN SubSubjects ssm ON

lishm.subsubjectid = ssm.subsubjectid INNER JOIN Subjects sm ON ssm.subjectid = sm.subjectid WHERE

sm.SubjectTitle = s.SubjectTitle AND litm.LibraryItemReceivedByGender = ''Female'' AND



"Female"'

-----------------------------------------------------------------------------
-- Generating Query for Total Number of Issuance
-----------------------------------------------------------------------------



lim.LibraryItemID, lim.LibraryItemTitle, litm.LibraryItemIssueDate FROM LibraryItemTransactions litm

INNER JOIN LibraryItems lim ON litm.LibraryItemID = lim.LibraryItemID INNER JOIN

LibraryItemsubHeadings lishm ON lim.LibraryItemID = lishm.LibraryItemID INNER JOIN SubSubjects ssm ON

lishm.subsubjectid = ssm.subsubjectid INNER JOIN Subjects sm ON ssm.subjectid = sm.subjectid WHERE





----------------------------------------------------------------------------
-- Generating the Query from above Sub Queries
----------------------------------------------------------------------------






lit.LibraryItemID = li.LibraryItemID'


lish.LibraryItemID'


ss.subsubjectid'


-----------------------------------
-- Executing the Query
-----------------------------------

RETURN
GO

Any help will be highly apriciated.

Thanks/AKG

SQL Server75  
 
 
Jacco





PostPosted: Tue Jul 06 06:55:32 CDT 2004 Top

SQL Server Developer >> Help Required of Stored Procedure Looks like your stored procedure has been created with the option SET
QUOTED_IDENTIFIER OFF. It won't treat strings inside double quotes as object
names then. Recreating the procedure and preceding it with SET
QUOTED_IDENTIFIER ON will solve the problem I think.

--
Jacco Schalkwijk
SQL Server MVP




> Hi,
>
> I'm facing a strange kind of a problem. I'm generating an SQL query
consist on many sub-queries in my
>
> stored procedure from the supplied parameters. It compiles successfully
but when I try to excute it
>
> using Query Analyzer, I get the following error:
>
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 'M'.
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 'F'.
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 'O'.
> Stored Procedure: library_management_system.dbo.RptSbjtStat
> Return Code = 0
>
> But if I get the query as an output in a string as given below:
>
> SELECT DISTINCT s.SubjectTitle AS "Subject Title",
> (SELECT COUNT(*) FROM (SELECT DISTINCT sm.SubjectTitle,
lim.LibraryItemID, lim.LibraryItemTitle,
>
> litm.LibraryItemIssueDate FROM LibraryItemTransactions litm INNER JOIN
LibraryItems lim ON
>
> litm.LibraryItemID = lim.LibraryItemID INNER JOIN LibraryItemsubHeadings
lishm ON lim.LibraryItemID =
>
> lishm.LibraryItemID INNER JOIN SubSubjects ssm ON lishm.subsubjectid =
ssm.subsubjectid INNER JOIN
>
> Subjects sm ON ssm.subjectid = sm.subjectid WHERE sm.SubjectTitle =
s.SubjectTitle AND
>
> litm.LibraryItemReceivedByGender = 'Male' AND litm.LibraryItemIssueDate
BETWEEN '01/01/2000' AND
>
> '01/01/2005') AS "M") AS "Male",
> (SELECT COUNT(*) FROM (SELECT DISTINCT sm.SubjectTitle,
lim.LibraryItemID, lim.LibraryItemTitle,
>
> litm.LibraryItemIssueDate FROM LibraryItemTransactions litm INNER JOIN
LibraryItems lim ON
>
> litm.LibraryItemID = lim.LibraryItemID INNER JOIN LibraryItemsubHeadings
lishm ON lim.LibraryItemID =
>
> lishm.LibraryItemID INNER JOIN SubSubjects ssm ON lishm.subsubjectid =
ssm.subsubjectid INNER JOIN
>
> Subjects sm ON ssm.subjectid = sm.subjectid WHERE sm.SubjectTitle =
s.SubjectTitle AND
>
> litm.LibraryItemReceivedByGender = 'Female' AND litm.LibraryItemIssueDate
BETWEEN '01/01/2000' AND
>
> '01/01/2005')AS "F") AS "Female",
> (SELECT COUNT(*) FROM (SELECT DISTINCT sm.SubjectTitle,
lim.LibraryItemID, lim.LibraryItemTitle,
>
> litm.LibraryItemIssueDate FROM LibraryItemTransactions litm INNER JOIN
LibraryItems lim ON
>
> litm.LibraryItemID = lim.LibraryItemID INNER JOIN LibraryItemsubHeadings
lishm ON lim.LibraryItemID =
>
> lishm.LibraryItemID INNER JOIN SubSubjects ssm ON lishm.subsubjectid =
ssm.subsubjectid INNER JOIN
>
> Subjects sm ON ssm.subjectid = sm.subjectid WHERE sm.SubjectTitle =
s.SubjectTitle AND
>
> litm.LibraryItemIssueDate BETWEEN '01/01/2000' AND '01/01/2005') AS "O")
AS "Overall"
> FROM LibraryItemTransactions lit INNER JOIN LibraryItems li ON
lit.LibraryItemID = li.LibraryItemID
>
> INNER JOIN LibraryItemsubHeadings lish ON li.LibraryItemID =
lish.LibraryItemID INNER JOIN
>
> SubSubjects ss ON lish.subsubjectid = ss.subsubjectid INNER JOIN Subjects
s ON ss.subjectid =
>
> s.subjectid
>
> When I excute it, it runs perfectly and give the desired output that is:
>
> Information Technology 2 1 3
> Languages 0 1 1
> Maths 1 0 1
>
> Here is the code of my Stored Procedure:
>
> CREATE PROCEDURE RptSbjtStat


> AS
>
> ------------------------------------------------------------------------
> -- Generating Query for Total Number of Males
> ------------------------------------------------------------------------


lim.LibraryItemID,
>
> lim.LibraryItemTitle, litm.LibraryItemIssueDate FROM
LibraryItemTransactions litm INNER JOIN
>
> LibraryItems lim ON litm.LibraryItemID = lim.LibraryItemID INNER JOIN
LibraryItemsubHeadings lishm ON
>
> lim.LibraryItemID = lishm.LibraryItemID INNER JOIN SubSubjects ssm ON
lishm.subsubjectid =
>
> ssm.subsubjectid INNER JOIN Subjects sm ON ssm.subjectid = sm.subjectid
WHERE sm.SubjectTitle =
>
> s.SubjectTitle AND litm.LibraryItemReceivedByGender = ''Male'' AND
litm.LibraryItemIssueDate BETWEEN
>

"Male"'
>
> --------------------------------------------------------------------------
> -- Generating Query for Total Number of Female
> --------------------------------------------------------------------------


sm.SubjectTitle,
>
> lim.LibraryItemID, lim.LibraryItemTitle, litm.LibraryItemIssueDate FROM
LibraryItemTransactions litm
>
> INNER JOIN LibraryItems lim ON litm.LibraryItemID = lim.LibraryItemID
INNER JOIN
>
> LibraryItemsubHeadings lishm ON lim.LibraryItemID = lishm.LibraryItemID
INNER JOIN SubSubjects ssm ON
>
> lishm.subsubjectid = ssm.subsubjectid INNER JOIN Subjects sm ON
ssm.subjectid = sm.subjectid WHERE
>
> sm.SubjectTitle = s.SubjectTitle AND litm.LibraryItemReceivedByGender =
''Female'' AND
>

@IssueToDate + ''')AS "F") AS
>
> "Female"'
>
> --------------------------------------------------------------------------
---
> -- Generating Query for Total Number of Issuance
> --------------------------------------------------------------------------
---


sm.SubjectTitle,
>
> lim.LibraryItemID, lim.LibraryItemTitle, litm.LibraryItemIssueDate FROM
LibraryItemTransactions litm
>
> INNER JOIN LibraryItems lim ON litm.LibraryItemID = lim.LibraryItemID
INNER JOIN
>
> LibraryItemsubHeadings lishm ON lim.LibraryItemID = lishm.LibraryItemID
INNER JOIN SubSubjects ssm ON
>
> lishm.subsubjectid = ssm.subsubjectid INNER JOIN Subjects sm ON
ssm.subjectid = sm.subjectid WHERE
>
> sm.SubjectTitle = s.SubjectTitle AND litm.LibraryItemIssueDate BETWEEN '''
+ @IssueFromDate + ''' AND
>

>
> --------------------------------------------------------------------------
--
> -- Generating the Query from above Sub Queries
> --------------------------------------------------------------------------
--


@MaleQuery + ', ' +
>


LibraryItems li ON
>
> lit.LibraryItemID = li.LibraryItemID'

li.LibraryItemID =
>
> lish.LibraryItemID'

lish.subsubjectid =
>
> ss.subsubjectid'

s.subjectid'
>
> -----------------------------------
> -- Executing the Query
> -----------------------------------

> RETURN
> GO
>
> Any help will be highly apriciated.
>
> Thanks/AKG


 
 
Wayne





PostPosted: Tue Jul 06 08:16:18 CDT 2004 Top

SQL Server Developer >> Help Required of Stored Procedure It also looks like you are using double quotes... try changing them to
single quotes as well.

--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, C****te, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)

I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org



> Hi,
>
> I'm facing a strange kind of a problem. I'm generating an SQL query
consist on many sub-queries in my
>
> stored procedure from the supplied parameters. It compiles successfully
but when I try to excute it
>
> using Query Analyzer, I get the following error:
>
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 'M'.
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 'F'.
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 'O'.
> Stored Procedure: library_management_system.dbo.RptSbjtStat
> Return Code = 0
>
> But if I get the query as an output in a string as given below:
>
> SELECT DISTINCT s.SubjectTitle AS "Subject Title",
> (SELECT COUNT(*) FROM (SELECT DISTINCT sm.SubjectTitle,
lim.LibraryItemID, lim.LibraryItemTitle,
>
> litm.LibraryItemIssueDate FROM LibraryItemTransactions litm INNER JOIN
LibraryItems lim ON
>
> litm.LibraryItemID = lim.LibraryItemID INNER JOIN LibraryItemsubHeadings
lishm ON lim.LibraryItemID =
>
> lishm.LibraryItemID INNER JOIN SubSubjects ssm ON lishm.subsubjectid =
ssm.subsubjectid INNER JOIN
>
> Subjects sm ON ssm.subjectid = sm.subjectid WHERE sm.SubjectTitle =
s.SubjectTitle AND
>
> litm.LibraryItemReceivedByGender = 'Male' AND litm.LibraryItemIssueDate
BETWEEN '01/01/2000' AND
>
> '01/01/2005') AS "M") AS "Male",
> (SELECT COUNT(*) FROM (SELECT DISTINCT sm.SubjectTitle,
lim.LibraryItemID, lim.LibraryItemTitle,
>
> litm.LibraryItemIssueDate FROM LibraryItemTransactions litm INNER JOIN
LibraryItems lim ON
>
> litm.LibraryItemID = lim.LibraryItemID INNER JOIN LibraryItemsubHeadings
lishm ON lim.LibraryItemID =
>
> lishm.LibraryItemID INNER JOIN SubSubjects ssm ON lishm.subsubjectid =
ssm.subsubjectid INNER JOIN
>
> Subjects sm ON ssm.subjectid = sm.subjectid WHERE sm.SubjectTitle =
s.SubjectTitle AND
>
> litm.LibraryItemReceivedByGender = 'Female' AND litm.LibraryItemIssueDate
BETWEEN '01/01/2000' AND
>
> '01/01/2005')AS "F") AS "Female",
> (SELECT COUNT(*) FROM (SELECT DISTINCT sm.SubjectTitle,
lim.LibraryItemID, lim.LibraryItemTitle,
>
> litm.LibraryItemIssueDate FROM LibraryItemTransactions litm INNER JOIN
LibraryItems lim ON
>
> litm.LibraryItemID = lim.LibraryItemID INNER JOIN LibraryItemsubHeadings
lishm ON lim.LibraryItemID =
>
> lishm.LibraryItemID INNER JOIN SubSubjects ssm ON lishm.subsubjectid =
ssm.subsubjectid INNER JOIN
>
> Subjects sm ON ssm.subjectid = sm.subjectid WHERE sm.SubjectTitle =
s.SubjectTitle AND
>
> litm.LibraryItemIssueDate BETWEEN '01/01/2000' AND '01/01/2005') AS "O")
AS "Overall"
> FROM LibraryItemTransactions lit INNER JOIN LibraryItems li ON
lit.LibraryItemID = li.LibraryItemID
>
> INNER JOIN LibraryItemsubHeadings lish ON li.LibraryItemID =
lish.LibraryItemID INNER JOIN
>
> SubSubjects ss ON lish.subsubjectid = ss.subsubjectid INNER JOIN Subjects
s ON ss.subjectid =
>
> s.subjectid
>
> When I excute it, it runs perfectly and give the desired output that is:
>
> Information Technology 2 1 3
> Languages 0 1 1
> Maths 1 0 1
>
> Here is the code of my Stored Procedure:
>
> CREATE PROCEDURE RptSbjtStat


> AS
>
> ------------------------------------------------------------------------
> -- Generating Query for Total Number of Males
> ------------------------------------------------------------------------


lim.LibraryItemID,
>
> lim.LibraryItemTitle, litm.LibraryItemIssueDate FROM
LibraryItemTransactions litm INNER JOIN
>
> LibraryItems lim ON litm.LibraryItemID = lim.LibraryItemID INNER JOIN
LibraryItemsubHeadings lishm ON
>
> lim.LibraryItemID = lishm.LibraryItemID INNER JOIN SubSubjects ssm ON
lishm.subsubjectid =
>
> ssm.subsubjectid INNER JOIN Subjects sm ON ssm.subjectid = sm.subjectid
WHERE sm.SubjectTitle =
>
> s.SubjectTitle AND litm.LibraryItemReceivedByGender = ''Male'' AND
litm.LibraryItemIssueDate BETWEEN
>

"Male"'
>
> --------------------------------------------------------------------------
> -- Generating Query for Total Number of Female
> --------------------------------------------------------------------------


sm.SubjectTitle,
>
> lim.LibraryItemID, lim.LibraryItemTitle, litm.LibraryItemIssueDate FROM
LibraryItemTransactions litm
>
> INNER JOIN LibraryItems lim ON litm.LibraryItemID = lim.LibraryItemID
INNER JOIN
>
> LibraryItemsubHeadings lishm ON lim.LibraryItemID = lishm.LibraryItemID
INNER JOIN SubSubjects ssm ON
>
> lishm.subsubjectid = ssm.subsubjectid INNER JOIN Subjects sm ON
ssm.subjectid = sm.subjectid WHERE
>
> sm.SubjectTitle = s.SubjectTitle AND litm.LibraryItemReceivedByGender =
''Female'' AND
>

@IssueToDate + ''')AS "F") AS
>
> "Female"'
>
> --------------------------------------------------------------------------
---
> -- Generating Query for Total Number of Issuance
> --------------------------------------------------------------------------
---


sm.SubjectTitle,
>
> lim.LibraryItemID, lim.LibraryItemTitle, litm.LibraryItemIssueDate FROM
LibraryItemTransactions litm
>
> INNER JOIN LibraryItems lim ON litm.LibraryItemID = lim.LibraryItemID
INNER JOIN
>
> LibraryItemsubHeadings lishm ON lim.LibraryItemID = lishm.LibraryItemID
INNER JOIN SubSubjects ssm ON
>
> lishm.subsubjectid = ssm.subsubjectid INNER JOIN Subjects sm ON
ssm.subjectid = sm.subjectid WHERE
>
> sm.SubjectTitle = s.SubjectTitle AND litm.LibraryItemIssueDate BETWEEN '''
+ @IssueFromDate + ''' AND
>

>
> --------------------------------------------------------------------------
--
> -- Generating the Query from above Sub Queries
> --------------------------------------------------------------------------
--


@MaleQuery + ', ' +
>


LibraryItems li ON
>
> lit.LibraryItemID = li.LibraryItemID'

li.LibraryItemID =
>
> lish.LibraryItemID'

lish.subsubjectid =
>
> ss.subsubjectid'

s.subjectid'
>
> -----------------------------------
> -- Executing the Query
> -----------------------------------

> RETURN
> GO
>
> Any help will be highly apriciated.
>
> Thanks/AKG


 
 
Joe





PostPosted: Tue Jul 06 09:35:06 CDT 2004 Top

SQL Server Developer >> Help Required of Stored Procedure 1) Double quotes have special meaning in SQL; single quotes enclose
strings. But that is not the REAL problem with this code.

2) Dynamic SQL should be avoided at all costs. It is dangerous,
expensive and proprietary. In effect, you are saying to the world that
you are such a bad programmer, you have to let the user and the system
figure it all out at run time.

3) Why are dates being passed AS VARCHAR(50)? Once more the "all-purpose
newbie datatype shows up! Improper datatypes is a certain sign that your
entire schema is a mess and needs to be re-done. Have you done a data
audit to see what the data quality is like?

4) Since you did not post any DDL, all we can do is guess, but this is
how you should write this kind of summary query. Building a huge
subquery for EACH special case then filtering them is a waste of both
time and space. Think in terms of sets, not in terms of steps in a
process. SQL is not a procedural language and when you write procedural
code like that, you destroy all the advantages of an RDBMS, take orders
of magnitude longer to run and do not get the right answers anyway.

CREATE PROCEDURE RptSbjtStat
(@issuefrom_date DATETIME,
@issueto_date DATETIME)
AS
SELECT SUM (CASE WHEN LITM.itemreceivedbygender = 'male'
THEN 1 ELSE 0 END) AS total_males,
SUM (CASE WHEN LITM.itemreceivedbygender = 'female',
THEN 1 ELSE 0 END) AS total_females,
COUNT(*) AS total_issuance
FROM Libraryitemtransactions AS LITM,
LibraryItems AS LIM,
LibraryItemSubheadings AS LISHM,
Subsubjects AS SSM,
Subjects AS SM
WHERE LIM.item_id = LISHM.item_id
AND LITM.item_id = LIM.item_id
AND LITM.itemissue_date

-- do you really need the next three predicates when you have an item
id?
-- Doesn't the subject_id match the subject_title one to one?
AND LISHM.subsubject_id = SM.subsubject_id
AND SSM.subject_id = SM.subject_id
AND SM.subject_title = SSM.subject_title;

When I see names like "subsubject_id" and "subject_id" in a library, I
wonder where the Dewey Decimal Code is. Surely nobody designed their
own subject areas in the face of an industry standard that strong.

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!