Advertisements

Archive

Archive for March, 2011

How to retrieve last 12 months records form a table – MSDN TSQL forum

March 24, 2011 Leave a comment

–> Question:

I have a data field in my table that i would to use within my WHERE clause, to get the last 12 months dataset.

So for example, WHERE date between ‘20110323’ and ‘20100323’

Is this possible? I know I have to use something like GETDATE() or something but not quite sure how to incorporate this.
 

–> My Answer:

You can make your WHERE clause like this:

WHERE date_field between getdate() and getdate()-365

-- OR

WHERE date_field between getdate() and dateadd(m,-12,getdate()

 

–> Answer by Uri Dimant:

You can make your WHERE clause like this:

SELECT * 
FROM tbl 
WHERE dt >= DATEADD(month,-12,DATEADD(day,DATEDIFF(day,0,GETDATE()),0)) 
AND dt <=DATEADD(day,DATEDIFF(day,0,GETDATE()),0)

 

Ref link.


Advertisements

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.
 


EXECUTE (or EXEC) vs sp_executesql

March 22, 2011 6 comments

–> EXECUTE:
As per MS BOL EXECUTE executes a command string or character string within a TSQL batch, or one of the following modules: system stored procedure, user-defined stored procedure, scalar-valued user-defined function, or extended stored procedure. The TSQL query can be a direct string or a variable of char, varchar, nchar, or nvarchar data type.

–> sp_executesql:
As per MS BOL sp_executesql executes a TSQL statement or batch that can be reused many times, or one that has been built dynamically. The TSQL statement or batch can contain embedded parameters. The SQL query is a Unicode string or a Unicode variable that contains a Transact-SQL statement or batch. Here the variable datatype is restricted to Unicode nchar or nvarchar only. If a Unicode constant (SQL string) is used then the it must be prefixed with N.

–> Main difference performance wise: sp_executesql is generally preferred over EXEC() when executing dynamic T-SQL. sp_executesql works by creating a stored procedure using the specified query, then calling it using the supplied parameters. Unlike EXEC(), sp_executesql provides a mechanism that allows you to parameterize dynamic T-SQL and encourage plan reuse. A dynamic query that is executed using sp_executesql has a much better chance of avoiding unnecessary compilation and resource costs than one ran using EXEC().

Let’s check the difference with a simple example using [AdventureWorks] database’s [Person].[Contact] table:

USE [AdventureWorks]
GO

DECLARE @str NVARCHAR(1000)
DECLARE @fName NVARCHAR(20)
DECLARE @ePromo INT

SET @fName = N'James'
SET @ePromo = 1

--// Using EXECUTE or EXEC
SET @str = N'
SELECT ContactID, FirstName, MiddleName, LastName, EmailAddress, Phone, EmailPromotion
FROM Person.Contact
WHERE FirstName = ''' + @fName + '''
AND EmailPromotion = ' + CAST(@ePromo as NVARCHAR(20))

PRINT @str

EXEC (@str)
GO

--// Using sp_executesql
DECLARE @str NVARCHAR(1000)
DECLARE @fName NVARCHAR(20)
DECLARE @ePromo INT

DECLARE @paramList NVARCHAR(500)
SET @paramList = N'@fNameParam NVARCHAR(20), @ePromoParam INT'

SET @str = N'
SELECT ContactID, FirstName, MiddleName, LastName, EmailAddress, Phone, EmailPromotion
FROM Person.Contact
WHERE FirstName = @fNameParam
AND EmailPromotion = @ePromoParam'

SET @fName = N'James'
SET @ePromo = 1
EXECUTE sp_executesql @str, @paramList, @fNameParam=@fName, @ePromoParam=@ePromo

-- When the match is LIKE
SET @str = N'
SELECT ContactID, FirstName, MiddleName, LastName, EmailAddress, Phone, EmailPromotion
FROM Person.Contact
WHERE FirstName LIKE ''%'' + @fNameParam + ''%''
AND EmailPromotion = @ePromoParam'

SET @fName = N'James'
SET @ePromo = 0
EXECUTE sp_executesql @str, @paramList, @fNameParam=@fName, @ePromoParam=@ePromo
GO

Security Note:
Before you call EXECUTE or sp_executesql with a character string, validate the character string. Never execute a command constructed from user input that has not been validated. For more information, see SQL Injection.

Is WITH (READPAST) affected by readlocks – MSDN TSQL forum

March 19, 2011 Leave a comment

–> Question:

If a transaction calls a SELECT using the table hint WITH(READPAST)… will it skip rows that are locked by other SELECT statements that are in READ COMMITTED mode and are issuing shared locks?

ie. Does READPAST skip rows that are locked by SELECT statements, not just UPDATE and INSERT statements?
 

–> Answer:

I don’t think READPAST will skip rows locked by SELECT statement, but only in UPDATE & DELETEs.

Check here for an example and demo: Difference between NOLOCK and READPAST table hints

You may also check a similar old post in MSDN forum for more into.
 

Ref link.


Convert unicode Characters to be displayed in ASCII format – MSDN TSQL forum

March 18, 2011 Leave a comment

–> Question:

Is there a function (or any other way) in T SQL, by which I can somehow convert a unicode character (UTF – 8) and then display it in normal ASCII format?

Eg: I want to convert “Ha Nội” to “Hanoi” either programatically or in SQL.
Another Example: Réunion as “Reunion”
 

One other problem is sometimes some characters in unicode come up as “?” when cast using the above method:

Eg: Ḩaḑramawt ?a?ramawt
 

–> Answer:

This is related to database COLLATION settings.

This would not be the best way, but just in case an idea to sail you through….

SELECT 
	'Réunion', 
	CAST('Réunion' AS VARCHAR(100)) COLLATE SQL_Latin1_General_CP1253_CI_AI

… you can also play with COLLATE DATABASE_DEFAULT option.

For more info you can check my blog post on COLLATION, Collation Conflicts and Change a Database Collation.
 

Regarding your other problem, you want to match the foreign language characters to English, right? Which is not possible in most of the cases.

Just like in French, Réunion, contains ‘é’ which is similar to English ‘e’ but with l’accent aigue accent.

But this is not feasible for every character, and other languages too, like Chinese, Hindi. How can you map, and even if you map what symbol will it show? And thus every character cannot be matched against English and you’ll see invalid characters in your result set.
 

Ref link.