Stored Procedure | SQL with Manoj

Passing multiple/dynamic values to Stored Procedures & Functions | Part 2 – by passing XML

September 9, 2012 4 comments

In my previous post [Part 1] we saw how to pass multiple values to a parameter as a CSV string in an SP.

Here in the second part of this series we will use XML string that will contain the set of values and pass as an XML param variable to the SP. Then inside the SP we will parse this XML and use those values in our SQL Queries, just like we did in previous post with CSV string:

USE [AdventureWorks2012]
GO

-- Create an SP with XML type parameter:
CREATE PROCEDURE uspGetPersonDetailsXML (
	@persons XML
)
AS
BEGIN
	--DECLARE @persons XML
	--SET @persons = '<root><Name>Charles</Name><Name>Jade</Name><Name>Jim</Name><Name>Luke</Name><Name>Ken</Name></root>'

	SELECT T.C.value('.', 'NVARCHAR(100)') AS [Name]
	INTO #tblPersons
	FROM @persons.nodes('/root/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

-- Create XML string:
DECLARE @xml XML
SET @xml = '<root>
				<Name>Charles</Name>
				<Name>Jade</Name>
				<Name>Jim</Name>
				<Name>Luke</Name>
				<Name>Ken</Name>
			</root>'

-- Use the XML string as parameter which calling the SP:
EXEC uspGetPersonDetailsXML @xml
GO
-- Check the output, objective achieved 🙂

-- Final Cleanup
DROP PROCEDURE uspGetPersonDetailsXML
GO

This approach looks much cleaner and more stable to me than the previous one (CSV). XML is a de-facto standard to store and transmit data in a more structured way. Thus I prefer XML string over the CSV string on these type of cases.

The CSV approach is no different than this one, it internally converts the CSV string to an XML then parse it. Thus, like CSV if the XML string becomes lengthy it will also take time to parse the whole XML and then use the values in SQL queries in the SP.

In next [blog post] we will see how we can perform the same operation by using temporary (temp, #) tables.

Categories: SQL Tips, Stored Procedures, XML Tags: multiple values to Stored Procedures, Stored Procedure, XML SQL

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].

Categories: SQL Tips, Stored Procedures Tags: multiple values to Stored Procedures, Stored Procedure

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

Categories: SQL Server 2012, Stored Procedures Tags: Denali, SQL Server 2012, Stored Procedure, WITH RESULT SETS

Select multiple number of OrderIDs and send the criteria to a Stored Procedure parameter – MSDN TSQL forum

April 21, 2011 Leave a comment

–> Question:

I’m trying to select any number of orders and call a stored procedure only one time. So my question is in order to perform a select statement in a stored procedure, is it possible to have n number of parameters, and if it is possible what is the best way to call a stored procedure with any number of orderIds? Or is it better to send in a nvarchar 30000 with xml and parse the xml for each order id and stick it into my stored procedure that way? I’m looking for a better way to perform this operation that is reliable.

example:

select * 
from tblOrders 
where OrderId = 1 or OrderId = 2 or OrderId = 1000 etc...

–> Answer:

Yes, not only XML, but there are various ways to pass multiple values to a Stored Proc via parameters, like:
1. Passing CSV string
2. Passing an XML or JSON string
3. Using temp-table
4. Using TVPs
… check here for all these options, link.

You can go with by creating a temp table:

CREATE TABLE #temptblOrders (OrderID int)

… store all OrderIDs into this.

Use this temp table inside your proc as:

SELECT * 
FROM tblOrders 
WHERE OrderId in (
	SELECT OrderID 
	FROM #temptblOrders
)

Ref link.

Categories: SQL Server Questions Tags: MSDN TSQL forum, Stored Procedure

Using OUTPUT Parameters in Stored Procedures

March 23, 2011 11 comments

According to MS-BOL, SQL Server Stored-Procedures can return data in 4 forms:

1. Return Code: which are always an integer value.

2. OUTPUT Parameter: which can return either data (such as an integer or character value) or a cursor variable (cursors are result sets that can be retrieved one row at a time).

3. A result set for each SELECT statement contained in the stored procedure or any other stored procedures called by the stored procedure.

4. A global cursor that can be referenced outside the stored procedure.

Specifying OUTPUT keyword to the parameters in the Stored Procedures can return the values of the parameters to the calling program.

Let’s check this with a simple example by using AdventureWorks database:

USE [AdventureWorks]
GO

--// Create Stored Prcedure with OUTPUT parameter
CREATE PROCEDURE getContactName
	@ContactID INT,
	@FirstName VARCHAR(50) OUTPUT,
	@LastName  VARCHAR(50) OUTPUT
AS
BEGIN
	SELECT @FirstName = FirstName, @LastName = LastName
	FROM Person.Contact
	WHERE ContactID = @ContactID
end
GO

--// Test the Procedure
DECLARE @CID INT, @FName VARCHAR(50), @LName VARCHAR(50)

--/ Test# 1
SET @CID = 100
EXEC getContactName @ContactID=@CID,
					@FirstName=@FName OUTPUT,
					@LastName=@LName OUTPUT

SELECT @FName as 'First Name', @LName as 'Last Name'
--/ Output
-- ContactID	First Name	Last Name
-- 100			Jackie		Blackwell

--/ Test# 2
SET @CID = 200
EXEC getContactName @ContactID=@CID,
					@FirstName=@FName OUTPUT,
					@LastName=@LName OUTPUT

SELECT @FName as 'First Name', @LName as 'Last Name'
--/ Output
-- ContactID	First Name	Last Name
-- 200			Martin		Chisholm
GO

--// Final Cleanup
DROP PROCEDURE getContactName
GO

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

Categories: SQL Tips Tags: OUTPUT, OUTPUT Parameter, Stored Procedure

Newer Entries