Incorrect syntax near '-' 1

[CRuser89]

Hello,

I am using SQL Server 2005. I have a stored procedure that requires a parameter at execution - employee name. When I type in any name like donald, mike, john then it works fine. But when I use name like Sarah-Lee with a hypen, it gives me the error even though I've declared the variable as varchar(100):

Incorrect syntax near '-'

Can someone please help?

thanks,
Kathy

 

[SQLDenis]

how are you calling this proc?

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[CRuser89]

Hello Denis,

Thanks for your reply. I am calling this procedure by using the command below:

exec sp_GetPerson Smith

Smith is the parameter input required (last name of person). When I use Le-Ann it will give me the error.

 

[SQLDenis]

exec sp_GetPerson 'Smith'
exec sp_GetPerson 'Sarah-Lee'

also don't prefix your procs with sp_

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[CRuser89]

Thanks Denis....I was told by my manager that we need to prefix our procs with sp_. Can you please tell me why it shouldn't be prefixed this way?

 

[SQLDenis]

here is why

http://msdn2.microsoft.com/en-us/library/ms190669.aspx

Naming Stored Procedures

We recommend that you do not create any stored procedures using sp_ as a prefix. SQL Server uses the sp_ prefix to designate system stored procedures. The name you choose may conflict with some future system procedure. If your application uses nonschema qualified name references and your own procedure name conflicts with a system procedure name, your application will break because the name binds to the system procedure, not your own.

A user-defined stored procedure that has the same name as a system stored procedure and is either nonqualified or is in the dbo schema will never be executed; the system stored procedure will always execute instead. The following example demonstrates this behavior.

USE AdventureWorks;
GO
CREATE PROCEDURE dbo.sp_who
AS
SELECT FirstName, LastName FROM Person.Contact;
GO
EXEC sp_who;
EXEC dbo.sp_who;
GO
DROP PROCEDURE dbo.sp_who;
GO

Using an explicit schema qualifier also provides a slight performance advantage. Name resolution is slightly faster if the Database Engine does not have to search multiple schemas to find the procedure. For more information, see Executing a Stored Procedure.


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLSister]

Because that is what the system procs are prefaced with. It will look for it as a system proc first and then and onll if it can't find it as a system proc will it look for it as a user proc which is less efficient. And if Microsoft should ever write a proc with the same name as one of the ones you write, then yours will never execute. Procs should begin with some other prefix such as usp (stands for user stored procedure).

Questions about posting. See faq183-874

 

[CRuser89]

Thank you all very much for the in-depth explanation. I learned several new things today...Thanks again...