USE statement with variable

[Walter]

Hi,
I am doing a really simple test with SQL Server 7.0:

Using the Query Analyzer
Logged as sa
Located in master database

#1 USE Test
#2 EXEC('USE Test')

#1 => the database context is switched to Test
#2 => the database is NOT switched

???

[Trevor Best]

Re: USE statement with variable

On 23 Dec 2003 08:04:49 -0800 in comp.databases. ms-sqlserver,
leonf00@hotmail .com (Walter) wrote:
[color=blue]
>Hi,
>I am doing a really simple test with SQL Server 7.0:
>
>Using the Query Analyzer
>Logged as sa
>Located in master database
>
>#1 USE Test
>#2 EXEC('USE Test')
>
>#1 => the database context is switched to Test
>#2 => the database is NOT switched
>
>???[/color]

The "use" in the second example is only valid for that batch of
dynamic SQL, e.g.

use db1
select * from wibble
exec ('use db2 select * from wibble')
select * from wibble

This would get you the contents of db1..wibble, followed by
db2..wibble, followed by db1..wibble

To do a lot to different databases you would probably need build up a
large string of SQL commands then exec it, e.g. what I use for all my
databases:


CREATE PROCEDURE spMAExec @paramSQL nvarchar(3950) AS
-- Execute bit of SQL on all MA databases
declare csr cursor for select [name] from master..sysdata bases
declare @dbname nvarchar(255)
declare @SQL nvarchar(4000)
declare @IsMA bit

open csr
fetch next from csr into @dbname
while @@fetch_status= 0
begin
exec @isMA = spIsMA @dbname
set @dbname = '[' + @dbname + ']'
if @IsMA = 1
begin
set @SQL = 'use ' + @dbname + char(13) + char(10) +
@paramSQL
print @SQL
exec sp_executesql @SQL
end
fetch next from csr into @dbname
end
close csr
deallocate csr

GO

CREATE Procedure spIsMA @dbname varchar(100), @debug int = 0
as
set nocount on

-- Return true if specified database is MA
declare @bMA bit, @strProfile varchar(50)
declare @strSQL nvarchar(1024)
declare @strParams nvarchar(1024)
set @bMA = 0

if system_user = 'sa'
begin

set @strSQL=
'if exists(select * from [' + @dbname +
'].dbo.sysobjects where [name] = ''zstblAppProfi le'')
begin
set @strProfileOUT = (select [Value] from [' +
@dbname + '].dbo.zstblAppPr ofile where [key] = ''AppSignature' ')
--print @strProfileOUT
end'
set @strParams = '@strProfileOUT varchar(50) OUTPUT'

if @debug=1 print @strSQL
if @debug=1 print @strProfile
execute sp_executesql @strSQL, @strParams,
@strProfileOUT = @strProfile output
if @debug=1 print @strProfile

if @strProfile = 'CAPS$'
begin
set @bMA = 1
-- log database name for plebe
if not exists(select * from zstblMADatabase s
where dbname=@dbname)
begin
insert into zstblMADatabase s (dbname)
values (@dbname)
end
end
else
begin
-- unlog database name for plebe
if exists(select * from zstblMADatabase s where
dbname=@dbname)
begin
delete from zstblMADatabase s where
dbname = @dbname
end

end
end
else
begin
-- plebe may not have permission to look into other
database so we'll look
-- in the table created by sa
if exists(select * from zstblMADatabase s where
dbname=@dbname)
begin
set @bMA = 1
end

end

--select 1 as One, @bMA As IsMA
return @bMA
GO

--
A)bort, R)etry, I)nfluence with large hammer.

[Walter]

Re: USE statement with variable

Thank you for your help Trevor!
I changed
EXEC('USE ' + @DB_NAME)
if not exists (select * from sysusers where name = N'PMOAdmin' and uid
< 16382)
EXEC sp_grantdbacces s N'PMOAdmin', N'PMOAdmin'

to:
EXEC('USE ' + @DB_NAME +
' if not exists (select * from sysusers where name = N''PMOAdmin'' and
uid < 16382)
EXEC sp_grantdbacces s N''PMOAdmin'', N''PMOAdmin''')
[color=blue]
> To do a lot to different databases you would probably need build up a
> large string of SQL commands then exec it, e.g. what I use for all my
> databases:[/color]
That's not exactly what I want to do. First I create a login onto the
server. Than I go to the database to create roles/users/rights, ...
Than I need to create a role/user/rights in another database.

I'll explain the reason for that, maybe you can show me a better
approach (I am still novice in SQL programming :)

I need to copy data from Database "S" -> "D"
I need to be able to start the transfer with a client command. What I
use now is a stored proc "Start_proc " that goes like this:
INSERT INTO TableD1...
SELECT *
FROM [S].[dbo].TableS1
etc.

So I need SELECT rights in Database S...
[color=blue]
>
>
> CREATE PROCEDURE spMAExec @paramSQL nvarchar(3950) AS
> -- Execute bit of SQL on all MA databases
> declare csr cursor for select [name] from master..sysdata bases
> declare @dbname nvarchar(255)
> declare @SQL nvarchar(4000)
> declare @IsMA bit
>
> open csr
> fetch next from csr into @dbname
> while @@fetch_status= 0
> begin
> exec @isMA = spIsMA @dbname
> set @dbname = '[' + @dbname + ']'
> if @IsMA = 1
> begin
> set @SQL = 'use ' + @dbname + char(13) + char(10) +
> @paramSQL
> print @SQL
> exec sp_executesql @SQL
> end
> fetch next from csr into @dbname
> end
> close csr
> deallocate csr
>
> GO
>
> CREATE Procedure spIsMA @dbname varchar(100), @debug int = 0
> as
> set nocount on
>
> -- Return true if specified database is MA
> declare @bMA bit, @strProfile varchar(50)
> declare @strSQL nvarchar(1024)
> declare @strParams nvarchar(1024)
> set @bMA = 0
>
> if system_user = 'sa'
> begin
>
> set @strSQL=
> 'if exists(select * from [' + @dbname +
> '].dbo.sysobjects where [name] = ''zstblAppProfi le'')
> begin
> set @strProfileOUT = (select [Value] from [' +
> @dbname + '].dbo.zstblAppPr ofile where [key] = ''AppSignature' ')
> --print @strProfileOUT
> end'
> set @strParams = '@strProfileOUT varchar(50) OUTPUT'
>
> if @debug=1 print @strSQL
> if @debug=1 print @strProfile
> execute sp_executesql @strSQL, @strParams,
> @strProfileOUT = @strProfile output
> if @debug=1 print @strProfile
>
> if @strProfile = 'CAPS$'
> begin
> set @bMA = 1
> -- log database name for plebe
> if not exists(select * from zstblMADatabase s
> where dbname=@dbname)
> begin
> insert into zstblMADatabase s (dbname)
> values (@dbname)
> end
> end
> else
> begin
> -- unlog database name for plebe
> if exists(select * from zstblMADatabase s where
> dbname=@dbname)
> begin
> delete from zstblMADatabase s where
> dbname = @dbname
> end
>
> end
> end
> else
> begin
> -- plebe may not have permission to look into other
> database so we'll look
> -- in the table created by sa
> if exists(select * from zstblMADatabase s where
> dbname=@dbname)
> begin
> set @bMA = 1
> end
>
> end
>
> --select 1 as One, @bMA As IsMA
> return @bMA
> GO[/color]