How to declare a variable in SQL Server and use it in the same Stored Procedure
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 yourSELECT
(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.
In sql 2012 (and maybe as far back as 2005), you should do this:
EXEC AddBrand @BrandName = 'Gucci', @CategoryId = 23
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
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$$