convert this script to a stored procedure

[divinyl]

hi all

i'm a SP newbie - can someone help me convert this script to a stored procedure? i've tried but i'm failing miserably. The stored procedure will exist in a user database - not the master - so i tried putting master.dbo before sysdatabases but it says it doesn't know what this item is...HELP

-------------
declare @IDENT INT, @sql varchar(1000), @DBNAME VARCHAR(200)

select @IDENT=min(DBID) from SYSDATABASES WHERE [DBID] > 4 AND NAME NOT IN

('pubs','northwind')
while @IDENT is not null
begin
SELECT @DBNAME = NAME FROM SYSDATABASES WHERE DBID = @IDENT
/*Change disk location here as required*/
SELECT @SQL = 'BACKUP DATABASE '+@DBNAME+' TO DISK =

''\\server1\upgrade_dumps\'+@DBNAME+'.BAK''WITH INIT'
PRINT @SQL
EXEC (@SQL)
select @IDENT=min(DBID) from SYSDATABASES WHERE [DBID] > 4 and DBID>@IDENT

AND NAME NOT IN ('pubs','clientmatter')
end

----------

I know you cannot use the "Use" statement in a stored proc -what's a way round this ??

 

[DrSql]

Try this..

CREATE PROC sp_backup
as
set nocount On
declare @IDENT INT
declare @sql varchar(1000)
declare @DBNAME VARCHAR(200)

select @IDENT=min(DBID) from SYSDATABASES
WHERE [DBID] > 4
AND NAME NOT IN

('pubs','northwind')
while @IDENT is not null
begin
SELECT @DBNAME = NAME FROM SYSDATABASES WHERE DBID = @IDENT
/*Change disk location here as required*/
SELECT @SQL = 'BACKUP DATABASE '+@DBNAME+' TO DISK =

''\\server1\upgrade_dumps\'+@DBNAME+'.BAK''WITH INIT'
PRINT @SQL
EXEC (@SQL)
select @IDENT=min(DBID) from SYSDATABASES
WHERE [DBID] > 4
and DBID>@IDENT

AND NAME NOT IN ('pubs','clientmatter')
end
set nocount OFF

Dr.Sql
Good Luck.