SQL with Manoj

In my previous posts we saw how to pass multiple values to an SP by using CSV list and XML data, which are almost of same type. Here in this post we will see how we can achieve the same objective without passing values as parameters and by using temporary (temp, #) tables.

Here in the third part of this series the Stored Procedure will be created in such a way that it will use a Temporary Table, which does not exist in compile time. But at run time the temp-table should be created before running the SP. In this approach there is no need to pass any parameter with the SP, let’s see how:

-- Create Stored Procedure with no parameter, it will use the temp table created outside the SP:
CREATE PROCEDURE uspGetPersonDetailsTmpTbl
AS
BEGIN
	
	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

END
GO

-- Now, create a temp table, insert records with same set of values we used in previous 2 posts:
CREATE TABLE #tblPersons (Name NVARCHAR(100))

INSERT INTO #tblPersons
SELECT Names FROM (VALUES ('Charles'), ('Jade'), ('Jim'), ('Luke'), ('Ken') ) AS T(Names)

-- Now execute the SP, it will use the above records as input and give you required results:
EXEC uspGetPersonDetailsTmpTbl
-- Check the output, objective achieved 🙂

DROP TABLE #tblPersons
GO

-- Final Cleanup
DROP PROCEDURE uspGetPersonDetailsTmpTbl
GO

This approach is much better than the previous 2 approaches of using CSV or XML data.

The values will be entered in temp tables and then will be accessed inside the SP. There is no parsing involved as the records are directly read from temp table and used in SQL query just like normal queries. Also there is no limit of records if you compare with CSVs & XML data.

But, the catch here is, the temp table should exist before executing the SP, if for some reason it is not there the code will crash. But its rare and can be handled by some checks.

In my next and last [blog post] of this series we will see a new feature of SQL Server 2008 i.e. TVP, which can be used in such type of scenarios, check here.

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