Stored Procedure not working with variables

Question

I have a stored procedure as follows:

CREATE Procedure [dbo].[GetDrugPackID]
    @Search as nvarchar
As
Begin
    SELECT DrgPack.ID, DrgPack.PackSize, Drg.BrandName, Drg.DIN, Drg.Strength
  FROM [Pharmacy].[dbo].[DrgPack] INNER JOIN
  Drg ON Drg.ID = DrgPack.DrgID INNER JOIN
  DrgPackUPc ON DrgPack.ID = DrgPackUpc.DrgPackId
  WHERE DrgPackUpc.Upc = '@Search'
End

When I run the above by passing the search variable as 04029125070527 I get no results.

But if I edit my stored procedure as follows, I get a result. The only difference is in the above I'm trying to send the variable and in the second its hard coded (not what I want to do, just trying to debug).

CREATE Procedure [dbo].[GetDrugPackID]
    @Search as nvarchar
As
Begin
    SELECT DrgPack.ID, DrgPack.PackSize, Drg.BrandName, Drg.DIN, Drg.Strength
  FROM [Pharmacy].[dbo].[DrgPack] INNER JOIN
  Drg ON Drg.ID = DrgPack.DrgID INNER JOIN
  DrgPackUPc ON DrgPack.ID = DrgPackUpc.DrgPackId
  WHERE DrgPackUpc.Upc = '04029125070527'

What am I doing wrong here?

you forgot to set the length of your var "@Search as nvarchar" it needs to be "@Search as nvarchar(50)" or what ever length you need — Yair I
– Yair I, Commented May 24, 2021 at 21:43
The datatype of @Search should match exactly what DrgPackUpc.Upc is. So if that is varchar(20) for example then you should use that rather than nvarchar(50) — Martin Smith
– Martin Smith, Commented May 24, 2021 at 21:54

Dale K · Accepted Answer · 2021-05-24 21:58:43Z

4

You need to declare the size of your nvarchar parameter. Without a size declaration, your parameter is declared implicitly as an nvarchar(1).

In addition, your parameter should not be in single quotes.

WHERE DrgPackUpc.Upc = @Search

edited May 24, 2021 at 21:58

Dale K

28.1k15 gold badges59 silver badges85 bronze badges

answered May 24, 2021 at 21:43

Wes H

4,4992 gold badges16 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Pharm Over a year ago

Thank you!! This was it, I missed both of those

Wes H Over a year ago

Please mark as an answer if it solved your question.

Pharm Over a year ago

Will do, I'm required to wait 10 minutes to select as solved.

Collectives™ on Stack Overflow

Stored Procedure not working with variables

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related