Kill all user sessions using dynamic SQL - exec within exec

Environment: SQL Server 2005/2008

I would like to kill all the user connections on a particular DB using dynamic SQL. I am aware of other methods like altering the DB mode to single/offline etc., but would like to do using dynamic SQL.

I am trying to avoid cursor, while loop and also any usage of variable in executing the dynamic SQL. Here is my code using sysprocesses:

 
-- Construct KILL SQL
exec ('select ''kill '' + cast(spid as varchar(20)) + '';'' from sys.sysprocesses
    where spid NOT IN (@@spid)
    and spid > 50') 

After constructing individual kill statements now I would like to exec without declaring and using a variable as below. Am I missing something or that an exec within exec doesn't work?

  
-- execute KILL SQL
exec ('exec (''select ''''kill '''' + cast(spid as varchar(20)) + '''';'''' from sys.sysprocesses
where spid NOT IN (@@spid)
and spid > 50'')')

I know a better way to declare a variable and do it as below but I would开发者_StackOverflow中文版 like above to work if it is possible without using a variable:

 
-- routine way of doing KILL
declare @dsql nvarchar(max)=''
select @dsql = 'kill ' + cast(spid as varchar(20)) + ';' from sys.sysprocesses
where spid NOT IN (@@spid)
and spid > 50
exec(@dsql)

None of your solutions is restricted to only one database. your code kill all user sessions in all databases.

declare @dsql nvarchar(max)=''
select @dsql = 'kill ' + cast(spid as varchar(20)) + ';' from sys.sysprocesses
where spid NOT IN (@@spid)
and spid > 50
AND dbid = <your database id>
exec(@dsql)

But the Problem with all these kill options per code will be the same: Set a database offline without the AdminSession will not kill reliable all sessions. And i also have no solution for that.

I'd suggest that your last version is the best solution.