How to use local variables in stored procedure

Declare @GenricKey int
Declare @PackingKey int
Declare @ShelfKey int
Declare @ManufactureKey int
Declare @TherapeuticKey int
 
	
Select @GenricKey = GenricKey from GenricMaster where GenricName = @GenricName
Select @PackingKey = PackingKey from PackingMaster where PackingName = @PackingName
Select   @ManufactureKey = ManufactureKey from ManufactureMaster where  ManufactureName = @ManufactureName
Select   @ShelfKey = ShelfKey from ShelfMaster where ShelfName = @ShelfName
Select   @TherapeuticKey = TherapeuticKey  from TherapeuticCategoryMaster where TherapeuticName = @TherapeuticName
 
Insert into dbo.ProductMaster
(ProductName,PackingKey,ShelfKey,ManufactureKey,GenricKey,TherapeuticKey)
values
(@ProductName,@PackingKey,@ShelfKey,@ManufactureKey,@GenricKey,@TherapeuticKey )

You are searching again a string '@Whatever' instead of parameter.

You just have to remove the quotes you placed around the variables in the where clauses.
That should do the trick for you. :)

@ProductName nvarchar(50),
	@GenricName int,
	@PackingName int,
	@ShelfName int,
	@ManufactureName int,
	@TherapeuticName int
AS
	Declare @GenricKey int
	Declare @PackingKey int
	Declare @ShelfKey int
	Declare @ManufactureKey int
	Declare @TherapeuticKey int
 
	
Select @GenricKey = GenricKey from GenricMaster where GenricName = @GenricName
Select @PackingKey = PackingKey from PackingMaster where PackingName = @PackingName
Select   @ManufactureKey = ManufactureKey from ManufactureMaster where  ManufactureName = @ManufactureName
Select   @ShelfKey = ShelfKey from ShelfMaster where ShelfName = @ShelfName
Select   @TherapeuticKey = TherapeuticKey  from TherapeuticCategoryMaster where TherapeuticName = @TherapeuticName
 
Insert into dbo.ProductMaster
(ProductName,PackingKey,ShelfKey,ManufactureKey,GenricKey,TherapeuticKey)
values
(@ProductName,@PackingKey,@ShelfKey,@ManufactureKey,@GenricKey,@TherapeuticKey )

Hope that solves your issue! Besides that I would formulate the assignment part like this for clarity:

SET @GenricKey      = (SELECT GenricKey      FROM GenricMaster              WHERE GenricName = @GenricName);
SET @PackingKey     = (SELECT PackingKey     FROM PackingMaster             WHERE PackingName = @PackingName);
SET @ManufactureKey = (SELECT ManufactureKey FROM ManufactureMaster         WHERE ManufactureName = @ManufactureName);
SET @ShelfKey       = (SELECT ShelfKey       FROM ShelfMaster               WHERE ShelfName = @ShelfName);
SET @TherapeuticKey = (SELECT TherapeuticKey FROM TherapeuticCategoryMaster WHERE TherapeuticName = @TherapeuticName);

It is a little more verbose and clearer that an assignment is taking place, but this is just my personal preference.

Regards,

Manfred