Archive

Archive for September, 2012

Passing multiple/dynamic values to Stored Procedures & Functions | Part 4 – by using TVP

September 10, 2012 2 comments

This is the last fourth part 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, TVPs 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):

-- 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 | Part 3 – by using #table

September 9, 2012 3 comments

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.

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.

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 DBA – Upgrade to SQL Server 2012 – Use Upgrade Advisor

September 2, 2012 Leave a comment

Are you planning to upgrade your SQL Servers to 2012? YES!

How will you make sure that you are ready for Upgrade? ???
How will you make sure that the Upgrade will be seamless? 😦

SQL Server 2012 “Upgrade Advisor” is for you to check and analyze instances of all previous SQL Server versions i.e. 2008 R2, 2008, 2005 and even 2000 in preparation for upgrading to SQL Server 2012.

“Upgrade Advisor” identifies all features and configuration changes that might affect your upgrade. It provides links to documentation that describes each identified issue and how to resolve it and also generates a report that captures identified issues to fix either before or after you upgrade.

This tool has some prerequisites to install, and if you don’t install them you might see following error while installation:

Setup is missing prerequisites:

-Microsoft SQL Server 2012 Transact-SQL Script DOM, 
which is not installed by Upgrade Advisor Setup.
To continue, install SQL Server 2012 Transact-SQL Script DOM from below 
hyperlink and then run the Upgrade Advisor Setup operation again :

Go to http://go.microsoft.com/fwlink/?LinkID=216742

The above error mentions to install “MS SQL Server 2012 Transact-SQL ScriptDom” component. Install it from here: http://www.microsoft.com/en-us/download/details.aspx?id=29065&ocid=aff-n-we-loc–ITPRO40886&WT.mc_id=aff-n-we-loc–ITPRO40886

But for this also “MS DOT NET 4.0” is required on your system. To Install it check this link: http://www.microsoft.com/en-us/download/details.aspx?id=17851

After installing Upgrade Advisor, launch the tool, it gives you 2 options:
1. Launch Upgrade Advisor Analysis Wizard
2. Report Upgrade Advisor Viewer

–> Analysis Wizard lets you run alanysis on following SQL components shown in image below:

–> Report Viewer tell you about the changes that needs attention or needs change/fix before or after upgrading to 2012. After Analysis Wizard finishes it creates repots which is in the form of an XML file.

The Report Viewer tool uses this XML file generated and give you details of components that may be affected. The report provides Importance, When to fix (Before/After), Description and links to information that will help you fix or reduce the effect of the known issues, image below:

After you are done with this Analysis and checking Reports, now the time is to work and fix on issues listed in the Reports.

Other than this you also need to check Microsoft BOL and/or MSDN articles to check for Discontinued and Deprecated features, Breaking and Behavior Changes.
Check following important links:
1. SQL Server Backward Compatibility: http://msdn.microsoft.com/en-us/library/cc707787
2. Database Engine Backward Compatibility: http://msdn.microsoft.com/en-us/library/ms143532
3. Analysis Services Backward Compatibility: http://msdn.microsoft.com/en-us/library/ms143479
4. Integration Services Backward Compatibility: http://msdn.microsoft.com/en-us/library/ms143706
5. Reporting Services Backward Compatibility: http://msdn.microsoft.com/en-us/library/ms143251
6. Other Backward Compatibility: http://msdn.microsoft.com/en-us/library/cc280407