[sql-server] How to declare a variable in SQL Server and use it in the same Stored Procedure

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

None of the above methods worked for me so i'm posting the way i did

DELIMITER $$
CREATE PROCEDURE AddBrand()
BEGIN 

DECLARE BrandName varchar(50);
DECLARE CategoryID,BrandID  int;
SELECT BrandID = BrandID FROM tblBrand 
WHERE BrandName = BrandName;

INSERT INTO tblBrandinCategory (CategoryID, BrandID) 
       VALUES (CategoryID, BrandID);
END$$

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

In sql 2012 (and maybe as far back as 2005), you should do this:

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