SQL Server Stored Procedure Syntax Error

Question

I am trying to write a stored procedure that returns a deposit record for a specific person, the number of deposits for that person and the row number of that deposit for that person. The parameter @personID is always supplied the correct personID; however @SpecificRow is either 0 (meaning: return most recent deposit) or a row number (meaning:return record at this specific row).

Script:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [schema].[procedure]
    @personID varchar(5),
    @SpecificRow int,
    @RowNumber INT OUTPUT,
    @RowCount INT OUTPUT
AS
BEGIN
    IF OBJECT_ID('#TempSortedDeposits') IS NOT NULL 
       DROP Table #TempSortedDeposits

    -- get all deposits with record number into temp file for specific personID
    SELECT 
        ROW_NUMBER() OVER (ORDER BY Date ASC) AS RecordNo, *
    INTO 
        #TempSortedDeposits
    FROM 
        persons.Deposits
    WHERE 
        personID = @personID

    -- get record count in file
    SELECT @RowCount = COUNT(personID)
    FROM persons.Deposits
    WHERE personID = @personID 

    IF @SpecificRow = 0 --get most recent record
        SET @RowNumber = @RowCount;
    ELSE
        --get record by rownumber
        SET @RowNumber = @SpecificRow;

    SELECT * 
    FROM #TempSortedDeposits 
    WHERE RecordNo = @RowNumber
END

When I try running the alter statement, I get the following error:

Msg 102, Level 15, State 1, Procedure procedure, Line 33 [Batch Start Line 9]
Incorrect syntax near ''.

Any insight?

Thanks.

@FullMoonFisher this shouldn't cause an error on compile, just execution. — S3S
– S3S, Commented Aug 9, 2017 at 18:52

OLIVER.KOO · Accepted Answer · 2017-08-09 19:02:23Z

1

I deleted this line SELECT * FROM #TempSortedDeposits WHERE RecordNo = @RowNumber and manually re-type it then it worked.

This is because the error occurs due to copy and paste. That result to pasted code contains unprintable characters like d non-braking spaces.

edited Aug 9, 2017 at 19:02

answered Aug 9, 2017 at 18:50

OLIVER.KOO

6,1133 gold badges38 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ti034 Over a year ago

Thanks. How did you identify that line as being the problem?

OLIVER.KOO Over a year ago

because of this error "Incorrect syntax near ''. is almost always invoked by unprintable characters as nothing printed out after "near".

S3S · Accepted Answer · 2017-08-09 19:01:44Z

Just a side note... your #TempSortedDeposits table dropping like you think it is.

Here's a quick example:

create table #TempSortedDeposits (i int)
insert into #TempSortedDeposits
values
(1)

IF OBJECT_ID('#TempSortedDeposits') IS NOT NULL DROP TABLE #TempSortedDeposits
select * from #TempSortedDeposits

If you run this the first time, it will return 1 when it shouldn't, because it should have been dropped. On the second run (same connection) you will get this error, showing you that the table still exists and wasn't dropped:

Msg 2714, Level 16, State 6, Line 2 There is already an object named '#TempSortedDeposits' in the database.

To fix this, change the syntax to:

IF OBJECT_ID('tempdb..#TempSortedDeposits') IS NOT NULL DROP TABLE #TempSortedDeposits

Here is a test

create table #TempSortedDeposits (i int)
insert into #TempSortedDeposits
values
(1)

IF OBJECT_ID('tempdb..#TempSortedDeposits') IS NOT NULL DROP TABLE #TempSortedDeposits
select * from #TempSortedDeposits

If you first manually drop the table (since we created it with the last run) and then run this, you will get the error message:

Msg 208, Level 16, State 0, Line 7 Invalid object name '#TempSortedDeposits'.

Which means the select failed, thus showing that the table was in fact dropped.

piyush jain · Accepted Answer · 2017-08-10 20:21:57Z

1

I have removed the extra character present in below line and re execute this proc. Line is SELECT * FROM #TempSortedDeposits WHERE RecordNo = @RowNumber

answered Aug 10, 2017 at 20:21

piyush jain

1038 bronze badges

Collectives™ on Stack Overflow

SQL Server Stored Procedure Syntax Error

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related