Re: Help Required of Stored Procedure

From: Jacco Schalkwijk (jacco.please.reply_at_to.newsgroups.mvps.org.invalid)
Date: 07/06/04

Next message: simon: "Re: Convert datetime"
Previous message: hngo01: "access data while out of the network"
In reply to: AKG: "Help Required of Stored Procedure"
Next in thread: Wayne Snyder: "Re: Help Required of Stored Procedure"
Messages sorted by: [ date ] [ thread ]

Date: Tue, 6 Jul 2004 12:55:32 +0100

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
"AKG" <AKG@discussions.microsoft.com> wrote in message
news:F5C020EA-C124-4509-BF48-539AF4C9BE5B@microsoft.com...
> 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
> ------------------------------------------------------------------------
> DECLARE @MaleQuery [varchar](1000)
> SET @MaleQuery = '(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
>
> ''' + @IssueFromDate + ''' AND ''' + @IssueToDate + ''') AS "M") AS
"Male"'
>
> --------------------------------------------------------------------------
> -- Generating Query for Total Number of Female
> --------------------------------------------------------------------------
> DECLARE @FemaleQuery [varchar](1000)
> SET @FemaleQuery = '(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 ''' + @IssueFromDate + ''' AND ''' +
@IssueToDate + ''')AS "F") AS
>
> "Female"'
>
> --------------------------------------------------------------------------
---
> -- Generating Query for Total Number of Issuance
> --------------------------------------------------------------------------
---
> DECLARE @OverallQuery [varchar](1000)
> SET @OverallQuery = '(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 '''
+ @IssueFromDate + ''' AND
>
> ''' + @IssueToDate + ''') AS "O") AS "Overall"'
>
> --------------------------------------------------------------------------
--
> -- Generating the Query from above Sub Queries
> --------------------------------------------------------------------------
--
> DECLARE @SQLQuery [varchar](4000)
> SET @SQLQuery = 'SELECT DISTINCT s.SubjectTitle AS "Subject Title", ' +
@MaleQuery + ', ' +
>
> @FemaleQuery + ', ' + @OverallQuery
> SET @SQLQuery = @SQLQuery + ' FROM LibraryItemTransactions lit INNER JOIN
LibraryItems li ON
>
> lit.LibraryItemID = li.LibraryItemID'
> SET @SQLQuery = @SQLQuery + ' INNER JOIN LibraryItemsubHeadings lish ON
li.LibraryItemID =
>
> lish.LibraryItemID'
> SET @SQLQuery = @SQLQuery + ' INNER JOIN SubSubjects ss ON
lish.subsubjectid =
>
> ss.subsubjectid'
> SET @SQLQuery = @SQLQuery + ' INNER JOIN Subjects s ON ss.subjectid =
s.subjectid'
>
> -----------------------------------
> -- Executing the Query
> -----------------------------------
> EXECUTE (@SQLQuery)
> RETURN
> GO
>
> Any help will be highly apriciated.
>
> Thanks/AKG

Next message: simon: "Re: Convert datetime"
Previous message: hngo01: "access data while out of the network"
In reply to: AKG: "Help Required of Stored Procedure"
Next in thread: Wayne Snyder: "Re: Help Required of Stored Procedure"
Messages sorted by: [ date ] [ thread ]

Relevant Pages

Strange bug, hard to reproduce - is it known?
... tables used in the query, nor the query itself were changed (I did make ... particular stored procedure), yet it did work correctly before. ... tables involved in my test database, using the EXACT same columns, ... INNER JOIN dbo.GR AS r ...
(microsoft.public.sqlserver.programming)
RE: Strange bug, hard to reproduce - is it known?
... > tables used in the query, nor the query itself were changed (I did make ... > particular stored procedure), yet it did work correctly before. ... > tables involved in my test database, using the EXACT same columns, ... > INNER JOIN dbo.GR AS r ...
(microsoft.public.sqlserver.programming)
Re: Help Required of Stored Procedure
... I'm generating an SQL query ... > litm.LibraryItemIssueDate FROM LibraryItemTransactions litm INNER JOIN ... LibraryItems lim ON ... > lishm.LibraryItemID INNER JOIN SubSubjects ssm ON lishm.subsubjectid = ...
(microsoft.public.sqlserver.programming)
RE: Parameter from form
... stored procedure but same premiss) ... dbo.TEST_TYPES.Test_Type INNER JOIN ... Post the SQL of your query. ... First off I am using Microsoft acess 2003 and am running of an SQL Server ...
(microsoft.public.access.queries)
Access Reports from SQL Server Stored Procedures
... except when I add a paramter to the Stored Procedure. ... TK.TimeKeeperID INNER JOIN ... Now, if I set up a report with this stored procedure and run the report, it ... then it will run the 2nd query... ...
(microsoft.public.access.reports)