Incorrect syntax near - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Incorrect syntax near

Author

Topic

JezLisle
Posting Yak Master

132 Posts

Posted - 2008-10-16 : 06:27:58

I have written a Stored Procedure and cant workout where I've gone wrong, this below is the error message and cant see what the problem is

Msg 102, Level 15, State 1, Line 9
Incorrect syntax near '@StaffID'.

This is my code
DECLARE
@ContServID VARCHAR(25),
@StaffID VARCHAR(10),
@SQL VARCHAR(8000)

SELECT
--@ContServID = 'PHYSIO'
@ContServID = Null
@StaffID = P%
@SQL = 'SELECT ContactServiceID, StaffID ' +
'FROM PARAMED_TEMP ' +
'WHERE StaffID Like (' + @StaffID + ') ' +
'AND ContactServiceID = @ContServID '

PRINT @SQL

I'm trying to run a SP where eventually the Variables will be declared in the EXEC Statement when I run it

This is my original SQL where I'm working from
SELECT ContactServiceID, StaffID
FROM PARAMED_TEMP
WHERE (StaffID Like 'P%')
AND ContactServiceID IS Null

What have I done wrong or missed?

ranganath
Posting Yak Master

209 Posts

Posted - 2008-10-16 : 06:36:37

Hi,

DECLARE
@ContServID VARCHAR(25),
@StaffID VARCHAR(10),
@SQL VARCHAR(8000)

SELECT
--@ContServID = 'PHYSIO'
@ContServID = Null,
@StaffID = 'P%',
@SQL = 'SELECT ContactServiceID, StaffID ' +
'FROM PARAMED_TEMP ' +
'WHERE StaffID Like ''' + @StaffID + ''' ' +
'AND ContactServiceID = @ContServID '

PRINT @SQL

JezLisle
Posting Yak Master

132 Posts

Posted - 2008-10-16 : 07:05:30

Excellent Thanks for that, So now that works I've created it as a SP, but get this error when executing the EXEC Statement

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'IS'.

My SP
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [jez].[sp_ZZ]
AS
DECLARE
@ContServID VARCHAR(25),
@StaffID VARCHAR(10),
@SQL VARCHAR(8000)

SELECT
@SQL = 'SELECT ContactServiceID, StaffID ' +
'FROM PARAMED_TEMP ' +
'WHERE StaffID Like ''' + @StaffID + ''' ' +
'AND ContactServiceID = @ContServID '

This is the EXEC Statement
EXEC sp_ZZ 'P%', IS NULL

Where have I gone wrong with the EXEC Statement?

ddamico
Yak Posting Veteran

76 Posts

Posted - 2008-10-16 : 07:31:09

First off, I don't see any input parameters setup to allow you pass those parameters in.

CREATE PROCEDURE sp_ZZ (
@param1 VARCHAR(10), @param2 VARCHAR(10) )
AS
BEGIN
END

1. The DECLARE means you are declaring the variable internally not so you can't pass anything in
2. You have to put single quotes around strings especially with spaces
3. You should pass in the string 'IS NULL' that should be part of your query in the procedure

I think this should solve some of the issues you are seeing.

ranganath
Posting Yak Master

209 Posts

Posted - 2008-10-16 : 07:47:48

CREATE PROCEDURE sp_ZZ
(
@ContServID VARCHAR(32),
@StaffID VARCHAR(32)
)
AS
BEGIN
declare
@SQL VARCHAR(8000)
Select @sql = 'SELECT ContactServiceID, StaffID
FROM PARAMED_TEMP'
declare @str varchar(8000)
Select @Str = @Sql + ' where StaffID Like ''' + @StaffID + ''''
if @ContServID is not null
Select @str = @str + 'and ContactServiceID = ''' +@ContServID+''''
Exec( @str)

END

Exec sp_ZZ 'asa', 'p%'

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-10-16 : 08:13:14

U Don't need Dynamic sql for doing this, unless @StaffId is CSV
try this


DECLARE @ContServID VARCHAR(25),
		@StaffID VARCHAR(10),
		@SQL VARCHAR(8000)

SELECT @ContServID = Null
		@StaffID = 'P%'

SELECT ContactServiceID, StaffID FROM PARAMED_TEMP 
WHERE StaffID Like @StaffID
		AND (@ContServID IS NULL OR ContactServiceID = @ContServID)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-16 : 08:49:08

quote:

Originally posted by PeterNeo

U Don't need Dynamic sql for doing this, unless @StaffId is CSV
try this


DECLARE @ContServID VARCHAR(25),
		@StaffID VARCHAR(10),
		@SQL VARCHAR(8000)

SELECT @ContServID = Null
		@StaffID = 'P%'

SELECT ContactServiceID, StaffID FROM PARAMED_TEMP 
WHERE StaffID Like @StaffID
		AND (@ContServID IS NULL OR ContactServiceID = @ContServID)

SELECT @ContServID = Null
@StaffID = 'P%'

should be

SELECT @ContServID = Null,
@StaffID = 'P%'

Madhivanan

Failing to plan is Planning to fail

JezLisle
Posting Yak Master

132 Posts

Posted - 2008-10-16 : 08:58:51

Thanks for that, I've tried it and it brings back to many results...

All I want to try and do is this,

SELECT ContactServiceID, StaffID
FROM PARAMED_TEMP
WHERE (StaffID Like 'P%')
AND ContactServiceID IS Null

but input variables so that the 'P%' can be changed to something else and the Is Null can be is Null or it maybe something else all depending on the EXEC Statement where I want to select the Variables.

JezLisle
Posting Yak Master

132 Posts

Posted - 2008-10-16 : 09:33:20

Using the thread Posted - 10/16/2008 : 08:13:14
I end up with over 11 thousand results and the ContractServiceID doesnt contain just the nulls.

If I run the Simple SQL in thread above then it returns about 63 results which is more along the lines for the results I was looking for

How can I amend this SP?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-16 : 13:19:23

quote:
Originally posted by JezLisle

Using the thread Posted - 10/16/2008 : 08:13:14
I end up with over 11 thousand results and the ContractServiceID doesnt contain just the nulls.

If I run the Simple SQL in thread above then it returns about 63 results which is more along the lines for the results I was looking for

How can I amend this SP?

do you mean this?

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [jez].[sp_ZZ]

@ContServID VARCHAR(25),
@StaffID VARCHAR(10)
AS

SELECT ContactServiceID, StaffID FROM PARAMED_TEMP 
WHERE StaffID Like @StaffID
AND COALESCE(ContactServiceID,0) = COALESCE(@ContServID,0)
GO

EXEC  Null,'P%'

JezLisle
Posting Yak Master

132 Posts

Posted - 2008-10-17 : 03:33:43

Thanks, I've tried this but it doesnt work as its trying to change, @ContServID is not a bit.

This is the error message

Msg 245, Level 16, State 1, Procedure sp_1, Line 8
Conversion failed when converting the varchar value 'PHYSIO' to data type int.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-17 : 05:36:54

It means StaffID is of INT datatype where you need to pass INT values only

Madhivanan

Failing to plan is Planning to fail

JezLisle
Posting Yak Master

132 Posts

Posted - 2008-10-17 : 05:57:52

I'm getting a bit confused here...

All I'm looking to do is create a dynamic SQL so that the StaffID can be changed through the EXEC Statement

SELECT ContactServiceID, StaffID
FROM PARAMED_TEMP
WHERE (StaffID Like 'P%')
AND ContactServiceID IS Null

The ContactServiceID will always be NULL, the only thing to change would be the StaffID

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-17 : 06:14:02

how will StaffID contain varchar values if its int?

JezLisle
Posting Yak Master

132 Posts

Posted - 2008-10-17 : 06:28:27

Heres an example StaffID - P421 thats whay its a VARCHAR

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-17 : 08:25:41

Post the table structure

Madhivanan

Failing to plan is Planning to fail