help in SP

Question

Post reply

help in SP

Nsh

SSCommitted

Points: 1639
More actions
March 30, 2006 at 5:03 pm

#171034

hi,
I have a stored proc 'MySP' running in context of master db - it contains the foll. stmt:
--- create proc
if exists (select name from sysobjects where name = 'mySP')
    drop proc mySP
GO
CREATE PROC mySP
if exists (select name from sysdatabases where name = 'MyDB')
             begin
                   drop database MyDB
                   create database MyDB
             end
else
      create database MyDB
--set permission to MyDB
--calling another stored proc

Now when this is executed - if there is a db named MyDB - it shd be dropped and created else freshly create the db and then all following stmts mst executed in context of master after the create statment's execution
how do i get this back to master context after the create stmt
plz help.
thank you,
nsh

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Brian Fenton SSC-Addicted Points: 429 More actions · Answer 1

I hope I'm not missing something here, but from what I'm seeing you should be able to use:

CREATE PROC mySP

if exists (select name from sysdatabases where name = 'MyDB')

begin

drop database MyDB

create database MyDB

end

else

create database MyDB

USE master

--set permission to MyDB

--calling another stored proc

I don't think I quite understand the order that you're doing things at the end, but if you need to switch database contexts the USE command is the way to go. Just be careful that your USE command is within the stored procedure if you're trying to affect commands within the stored procedure.

Brian

sushila SSC-Dedicated Points: 35293 More actions · Answer 2

I am consumed with curiosity.....maybe my particularly insulated existence prevents me from coming across many interesting situations and scc is the only place I know that allows me peeks into the "rest of the world"..what is the situation that requires dropping and creating entire databases-also why is the procedure itself dropped and created each time?

**ASCII stupid question, get a stupid ANSI !!!**

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 3

You can not use "USE" in a stored proc unless you employ dynamic SQL.

You can fully qualify, for example:

exec master.dbo.xp_sendmail

Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor