"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));