Interagire con db differente in una stored procedure

lunedì 05 marzo 2007 - 18.24

fabiogvn Profilo | Newbie


Sto cercando di creare una stored procedure che crei un database ed alcune tabelle nel nuovo database creato. Questa procedura è memorizzata in un database A, la chiamo passando come parametro il nome del nuovo database e la creazione avviene regolarmente. Il problema è nel creare le nuove tabelle, perchè il sistema le crea nel database A, ovvero quello dove è salvata la stored procedure, nonostante dentro la procedura, dopo la creazione del nuovo database, abbia inserito un'istruzione USE Nuovodb.

Come posso fare per fargli creare le tabelle nel nuovo database?

Il codice della procedura è il seguente:

CREATE PROCEDURE [dbo].[wc_CreateNewDb] @Dbname nvarchar(128) AS BEGIN /* creazione database*/ if @Dbname=null begin RAISERROR ('Nome database non specificato',16,1) return -1 end else exec('CREATE DATABASE ' + @Dbname) /* Creazione tabella Users */ exec ('USE ' + @Dbname) CREATE TABLE dbo.Users ( IdUser int NOT NULL IDENTITY (1, 1), Nome varchar(50) NOT NULL, Password varchar(50) NOT NULL, Admin smallint NOT NULL ) ON [PRIMARY] END

lbenaglia Profilo | Guru

>/* Creazione tabella Users */
>exec ('USE ' + @Dbname)

Ciao fabiogvn,

il codice eseguito dinamicamente tramite EXEC o sp_executesql ha uno scope differente rispetto a quello della procedura chiamante. Dato che dopo la USE non è presente alcuna istruzione, tale comando rimarrà fine a sè stesso.
I Books Online riportano:

"EXECUTE"
http://msdn.microsoft.com/library/en-us/tsqlref/ts_ea-ez_05ro.asp

"Statement(s) inside the EXECUTE statement are not compiled until the EXECUTE statement is executed.

Changes in database context last only until the end of the EXECUTE statement. For example, after the EXEC in this example, the database context is master:

USE master EXEC ("USE pubs") SELECT * FROM authors"

Per risolvere il problema puoi concatenare alla USE la creazione della tabella separandola con un ";", eseguendo l'intero codice dinamicamente con la EXEC.

Ad ogni modo non capisco la necessità di definire una stored procedure per creare oggetti ricorrendo tra l'altro al dynamic SQL (operazione assai pericolosa):

"The Curse and Blessings of Dynamic SQL"
http://www.sommarskog.se/dynamic_sql.html

Ciao!
--
Lorenzo Benaglia
Microsoft MVP - SQL Server
http://blogs.dotnethell.it/lorenzo/
http://italy.mvps.org

fabiogvn Profilo | Newbie

Si, così funziona, comunque ho seguito il tuo consiglio ed ho spostato il codice per la creazione delle tabelle direttamente sulla pagina ASP
Partecipa anche tu! Registrati!
Hai bisogno di aiuto ?
Perchè non ti registri subito?

Dopo esserti registrato potrai chiedere
aiuto sul nostro Forum oppure aiutare gli altri

Consulta le Stanze disponibili.

Registrati ora !
Copyright © dotNetHell.it 2002-2023
Running on Windows Server 2008 R2 Standard, SQL Server 2012 & ASP.NET 3.5