Coalesce doesn't return the first value? Is that normal? Or just me?

Last Post 27 Nov 2012 09:33 AM by noman zafar. 6 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
SQLme
New Member
New Member

--
05 Nov 2012 07:01 AM
hey guys... i think this one is alittle weird. it could be just something i'm doing, but let me know whats up. it would help with another person looking at it.

ok so i queried sys.database_principals to get a list of the database roles exluding
the special roles. basically the following is returned ( which is what i wanted )

public
guest
NT SERVICE\MSSQLSERVER
user created role1...
user created role2...
user created role3...
etc.

i then concatinated the results to include the 'revoke' statement so the results look like this:

revoke execute on sp_createorphan to [guest];
revoke execute on sp_createorphan to [public];
revoke execute on sp_createorphan to [NT SERVICE\MSSQLSERVER];

ok so thats all good, then i take the concatinated results, and put them into a variable,
and then run the variable using the COALESCE function. all together it look like this:


declare @revoke_role_permissions_on_sp_createorphan varchar(max)
select @revoke_role_permissions_on_sp_createorphan =
coalesce (
@revoke_role_permissions_on_sp_createorphan +
'revoke execute on sp_createorphan to [' + name + ']; ', ''
)
from
sys.database_principals where name not in
(
'dbo'
,'INFORMATION_SCHEMA'
,'sys'
,'##MS_PolicyEventProcessingLogin##'
,'##MS_AgentSigningCertificate##'
,'db_owner'
,'db_accessadmin'
,'db_securityadmin'
,'db_ddladmin'
,'db_backupoperator'
,'db_datareader'
,'db_datawriter'
,'db_denydatareader'
,'db_denydatawriter'
)

select @revoke_role_permissions_on_sp_createorphan

however; the results are turned as the following. where the hell is the first one for [public] role ?

revoke execute on sp_createorphan to [guest];
revoke execute on sp_createorphan to [NT SERVICE\MSSQLSERVER];


i also noticed that all my COALESCE statements are missing the first value so it must be something i'm doing right?

i hope the tsql formatting works out on this. i did my best to polish it up before submitting
this question.



new2sql
New Member
New Member

--
07 Nov 2012 10:09 AM
hi sqlme,

i have a simple solution that might work for you that is not a COALESCE and not a CURSOR.

i'm not as good as some of the other guys on here, but it looks to me like what you are
trying to do is simply use the COALESCE function to run the results of a query plus whatever
else you have concatenated in there, but... you are having trouble because the COALESCE for
whatever reason is leaving out the first row of your query.

if you would like to use a statement that basically does the same thing without all the
potential arguments, or quagmires that go with COALESCE, and if you are a bit flexible you
might want to try this little gem.

i got this simple little doohicky ( i don't even know what to call it ) from another one
of the pro's on here called 'sqladmin'.

i've been using this forever now, and it works great for those simple scripts you might want
to write instead of using a CURSOR, or COALESCE.

here's the code:

*************************************

declare @run_the_result_set varchar(max)
set @run_the_result_set = ''
select @run_the_result_set = @run_the_result_set
+ 'my concatenated query [' + MyColumn + '] whatever else you need; '
from MyTable

select (@run_the_result_set)

-- change the 'select' on the last line to exec to actually run the result set.

*************************************

see how cool this is. i mean simple right? perfect for running a simple result set, or
output of another query. i love this thing. i can't thank sqladmin enough for it :)





SQLme
New Member
New Member

--
07 Nov 2012 11:19 AM


omg! new2sql ~ this is awesome!! why am i just now finding out about this?

what a great little piece of code. it works perfectly, and is far simpler to script out than COALESCE and CURSORS.

sqladmin is the man!

incidentally i've used his stuff plenty of times but i didn't see this one till you just pointed it out.

by the way; what is this called? i mean; it's not a function, more like i don't know... variable to variable? how would you
address this kind of trick?

anyone?
sqladmin
New Member
New Member

--
08 Nov 2012 10:32 AM

glad those scripts are helpful.

you were asking about the 'name' of this process. i'm not 100% sure there is an official 'term' for the way you populate an empty variable string with a result set.

i've always called it "running the result set"

there are a few ways to do this, but it's true; this is the best way for the sake of simplicity.

[b]

declare    @run_the_result_set        varchar(max) 
set        @run_the_result_set        = '' 
select    @run_the_result_set        = @run_the_result_set 
+ 'my concatenated query [' + MyColumn + '] whatever else you need; ' 
from MyTable 

select    (@run_the_result_set) 

[/b]

this little code snippet could probably found with a dozen different mods. a quick google would give you what you need.

- sqladmin
sqladmin
New Member
New Member

--
08 Nov 2012 10:35 AM
fyi; disregard the previous code snippet. formatting didn't take.

[b]declare @run_the_result_set varchar(max)
set @run_the_result_set = ''
select @run_the_result_set = @run_the_result_set
+ 'my concatenated query [' + MyColumn + '] whatever else you need; '
from MyTable

select (@run_the_result_set)[/b]
sqladmin
New Member
New Member

--
08 Nov 2012 10:35 AM
[b] Grrr... sqlmag; fix the formatting thing.[/b]
noman zafar
New Member
New Member

--
27 Nov 2012 09:33 AM
It might be bit old... and the problem reported by SQLme seemed to be solved as well. But i got the same problem today and googled it and found this Q.
It didnt help me though but i have fixed the issue myself.
I thought it may be worth while to put the solution online because may be the work around above will not best fit to every one.
The simple solution for this first missing value is, just initialize the variables after declaration..

declare @revoke_role_permissions_on_sp_createorphan varchar(max)
[b]set @revoke_role_permissions_on_sp_createorphan varchar=' '[/b]
select @revoke_role_permissions_on_sp_createorphan =
coalesce (
@revoke_role_permissions_on_sp_createorphan +
'revoke execute on sp_createorphan to [' + name + ']; ', ''
)
You are not authorized to post a reply.

Acceptable Use Policy