problem with # sign

I have a stored proc that takes a parameter and finds the correct row.
Simple and easy. Unfortunately the data contains various symbols such as #
and -. When I run the proc and try to pass a value that contains a symbol I
get an error. The sp works fine when there are no symbols. Any
suggestions?
Laura KOh and here is the sp.
@.ProductCode nvarchar (5)
AS
SELECT pc.strcolorID, p.intproductID
FROM dbo.tblProducts p join tblJctProductsColors pc
On p.IntProductID=pc.intProductID
WHERE strProductCode = @.ProductCode
Simple sp. but wont work with symbols. I ran the proc with #322 and I get
an error. Sorry I am a newbie. Any help appreciated.
Thanks
Laura K
"Laura K" <klkazanAT@.ATcharter.net> wrote in message
news:%23du%23BpJoFHA.3316@.TK2MSFTNGP14.phx.gbl...
>I have a stored proc that takes a parameter and finds the correct row.
>Simple and easy. Unfortunately the data contains various symbols such as #
>and -. When I run the proc and try to pass a value that contains a symbol
>I get an error. The sp works fine when there are no symbols. Any
>suggestions?
> Laura K
>|||What about the table structures? You don't say what the column definitions
are.
"Laura K" <klkazanAT@.ATcharter.net> wrote in message
news:u1OSNtJoFHA.2472@.TK2MSFTNGP15.phx.gbl...
> Oh and here is the sp.
> @.ProductCode nvarchar (5)
> AS
>
> SELECT pc.strcolorID, p.intproductID
> FROM dbo.tblProducts p join tblJctProductsColors pc
> On p.IntProductID=pc.intProductID
> WHERE strProductCode = @.ProductCode
> Simple sp. but wont work with symbols. I ran the proc with #322 and I get
> an error. Sorry I am a newbie. Any help appreciated.
> Thanks
> Laura K
> "Laura K" <klkazanAT@.ATcharter.net> wrote in message
> news:%23du%23BpJoFHA.3316@.TK2MSFTNGP14.phx.gbl...
>|||Hi
You may want to put checks into the client to stop data being passed that
will not be valid. You do not say what the error is, as #322 I would expect
no rows to be returned rather than any specific error.
John
"Laura K" <klkazanAT@.ATcharter.net> wrote in message
news:u1OSNtJoFHA.2472@.TK2MSFTNGP15.phx.gbl...
> Oh and here is the sp.
> @.ProductCode nvarchar (5)
> AS
>
> SELECT pc.strcolorID, p.intproductID
> FROM dbo.tblProducts p join tblJctProductsColors pc
> On p.IntProductID=pc.intProductID
> WHERE strProductCode = @.ProductCode
> Simple sp. but wont work with symbols. I ran the proc with #322 and I get
> an error. Sorry I am a newbie. Any help appreciated.
> Thanks
> Laura K
> "Laura K" <klkazanAT@.ATcharter.net> wrote in message
> news:%23du%23BpJoFHA.3316@.TK2MSFTNGP14.phx.gbl...
>|||I get specific errors.
When I use 86-283 there error is
Line 1: Incorrect syntax near '-'
I also have spaces in some of the codes so when I use
spGetProductColor TUK Elvis W
Line 1: Incorrect syntax near 'Elvis'.
Upon closer inspection I have no problems with the # sign thus making the
title of my post obsolete. I do however have problems with the - and
spaces.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:OnpT6tLoFHA.632@.tk2msftngp13.phx.gbl...
> Hi
> You may want to put checks into the client to stop data being passed that
> will not be valid. You do not say what the error is, as #322 I would
> expect no rows to be returned rather than any specific error.
> John
> "Laura K" <klkazanAT@.ATcharter.net> wrote in message
> news:u1OSNtJoFHA.2472@.TK2MSFTNGP15.phx.gbl...
>|||The column in question is a brandcode which may include numbers, letters or
what have you depending on the manufactures usage. The datatype is
nvarchar. It is not related to any other table. The table is related by the
productID to many other tables.
Not sure if this is what you are asking.
Laura
"Colin Dawson" <newsgroups@.cjdawson.com> wrote in message
news:5rDLe.88549$G8.69248@.text.news.blueyonder.co.uk...
> What about the table structures? You don't say what the column
> definitions are.
> "Laura K" <klkazanAT@.ATcharter.net> wrote in message
> news:u1OSNtJoFHA.2472@.TK2MSFTNGP15.phx.gbl...
>|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.|||Laura,
try
exec spGetProductColor 'TUK Elvis W'
and see if that fixes it.
Stu|||It takes care of the error but now it returns nothing. However when I put
quotes around a value that has no spaces or dashes it still returns a color.
Laura
"Stu" <stuart.ainsworth@.gmail.com> wrote in message
news:1124043325.349594.239730@.o13g2000cwo.googlegroups.com...
> Laura,
> try
> exec spGetProductColor 'TUK Elvis W'
> and see if that fixes it.
> Stu
>|||Excuse the fact that I am new at this but let me know how to post DDL.
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1124041314.536726.261720@.f14g2000cwb.googlegroups.com...
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, data types, etc. in
> your schema are. Sample data is also a good idea, along with clear
> specifications. It is very hard to debug code when you do not let us
> see it.
>