T-SQL Tuesday #18 - CTEs - The permission hierarchy problem

May 10, 2011 00:01 by Matt Whitfield

Recently, I was looking at a problem which involved an arbitrary tree structure for permissions. The idea was similar to NTFS security, in that the permission that was defined furthest from the root of the tree would apply to any items below it.

So, consider the following structure:


  • Bob
  • James
  • Tim


  • Administrator
  • Reporter
  • Engineer


Company A

So, Bob is the manager at Company A. He gets the admin role at all levels. James is the manager for Company A/North. He gets the admin role for Company A/North, but he is allowed to run reports on Company A's other regions. Tim is an engineer for RecursiveTown - and he just has the engineer role for that town.

Let's have some (very) basic table structures that meet our needs, data-wise:

CREATE TABLE [dbo].[Users] (    ID     INT           IDENTITY (1, 1) NOT NULL PRIMARY KEY CLUSTERED,    [Name] NVARCHAR (50));CREATE TABLE [dbo].[Roles] (    ID     INT           IDENTITY (1, 1) NOT NULL PRIMARY KEY CLUSTERED,    [Name] NVARCHAR (50));CREATE TABLE [dbo].[Containers] (    ID                INT           IDENTITY (1, 1) NOT NULL PRIMARY KEY CLUSTERED,    ParentContainerID INT           FOREIGN KEY REFERENCES [dbo].[Containers] (ID)                                     ON DELETE NO ACTION ON UPDATE NO ACTION,    [Name]            NVARCHAR (50));CREATE TABLE [dbo].[UserContainerRoles] (    ID          INT IDENTITY (1, 1) PRIMARY KEY CLUSTERED,    ContainerID INT NOT NULL FOREIGN KEY REFERENCES [dbo].[Containers] ([ID])                              ON DELETE NO ACTION ON UPDATE NO ACTION,    RoleID      INT NOT NULL FOREIGN KEY REFERENCES [dbo].[Roles] ([ID])                              ON DELETE NO ACTION ON UPDATE NO ACTION,    UserID      INT NOT NULL FOREIGN KEY REFERENCES [dbo].[Users] ([ID])                              ON DELETE NO ACTION ON UPDATE NO ACTION);

Simple... Let's look at the data that might be within them:






1<NULL>Company A



So - how can we, succinctly, get a list of the containers and roles that apply to a particular user?

Enter the recursive CTE. Now, recursive CTEs can be a bit interesting. They can also suck, performance-wise. But, for smaller data-access patterns, such as this one, they can provide some very flexible solutions to some problems that previously were not easy to solve at all.

Recursive CTEs follow a simple pattern, but it can seem a bit daunting at first. They all follow this pattern:


The Anchor is the statement that returns the basic result set - the first level of recursion. This is the result set that we start with. In the example above, it is the containers to which the user has been given a direct role assignment through the UserContainerRoles table.

The Recursor is the interesting part - it's a query that references the Anchor, and provides the next level of result set. Very nice, but what happens next? Well, next the result set that was last returned by the recursor is passed to the recursor again - and this loop continues until either the recursor part returns no more rows, or the maxrecursion limit is reached.

Now I'm assuming that the fact that you're still reading means you probably just want to see the code. Who am I to stand in your way?

CREATE FUNCTION [dbo].[fn_GetContainerRolesForUserID](@UserID int)  RETURNS TABLEASRETURN (WITH   Hierarchy (ContainerID, RoleID, HierarchyLevel)  AS     (SELECT [c].[ID],                 [ucr].[RoleID],                 1 AS HierarchyLevel          FROM   [dbo].[Containers] AS [c]                 INNER JOIN                 [dbo].[UserContainerRoles] AS [ucr]                 ON [ucr].[ContainerID] = [c].[ID]          WHERE  [ucr].[UserID] = @UserID          UNION ALL          SELECT [c].[ID],                 [h].[RoleID],                 [h].[HierarchyLevel] + 1 AS HierarchyLevel          FROM   [dbo].[Containers] AS [c]                 INNER JOIN                 [Hierarchy] AS [h]                 ON [c].[ParentContainerID] = [h].[ContainerID])  SELECT [ContainerID],         [Name],         [RoleID]  FROM   (SELECT [ContainerID],                 [Name],                 [RoleID],                 [HierarchyLevel],                 ROW_NUMBER() OVER (PARTITION BY [h].[ContainerID]                                     ORDER BY [HierarchyLevel]) AS __RN          FROM   [Hierarchy] AS [h] INNER JOIN                  [dbo].[Containers] AS [c]            ON     [h].[ContainerID] = [c].[ID]) AS iDat  WHERE  [__RN] = 1)

The only non-obvious thing about this query is the use of the ROW_NUMBER function. This is what causes a permission defined at a lower level to take precedence over one defined at a higher level - this is because HeirarchyLevel increases each time it passes through the recursor section of the query, so we want to choose the container role with the lowest hierarchy level for each container.

So, let's look at each of our results:

SELECT [ContainerID], [Name], [RoleID]   FROM [dbo].[fn_GetContainerRolesForUserID](1)

1Company A1

Ok, so Bob has the admin role at all levels - just what we wanted to see...

SELECT [ContainerID], [Name], [RoleID]   FROM [dbo].[fn_GetContainerRolesForUserID](2)

1Company A2

James has the Admin role for North, but the Reporter role everywhere else - again, just what we want...

SELECT [ContainerID], [Name], [RoleID]   FROM [dbo].[fn_GetContainerRolesForUserID](3)


This just shows that Tim has the Engineer role at RecursiveTown only - excellent...

Simple, right? Happy T-SQL Tuesday!

Tags: , ,

community | t-sql

Finding the age of a person in T-SQL

December 1, 2010 00:28 by Matt Whitfield

Ok, sounds simple, right? How old are you? Easy - it's the difference in years between your date of birth and the current date...

So, expressed in T-SQL, that's:

DECLARE @dob [datetime], @currentdate [datetime]SET @dob = '19791101'SET @currentdate = '20101027'SELECT DATEDIFF(yy, @dob, @currentdate)


Well, no. DATEDIFF will return the difference between the year numbers, but won't consider the lower-order parts of the date. So, someone who was born in November, like me, would show up as being a year older than they are from January onwards. So we need to take into account the lower-order parts of the date. What we need to do is make sure that the day of the year of the current date is greater than or equal to the day of the year of the date of birth. Again, expressed in T-SQL, that's:

SELECT DATEDIFF(yy, @dob, @currentdate) -        CASE WHEN DATEPART(y, @dob) > DATEPART(y, @currentdate)             THEN 1             ELSE 0             END

I.e. we subtract one from the number that DATEDIFF(yy) returns if the current day of the year is less than the dat of the year on which the date of birth falls. Great, job done. Almost.

What about leap years? Let's consider someone who was born on the first of march, 1980. Using the code above, they are always a year younger than they actually are on their birthday. Why is this? Because the day of the year for the 1st of March in a leap year is 61, wheras the day of the year for the 1st of March in a non-leap year is 60. Rats.

So we need to delve a little deeper, and consider the actual month and day parts. If the month of the date of birth is greater than the month of the current date, then we subtract a year. If the month of the date of birth is less than the month of the current date, then we're all good, otherwise we need to look at the day of the current month. So that leaves us with:

SELECT DATEDIFF(yy, @dob, @currentdate) -   CASE WHEN DATEPART(m, @dob) > DATEPART(m, @currentdate)        THEN 1        WHEN DATEPART(m, @dob) < DATEPART(m, @currentdate)        THEN 0       ELSE CASE WHEN DATEPART(d, @dob) > DATEPART(d, @currentdate)             THEN 1             ELSE 0             END       END

However, that's a little bit onerous. Another way around it is to subtract the number of years difference from the current date, and then compare the resulting dates. In T-SQL this is then:

SELECT DATEDIFF(yy, @dob, @currentdate) -   CASE WHEN @dob > DATEADD(yy, DATEDIFF(yy, @dob, @currentdate) * -1, @currentdate)       THEN 1 ELSE 0 END

So there it is, reliable logic with which to determine exactly how old someone is in SQL Server. So, we'd want to put that in a scalar function, right? NO!

Scalar functions just suck performance out of any query. CLR Scalar functions are much more performant. How would this look in CLR code?

[Microsoft.SqlServer.Server.SqlFunction]public static int fn_GetAge(DateTime dateOfBirth, DateTime currentDate){    int yearsDifference = currentDate.Year - dateOfBirth.Year;    return yearsDifference -            ((dateOfBirth > currentDate.AddYears(yearsDifference * -1)) ? 1 : 0);}

Simple, succinct, performant. That's the way to do it.

Tags: , ,


The IN and NOT IN constraints and NULL values

November 24, 2010 01:28 by Matt Whitfield

Ok, so the IN clause, it's a simple way of saying 'I want a value that is also present in the result of this single column sub-query'.

But it has hidden complexities - let's have a look at those.

Consider the following test tables:

DECLARE @testData TABLE (value [int] NULL)INSERT INTO @testData ([value]) VALUES (1)INSERT INTO @testData ([value]) VALUES (2)INSERT INTO @testData ([value]) VALUES (3)INSERT INTO @testData ([value]) VALUES (NULL)DECLARE @testMatchValues TABLE (value [int] NULL)INSERT INTO @testMatchValues ([value]) VALUES (1)INSERT INTO @testMatchValues ([value]) VALUES (2)INSERT INTO @testMatchValues ([value]) VALUES (5)

So, if we run the query

SELECT * FROM @testData [td]  WHERE [value] IN (SELECT [tmv].[value] FROM @testMatchValues [tmv])

Then we expect to receive rows containing the values 1 and 2, because those are the intersection of the two test tables. Consequently, we also expect the same result when we reverse the tables:

SELECT * FROM @testMatchValues [tmv]  WHERE [value] IN (SELECT [td].[value] FROM @testData [td])

And this is what we get, from both queries.

So, when we run a query with NOT IN, we expect to get the other rows that do not form part of the intersection. For example, with the following query:

SELECT * FROM @testData [td]  WHERE [value] NOT IN (SELECT [tmv].[value] FROM @testMatchValues [tmv])

We expect to receive 3 and NULL, right? Well, that depends on the setting of ANSI_NULLS. If ANSI_NULLS is ON, then we get only the value 3.

Why is this?

It's because the NULL value is not equal to to any row from @testMatchValues - but, at the same time, it is not different from any row from @testMatchValues.


Let's look at some simpler SQL:

DECLARE @test [int]SELECT CASE when @test = 3 THEN 1 ELSE 0 END AS TestEqualsThree,        CASE WHEN @test <> 3 THEN 1 ELSE 0 END AS TestDoesNotEqualThree

In this SQL, the value of @test is NULL, because it is not assigned. And both values are returned as 0 - meaning that @test = 3 is false, and @test <> 3 is false. Basically, both comparisons result is UNKNOWN, which is neither true nor false, but when evaluated in a CASE statement or WHERE clause, would not count as a match.

We can also see the same effect in reverse - so consider the SQL:

SELECT * FROM @testMatchValues [td]  WHERE [value] NOT IN (SELECT [tmv].[value] FROM @testData [tmv])

We might reasonably expect this to return us a single row with the value 5 - but it returns us no rows at all. Again, why?

Well, let's simplify it a bit. Consider this SQL:

SELECT 'Present' WHERE 5 NOT IN (1, 2, 3, NULL)

This is effectively replicating the match on the value 5 from @testMatchValues in the query above.

This query may also be written as:

SELECT 'Present' WHERE 5 <> 1 AND 5 <> 2 AND 5 <> 3 AND 5 <> NULL

And there we have it - we saw above that @test <> 3 would not count as a match - and here, in the same way, 5 <> NULL will not count as a match, because UNKNOWN is not true.

What have we learnt?

That any SQL using NOT IN where the list of values contains NULL will never return anything at all, because value <> NULL will never be a match.

Tags: ,


IF...THEN in a SQL Server SELECT Clause

November 19, 2010 21:45 by Matt Whitfield

This is a commonly asked question: 'How do I do the equivalent of an IF...THEN in a SELECT Clause?'

The answer is fairly commonly given that the Transact-SQL CASE expression is the way to achieve this. So, for example, we might want to achieve:

SELECT (IF [name] LIKE '%ID' THEN 'Yes' ELSE 'No') as ColumnNameEndsWithID  FROM [sys].[columns];

This is done using the case expression:

SELECT (CASE WHEN [name] LIKE '%ID' THEN 'Yes' ELSE 'No' END) as ColumnNameEndsWithID  FROM [sys].[columns];

So the syntax here is fairly close to how we would express it in natural language - and will look fairly familiar to anyone who has used a ternary operation in C, C++ or C#.

However, the CASE expression can also handle switch style syntax. So, for example, we might want to return the letter 'A', 'B', 'C' or 'D' depending on whether a column's value is 0, 1, 2 or 3.

This would look like the following:

SELECT TOP 10 CASE [integerValue]              WHEN 0 THEN 'A'               WHEN 1 THEN 'B'               WHEN 2 THEN 'C'               WHEN 3 THEN 'D'               END AS [characterValue]FROM   [dbo].[myTable];

Excellent - we have the basics of a switch syntax - although we are limited to expressions and not statements here - i.e. we can return different values based on the input value, but we cannot perform different operations.

This works well when the input value to the CASE statement is deterministic. What do we mean by deterministic? A deterministic function always returns the same value for the same inputs. So, looking at the value of the column at a given point in time is deterministic. A non-deterministic function might be something like GETDATE() - which returns a different value each time it is called.

So how does CASE cause us problems with non-deterministic input? Consider the following SQL:

SELECT TOP 10 CASE ABS(CONVERT (INT, RAND() * object_id)) % 4               WHEN 0 THEN 'A'               WHEN 1 THEN 'B'               WHEN 2 THEN 'C'               WHEN 3 THEN 'D'               ENDFROM   [sys].[columns];

What do we expect this to do? We expect it to return us a random character, 'A', 'B', 'C' or 'D' for each of the 10 rows we're selecting. What happens when we run that? Well, you'll get a random result set, but here's the one I got:


Hang on - NULL? Why is that there? Ok, so we're asking for a random positive value, then taking modulo 4 - which will always be 0, 1, 2 or 3. So how come our CASE statement isn't matching on any of those branches? To answer that question, we have to look at how the query is executed - or how it is expanded. I know I was surprised when I saw this in a presentation given by Peter Larsson, but the actual statement you're running is more akin to:

SELECT TOP 10 CASE WHEN ABS(CONVERT (INT, RAND() * OBJECT_ID)) % 4 = 0 THEN 'A' ELSE                CASE WHEN ABS(CONVERT (INT, RAND() * OBJECT_ID)) % 4 = 1 THEN 'B' ELSE                  CASE WHEN ABS(CONVERT (INT, RAND() * OBJECT_ID)) % 4 = 2 THEN 'C' ELSE                    CASE WHEN ABS(CONVERT (INT, RAND() * OBJECT_ID)) % 4 = 3 THEN 'D' END                  END                END              ENDFROM   [sys].[columns];

Oh. So, because the expression on which we're matching is non-deterministic, we are evaluating the expression on each branch of the CASE statement, and coming up with a different value each time. That would explain how we could fail to match on any of the branches - seeing as the value tested against each branch is different from the last value that was tested.

You can see the same effect when using non-deterministic scalar functions too. The moral of the story? Be careful when using case with non-deterministic expressions...

Tags: ,



November 9, 2010 15:32 by Matt Whitfield

So, we all know we can UPDATE and DELETE data in tables - but what happens when more than one table is involved?

We know we can use JOIN syntax to query data from multiple tables in SELECT statements, and that it's easy to pipe the results of a SELECT statement to an INSERT statement.

However, how do we use JOIN with UPDATE and DELETE? Let's have a look. We'll start off with a couple of simple table variables holding a couple of rows to demonstrate...

DECLARE @data TABLE (i [int], j [int])INSERT INTO @data ([i], [j]) VALUES (1, 10)INSERT INTO @data ([i], [j]) VALUES (2, 20)INSERT INTO @data ([i], [j]) VALUES (3, 30)INSERT INTO @data ([i], [j]) VALUES (4, 40)DECLARE @keys TABLE (i [int], j [int])INSERT INTO @keys ([i], [j]) VALUES (1, 100)INSERT INTO @keys ([i], [j]) VALUES (2, 210)

So, let's say we want to delete from @data where there are matching rows in @keys... We could use:

DELETE FROM @data WHERE [i] IN (SELECT [i] FROM @keys)

But, this would get complicated. Say we wanted to only delete the rows where @keys.j = @data.j * 10?

Enter the DELETE JOIN:

DELETE @data  FROM @data [d] INNER JOIN        @keys [k]     ON [d].[i] = [k].[i]    AND [d].[j] * 10 = [k].[j]SELECT *   FROM @data

And here's what we get:


Nice. But, be careful - if you specify a LEFT OUTER JOIN then all the rows in @data would be deleted - so you do have to make sure that you use a JOIN that represents the value that you want.

So, how do we UPDATE from one table to the other? Again, it's very simple:

UPDATE @data   SET [j] = [k].[j] / 10  FROM @data [d] INNER JOIN        @keys [k]     ON [d].[i] = [k].[i] 

Again though, we need to be careful... If we use a LEFT OUTER JOIN then the values of the rows with no match will receive NULL - and the contents of the @data table would look like the following:


So - JOIN in UPDATE and DELETE statements can be really helpful - but we must use them with care, respect their meaning and take the time to understand what they will do.

Tags: ,


T-SQL Tuesday #12 - Why are DBA skills necessary?

November 2, 2010 00:00 by Matt Whitfield

So, I thought I would try and participate in T-SQL Tuesday, and the topic for this month is 'Why are DBA skills necessary?'. This one is a topic that's close to my heart, as it's an area where I have been bitten in the past while at the same time I continue to see businesses struggle on regardless today.

Let's rewind to way back when (ok, so I'm only talking about 9 years ago) when my career with SQL Server really started to take shape. I'd been tinkering around with SQL Server for 4 years prior to that, but never really got into it.

The task I had was to design a database for an on-line transaction processing system, which would process loyalty transactions from an unspecified number of client devices. When I say system, I do mean system. I mean hardware, software & database. In fact, I even designed the logo. This was a small shop.

At the time, I didn't have much experience as a DBA at all, and designed the system based on what I perceived to be best practices. Actually, my idea of best practice back then was 'interesting' to say the least - focusing on just the database design - there was a total lack of referential integrity, a unique constraint to me was not being able to use the coffee machine because it was being serviced and a transaction was something I paid money in a shop for. I really had no idea.

There were several design mistakes that I made along the way, which all had to be rectified at one time or another. Let's get an example of what I'm talking about:

  • Not thinking up-front about where the clustered index would go was probably the mistake that caused the biggest headache.
  • Not understanding the difference between @@IDENTITY and scope_identity() was fun when data was linked incorrectly - and this wouldn't have happened had referential integrity been enforced.
  • Not having a clear naming scheme - sometimes xxxID meant a reference to ID in table xxx, and sometimes it was a client-supplied match value (think TerminalID - was this a link to the tblCfgTerminals table, or was it the number that the client terminal used to identify itself?).

So, I was making some pretty newbie mistakes - and these were mistakes that I had to live with over a considerable period of time.

Hang on, what on earth does this have to do with DBA skills being important?

Here's the core of what I'm trying to get at - which is that DBA skills are absolutely essential in a mentoring capacity. Not important, essential. And not only DBA skills, but DBA experience. Would a freshly certified MCDBA (or whatever this week's exam is called) have been able to see the mistakes I was making? Would they have been able to tell me a story that really engaged me with their advice, or would it have been the 'because this web page says so'?

Had I had a seasoned and battle-hardened DBA from which to learn, then I would have still made mistakes, no doubt - but I would have made far less mistakes. The time I spent fixing those mistakes could have been spent on learning some new facet that I hadn't yet understood.

Coming from a development background, as I have, I would have found the presence of a DBA hugely beneficial.

So, fast forward back to the present day, and I still see projects and solutions being delivered where it's clear that there has been a total lack of a DBA's touch in the development of systems.

Some of the things I see today make me understand that actually, the mistakes I was making back at the start of my career were not uncommon. In fact, they were very common. Looking at a few soundbite examples of things I've seen recently:

  • A company who have client devices that have well-structured configuration. Instead of this being stored in tables, it's stored in XML with a non-defined schema - and stored in the database as ntext. I thought the developer was joking when I asked 'can we see which devices have x set to y' and he said 'not easily'.
  • Monitoring systems that dump events into tables with no indexes at all, and then create new tables with the same schema arbitrarily - meaning you have to look in a different table based on the date you're looking for, and there is no way of telling which table you have to look in before you look.
  • Reports being generated with nested cursors when relatively simple DML performs the same operation 100 or 1000 times quicker.
  • People using in-line SQL in applications because they don't realise that stored procedures exist.
  • People realising that stored procedures do exist, but running everything in dynamic SQL because they haven't quite understood.

The list goes on. There is a whole world of epic-database-fail out there, and that world needs us. It needs us to use our skills and experience to educate and influence, in a positive way, to try and make sure that the world of the database is understood much more widely, and that fundamental mistakes can be designed out and not rectified later.

The onus is on us to translate the failures of design into terms which management can easily understand - monetary terms. How costly would a system fail be because not enough thought was put in at the beginning? Is it even possible to put a cost on damage to reputation when dealing with failures of a service that is growing in popularity? It's in these terms that we must explain the importance of designing something right from the get go.

Nobody wants to build a house with no foundations, and nobody wants to buy a car where brakes are an optional extra. I think it's time to realise that the following statement is just as true:

'Nobody wants to build a data system without getting a DBA involved, right at the start'.

Tags: , , ,

community | t-sql

How to drop/kill all connections to a database from T-SQL

October 27, 2010 20:43 by Duncan Grist

A very quick, but hopefully very useful blog post from me today.

If you ever find the need to drop all connections to a database, either to delete it, perform maintenance or for any other reason, you can do it in a couple of commands straight from T-SQL code:


In order of the database server to change the access policy over to read only it must immediately kill all active connections that were made using the old access policy, which just so happens to be all active connections. The proceeding command restores the original access policy of the database.

Preventing new connections

Frequently, when you have the need to kill all active connections, a way of stopping any new connections from connecting for a period of time would also be useful.

This can be achieved by executing:


and to allow new connections again:


I hope you find these tips useful. That's all for now.

Tags: , ,

hidden features

How to loop over table rows in TSQL

September 22, 2010 20:19 by Duncan Grist

A question that I often see asked on the net, and one I thought it would be a good area to touch on briefly here is “how do I loop over table rows in TSQL?”

Typically newcomers to SQL programming have a long history of functional programming and believe that this need arises because they have certain predicate logic that needs to be applied to each row explicitly and therefore a loop-style approach seems the most natural to them.

Likely, after a little Googling on the subject, developers will quickly come across the concept of cursors and believe that their prayers have been answered! However, the purpose of this quick blog post is to provide a warning that this is probably not the case.

Cursors are slow. This revelation will come as no surprise to most seasoned SQL developers, as cursor-based operations generally perform orders of magnitude slower in every case I can think of and will normally contain much more complicated code that set-based approaches.

If you find yourself in a similar situation I would first strongly suggest you simply take a small step back and re-examine your approach. I believe that if you find yourself thinking about the words “iterate” or “loop” and “SQL” in the same sentence then you’re probably doing something wrong.

SQL is all about data sets and set-based programming. Microsoft SQL Server and indeed all relational SQL-based DBMS solutions are designed around this concept. There are literally a tonne of optimisations which go on behind the scenes of your SQL code in order to make it run monumentally fast on very large data sets. Writing a manually coded loop throws a lot of this performance potential away.

There are of course valid reasons to use a loop, and indeed cursors, along with alternatives for those who want to loop in TSQL without using cursors. However, I believe that if you spent the time finding out how to implement your query using a set-based method you’ll come up with something that is more elegant, shorter, and much, much faster and you’ll be proud of it.

Tags: , , ,


Preventing non-qualified object use with DDL Triggers

September 20, 2010 09:05 by Matt Whitfield

There was a question on Ask SQL Server Central recently, which got me thinking. Was there a way that we could mandate that all object use should be qualified? As it turns out, the answer is relatively simple - and took the form of a DDL trigger.

Now, this DDL trigger is 2008+ only, due to it's use of [sys].[sql_expression_dependencies], but what it effectively does is make sure that any object that is referenced by the object we've just created must be referenced by it's schema qualified name.

Without further ado, here is the code:

ALTER TRIGGER [trig_InhibitNonQualifiedObjectUse] ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTSAS BEGIN    SET NOCOUNT ON;    DECLARE @data XML;    DECLARE @schema sysname;    DECLARE @object sysname;    DECLARE @eventType sysname;    DECLARE @fullObjectName [varchar](MAX);     SET @data = EVENTDATA();    SET @eventType = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'sysname');    SET @schema = @data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname');    SET @object = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname');    SET @fullObjectName = QUOTENAME(@schema) + '.' + QUOTENAME(@object);        IF @object IS NOT NULL    BEGIN        IF EXISTS (SELECT * FROM [sys].[sql_expression_dependencies]                   WHERE [referencing_id] = OBJECT_ID(@fullObjectName)                     AND [referenced_schema_name] IS NULL)        BEGIN            DECLARE @errorMessage [varchar](MAX);            SET @errorMessage = @eventType + ' - ' + @fullObjectName +                 ': Operation terminated due to non-qualified object use';            RAISERROR(@errorMessage,17,0)            ROLLBACK;        END    ENDEND;GO

Tags: , ,


Inserting into a temporary table from a stored procedure with implicit creation

September 7, 2010 10:28 by Matt Whitfield

One thing that I see a lot is people trying to export data from a stored procedure into a temporary table, and have the temporary table created based on the schema of the result set. There is a common solution using OPENROWSET which isn't ideal, but here it is:

sp_configure 'Show Advanced Options', 1GORECONFIGUREGOsp_configure 'Ad Hoc Distributed Queries', 1GORECONFIGUREGOSELECT * INTO #TempTable   FROM OPENROWSET('SQLNCLI', 'Server=(local);Trusted_connection=yes;',                              'EXEC [TestDatabase].[dbo].[stproc_test]')SELECT * FROM #TempTable

Ok, so that works. Or does it? Does Server=(local) actually get us to the server we want? What about if we have multiple instances? Ok, so we could use @@SERVERNAME to build the provider string and... Oh. OPENROWSET won't accept anything but a string in there. Not an expression that results in a string, nor a variable of a string type.

Well, at least we can build up the SQL string to remove the hard-coded reference to the... Oh. OPENROWSET places similar restrictions on the SQL statement to be executed.

All in all, I would say this is a practice to be avoided. You will end up with hard-coded references to both your server and instance name and your database name in code - and, worst of all, they are in a string. Why is that a problem? Because it means that there is no tool that will validate this for you before the SQL gets run for the first time. You can see it now, the operations team move the database onto a new system, and keep the old system live as a GTH plan. The queries continue to run, merrily accessing the old data, making it look (initially at least) like the system is working. Then, a couple of weeks later, somebody who is writing 'an urgent report for the directors' suddenly realises that the data isn't updating, and hasn't been updating since the system move. Cue panic investigation from the operations team, who find the above, then have to try and find all instances of that style of code in the whole database.

Not exactly how to win friends and influence people.

Tags: , ,


Schema Surf

Tag cloud


<<  September 2018  >>

View posts in large calendar