Advanced T-SQL In a Nutshell




Advanced T-SQL In a Nutshell

t-sql

Image credit: wikipedia.org

When learning about advanced tsql in a nutshell , we have to follow the syntax and proper knowledge about the conditions and loops. Take it easy and get relaxed because now I am going to show you the real advanced T-Sql statements from my own project. I will show you how to write stored procedures, triggers and cursors without getting troubled :).
So, here we go

What is a Stored Procedure ?

A stored procedure is basically pre-compiled set of statements which are executed on each invocation to the procedure.

Syntax of Stored Procedure

Here is the syntax for your convenience and believe me, it is very easy

Use Database
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

Create Procedure [Procedure Name] (@parameter1 type, @paramneter2 type)

USE [Budget]
GO
/****** Object: StoredProcedure [dbo].[proc_Add] Script Date: 1/23/2014 1:06:08 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
AS

Statement(s)

Now here’s comes my hand made tasty tasty procedures.

Create Procedure [dbo].[proc_Add]

(@userID nvarchar(50),@userName nvarchar(50),@budgetPlan nvarchar(10),
@amountInhand float,@amountConsumed float,@startDay nvarchar(50),@endDay nvarchar(50),
@currentAmount float)
AS
Insert Into dbo.tbl_BudgetManagement(userID,userName,budgetPlan,amountInhand,
amountConsumed,startDay,endDay,currentAmount)
Values(@userID,@userName,@budgetPlan,
@amountInhand,@amountConsumed,@startDay,@endDay,
@currentAmount)

There’s nothing so complex about it. It is written to insert a row to the table dbo.tbl_BudgetManagement

Here’s that table for your convenience

USE [Budget]
GO
/****** Object: StoredProcedure [dbo].[proc_BalanceBudget] Script Date: 1/23/2014 1:21:06 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[proc_BalanceBudget]
(@ID int,@userID nvarchar,@consumedAmount float)

AS

BEGIN

TRAN

Update tbl_Bank Set [email protected] Where [ID][email protected] AND [email protected]

Update tbl_Loan Set [email protected] Where [ID][email protected] AND [email protected]

Commit

The above procedure updates the table. We started the transaction by using BEGIN TRAN
and completed it using the word COMMIT. This is because we want to update both the tables in a single go.

USE [Budget]
GO

/****** Object: StoredProcedure [dbo].[proc_SelectBudgetsbyID] Script Date: 1/23/2014 1:27:16 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[proc_SelectBudgetsbyID]

(@userID nvarchar(50))

AS

Select * From dbo.tbl_BudgetManagement Where [userID][email protected]

GO

Quite easy…. Isn’t it ?

USE [Budget]
GO

/****** Object: StoredProcedure [dbo].[proc_ComplexUpdateBank] Script Date: 1/23/2014 2:30:23 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[proc_ComplexUpdateBank]
(@ID int,@userID nvarchar(50),@loanPenaltyperday float,@consumedAmount float,@interestAmount float)

AS

Begin Tran

Declare @LoanPenalty float

Set @LoanPenalty=(Select loanPenaltyperday From tbl_Bank Where [email protected] AND [email protected] )

If @LoanPenalty Is Not Null OR @LoanPenalty >0

Begin

Set @LoanPenalty=@LoanPenalty-@loanPenaltyperday

End

Else

Begin

Set @LoanPenalty=0

End

Declare @Amount float

Set @Amount=(Select loanAmount From tbl_Bank Where [email protected] AND [email protected])

If @Amount Is Not Null OR @Amount>0

Begin

Set @[email protected] – @consumedAmount

End

Else

Begin

Set @Amount=0

End

Declare @Interest float

Set @Interest=(Select interestAmount From tbl_Bank Where [email protected] AND [email protected] )

If @Interest Is Not Null OR @Interest>0

Begin

Set @Interest=@Interest-@interestAmount

End

Else

Begin

Set @Interest=0

End

Update tbl_Bank Set [email protected],[email protected],[email protected],[email protected] Where [email protected] AND [email protected]

Commit

GO

The above stored procedure looks complex if you think it is. But it actually has few more statements
and the if condition and other checks are quite straight forward. One more thing I want to discuss that those variables which we declare with the word Declare can be called the local variables of the procedure.Those which are in the brackets adjacent to the procedure name are input or output variables.

To grasp the basic idea of Output Parameters ,click here

USE [Budget]
GO

/****** Object: StoredProcedure [dbo].[proc_InsertintoandSelectFromTempstrtdtCursor] Script Date: 1/23/2014 3:20:46 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE Procedure [dbo].[proc_InsertintoandSelectFromTempstrtdtCursor]
(@userID nvarchar(50))

AS

If Exists (Select * From tbl_Tempstrtdt Where [email protected])

Begin

Delete From tbl_Tempstrtdt Where [email protected]

End

Declare @startDay datetime
DECLARE curName CURSOR FOR SELECT startDay FROM tbl_BudgetManagement Where [email protected]
OPEN curName

FETCH NEXT FROM curName INTO @startDay

WHILE @@FETCH_STATUS = 0
BEGIN

–insert code here
FETCH NEXT FROM curName INTO @startDay

Insert Into tbl_Tempstrtdt(userID,startDay) Values(@userID,@startDay)
END

CLOSE curName
DEALLOCATE curName

Select Min(startDay) as minDate From tbl_Tempstrtdt Where [email protected]

GO

The above procedure uses a technique called Cusror. Just forget everything and look carefully at the given lines

DECLARE curName CURSOR FOR SELECT startDay FROM tbl_BudgetManagement Where [email protected]
OPEN curName

FETCH NEXT FROM curName INTO @startDay

WHILE @@FETCH_STATUS = 0
BEGIN

–insert code here
FETCH NEXT FROM curName INTO @startDay

Insert Into tbl_Tempstrtdt(userID,startDay) Values(@userID,@startDay)
END

CLOSE curName
DEALLOCATE curName

* A cursor name curName has been declared. It holds all the startDate column values returned from tbl_BudgetManagement per user basis

* As it is not possible to store stream-like multiple values of a particular column in a local variable by using just Declare keyword, we have to feed the values into the cursor
by using the select statement.

* Now we also have to fetch these values from that cursor. For this reason, we have used the while loop where @@Fetch-Status=0 indicates that keep on pulling the values contained in the cursor into the local variable @startDay till the @Fetch-Status flag is ‘0’.We have to fetch the value contained inside the cursor on each iteration of the loop till it terminates.

* Close the Cursor and De-allocate the occupied space


Syed Adeel Ahmed
Syed Adeel Ahmed
Analyst, Programmer, Educationist and Blogger at Technofranchise
Computer Systems Engineer from Sir Syed University Of Engineering & Technology.I am passionate about all types of programming.

Published by

Syed Adeel Ahmed

Computer Systems Engineer from Sir Syed University Of Engineering & Technology.I am passionate about all types of programming.