Stored Procedure passing chars, getting error

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 Procedure passing chars, getting error

Author

Topic

paintbawler1
Starting Member

1 Post

Posted - 2007-09-13 : 11:36:35

I've created a stored procedure that is supposed to execute w/ multiple char values as the parameter, but I keep getting an error that no one I've talked to can help me with. Here is the stored procedure:


ALTER PROCEDURE [dbo].[FindPlayer] @PlayID VARCHAR(100)
AS

DECLARE @SQL VARCHAR(1000)

SELECT @SQL = 'SELECT * FROM Players '
SELECT @SQL = @SQL + ' WHERE PlayerID = (' + @PlayID + ')'

EXEC(@SQL)

Here is the execute statement:


EXEC FindPlayer "'ALESH01','ASHTH01'"

And here is the error I get when I do execute:


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

Now I've got this SP to work with just passing one char value as the parameter, but when I try two char values I ALWAYS get that error. I've tried multiple different placements of the quotes and comma, but still have had no luck. Hope someone can help me out.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-13 : 11:44:36

EXEC FindPlayer 'ALESH01,ASHTH01'

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-13 : 11:47:36

[code]ALTER PROCEDURE [dbo].[FindPlayer] @PlayID VARCHAR(100)
AS

DECLARE @SQL VARCHAR(1000)

SET @SQL = 'SELECT * FROM Players '
SET @SQL = @SQL + ' WHERE PlayerID IN (''' + REPLACE(@PlayID, ',', ''',''') + ''')'

--print @sql

EXEC(@SQL)[/code]

E 12°55'05.25"
N 56°04'39.16"

X002548
Not Just a Number

15586 Posts

Posted - 2007-09-13 : 13:15:50

quote:
Originally posted by Peso

EXEC FindPlayer 'ALESH01,ASHTH01'

E 12°55'05.25"
N 56°04'39.16"

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

X002548
Not Just a Number

15586 Posts

Posted - 2007-09-13 : 13:16:08

quote:
Originally posted by X002548

[quote]Originally posted by Peso

EXEC FindPlayer 'ALESH01,ASHTH01'

E 12°55'05.25"
N 56°04'39.16"

DECLARE @x varchar(100)
SET @x= '''ALESH01'',''ASHTH01'''

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-13 : 13:51:55

Not if you see the changes I have made to the SP too...

E 12°55'05.25"
N 56°04'39.16"

X002548
Not Just a Number

15586 Posts

Posted - 2007-09-13 : 13:58:14

Peter,

Isn't it time for you to be knocking back some aquivit?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Kristen
Test

22859 Posts

Posted - 2007-09-13 : 14:04:20

The only time I could get a post in edge-ways was when he took a week's holiday

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-13 : 14:12:03

What to do?

I am commuting 400 miles by flying to Stockholm on mondays, and fly back home 400 more miles on fridays.
All weekdays I am sitting in a hotelroom. Only pleasure is watching TV and surfing the Net.

E 12°55'05.25"
N 56°04'39.16"

Kristen
Test

22859 Posts

Posted - 2007-09-13 : 14:15:23

Get a life, then we can give up ours!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-13 : 14:21:31

The bright side is that this assigment is due October 31!
Then I get normal life back. And with a new DBA due on 22-23 november, there will be not much time to spend here.

Next assignment is to Copenhagen for six months. Then I can drive home every night, instead of sitting here.

E 12°55'05.25"
N 56°04'39.16"

Kristen
Test

22859 Posts

Posted - 2007-09-13 : 14:35:39

Just in time for the long Winter evenings?!

X002548
Not Just a Number

15586 Posts

Posted - 2007-09-13 : 14:36:21

quote:
Originally posted by Peso

Next assignment is to Copenhagen for six months.

E 12°55'05.25"
N 56°04'39.16"

Oh, he'll by mia

What with the ladies and the ha_h selection

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

X002548
Not Just a Number

15586 Posts

Posted - 2007-09-13 : 14:37:09

Wait! That's Amsterdam....similar though?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources