Advanced TSQL In a Nutshell

Advanced TSQL 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
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.
Syed Adeel Ahmed on FacebookSyed Adeel Ahmed on GoogleSyed Adeel Ahmed on InstagramSyed Adeel Ahmed on Twitter

Published by

Syed Adeel Ahmed

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