Unique constraint that allows multiple nulls and empty strings

T-SQL’s UNIQUE constraint allows for NULL values, but only one null value is allowed per column. There is, however, a nifty little way around this, via the WHERE clause.

For example, here’s a UNIQUE constraint that makes sure an ID is unique but allows for both multiple NULL values and multiple empty strings:

CREATE UNIQUE NONCLUSTERED INDEX [UNIQUE_ID] ON [dbo].[tblParticipant]
(
[ID] ASC
)
WHERE ([ID] IS NOT NULL AND [ID] <> ”)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Advertisements

T-SQL Dynamic Where Clause and Nullable Columns

There are all kinds of very helpful articles online, like this one, explaining how to create a dynamic WHERE clause using COALESCE or ISNULL or CASE.  Generally, they describe something like this:

declare @FirstName NVarchar(50) = null
declare @LastName NVarchar(50) = null
declare @StreetAddress NVarchar(100) = null
declare @City NVarchar(50) = null
declare @JobNumber NVarchar(50) = ‘543216’

select e.FirstName, e.LastName, e.DeptID, a.StreetAddress, a.City,
a.StateID, a.ZipCode, j.JobName, j.JobNumber
from tblEmployee e
left join tblAddress a on e.EmployeeID = a.EmployeeID
left join tblJobs j on e.EmployeeID = j.EmployeeID
where e.FirstName = COALESCE(@FirstName, e.FirstName) AND
    e.LastName = COALESCE(@LastName, e.LastName) AND
    a.StreetAddress = COALESCE(@StreetAddress, a.StreetAddress) AND
    a.City = COALESCE(@City, a.City) AND
    j.JobNumber = COALESCE(@JobNumber, j.JobNumber)

And this works great as long as the columns in our WHERE clause all contain data.  However, any column with a NULL value will not be returned.  For instance, in the above example we are searching only by Job Number, so any record with a Job Number of 543216 should be returned.  But, if the StreetAddress field for any of those records is NULL, then they won’t be returned, even though there is a value of 543216 for the Job Number.

Why is this?  Because in our WHERE clause we are using the equal operator, and NULL can’t equal anything.  NULL isn’t a value – it just means UNKNOWN.  So NULL = NULL won’t return true, because what you’re really asking is “Does this unknown thing equal that unknown thing?”  And, of course, the answer to that is “Who knows?”  It can’t evaluate to true, so it must return false.

The solution, then, is to adjust the WHERE clause so that, if the values are NULL, we convert them to something known that we can compare.  If we add an ISNULL function to the column side of the equal sign, and add the same replacement value as the final value in the COALESCE function on the other side, as below, then the query should be able to evaluate and return all rows, even ones with NULL values.

declare @FirstName NVarchar(50) = null
declare @LastName NVarchar(50) = null
declare @StreetAddress NVarchar(100) = null
declare @City NVarchar(50) = null
declare @JobNumber NVarchar(50) = ‘543216’

select e.FirstName, e.LastName, e.DeptID, a.StreetAddress, a.City,
a.StateID, a.ZipCode, j.JobName, j.JobNumber
from tblEmployee e
left join tblAddress a on e.EmployeeID = a.EmployeeID
left join tblJobs j on e.EmployeeID = j.EmployeeID
where ISNULL(e.FirstName, ”) = COALESCE(@FirstName, e.FirstName, ”) AND
    ISNULL(e.LastName, ”) = COALESCE(@LastName, e.LastName, ”) AND
    ISNULL(a.StreetAddress, ”) = COALESCE(@StreetAddress, a.StreetAddress, ”) AND
    ISNULL(a.City, ”) = COALESCE(@City, a.City, ”) AND
    ISNULL(j.JobNumber, ”) = COALESCE(@JobNumber, j.JobNumber, ”)

T-SQL functions to create comma separated list

Here are a couple of ways to use T-SQL to create a comma separated list of column values in SQL Server.  The first uses FOR XML PATH, and returns a csv for each ID in the table – this could be used in a table function.

The second uses COALESCE, and returns a csv for a specified ID – this could be used in a scalar function.  Also, since FOR XML PATH is not available prior to SQL Server 2005, the COALESCE version can be used in SQL Server 2000.

First, let’s build a table variable to play with:

DECLARE @tblA TABLE(ID INT, Value INT)

INSERT INTO @tblA VALUES(1000,1)
INSERT INTO @tblA VALUES(1000,2)
INSERT INTO @tblA VALUES(1000,3)
INSERT INTO @tblA VALUES(1001,2)
INSERT INTO @tblA VALUES(1001,3)
INSERT INTO @tblA VALUES(1001,4)
INSERT INTO @tblA VALUES(1001,5)

Here’s the FOR XML PATH version:

SELECT ID,
STUFF(
 (
 select ‘, ‘+ CAST(value AS VARCHAR)
 from @tblA b
 WHERE a.ID = b.ID
 FOR XML PATH(”)
 )
,1,1,”) AS Value
FROM @tblA a
GROUP BY a.ID

Result of query:

ID          Value
———————–
1000         1, 2, 3
1001         2, 3, 4, 5

And here’s the COALESCE version:

declare @List varchar(500);
select @List = COALESCE(@List + ‘, ‘, ”) + cast(Value as varchar(5))
from @tblA
where ID = 1000;
select @List;

Results of query:

Value
——-
1, 2, 3

How to Query SQL Server Role Members

You can use SQL Server Database Roles to control access to various parts of your application. Let’s say I have created a .NET application. I have a menu called Administration and I only want certain users to be able to see the menu. One way to do this is to use Active Directory Groups. Another way is to use SQL Server Database roles.

For this example I create a role called MyAppAdmin. Only members of MyAppAdmin should be able to see the Administration menu. After creating the role and assigning appropriate permissions to the role, I want to check, within my .NET application, to see if the logged on user is a member of the MyAppAdmin role. Here’s how to do this:

Create a stored procedure:

CREATE procedure [dbo].[uspTestDatabaseRole]
— Returns 1 if user is a member of the specified role or if user is dbo
@RoleName varchar(30)
as
declare @Return integer
if USER_NAME() = ‘dbo’
select @Return = 1
else
select @Return = IS_MEMBER(@RoleName)
select @Return

In this procedure the variable @RoleName refers to the Database Role you are checking. You can also use it to check membership in a Windows group. You may notice if the person calling the stored procedure is dbo, I return 1 no matter what. You may not want the procedure to work this way so you should modify for your own purposes.

Now all you need to do is call this procedure from your .NET code and it will return 1 if the person is a member of the database role you are checking or if the person is dbo. Otherwise, it will return 0.

Ordinal Numbers in T-SQL

I wanted to write a query that took numbers and turned them into ordinal numbers – re: 1 = 1st, 2 = 2nd, 3 = 3rd, etc.

I took this code from Chip Pearson’s web site. His posting was about ordinal numbers in Excel. I converted the code to a T-SQL function, and I can now use it in any other T-SQL queries I write. Here it is:

create Function dbo.OrdinalNumber
/* used to return an ordinal number */
(@Number integer)
returns varchar(6)
as begin

declare @N int
declare @Suffix char(18)
declare @Result varchar(6)

select @N = @Number%100
select @Suffix = ‘stndrdthththththth’

If ((Abs(@N) >= 10) And (Abs(@N) <= 19))
Or ((Abs(@N) % 10) = 0)
begin
select @Result= cast(@Number as varchar) + ‘th’
end
else
begin
select @Result = cast(@Number as varchar) + substring(@Suffix,
((Abs(@N) % 10) * 2) – 1, 2)
end
return @Result
end

To use it just feed it the number you want to convert.

select dbo.ordinalnumber(1)
will return “1st”

Using “Execute As” to Test a Stored Procedure as a Different User

In an earlier blog post, I wrote about how to use “Execute As” in a stored procedure to access a separate database. When you include “Execute As” in a stored procedure, you can pull data from a separate database without giving your users direct permissions on that database.

Another way I really like to use “Execute As” is to test the permissions on my stored procedures. You may have had a time or two where a stored procedure ran great when you ran it, but when your user tried it… not so great.

Here’s how to test as that user from either Query Analyzer or SQL Server Management Studio. First, run this line of code, substituting SOMEDOMAIN and Someuser with the appropriate information.

execute as login = ‘SOMEDOMAINSomeuser’

Now run this line of code. It should return the login you just entered.

SELECT SUSER_NAME(), USER_NAME();

Now run your stored procedure.

When you are done testing, make sure you run this line (word, actually) of code:

Revert

This will set the login back to your login.

Simple way to renumber records in SQL Server

Recently I needed to renumber a column in a database.  Specifically, I needed to be able to take a Report Number series that started with 1 (1, 2, 3, 4) and change the start number and renumber all subsequent Report Numbers (e.g, change above to 14, 15, 16, 17).

I thought I’d probably be able to use a table variable with an identity column – hoped I wouldn’t have to use a cursor.  I did a quick Google search, to see how other people handled similar things and hopefully get some examples.

I saw the table variable solutions I expected, but then came across a post containing this really nifty bit of code that did the trick in four lines – very slick.  Here’s the example as it appeared in the post:

declare @variable int
set @variable = 0
update <tablename>
SET @variable = <columnname> = @variable + 1 

and here’s how I implemented it, along with a WHERE statement so that I only renumber the Report Numbers associated with a particular Job (@ReportNum is the new starting number, and is a parameter fed in to the stored procedure):

declare @counter int
set @counter = @ReportNum – 1
update tblReport
set @counter = ReportNumber = @counter + 1
where JobID = @JobID

Works like a charm.  I repeat – very, very slick:)

How to connect to a SQL Server Named Instance

This is something I just don’t remember, so I thought this would be a good place to post it. If you are trying to connect to a named instance of SQL Server, for example TECHKNOWSOLVETECHKNOWSOLVE2000, you might get an error in your code that says this server doesn’t exist. The quick and easy fix is to refer to the server by its port number, ie TECHKNOWSOLVE, 444. In this instance TECHKNOWSOLVE is the name of the server and the port number is 444. Note that the server name is followed by a comma, not the backslash that is used in named instances.

How do you find out what port number your named instance is using? In SQL Server 2000, use the Server Network Utility. Choose your named instance. Under “Enabled Protocols”, click on “TCP/IP”. Click on the “Properties” button. There you will see a number for “Default Port”. This is the number that will follow your server name.

I haven’t tried this with SQL Server 2005, but this article tells you how to find the port for SQL Server 2005 (and SQL Server 2000, for that matter.)

Using “Execute As” in a Stored Procedure To Access A Separate Database

Here is a scenario that I often run across. A client wants me to create an add-on to a pre-existing application that runs on SQL Server. I don’t want to touch the existing application and database (let’s call this database PROPIETARY) so I create a separate database (we’ll call it TECHKNOWSOLVE) that queries the existing database. Now, in the PROPRIETARY database is a table (often an employees table) that contains all sorts of confidential information (like salary, age, social security number, etc) that I don’t need. In the TECHKNOWSOLVE database I just need a few columns from this table, like LastName, FirstName, and EmployeeID.

Here’s what I do. I create a job that runs a stored procedure once a day. In the stored procedure I delete the data in the TECHKNOWSOLVE Employees table and then repopulate it with the data I need from the PROPRIETARY Employees table. All works well since the job runs under my login and I am a system administrator.

But what if users need to update the TECHKNOWSOLVE Employees table before the next job runs? If I create a button for them within my application that runs the stored procedure, they will get an error unless I give them select permission on the Employees table in PROPRIETARY and that’s a bad idea. I could grant column permissions within the Employees table in PROPRIETARY but I’d really like to touch the PROPRIETARY database as little as possible. So instead, I make a change in my stored procedure.

Here’s the relevant part of the original procedure:

Create procedure uspGetEmployees
as
insert into tblEmployees select employee_no, first_name, last_name
from PROPRIETARY.dbo.employees

Here’s the change:

Create procedure uspGetEmployees
with execute as ‘DOMAINNAMEAllPowerfulUser’
as
insert into tblEmployees select employee_no, first_name, last_name
from PROPRIETARY.dbo.employees

Now if you run this you will get an error: The Server principal “User” is not able to access the database “model” under the current security context. That’s because you’re not done yet. There are a few steps you’re going to have to take before you can get the stored procedure to work. This MSDN article gives some excellent information about extending database impersonation but I thought it also left a few things out, thus my tutorial here.
 

The first thing you have to do is make sure the OWNER of your database, in this case TECHKNOWSOLVE, is also a user in the PROPRIETARY database. Once you’ve done that, you have to grant that user authenticate permissions in the PROPRIETARY database. Here’s how you do that:

  • In the PROPRIETARY database, under Security and then users, right-click on the user who is the owner of TECHKNOWSOLVE. Choose Properties
  • Click on Securables. Click Add
  • Choose Specific Objects and Click OK
  • Click on the Object Types button, click Databases and Click OK
  • Click on the Browse button
  • Select the applicable database (PROPRIETARY) and click OK.
    Down below you will see all the permissions. Scroll down until you see Authenticate.
    Check Grant and click OK

Ah, but we’re not done yet! Now we have to tell the SQL Server that our TECHKNOWSOLVE database is TRUSTWORTHY.

In the Master Database, run this query:

ALTER DATABASE TECHKNOWSOLVE SET TRUSTWORTHY ON;

Now you should be ready to go, but you could still run into trouble. If you don’t have Service Pack 1 or later installed and you use this with a stored procedure that includes transactions and rollbacks, you could get the following error: A severe error occurred on the current command. The results, if any, should be discarded. This error will be fixed by installing the latest service pack.

You may also get this error: The database owner SID recorded in the master database differs from the database owner SID recorded in database ‘YOURDATABASENAME’. You should correct this situation by resetting the owner of database ‘YOURDATABASENAME’ using the ALTER AUTHORIZATION statement.

This blog post gives good information about this error. The easiest solution I found was to change the database owner of TECHKNOWSOLVE to sa, using this command:
EXEC dbo.sp_changedbowner @loginame = N’sa’, @map = false

 

Pass multiple strings to stored procedure IN statement using XML

(Revised 3.18.08)

Let’s say you have a listbox with multiple items selected, like this:listboxAnd let’s say that you want to take the selected items and send them to a stored procedure to run in an IN statement in the WHERE clause, like this: SELECT * FROM tblProjects WHERE ProjectCity IN ('Lewiston', 'Portland', 'Spokane')

Turns out it’s not a simple operation, because you can’t send an array to a stored procedure, and if you send it as a comma-separated string, SQL Server just thinks it’s one big string.If you go the comma-separated string route, you can break down the string and loop through its parts using T-SQL, but it’s clunky. Another way is to send the values as XML and then query the XML in the stored procedure.

First, retrieve the values from the listbox and put them in an xml string:

'initialize a new stringbuilder object
Dim sb As New StringBuilder

'add the root element of the xml string to the stringbuilder
sb.Append("<root>")

'loop through the listbox items
For Each li As ListItem In Me.lstCities.Items

'if the item is selected, add it to an xml element string, and add
'the string to the stringbuilder
If li.Selected Then
sb.Append(String.Format("<item value=""{0}"" />", li.Text))
End If
Next

'add the root element closing tag to the stringbuilder
sb.Append("</root>")

Calling sb.ToString will produce the following value:

<root>
<item value="Lewiston" />
<item value="Portland" />
<item value="Spokane" />
</root>

At this point you’d load the sb.ToString value in a parameter and send it to the stored procedure:

'use stringbuilder value to create a sqlParameter object
Dim parameter As New Data.SqlClient.SqlParameter
With parameter
.ParameterName = "@XMLDoc"
.Value = sb.ToString
.DbType = Data.DbType.String
End With

'access database .....

OK, now we move to the SQL Server side, to figure out exactly what to do with this xml string once it arrives. In SQL Server 2000, you’ll need to use the OPENXML function to use XML as a datasource. In SQL Server 2005, because of its support for the xml data type, you can query the xml directly.

Let’s do a SQL Server 2000 example first. To use OPENXML, you’ve got to prepare the XML first by calling sp_xml_preparedocument.This system stored procedure prepares an internal representation of the xml document, and returns a handle that can be used to access the internal representation of the document.

Once you’ve got the document handle (which is an integer), you can use OPENXML to query the xml data:

declare @XMLDoc varchar(1000)
set @XMLDoc = '
<root>
<item value="Lewiston" />
<item value="Portland" />
<item value="Spokane" />
</root>
'

/* set variable for document handle*/
DECLARE @XMLDocHandle INT

/* call sp_xml_preparedocument. Send the document handle variable as an output parameter, and the xml document you want prepared*/
EXEC sp_xml_preparedocument @XMLDocHandle OUTPUT, @XMLDoc

/* use OPENXML to query the in-memory xml doc representation */
SELECT value
FROM OPENXML(@XMLDocHandle,'/root/item',2)
WITH (value VARCHAR(50) '@value')

/* call sp_xml_removedocument to remove the in-memory doc representation when you are done with it*/
EXEC sp_xml_removedocument @XMLDocHandle

OPENXML accepts three arguments – the handle of the in-memory xml doc to be queried, the XPath Query used to identify the nodes to be processed, and a flag that indicated the mappings between the xml doc and the in-memory rowset. The WITH clause identifies the XML data to be retrieved, and the SQL data type it should be returned as.

And here’s what you’d do in SQL Server 2005:

/*Note the xml data type, rather than varchar(n)*/
declare @XMLDoc xml
set @XMLDoc = '
<root>
<item value="Lewiston" />
<item value="Portland" />
<item value="Spokane" />
</root>'
SELECT x.Item.value('@value', 'varchar(50)')
FROM @XMLDoc.nodes('/root/item') AS x(Item)

The xml data type has five methods, two of which, nodes and value, are used here: nodes to propogate the ‘item’ elements in to a rowset, and value to retrieve a specific value from the rowset. The nodes method accepts one parameter, an XPath Query to retrieve the desired xml data. The value method accepts two parameters, an XPath Query to retrieve the desired xml data, and a SQL type that states what type the return value should be. Note that at the end of the FROM statement, there is an alias for both tablename (x) and column name (item), and that these aliases are used in the SELECT statement.

Both of the above T-SQL examples produce the following results:

value
----------
Lewiston
Portland
Spokane

(Note: if you copy and paste these examples in to SQL Server to run, you may need to re-type all the apostrophe’s and quotation marks in the T-SQL statement first or it will generate errors)

OK, that looks like something you could use in an IN statement. Now, we just need to build our stored procedure, querying the xml in a subquery in the IN statement. Here’s how it would look in SQL Server 2000:

CREATE PROCEDURE [dbo].[uspUseXML]

@XMLDoc VARCHAR(2000)

AS

DECLARE @XMLDocHandle INT

EXEC sp_xml_preparedocument @XMLDocHandle OUTPUT, @XMLDoc

SELECT * FROM tblProjects WHERE ProjectCity IN(
SELECT value
FROM OPENXML(@XMLDocHandle,'/root/item',2)
WITH (value VARCHAR(50) '@value'))

EXEC sp_xml_removedocument @XMLDocHandle

And here’s how we’d do it in SQL Server 2005 —
CREATE PROCEDURE [dbo].[uspUseXML]

@XMLDoc xml

AS

SELECT * FROM tblProjects WHERE ProjectCity IN(
SELECT x.Item.value('@value', 'varchar(50)')
FROM @XMLDoc.nodes('/root/item') AS x(Item))

Another way to do this would be to join the projects table and the xml using an INNER JOIN. Here’s the SQL Server 2000 example:

CREATE PROCEDURE uspUseXMLInJoin

@XMLDoc VARCHAR(2000)

AS

DECLARE @XMLDocHandle INT

EXEC sp_xml_preparedocumnet @XMLDocHandle OUTPUT, @XMLDoc

SELECT p.*
FROM tblProjects p
INNER JOIN
OPENXML(@XMLHandle, '/root/item',2)
WITH (city VARCHAR(50) '@value') as x
ON p.ProjectCity = x.city

And in SQL Server 2005:

CREATE PROCEDURE uspUseXMLInJoin

@XMLDoc xml

AS

SELECT p.*
FROM tblProjects p
INNER JOIN @XMLDoc.nodes('/root/item') AS x(City)
ON p.ProjectCity = x.City.value('@value', 'varchar(50)')

It’s beyond what I want to get in to here, but XML has many other nifty uses in SQL Server, especially in being able to update several rows of data without having to make several database calls. The MSDN site has lots of info, and there is also a very good article (free registration required) at SQLServerCentral.com that gives lots of examples and variations on its use. In the course of researching this, I also found an extremely excellent online article describing in great detail other ways of dealing with Arrays and Lists in SQL Server.