Select will not return a value

[jagilman]

I have this stored procedure.

CREATE PROCEDURE [sp_SWGetOnHandQuantity]

@ProdID varchar

AS SELECT OnHand FROM tblInventory WHERE ProdID = @ProdID

However it will not return the matching record.
My table has the field type varchar, and a unique index.
If I search on the identity field with an integer type the product is found. But I am unable to find it on the ProdID field.

Thanks [sig]<p>John A. Gilman<br><a href=mailto:gms@uslink.net>gms@uslink.net</a><br>[/sig]

 

[Bigley]

Instead of the '=' use the word 'Like' for comparing varchars

CREATE PROCEDURE [sp_SWGetOnHandQuantity]

@ProdID varchar

AS SELECT OnHand FROM tblInventory WHERE ProdID Like @ProdID

That should do it
[sig][/sig]

 

[jagilman]

Thank you Bigley, late Saturday nite I tried this and it worked.
@ProdID varchar (8)

[sig]<p>John A. Gilman<br><a href=mailto:gms@uslink.net>gms@uslink.net</a><br>[/sig]

 

[jcrivera]

When you're declaring parameters, local variables, etc. of type VARCHAR, you must specify how many characters long it is. Otherwise, the query won't work.

J.C. [sig][/sig]

 

[shabnam]

You could try
AS SELECT OnHand FROM tblInventory WHERE ProdID = ltrim(rtrim(@ProdID)), sometimes leading or trailing spaces cause problems.
[sig][/sig]