Incorrect syntax near 0

Question

Post reply

Incorrect syntax near 0

jsshivalik

SSChasing Mays

Points: 612
More actions
July 10, 2022 at 2:44 am

#4061202

BookId & StudentId has int DataType
ALTER PROC [dbo].[USP_SessionBookPlanningSearch] --'0','20,21,22'
(
@BookID VARCHAR(500),
@StudentID VARCHAR(500)
)
AS
BEGIN
with ROWCTE as
(
SELECT * from View_Session
Where (('''+@@BookId+'''=''0'' OR BookID IN('+ cast(@@BookId as varchar(Max))+'))
and (('''+@@@StudentID+'''=''0'' OR StudentID IN('+ cast(@@@StudentID as varchar(Max))+'))
)
SELECT * FROM ROWCTE
end
July 13, 2022 at 6:51 am

This was removed by the editor as SPAM

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

You start off with a param called @StudentId, which then becomes '@@@StudentId' in your query. Why are you doing that?

Also, if your param is declared as VARCHAR(500), why are you CASTing it to VARCHAR(MAX)?

Notwithstanding your answer to these questions, you can't write your query like this. You could split out the values in your parameters into temp tables and then join these to View_Session.

Your CTE is completely pointless here. Just use a SELECT directly.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Steve Collins SSCrazy Points: 2157 More actions · Answer 2

It appears to be a nonworking attempt at a catch all query. Imo the easy answer is two procedures or however many are necessary. In this case imo the OP could use 4 procedures instead.

The features added to SQL Server 2022 for parameter sniffing are kludge imo because it just facilitates an antipattern

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 3

And why on Earth are you convert IDs to VARCHAR(MAX)?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 4

Can you provide a sample of how you are calling the procedure and what is going into the parameters?