Archive
Passing multiple/dynamic values to Stored Procedures & Functions | Part4 – by using TVP
This is the last part (4th) of this series, in previous posts we talked about passing multiple values by following approaches: CSV, XML, #table. Here we will use a new feature introduced in SQL Server 2008, i.e. TVP (Table Valued Parameters).
As per MS BOL, Table-Valued Parameters are declared by using user-defined table types. We can use TVPs to send multiple rows of data to Stored Procedure or Functions, without creating a temporary table or many parameters. TVPs are passed by reference to the routines thus avoiding copy of the input data.
Let’s check how we can make use of this new feature (TVP):
–> Method #4 – If you are in 2008 and above try TVPs (Table Valued Parameters) and pass it as a parameter:
-- First create a User-Defined Table type with a column that will store multiple values as multiple records:
CREATE TYPE dbo.tvpNamesList AS TABLE
(
Name NVARCHAR(100) NOT NULL,
PRIMARY KEY (Name)
)
GO
-- Create the SP and use the User-Defined Table type created above and declare it as a parameter:
CREATE PROCEDURE uspGetPersonDetailsTVP (
@tvpNames tvpNamesList READONLY
)
AS
BEGIN
SELECT BusinessEntityID, Title, FirstName, MiddleName, LastName, ModifiedDate
FROM [Person].[Person] PER
WHERE EXISTS (SELECT Name FROM @tvpNames tmp WHERE tmp.Name = PER.FirstName)
ORDER BY FirstName, LastName
END
GO
-- Now, create a Table Variable of type created above:
DECLARE @tblPersons AS tvpNamesList
INSERT INTO @tblPersons
SELECT Names FROM (VALUES ('Charles'), ('Jade'), ('Jim'), ('Luke'), ('Ken') ) AS T(Names)
-- Pass this table variable as parameter to the SP:
EXEC uspGetPersonDetailsTVP @tblPersons
GO
-- Check the output, objective achieved 
-- Final Cleanup
DROP PROCEDURE uspGetPersonDetailsTVP
GO
So, we saw how we can use TVPs with Stored Procedures, similar to this they are used with UDFs.
TVPs are a great way to pass array of values as a single parameter to SPs and UDFs. There is lot of know and understand about TVP, their benefits and usage, check this [link].
Passing multiple/dynamic values to Stored Procedures & Functions | Part3 – by using #table
In 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.
The SP will be created in such a way that it will use a temp table, which does not exist in compile time. But at run time temp-table should be created before running the SP, let’s see how:
–> Method #3 – Using a temp table inside an SP which is created outside just before its execution. There is no need to pass any parameter with the SP here:
-- 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 next and last 4th 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.
Passing multiple/dynamic values to Stored Procedures & Functions | Part2 – by passing XML
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 this post we will use XML string that will contain the same set of values and pass it 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.
–> Method #2 – Passing an XML string as a parameter value in SP of XML datatype. We will need to parse XML inside the SP:
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 Part (3) we will see how we can perform the same operation by using temporary (temp, #) tables. Go to part3.
Passing multiple/dynamic values to Stored Procedures & Functions | Part1 – by passing CSV
This post comes from MSDN T-SQL Forum, [link].
Stored Procedures accept only fixed and pre-defined number of parameters thus there is a limitation you cannot provide dynamic number of params, like you can do with other languages, like C, C#, Java, etc. In these languages there is concept of Arrays which you can pass in a method/function, but in SQL there are no arrays and it does not have any datatype that support arrays. Thus if you have to provide multiple values to any parameter you cannot do it directly, though there are some workarounds.
Following are some workarounds or ways by which we can pass multiple values to a Stored Procedure or Function param, by:
1 – Passing a CSV: list of strings as a parameter to a (N)VARCHAR datatype parameter, then splitting/parsing it inside the SP or UDF.
2 – Passing an XML: string as an XML datatype parameter. We will need to parse the XML inside the SP.
3 – Using a temp table: inside an SP which is created outside just before its execution. Here there is no need to pass any parameter with the SP.
4 – Using TVPs: With SQL Server 2008 and above you can create TVPs or Table Valued Parameters and declare them by using user-defined table types. These TVPs can then be used to send multiple rows of data to SPs or UDFs, without creating a temp table or multiple parameters.
Let’s visit each of the above menthods by simple hands-on examples:
–> Method #1 – Passing CSV (Comma Separated Values) list of strings as an argument to a (N)VARCHAR parameter, then splitting/parsing it inside the SP:
-- As always I will use the AdventureWorks database
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 option listed above in #2, i.e. XML. Check this in my next blog post.
OUTPUT clause and MERGE statement
Just responded to a post in MSDN forum, link: http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/c06e1db4-7fd6-43c4-8569-5335d555dac8
Accroding to MS-BOL, OUTPUT clause returns information from, or expressions based on, each row affected by an INSERT, UPDATE, DELETE, or MERGE statement. These results can be returned to the processing application for use in such things as confirmation messages, archiving, and other such application requirements. The results can also be inserted into a table or table variable. Additionally, you can capture the results of an OUTPUT clause in a nested INSERT, UPDATE, DELETE, or MERGE statement, and insert those results into a target table or view.
Lets go with a self descriptive example:
–> OUTPUT with INSERT
create table manoj (sn int, ename varchar(50)) insert into manoj OUTPUT INSERTED.* values (1,'manoj'), (2,'hema'), (3,'kanchan'), (4,'pankaj')
This gives me following output instead of normal message (N row(s) affected):
sn ename
1 manoj
2 hema
3 kanchan
4 pankaj
select * from manoj
This gives me the same output as above:
sn ename
1 manoj
2 hema
3 kanchan
4 pankaj
–> OUTPUT with DELETE
delete from manoj OUTPUT DELETED.* where sn = 4
This gives me following output:
sn ename
4 pankaj
select * from manoj
Now the result set is changed to:
sn ename
1 manoj
2 hema
3 kanchan
–> OUTPUT with UPDATE
update manoj set ename = 'pankaj' OUTPUT DELETED.*, INSERTED.* from manoj where sn = 2
This gives me following output:
sn ename sn ename
2 hema 2 pankaj
select * from manoj
Now the result set is changed to:
sn ename
1 manoj
2 pankaj
3 kanchan
–> OUTPUT with MERGE
According to MS-BOL, MERGE performs insert, update, or delete operations on a target table based on the results of a join with a source table. For example, you can synchronize two tables by inserting, updating, or deleting rows in one table based on differences found in the other table.
create table manoj2 (sn int, ename varchar(50)) insert into manoj2 values (1,'manoj'), (2,'hema'), (3,'kanchan'), (4,'pankaj'), (5,'saurabh') select * from manoj2
This gives me following output instead of normal message (N row(s) affected):
sn ename
1 manoj
2 hema
3 kanchan
4 pankaj
MERGE manoj AS TARGET USING (SELECT sn, ename FROM manoj2) AS SOURCE ON (TARGET.sn = SOURCE.sn) WHEN MATCHED THEN UPDATE SET TARGET.ename = SOURCE.ename WHEN NOT MATCHED BY TARGET THEN INSERT (sn, ename) VALUES (sn, ename) OUTPUT $action, DELETED.*, INSERTED.*;
This gives me following output:
$action sn ename sn ename
INSERT NULL NULL 4 pankaj
INSERT NULL NULL 5 saurabh
UPDATE 1 manoj 1 manoj
UPDATE 2 pankaj 2 hema
UPDATE 3 kanchan 3 kanchan
select * from manoj
Now the result set is changed to:
sn ename
1 manoj
2 hema
3 kanchan
4 pankaj
5 saurabh
–> Final cleanup
drop table manoj drop table manoj2
Plz note: An UPDATE, INSERT, or DELETE statement that has an OUTPUT clause will return rows to the client even if the statement encounters errors and is rolled back. The result should not be used if any error occurs when you run the statement.
MS BOL:-
- On MERGE: http://technet.microsoft.com/en-us/library/bb510625.aspx
- On OUTPUT: http://technet.microsoft.com/en-us/library/ms177564.aspx
