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

User · Answer

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

User · Answer

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   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    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   You would then call this like this   EXEC AddBrand  Gucci   23      assuming the brand name was  Gucci  and category ID was 23

User · Answer

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

User · Answer

In sql 2012  and maybe as far back as 2005   you should do this   EXEC AddBrand  BrandName    Gucci    CategoryId   23

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

Examples related to sql-server

Examples related to stored-procedures

Examples related to local-variables