Tag: T-SQL

Recently I ran across some code that used a lot of QUOTENAME() calls. A colleague was having some trouble with the code, but what struck me was that I hadn’t often delved into the details of QUOTENAME and how it can be used in different ways. I’d always just passed in a string as a single parameter.

This post looks at a few details of how this function works.

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

QUOTENAME

The idea behind QUOTENAME() is that you pass in a string that might not be properly formatted to be an indentifier. QUOTENAME() returns the string with enclosing characters that ensure the string works in your code as a literal.

Here’s an example. If I have a string that is “Steve Jones”, I’ve quoted the string with double quotes. If I wanted to create a table with this string, I’d do this:

CREATE TABLE dbo.[Steve Jones] (id int);

I’ve explicitly put brackets around the string, which is what we commonly do in SQL Server if we have some reserved word or space we want in the object name.

NOTE: I am not recommending this, just showing this as an example.

If I were doing this in code, and maybe I wanted to dynamically create this table, I’d do this:

DECLARE @n nvarchar(20) = N'Steve Jones';
DECLARE @s nVARCHAR(100) 

SELECT @s = 'create table dbo.' + QUOTENAME(@n) + '(id int)'

EXEC(@s)

When QUOTENAME runs, by default, it will surround the string with brackets. You can see this in the results below.

This is a valid identifier, and we end up with a table that has a space in it’s name, which I abhor. But it works.

A Second Parameter

While this is how I’ve used QUOTENAME in the past, usually to clean up strings that might be reserved words, like name, there actually is an optional second parameter. The syntax for QUOTENAME is:

QUOTENAME ( string, [ character])

where

• string – the string that you need to quote
• character – a single character that represents the delimiters to be used to surround the string.

One might think that any character can be used, but that’s not true. Only a few characters are supported. The list is:

• brackets, [], which is the default
• braces, {}
• single quotes, ‘
• double quotes, “
• angle brackets, <>

That’s it. Anything else produces not an error but a NULL, as shown here:

What’s interesting is that the parameter is a single character, but the function works out what the matching character should be. For single and double quotes, this is easy. The same character is used, as you can see below. Note the single quote is escaped.

For brackets, if I use either the left or right bracket, the result has a left bracket on the left side and a right bracket on the right side. You can see that below.

Same thing for braces.

And angle brackets.

What’s what I’d expect, but it’s nice to know it works. This limits flexibility for the function, and if I were designing it, I might make the second parameter two characters that represent the left and right enclosures. Or make a separate parameter for each. That would allow me to do something like:

SELECT QUOTENAME(‘Steve Jones’, ‘_>’ )

and get

_Steve Jones>

I could have run with a space at the beginning and a comma or period at the end, helping me clean up text.

SQL New Blogger

This is a quick post that I actually spent about 10 minutes on during a flight. I had run into this while answering a friend and reading the docs, so I left a quick sentence as a reminder and then fleshed out this post. I spent another 10 minutes once I landed (and got plugged in) capturing the screen shots.

This is a good example of showing how I dug into a feature of SQL Server, I understand how it works, show how it can be used, and how I might have wished it would be used.

You could do this in a half hour at a coffee shop and start knowing that you can learn a few things and maybe show how you’d evaluate if this was needed in AI generated code, some of which you might see in the very near future.

I discovered a procedure recently that I wasn’t aware of: sp_sequence_get_range. This post looks at how the proc works.

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

The Setup

I have a sequence object, IDCounter, that is an integer with an increment of 2. The next value that is returned is shown here:

The next value returned will be 99 (increment by 2).

However, imagine that I know I need 10 new values. I don’t want a loop to get these values. Instead, I want to move the sequence to 10 values ahead.

These ten values will be 99, 101, 103, 105, 107, 109, 111, 113, 115, 117. The current value should then be 117 if we get 10 rows.

Let’s use sys.sp_sequence_get_range to do this. I’ll use this code:

DECLARE @i SQL_VARIANT
EXEC sys.sp_sequence_get_range @sequence_name = N'dbo.IDCounter', @range_size = 10, @range_first_value = @i OUTPUT
SELECT @i AS RangeStart

I need to use a SQL_VARIANT as the output, though I can cast this to anything once I have the value.

When I run this code, notice the output.

Now if I check the metadata, I’ll see the current value below as 117.

There are client side applications that gather a bunch of data and know they need to insert xx rows. This helps them both update the sequence and also reserve these values for themselves. Of course, if the application fails, these values might be lost.

SQL New Blogger

A quick post. This took me about 5 minutes to test and about 10 minutes to structure a quick post on something I learned.

As a follow-up, I’ll use another post to show how this works in an application that reserves these values and then another application performs an insert.

You could easily do this on your blog and show some knowledge.