如何在SQL Server中声明变量并在同一存储过程中使用它 [英] How to declare a variable in SQL Server and use it in the same Stored Procedure

问题描述

我正在尝试从一个表中的BrandID获取值并将其添加到另一表中.但是我无法使它正常工作.有人知道怎么做对吗?

Im trying to get the value from BrandID in one table and add it to another table. But I can't get it to work. Anybody know how to do it right?

CREATE PROCEDURE AddBrand
AS

DECLARE 
@BrandName nvarchar(50),
@CategoryID int,
@BrandID int

SELECT @BrandID = BrandID FROM tblBrand 
WHERE BrandName = @BrandName

INSERT INTO tblBrandinCategory (CategoryID, BrandID) 
       VALUES (@CategoryID, @BrandID) 

RETURN

推荐答案

您所拥有的东西出了什么问题?您会得到什么错误，或者您得到或没有得到什么结果，却与您的期望不符?

What's going wrong with what you have? What error do you get, or what result do or don't you get that doesn't match your expectations?

我可以看到该SP的以下问题，这可能与您的问题有关，也可能与您的问题无关.

I can see the following issues with that SP, which may or may not relate to your problem:

• SELECT中(末尾)在@BrandName之后有多余的)
• 您没有在任何地方将@CategoryID或@BrandName设置为任何值(它们是局部变量，但您没有为它们分配值)

• You have an extraneous ) after @BrandName in your SELECT (at the end)
• You're not setting @CategoryID or @BrandName to anything anywhere (they're local variables, but you don't assign values to them)

编辑:针对您的评论:该错误告诉您尚未为SP声明任何参数(并且尚未声明)，但您致电了它与参数.根据您对@CategoryID的答复，我猜您希望它成为参数而不是局部变量.试试这个:

Edit Responding to your comment: The error is telling you that you haven't declared any parameters for the SP (and you haven't), but you called it with parameters. Based on your reply about @CategoryID, I'm guessing you wanted it to be a parameter rather than a local variable. Try this:

CREATE PROCEDURE AddBrand
   @BrandName nvarchar(50),
   @CategoryID int
AS
BEGIN
   DECLARE @BrandID int

   SELECT @BrandID = BrandID FROM tblBrand WHERE BrandName = @BrandName

   INSERT INTO tblBrandinCategory (CategoryID, BrandID) VALUES (@CategoryID, @BrandID)
END

然后您将这样称呼:

EXEC AddBrand 'Gucci', 23

...假设品牌名称为"Gucci"，类别ID为23.

...assuming the brand name was 'Gucci' and category ID was 23.

这篇关于如何在SQL Server中声明变量并在同一存储过程中使用它的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！