explain this sql query [Resolved]

Posted by Gow.Net under Sql Server on 7/17/2012 | Points: 10 | Views : 1246 | Status : [Member] | Replies : 3
please any one explain this SQL Queries
CREATE PROCEDURE [dbo].[AddNewFiles]
(
@CategoryName varchar(50),
@FileName varchar(50),
@FileCost money,
@Description varchar(200),
@FileLocation nvarchar(MAX),
@EncryptFile nvarchar(MAX)

)
AS
BEGIN TRANSACTION
IF NOT EXISTS (SELECT 1 FROM Categories WHERE CategoryName=@CategoryName)
BEGIN
INSERT INTO dbo.Categories (CategoryName) VALUES (@CategoryName);
END

DECLARE @CatID int
SET @CatID = (SELECT CategoryID FROM dbo.Categories WHERE CategoryName=@CategoryName);
INSERT dbo.FileDetails (CategoryID, FileName, FileCost, Description,FileLocation,EncryptFile)
VALUES (@CatID,@FileName, @FileCost, @Description,@FileLocation,@EncryptFile );
COMMIT TRANSACTION
GO


gowthaman8870226416


Responses

Posted by: Rickeybglr on: 7/17/2012 [Member] Starter | Points: 50

Up
0
Down

Resolved
i ll explain you line by line.
Begin transaction ---commit transaction use when you r inserting data in more than 1 table at a time. it ensure tht insertion must happen in both the table or othrwise not in a single table.
if NOT exist .. this line checks categoryName is already present in table or not. if not then it will go further else execution of SP will not happen
SET @CatID -- after insertion into first table its catID get generated which you need to insert in 2nd table so you r fetching it from 1st table on the basis of categryname.
mark ths as a ans if ur satisfied



Gow.Net, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: ndpatil999-15890 on: 7/17/2012 [Member] Starter | Points: 25

Up
0
Down
Nice Explaination

Gow.Net, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Patel28rajendra on: 7/18/2012 [Member] Starter | Points: 25

Up
0
Down
Hi

Rickeybglr

Nice Explanation

R D Patel

Gow.Net, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response