How to declare a variable in SQL Server and use it in the same Stored Procedure

Question

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

Answer 1

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

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

In a comment you've said that after fixing the ) you get the error:

Procedure AddBrand has no parameters and arguments were supplied.

That's telling you that you haven't declared any parameters for the SP, 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), -- These are the
   @CategoryID int          -- parameter declarations
AS
BEGIN
   DECLARE @BrandID int

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

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

You would then call this like this:

EXEC AddBrand 'Gucci', 23

or this:

EXEC AddBrand @BrandName = 'Gucci', @CategoryID = 23

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