Georged Weblog

"Static" variables in SQL

I did not realise that declared variables in SQL are effectively “static” when used in rowsets. That is, the following code:

create table roles(role nvarchar(50))
GO
insert roles
select 'Administrator' union
select 'Editor' union
select 'User'
go

declare @roles nvarchar(1024)
select @roles = isnull(@roles + ', ', '') + role from roles
select @roles
go

drop table roles
go

yeilds “Administrator, Editor, User“.

Wow! Now instead of returning rowset of roles for a user and then concatenating results in client-side code, I can just write a function similar to:

create function fnGetUserRoleVerbose(@userid int, @delim nvarchar(2) = ', ')
returns nvarchar(1024)
as
begin
   declare @role nvarchar(1024)
  
   select
      @role = isnull(@role + @delim, '') + roles.name 
   from
      users
      inner join users_roles
         on users.id = userid
      inner join roles
         on roleid = roles.id
   where
      users.id = @userid
 
   return @role
end

and then just do this:

string roles = (string) SqlHelper.ExecuteScalar(
    myConnectionString, 
    CommandType.Text,
    string.Format("select dbo.fnGetUserRoleVerbose({0}, default)", userId));

Googless?

Could not google this morning... Dig deeper...

C:\>nslookup www.google.com ns1.google.com
Server:  ns1.google.com
Address:  216.239.32.10

*** ns1.google.com can't find www.google.com: Non-existent domain

I... can... work... without... Google... Must... continue... Try local brew...

C:\>nslookup www.google.com.au ns1.google.com
Server:  ns1.google.com
Address:  216.239.32.10

Non-authoritative answer:
Name:    www.google.akadns.net
Address:  216.239.53.99
Aliases:  www.google.com.au

Phew! Must be Anti-Oz conspiracy!