Stored Procedure not working with variables

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?

Answer

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

Advertisement

Answer