SQL Journey

Journey of SQL Server & Microsoft Business Intelligence

Archive for December, 2012

Posted by Prashant on December 3, 2012


SQL Journey

This post describes how to use result of an user defined function with CHECK constraint in SQL Server.  For demonstration, considered a situation where it is not  allowed to insert or update records where calculated age of a person is less than 18 years as per his/her Date of Birth.

So for this first we need to create the function before creating the CHECK constraint. Here is a function which will return age as per the date of birth provided.

Now create a table where CHECK constraint will refer to this function to check if the age of the person meets the required criteria or not (minimum 18 Years in this case).

Now try to insert a record where calculated age is less than 18 years and see what happens.

As the age does not meet the required criteria in defined CHECK constraint, it doesn’t allow to insert this record…

View original post 32 more words

Posted in SQL Server | Leave a Comment »

SQL Server – Resolve Collation Conflict with Temp Table

Posted by Prashant on December 2, 2012


When one of our projects deployed to production application was throwing a strange error and while debugging it is found that one of the stored procedure getting failed because of collation conflict issue.

The reason behind this error is the stored procedure used a temp table to join a table on string column. Temp tables are created on TempDB, so if you don’t specify collation for a string columns they inherit the collation of TempDB. If the collation of user database doesn’t match with the collation of TempDB and you are comparing and/or joining on string columns, it may cause this collation conflict issue.

Resolution:

There are many ways to resolve this issue. However the best way as per me is to change to specify the collation of the string column of temp table to database default, so that it will inherit the collation of .user database.

CREATE TABLE #tmpTable(IDCol INT, StringCol VARCHAR(100) COLLATE DATABASE_DEFAULT)

Smart coding 🙂

Posted in Interview Questions, SQL Server | Tagged: , , , | Leave a Comment »

SQL Server – List of Tables Used in Stored Procedure

Posted by Prashant on December 1, 2012


Today while developing a piece of logic in our development, my colleague said that I know there is a procedure which has same kind of logic and that may help us building the logic quickly. But the question was how to find that procedure !!!

Then the idea came up was if we can find the list of procedures that are dependable on Table1 & Table2 ?

…and that inspired me to write this post.

We spend some time researching system tables and here is the findings to find list of tables used in a procedure.

;WITH procs
AS
(
SELECT o1.name AS proc_name,
o2.name AS table_name,
ROW_NUMBER() OVER(PARTITION BY o1.name,o2.name ORDER BY o1.name,o2.name) AS row
FROM sysdepends d
INNER JOIN sysobjects o1
ON o1.id=d.id
INNER JOIN sysobjects o2
ON o2.id=d.depid
WHERE o1.xtype = 'P'
--AND o2.name = 'tabname1' OR o2.name = 'tblname2'
)
SELECT proc_name, table_name
FROM procs
WHERE row = 1
ORDER BY proc_name, table_name

Let me know if you have any better idea for this.

Posted in SQL Server | Tagged: , , , , | Leave a Comment »

 
%d bloggers like this: