Stored Proc...incoming param with dash

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)

Stored Proc...incoming param with dash

Author

Topic

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2008-08-27 : 12:24:36

Hello -

There is some vb code in an application that is calling a stored procedure as: "EXEC usp_List_My_Accounts my-alias, Closed"

The stored proc accepts two parameters (both are varchar) and works unless the person's alias has a dash in it. Like, bill-jones, for example.

I created a simple stored proc as:
CREATE PROCEDURE [dbo].[TestForDash]
@Param1 varchar(10),
@Param2 varchar(4)
AS
BEGIN
SELECT getDate()
END

And sure enough, if I try to execute it like:
EXEC TestForDash My-Param1, Param2
it fails with: Incorrect syntax near '-'.

If I execute like:
EXEC TestForDash MyParam1, Param2
it works fine.

I have tried a few things to try and handle this within the stored procedure...wrapping the first parameter with a quote or something, but that doesn't fix the syntax error.

I don't "own" the vb code, but was hoping to try and modify the stored proc so that there would be only one change (the db side), rather than having the devs go through ALL of their code and changing the way they invoke the stored proc by having them replace things like, "EXEC usp_List_My_Accounts my-alias, Closed" with "EXEC usp_List_My_Accounts 'my-alias', 'Closed'"

thanks for any help.
- will

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-08-27 : 12:33:22

no can do. you'll have to change the code. and while you're at it change it so you use parameters.

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2008-08-27 : 13:15:13

Thanks.

The existing stored proc does use parameters, is that what you meant by "change it to use parameters"?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-08-27 : 13:21:55

no i meant use parameters in your client code.
look up sql command parameters on google. depending on what VB are you using.

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2008-08-27 : 13:52:23

Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources