Archive

Archive for the ‘Stored Procedures’ Category

Passing multiple/dynamic values to Stored Procedures & Functions | Part 1 – by passing CSV

September 9, 2012 4 comments

Here in the first Part of this series we will use a CSV string that will contain comma separated list of values and pass as a VARCHAR datatype param variable to the SP. Then inside the SP we will parse this VARCHAR CSV string and use those values in our SQL Queries:

-- As always I will use the AdventureWorks database 🙂
USE [AdventureWorks2012]
GO

-- Create an SP with NVARCHAR(MAX) parameter:
CREATE PROCEDURE uspGetPersonDetailsCSV (
	@persons NVARCHAR(MAX)
)
AS
BEGIN
	--DECLARE @persons NVARCHAR(MAX)
	--SET @persons = 'Charles,Jade,Jim,Luke,Ken'

	SELECT T.C.value('.', 'NVARCHAR(100)') AS [Name]
	INTO #tblPersons
	FROM (SELECT CAST ('<Name>' + REPLACE(@persons, ',', '</Name><Name>') + '</Name>' AS XML) AS [Names]) AS A
	CROSS APPLY Names.nodes('/Name') as T(C)
	
	SELECT BusinessEntityID, Title, FirstName, MiddleName, LastName, ModifiedDate
	FROM [Person].[Person] PER
	WHERE EXISTS (SELECT Name FROM #tblPersons tmp WHERE tmp.Name  = PER.FirstName)
	ORDER BY FirstName, LastName

	DROP TABLE #tblPersons
END
GO

-- No execute this SP by passing a list of values comma separated as a single string:
EXEC uspGetPersonDetailsCSV 'Charles,Jade,Jim,Luke,Ken'
GO
-- Check the output, objective achieved 🙂

-- Final Cleanup
DROP PROCEDURE uspGetPersonDetailsCSV
GO

 

This is the most simplest and common method to pass multiple values to an SP, but not ideal. If there are multiple values with count ranging to thousands, then storing them in VARCHAR will not be possible and parsing them would be a big trade off.
 

So, to perform this operation we have another method by using XML String. Check this in my next [blog post].

SQL Server 2012 (a.k.a Denali) – New feature | WITH RESULT SETS

April 12, 2012 4 comments

“WITH RESULT SETS”, a new feature added in SQL Server 2012 allows us to tweak the Column Names and their Datatypes returned by an SP upon executed as per our needs. In SQL Server 2012 while calling an SP you can add the “WITH RESULT SETS” option and can provide the new Column Names and/or with new Datatypes with the parenthesis as per your wish (shown below).

Prior to this if one has to do the same, he has to either:
– Edit the same SP with new column names, or
– Create a new duplicate SP with different column headers, or
– Push records to a temp table first then use the records set form that table (discussed in later part of this post).

Let’s check this new feature by a simple example here by using a Stored Procedure from [AdventureWorks2012] database for SQL Server 2012:

USE [AdventureWorks2012]
GO

-- Normal SP call without using "WITH RESULT SETS" option:
EXEC [dbo].[uspGetEmployeeManagers] @BusinessEntityID = 100
Recordset with same headers as in SP:

RecursionLevel	BusinessEntityID	FirstName	LastName	OrganizationNode	ManagerFirstName	ManagerLastName
0		100			Lolan		Song		/3/1/9/7/		Kok-Ho			Loh
1		93			Kok-Ho		Loh		/3/1/9/			Peter			Krebs
2		26			Peter		Krebs		/3/1/			James			Hamilton
3		25			James		Hamilton	/3/			Ken			Sánchez
-- This is how you can use the new option and get desired headers:
EXEC [dbo].[uspGetEmployeeManagers] @BusinessEntityID = 100
WITH RESULT SETS 
(
	(
	Level INT,
	BusinessID INT, 
	EmpFirstName VARCHAR(50), 
	EmpLastName VARCHAR(50), 
	OrgNode VARCHAR(20), 
	ManagerFirstName VARCHAR(50), 
	ManagerLastName VARCHAR(50)
	)
)
Recordset with new headers as in SP:

Level	BusinessID	EmpFirstName	EmpLastName	OrgNode		ManagerFirstName	ManagerLastName
0	100		Lolan		Song		/3/1/9/7/	Kok-Ho			Loh
1	93		Kok-Ho		Loh		/3/1/9/		Peter			Krebs
2	26		Peter		Krebs		/3/1/		James			Hamilton
3	25		James		Hamilton	/3/		Ken			Sánchez

–> Traditonal appraoch prior to ver. 2012

-- Create a table with columns with required names:
CREATE TABLE #tempData (
	Level INT,
	BusinessID INT, 
	EmployeeFirstName VARCHAR(50), 
	EmployeeLastName VARCHAR(50), 
	OrgNode VARCHAR(20), 
	ManagerFirstName VARCHAR(50), 
	ManagerLastName VARCHAR(50)
	)

-- Insert records from the SP to the table:
INSERT INTO #tempData (Level, BusinessID, EmployeeFirstName, EmployeeLastName, OrgNode, ManagerFirstName, ManagerLastName)
EXEC @return_value = [dbo].[uspGetEmployeeManagers] @BusinessEntityID = 100

-- Finaly select the table and use the records:
SELECT * FROM #tempData

-- Final Cleanup:
DROP TABLE #tempData
GO

Well this is OK, but not very exciting feature for me as a developer.
I was hoping this would allow me to add/remove the columns and/or Concatenate them, like FirstName & LastName to FullName, but No!!!

For more information you can check MS BOL, here: http://msdn.microsoft.com/en-us/library/ms188332(v=sql.110).aspx

Best Practices while creating Stored Procedures

February 26, 2012 8 comments

Stored Procedures in SQL Server provides lot of benefits compared to single/multiple adhoc SQL statements. They help in achieving consistent implementation of logic across applications and improve performance. They also provides us flexibility to design desired business rules and logic by combining various SQL statements, control flow IF-ELSE statements, WHILE loops, and other programming constructs.

Thus to design them it requires a generic approach and some best practices that comes with experience as you work with them. I’m listing some of the best practices that must be followed everytime you create Stored Procedures:

1. SET NOCOUNT ON: Always use ‘SET NOCOUNT ON’ statement at the begening of your code inside the SP to reduce unnecessary network round trips.

2. PARAMETER SNIFFING: Do not use SP parameters directly within the WHERE clause of SQL statements. This may cause the case of Prameter Sniffing. To avod this assign the parameter values to local variables and then use them with SQL queries.

3. Use table variables: Try to use table variables instead of temporary tables inside the SP to cache small record sets whenever possible.

4. Use of Temp Tables: If you think that the temporary record set may exceed upto millions of rows, then try using temporary table instead of table variables. Also try creating indexes on temporary tables if required.

5. Avoid using CURSORS: This will make your SP slow and degrade the performance. Try using more effecient SET based approaches instead of using CURSORS.

6. Use TRY-CATCH block: for error handling, more info [link].

7. Using Transactions: Use Transactions by using BEGIN TRANSACTION and COMMIT/ROLLBACK TRANSACTION blocks to make sure that your actions follow ACID properties [link]. But keep the transctions as short as possible to create less blocking in database and thus avoiding deadlocks.

8. Aliasing objects: Do not use SQL Server reserve keywords while naming SQL Server objects, like for: tables, columns, views, etc. Although it allows most of the time, but its better to use other and better naming conventions.

9. Avoid GOTO stmt: Do not use GOTO statements in your code as it is considered a bad programming practice (and in every other programming language). Try comming up with a better approach/logic, use conditional IF-ELSE logic, WHILE loops and other programming constructs.

10. Avoid ‘sp_’ prefix : Do not prefix the SP name with ‘sp_’. If it begins with ‘sp_’ then the compiler will first search it in master database and then in the current one, thus delaying the execution.

11. Use fully qualified objects name: in your SQL queries, this helps in quickly finding the cached/compiled plan. Also execute the SP by using its fully qualified name, like: EXEC dbo.SPname

12. Use WITH ENCRYPTION: You can also use ‘WITH ENCRYPTION’ option while creating SP to hide the code.

13. Add Comments: Try to put comments wherever possible to give details and idea to other developers what actually your code is doing.

14. Use BEGIN-END code block: Try to bind your whole SP code within BEGIN-END block. Although it is optional but it looks good and gives a better picture.

16. Beautify your code: Well indent your code by uisng TABs, Spaces and New line (ENTER). Try going vertically downwards with your code rather expanding horizontally. This will make your code easy to read and more understandable to other developers.

17. Use following template to create your SP:

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName> 
	-- Add the parameters for the stored procedure here
	<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>, 
	<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
GO

You can also generate this template, in Object Explorer goto Database -> Programability -> Stored Procedures: Right click and select ‘New Stored Procedure…’. The above code will get generated.
 

>> Check & Subscribe my [YouTube videos] on SQL Server.
 


Creating Stored Procedure with Dynamic Search, Paging and Sorting

December 30, 2011 12 comments

In my last two previous post we learned to apply [Dynamic Search Capability] and [Pagination] in an SP.

Here in this post we will see how can we apply Sorting at different columns of the returned record set from a Stored Procedure.

In webpage’s data grids when you click on the column header the records are sorted according to the selected column. When you again click that column the ordering reverses for that column. So to implement this functionality we will be passing an extra parameter to the SP which will contain the Column name and sort order, DESC or ASC.

Let’s check this with a sample code using [Person].[Contact] Table of [AdventureWorks] Database:

USE [AdventureWorks]
GO

CREATE PROCEDURE USP_GET_Contacts_DynSearch_Paging_Sorting
(
	-- Optional Filters for Dynamic Search
	@ContactID			INT = NULL, 
	@FirstName			NVARCHAR(50) = NULL, 
	@LastName			NVARCHAR(50) = NULL, 
	@EmailAddress		NVARCHAR(50) = NULL, 
	@EmailPromotion		INT = NULL, 
	@Phone				NVARCHAR(25) = NULL,
	-- Pagination
	@PageNbr			INT = 1,
	@PageSize			INT = 10,
	-- Sort Details
	@SortCol			NVARCHAR(20) = ''
)
AS
BEGIN
	DECLARE 
		@lContactID			INT, 
		@lFirstName			NVARCHAR(50), 
		@lLastName			NVARCHAR(50), 
		@lEmailAddress		NVARCHAR(50), 
		@lEmailPromotion	INT, 
		@lPhone				NVARCHAR(25)
	
	DECLARE 
		@lPageNbr	INT,
		@lPageSize	INT,
		@lSortCol	NVARCHAR(20),
		@lFirstRec	INT,
		@lLastRec	INT,
		@lTotalRows INT

	SET @lContactID			= LTRIM(RTRIM(@ContactID))
	SET @lFirstName			= LTRIM(RTRIM(@FirstName))
	SET @lLastName			= LTRIM(RTRIM(@LastName))
	SET @lEmailAddress		= LTRIM(RTRIM(@EmailAddress))
	SET @lEmailPromotion	= @EmailPromotion
	SET @lPhone				= LTRIM(RTRIM(@Phone))

	SET @lPageNbr	= @PageNbr
	SET @lPageSize	= @PageSize
	SET @lSortCol	= LTRIM(RTRIM(@SortCol))
	
    SET @lFirstRec	= ( @lPageNbr - 1 ) * @lPageSize
    SET @lLastRec	= ( @lPageNbr * @lPageSize + 1 ) 
    SET @lTotalRows = @lFirstRec - @lLastRec + 1

    ; WITH CTE_Results
    AS (
		SELECT ROW_NUMBER() OVER (ORDER BY 
			CASE WHEN @lSortCol = 'ContactID_Asc' THEN ContactID
				END ASC,
			CASE WHEN @lSortCol = 'ContactID_Desc' THEN ContactID
				END DESC, 

			CASE WHEN @lSortCol = 'Title_Asc' THEN Title
				END ASC,
			CASE WHEN @lSortCol = 'Title_Desc' THEN Title
				END DESC, 

			CASE WHEN @lSortCol = 'FirstName_Asc' THEN FirstName
				END ASC,
			CASE WHEN @lSortCol = 'FirstName_Desc' THEN FirstName
				END DESC, 

			CASE WHEN @lSortCol = 'MiddleName_Asc' THEN MiddleName
				END ASC,
			CASE WHEN @lSortCol = 'MiddleName_Desc' THEN MiddleName
				END DESC, 

			CASE WHEN @lSortCol = 'LastName_Asc' THEN LastName
				END ASC,
			CASE WHEN @lSortCol = 'LastName_Desc' THEN LastName
				END DESC, 

			CASE WHEN @lSortCol = 'Suffix_Asc' THEN Suffix
				END ASC,
			CASE WHEN @lSortCol = 'Suffix_Desc' THEN Suffix
				END DESC, 

			CASE WHEN @lSortCol = 'EmailAddress_Asc' THEN EmailAddress
				END ASC,
			CASE WHEN @lSortCol = 'EmailAddress_Desc' THEN EmailAddress
				END DESC, 

			CASE WHEN @lSortCol = 'EmailPromotion_Asc' THEN EmailPromotion
				END ASC,
			CASE WHEN @lSortCol = 'EmailPromotion_Desc' THEN EmailPromotion
				END DESC, 

			CASE WHEN @lSortCol = 'Phone_Asc' THEN Phone
				END ASC,
			CASE WHEN @lSortCol = 'Phone_Desc' THEN Phone
				END DESC

			) AS ROWNUM,
			ContactID, 
			Title, 
			FirstName, 
			MiddleName, 
			LastName, 
			Suffix, 
			EmailAddress, 
			EmailPromotion, 
			Phone
		FROM Person.Contact
		WHERE 
			(@lContactID IS NULL OR ContactID = @lContactID)
		AND (@lFirstName IS NULL OR FirstName LIKE '%' + @lFirstName + '%')
		AND (@lLastName IS NULL OR LastName LIKE '%' + @lLastName + '%')
		AND (@lEmailAddress IS NULL OR EmailAddress LIKE '%' + @lEmailAddress + '%')
		AND (@lEmailPromotion IS NULL OR EmailPromotion = @lEmailPromotion)
		AND (@lPhone IS NULL OR Phone = @lPhone)
	)
	SELECT 
		ContactID, 
		Title, 
		FirstName, 
		MiddleName, 
		LastName, 
		Suffix, 
		EmailAddress, 
		EmailPromotion, 
		Phone
	FROM CTE_Results AS CPC
    WHERE 
		ROWNUM > @lFirstRec 
	AND ROWNUM < @lLastRec
	ORDER BY ROWNUM ASC 
		
END
GO

OK, now let’s test this SP:

-- No parameters provided, fetch first 10 default records:
EXEC USP_GET_Contacts_DynSearch_Paging_Sorting

-- On providing @SortCol = 'FirstName_Desc', will fetch 20 records sorted by FirstName:
EXEC USP_GET_Contacts_DynSearch_Paging_Sorting @SortCol = 'FirstName_Asc'

-- On providing @SortCol = 'LastName_Asc', will fetch 20 records sorted by LastName in descending order:
EXEC USP_GET_Contacts_DynSearch_Paging_Sorting @SortCol = 'LastName_Desc'

-- Following query will pull out 20 records in a page having FirstName LIKE 'Sam' sorted by LastName in Descending order:
EXEC USP_GET_Contacts_DynSearch_Paging_Sorting @FirstName = 'Sam', @PageSize = 20, @SortCol = 'LastName_Desc' 

Appreciate your valuable comments if we can do this in a different or/and better way.

Creating Stored Procedures with Dynamic Search & Paging (Pagination)

December 30, 2011 4 comments

In my previous post we learned how we can apply Dynamic Search capabilities to an SP.

Here we will see how can we apply paging (or pagination) on the returned recordsets from a Stored Procedures.

Pagination is required when lot of records are returned from an SP and the webpage becomes very heavy to load it, like more than 1k records. And it also becomes difficult for a user to handle that much records and view in a single screen.

Many webpages displaying records has First, Previous, Next & Last buttons on top and bottom of the data grid. So to make the buttons function we’ve to implement the paging functionality on SPs itself.

Let’s check with a sample code using [Person].[Contact] Table of [AdventureWorks] Database:

CREATE PROCEDURE USP_GET_Contacts_DynSearch_Paging
(
	-- Pagination
	@PageNbr			INT = 1,
	@PageSize			INT = 10,
	-- Optional Filters for Dynamic Search
	@ContactID			INT = NULL, 
	@FirstName			NVARCHAR(50) = NULL, 
	@LastName			NVARCHAR(50) = NULL, 
	@EmailAddress		NVARCHAR(50) = NULL, 
	@EmailPromotion		INT = NULL, 
	@Phone				NVARCHAR(25) = NULL
)
AS
BEGIN
	DECLARE 
		@lContactID			INT, 
		@lFirstName			NVARCHAR(50), 
		@lLastName			NVARCHAR(50), 
		@lEmailAddress		NVARCHAR(50), 
		@lEmailPromotion	INT, 
		@lPhone				NVARCHAR(25)
	
	DECLARE 
		@lPageNbr	INT,
		@lPageSize	INT,
		@lFirstRec	INT,
		@lLastRec	INT,
		@lTotalRows INT

	SET @lContactID			= @ContactID
	SET @lFirstName			= LTRIM(RTRIM(@FirstName))
	SET @lLastName			= LTRIM(RTRIM(@LastName))
	SET @lEmailAddress		= LTRIM(RTRIM(@EmailAddress))
	SET @lEmailPromotion	= @EmailPromotion
	SET @lPhone				= LTRIM(RTRIM(@Phone))

	SET @lPageNbr	= @PageNbr
	SET @lPageSize	= @PageSize
	
    SET @lFirstRec	= ( @lPageNbr - 1 ) * @lPageSize
    SET @lLastRec	= ( @lPageNbr * @lPageSize + 1 ) 
    SET @lTotalRows = @lFirstRec - @lLastRec + 1

    ; WITH CTE_Results
    AS (
		SELECT ROW_NUMBER() OVER (ORDER BY ContactID) AS ROWNUM,
			ContactID, 
			Title, 
			FirstName, 
			MiddleName, 
			LastName, 
			Suffix, 
			EmailAddress, 
			EmailPromotion, 
			Phone
		FROM Person.Contact
		WHERE 
			(@lContactID IS NULL OR ContactID = @lContactID)
		AND (@lFirstName IS NULL OR FirstName LIKE '%' + @lFirstName + '%')
		AND (@lLastName IS NULL OR LastName LIKE '%' + @lLastName + '%')
		AND (@lEmailAddress IS NULL OR EmailAddress LIKE '%' + @lEmailAddress + '%')
		AND (@lEmailPromotion IS NULL OR EmailPromotion = @lEmailPromotion)
		AND (@lPhone IS NULL OR Phone = @lPhone)
	)
	SELECT 
		ContactID, 
		Title, 
		FirstName, 
		MiddleName, 
		LastName, 
		Suffix, 
		EmailAddress, 
		EmailPromotion, 
		Phone
	FROM CTE_Results AS CPC
    WHERE 
		ROWNUM > @lFirstRec 
	AND ROWNUM < @lLastRec
	ORDER BY ROWNUM ASC 

END
GO

OK, now let’s test this SP:

-- No parameters provided, fetch first 10 default records:
EXEC USP_GET_Contacts_DynSearch_Paging

-- On providing @PageSize=20, will fetch 20 records:
EXEC USP_GET_Contacts_DynSearch_Paging @PageSize=20

-- On providing @PageNbr=2, @PageSize=10, will display second page, ContactID starting from 11 to 20:
EXEC USP_GET_Contacts_DynSearch_Paging @PageNbr=2, @PageSize=10

-- On providing @PageNbr=1, @PageSize=50, @FirstName = 'Sam', it will search FurstName like Sam and will fetch first 50 records:
EXEC USP_GET_Contacts_DynSearch_Paging @PageNbr=1, @PageSize=50, @FirstName = 'Sam'

SQL Server 2012 (a.k.a Denali) has a new mechanism to implement pagination by using OFFSET FETCH clause, check it here in my blog post.

Appreciate your valuable comments if we can do this in a different or/and better way.