Hi,
For suppose if you want to insert data from a table into multiple tables, then comes the use of Local and Global variables.
Example:
I have a Details table which contains the columns:
ProductName,
ManufacturerName,
CategoryName
I need to insert these details into multiple tables which are Product(ID, Name), Manufacturer(ID, Name), Category(ID, Name), Product_Category_Mapping(ProductID, CategoryID) and Product_Manufacturer_Mapping(ProductID, ManufacturerID)
Now, my proc will be something like this
CREATE PROC InsertDetails
AS
BEGIN
//Declaring Local Variables
DECLARE @ProductID INT,
@ManufacturerID INT,
@CategoryID INT
//Inserting Product Details into Product table
INSERT INTO Product
SELECT ProductName FROM Details
//Getting the ID of the Product that got inserted and storing the value into a global variable
SELECT @ProductID = @@IDENTITY
//Inserting Manufacturer Deatils
INSERT INTO Manufacturer
SELECT ManufacturerName FROM Details
SELECT @ManufacturerID = @@IDENTITY
//Inserting Category Details
INSERT INTO Category
SELECT CategoryName FROM Details
SELECT @CategoryID = @@IDENTITY
INSERT INTO Product_Category_Mapping VALUES(@ProductID, @CategoryID)
INSERT INTO Product_Manufacturer_Mapping VALUES(@ProductID, @ManufacturerID)
END
If you have another procedure and you want to use the same local variables , then again you need to declare the local variables and use them whereas for global variables there is no need of declaration. SQL Server provides some default global variables
Example:
@@Connection:
It is a global variable which contains the connection connection string, and will be same for any procedure.
Local and Global variables in SQL Server are similar to that of local and global variables in c#.net where Local variables are decalred inside the method and they can be used only by that particular method where the global variables can be used any where in the assembly.
Please Mark as Answer if satisfied...........
Thank You,
Kavya Shree M.
Kavya Shree Mandapalli
Allemahesh, if this helps please login to Mark As Answer. | Alert Moderator