Archive for the ‘Transact-SQL’ Category

Some Useful SQL Functions

August 30, 2005 in Transact-SQL | Comments (0)

I’m not sure why it is, but Transact-SQL seems to be shy a few important functions that are useful for parsing irregular data. Sure, they include the isNull function to let you substitute something useful for a NULL value, but they don’t provide good negation functions for the opposite case (at least not that I could find). I’ve invented a few new ones that provide some useful functionality and extend your ability to query against irregular data.

Here they are:

  • IsEmpty(@argument) – Takes a string value and tests to see if it is null or contains an empty string. If it is not empty, it returns the argument, otherwise it returns NULL. Use this inside an ifNull() test on fields that may contain nulls or empty strings.
  • IfEmpty(@argument, @default) – Similar to IsEmpty(), this one lets you pass in a default value to use if the argument is empty. It’s basically the same as IsNull(), except that it works with NULL and empty strings.
  • NotEmpty(@argument, @default) – This is the negation case that I found missing. It returns the default value if the tested value contains something, otherwise an empty string. Great for things like zip-plus 4 on zip codes, allowing you to print the dash only if the plus 4 field has a value in it.

Limitations:

These functions were designed mainly for returning formatted text for reporting or rendering into HTML or XSLT stylesheets, so they assume string arguments. You will have to coerce your values into strings to use them, or write similar functions for other data types.

The source:

CREATE FUNCTION IsEmpty
(
 @argument AS varchar(8000)
)
RETURNS varchar(8000)
BEGIN
 if @argument is null return null
 if len(@argument) = 0
 begin
  set @argument=null
 end
 return @argument
END

CREATE FUNCTION IfEmpty
(
 @argument AS varchar(8000),
 @default as varchar(8000)
)
RETURNS varchar(8000)
BEGIN
 if dbo.isEmpty(@argument) is null
 begin
  return @default
 end
 return @argument
END

CREATE FUNCTION NotEmpty
(
 @argument AS varchar(800),
 @default as varchar(800)
)
RETURNS varchar(8000)
BEGIN
 if dbo.isEmpty(@argument) is not null
 begin
  return @default
 end
 return ''
END


Adding GUID columns to existing SQL Server tables

October 5, 2004 in Transact-SQL | Comments (0)

Okay, so you’ve built your database and everything is going great, then you find out that it’s going to be distributed, or that multiple instances need to synchronize data across servers. This means that all of your unique integer keys are about to become useless, since any other instance might have generated the same identifiers. The best way to manage the conflicts is to add a rowguidcol column to your tables.

Depending on the size of your database, this in itself could be a daunting task. So do what I do… write a SQL query that will write a SQL query that will do the updates for you.

In this case, what I need is a query that will pull out all of the table names in the database and render some Transact-SQL to add the new unique identifier to the table, then go through and stuff the existing records with a new ID.

By querying sysobjects, we can find information about any object in the database. Objects of type U refer to user tables, so these are the ones we’re after.

You can use this script to create a stored procedure that will generate the appropriate script for every table in your system. Run this script, then take the output and run that to actually create the new column named guid and stuff the rows with newid() values.

Be careful about running this script again if you add tables later. The update query will give all of the guids new values, so if you have any dependencies built upon the guid column values, you’ll end up breaking the dependencies.

drop procedure sp_gen_guid_alterations
go

create procedure sp_gen_guid_alterations as
select
 'alter table [' + name + '] add guid uniqueidentifier rowguidcol
 not null default newid()'+
 char(13) + 'go' + char(13)
from
 sysobjects
where
 type = 'U'

go

The output will look something like this:

alter table person add guid uniqueidentifier rowguidcol
  not null default newid()
go


Tripping on SQL Grant Syntax Differences

July 16, 2004 in Transact-SQL | Comments (0)

This is the sort of thing that happens when you use different dialects of SQL. I’m blogging it here in hopes that it will cement it in my mind so that next time I can avoid tripping over it.

With Microsoft SQL Server, granting privileges to all of the objects in a database is pretty straightforward:

grant all on databasename to username

This should grant all rights to all objects in the database for this particular user, assuming that you have the rights to grant these privileges from your currently logged in account.

I spend a lot of time in Microsoft SQL Server these days, so sometimes when I go back and use MySQL I fall a little short on the jump. If you need to grant rights on all objects in a database, it’s similar, but just different enough to really hurt. Here’s the MySQL version:

grant all on mydatabase.* to username;
flush privileges;

Here are the distinctions:

  • If you leave the * off of the database name, MySQL will not grant permissions to the individual tables. I’m not sure what it does grant privileges to, besides access to the database itself, but it doesn’t tell you that you should have been more specific. This is how you can get stuck, thinking you granted the privileges when really nothing at all has happened.
  • You have to issue the flush privileges command or the new rights won’t be published. I usually remember to do this one, but if you’re trying various sets of rights, you might forget once in a while.

If you want to grant permissions to specific tables or columns, I’d recommend reading the docs. I’m not going to write about that here.


Transact SQL Loops

January 22, 2004 in Programming,Transact-SQL | Comments (0)

I noticed that someone had pinged my site searching for Transact SQL Loops. I figured maybe I ought to post one, since I’m sure my blog disappointed the searcher.

The first thing you have to do is declare a cursor. The cursor will allow you to walk through the results of a query one row at a time, so declare the cursor with a meaningful name and the query that will provide the results you’re looking for:

declare cust_cursor cursor for
  select
    customer_number
  from
    customers
  order by
    customer_number

You need to have some variables declared to catch the results of the fetch.

declare @customer_number as int

Now you can open the cursor and fetch the results. The system variable @@fetch_status will tell you when there is no data left in the cursor. You need to fetch one time before you start the loop so you’ll know if there’s any data to retreive at all, then again at the bottom of the loop to get the next record (if any). Your loop condition is based on @@fetch_status, so the minute it returns anything but zero (for success), you bail out of the loop.

Notice the fetch next/into syntax that reads the values from the next row in the cursor into the local variables you’ve created.

open cust_cursor
fetch next from cust_cursor into @log_date
while @@fetch_status = 0
begin
  ' Do stuff with the value you retrieved
  select @customer_number
  fetch next from cust_cursor into @customer_number
end

Once you’re through with your loop, you need to close the cursor and deallocate to free the resources that it was using.

close cust_cursor
deallocate cust_cursor