Hyphen not working in WHERE of stored procedure

[hollander]

I have a really odd scenario that I can't seem to find a solution to (unless it's that basic!).

It has to do with a hyphen in the value being passed to a stored procedure that's doing an UPDATE.

The procedure is of the form:

UPDATE tblItem SET NextNumber = @num WHERE (ItemNumber = @item)

This WORKS if the value of @item does NOT contain a hyphen/dash. However, when I pass a value to @item with a dash, say "ABC-1", the response from the server is that the 'query executed successfully'; I do not get the message '1 row affected by last query' as I should. Now, if I execute the command directly (not via the stored proc), it works fine.

Possibly even stranger, is that I can pass an item number with a dash when the stored proc is doing a SELECT. In other words, this

SELECT .... WHERE (ItemNumber=@item)

works just fine with a hyphen in the value for @item.

I'm puzzled! Any thoughts?

Jeff

 

[SQLSister]

let's start with what is the datatype of @item?
What other things does this proc do? it possible something is nulling out the value or changing it in some way?
Is there a trigger on the table that might be rejecting the update?

"NOTHING is more important in a database than integrity." ESquared

 

[hollander]

Hi SQLSister,

OMG, I'M SUCH AN IDIOT!

Here's the proc,

CREATE PROCEDURE [sp_DC1setNextNumber]
(@item [varchar](10),
 @num [varchar](10))
AS
UPDATE tblItem
SET EndingMailerNumber = @num WHERE (ItemNumber = @item)
GO

The problem was not with the hyphen, it was with the length of the value in @item. Values without a hyphen where under 10 characters long, so, no problem. The values with hyphens are over 10 chars long, so they were getting truncated. Changing to @item [varchar](20) did the trick. AARGH!

NEWBIE QUESTION: is there a way to display the contents of @item and @num?

Thanks for your help,
Jeff

 

[SQLSister]

Print @item + ', ' + @num
will show you the contents
or select @item, @num


"NOTHING is more important in a database than integrity." ESquared

 

[hollander]

Thx!