如何在存储过程中使用局部变量

问题描述：

亲爱的所有人，

我正在存储过程中执行此操作:

Dear All,

I was doing this in my stored Procedure:

        @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 )

当我运行此存储过程时，它会被执行，但是字段中的值(即插入查询中的值)将变为null.

请帮帮我
问候，
Mohi

When i run this stored procedure it gets executed but the values in the fields i.e. in the insert query they go as null.

Please Help me out
Regards,
Mohi

答

您不要在变量上使用''.也许这就是为什么您的Select语句未返回值的原因.删除变量上的''，就像这样

You don''t use '''' on variables. Maybe that''s why your Select statements are not returning a value. Remove the '''' on the variables, like this

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

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 )

您将再次搜索字符串"@Whatever"而不是参数.

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

只需删除在where子句中变量周围的引号即可.
那应该为您解决问题. :)

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);

正在进行任务要更加冗长和清晰，但这只是我个人的喜好.

问候，

曼弗雷德(Manfred)

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

Regards,

Manfred

如何在存储过程中使用局部变量

相关推荐