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.

Answer 2

CREATE PROCEDURE AddBrand @BrandName nvarchar(50) = null, @CategoryID int = null AS     BEGIN  DECLARE @BrandID int = null SELECT @BrandID = BrandID FROM tblBrand  WHERE BrandName = @BrandName  INSERT INTO tblBrandinCategory (CategoryID, BrandID)         VALUES (@CategoryID, @BrandID)  END  EXEC AddBrand @BrandName = 'BMW', @CategoryId = 1