It's really not that hard with the right tools. Here's one that I've been using for a while:

/*

EXECute a string of T-SQL commands over the contents

of a set, replacing token strings with column values as

indicated. The set is defined by the @FROM parameter.

Optionally allows for printing the final execution string,

switching databases, and user-specified TRY-CATCH handling.

Also allows the caller to switch quote characters, to ease

quote-doubling problems.

RBarryYoung Proactive Performance Solutions, Inc.

*/

ALTER PROC

OVER_SET (

@command AS NVARCHAR(MAX), -- Template SQL command

@from AS NVARCHAR(MAX), -- FROM..WHERE clause string

@subs1 AS NVARCHAR(MAX) = N'', -- Substitution parameters, these are

@subs2 AS NVARCHAR(MAX) = N'', -- of the form "<find>=<repl>" where:

@subs3 AS NVARCHAR(MAX) = N'', -- <find> will be searched for in @command, and

-- <repl> will replace it, if it was found

-- (typically, <repl> should be a column name

-- returned by the FROM clause)

@print AS BIT = 1, -- 0 = suppress PRINT of the SQL before executing

@catch AS VARCHAR(12) = 'continue',

-- TRY/CATCH option parameters. Choices are:

-- 'continue' on an error, print a message & continue

-- 'ignore' attempt to suppress all errors

-- 'raise' try to re-raise the error

-- 'none' no TRY/CATCH blocks

@use_db AS NVARCHAR(255) = N'', -- DB to switch to befor execution of the SQL text

@quote AS NVARCHAR(8) = N'' -- search for this character & replace with (').

)

AS

--

DECLARE @QT AS NVARCHAR(1), @cr AS NVARCHAR(1);

SELECT @QT = N'''', @cr = N'

';

DECLARE @find1 AS NVARCHAR(MAX), @prfx1 AS NVARCHAR(MAX), @sufx1 AS NVARCHAR(MAX)

DECLARE @find2 AS NVARCHAR(MAX), @prfx2 AS NVARCHAR(MAX), @sufx2 AS NVARCHAR(MAX)

DECLARE @find3 AS NVARCHAR(MAX), @prfx3 AS NVARCHAR(MAX), @sufx3 AS NVARCHAR(MAX)

DECLARE @prtst AS NVARCHAR(MAX), @prfxC AS NVARCHAR(MAX), @sufxC AS NVARCHAR(MAX)

DECLARE @newdb AS NVARCHAR(MAX), @declr AS NVARCHAR(MAX)

DECLARE @NewCmd AS NVARCHAR(MAX), @GenCmd AS NVARCHAR(MAX)

;

SELECT

@find1 = CASE WHEN @subs1 = N'' THEN N'' ELSE LEFT(@subs1,CHARINDEX(N'=',@subs1)-1) END,

@prfx1 = CASE WHEN @subs1 = N'' THEN N'' ELSE N'REPLACE(' END,

@sufx1 = CASE WHEN @subs1 = N'' THEN N'' ELSE N',@find1,'+RIGHT(@subs1,LEN(@subs1)-CHARINDEX(N'=',@subs1))+N')' END,

@find2 = CASE WHEN @subs2 = N'' THEN N'' ELSE LEFT(@subs2,CHARINDEX(N'=',@subs2)-1) END,

@prfx2 = CASE WHEN @subs2 = N'' THEN N'' ELSE N'REPLACE(' END,

@sufx2 = CASE WHEN @subs2 = N'' THEN N'' ELSE N',@find2,'+RIGHT(@subs2,LEN(@subs2)-CHARINDEX(N'=',@subs2))+N')' END,

@find3 = CASE WHEN @subs3 = N'' THEN N'' ELSE LEFT(@subs3,CHARINDEX(N'=',@subs3)-1) END,

@prfx3 = CASE WHEN @subs3 = N'' THEN N'' ELSE N'REPLACE(' END,

@sufx3 = CASE WHEN @subs3 = N'' THEN N'' ELSE N',@find3,'+RIGHT(@subs3,LEN(@subs3)-CHARINDEX(N'=',@subs3))+N')' END,

@newdb = CASE WHEN @use_db= N'' THEN N'' ELSE N'USE [' + @use_db + N'];' + @cr END,

@declr = N'DECLARE @_Num AS INT, @_Lin AS INT, @_Err AS NVARCHAR(MAX), @_Msg AS NVARCHAR(MAX);'+@cr

;

;WITH

[base] AS (SELECT cmd = @command),

[quot] AS (SELECT cmd = CASE @quote WHEN N'' THEN cmd ELSE REPLACE(cmd, @quote, @QT) END FROM [base]),

[dble] AS (SELECT cmd = N'N'+@qt+REPLACE(cmd, @QT, @QT+@qt)+@qt FROM [quot]),

[prnt] AS (SELECT cmd = CASE @print WHEN 1 THEN N' PRINT '+cmd+';'+@cr ELSE N'' END

+ N' EXEC('+cmd+N');' FROM [dble]),

[ctch] AS (SELECT cmd =

CASE @catch WHEN N'none' THEN cmd

ELSE N'BEGIN TRY'+@cr+cmd+@cr+N'END TRY'+@cr+N'BEGIN CATCH'+@cr

+ N' SELECT @_Num=ERROR_NUMBER(), @_Lin=ERROR_LINE(), @_Err=ERROR_MESSAGE()'+@cr

+ CASE @catch

WHEN N'continue' THEN

N' SELECT @_msg=''Continuing after Error(''+CAST(@_Num AS NVARCHAR)+'') at Line ''+CAST(@_Lin AS NVARCHAR)+'''

+@cr+' ''+@_Err;'+@cr

+N' PRINT @_msg; '+@cr

+N' PRINT '' ''; '+@cr

WHEN N'ignore' THEN N' -- ignore = do nothing'+@cr

WHEN N'fail' THEN

N' SELECT @_msg=''Failing after Error(''+CAST(@_Num AS NVARCHAR)+'') at Line ''+CAST(@_Lin AS NVARCHAR)+'''

+@cr+' ''+@_Err;'+@cr

+N' RAISERROR(@_Num, 16, 1);'+@cr

+N' PRINT '' ''; '+@cr

ELSE N' --BAD else branch, shouldnt get here' END

+ N'END CATCH;' END FROM [prnt])

SELECT

@NewCmd = @prfx1+@prfx2+@prfx3+ N'@command' +@sufx1+@sufx2+@sufx3,

@command = cmd + @cr

FROM [ctch]

;

SELECT @GenCmd = '

DECLARE @sql AS NVARCHAR(MAX); SET @sql = '''+@newdb+ +@declr+ '''

;WITH

[-@from] AS ( SELECT * FROM ' +@from+ ' )

, [-@subs] AS ( SELECT [-NewCmd] = ' +@NewCmd+ ' FROM [-@from] )

, [-@print] AS ( SELECT [-NewCmd] = [-NewCmd] FROM [-@subs] )

SELECT

@sql = @sql + ''

'' + [-NewCmd]

FROM [-@subs]

;

EXEC sp_executesql @sql;

'

;

EXEC sp_executesql @GenCmd

, N'@command NVARCHAR(MAX), @from NVARCHAR(MAX), @find1 NVARCHAR(MAX), @find2 NVARCHAR(MAX), @find3 NVARCHAR(MAX)'

, @command, @from, @find1, @find2, @find3

;

And I think this is how Bhuvnesh's problem would be done in it:

EXEC OVER_SET '

EXEC OVER_SET "

ALTER USER [{name}] WITH LOGIN = [{name}]; ",

@from = "sys.database_principals

WHERE ( type_desc = ""WINDOWS_GROUP"" OR type_desc = ""WINDOWS_USER"" )

AND name NOT like ""%dbo%"" AND name NOT LIKE ""%#%"" ",

@use_db = "{db}",

@subs1 = "{name}=name",

@catch = "continue",

@print = 1,

@quote = "`";

',

@from = 'sys.sysdatabases

WHERE dbid > 4',

@subs1 = '{db}=name',

@catch = 'continue',

@print = 0,

@quote = '"';

I haven't compared it for performance, but I've not had any complaints myself.

As for why to do it this way? Well, I hope it's obvious, it's a heck of a lot easier to write, and even easier to read. IMHO, anyway. 😉