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:)