|
|||||||||
|
|||||||||
|
|||||||||
 |
|||||||||
| |
||
|
|
|
|
|||||||||
|
|||||||||
|
|||||||||
|
|||||||||
| |
||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
ASP Free Forums Sponsor:
|
|
|
|
#1
May 13th, 2004, 03:54 AM
|
|||
|
|||
|
Dynamic Database Name
Hi guys,
I have a question, how can I put an SQL statement with a dynamic database name? I really don't know, is it like Declare $dbname varchar(30) select * from $dbname.dbo.Customer => $dbname is the variable but this is wrong, does anyone know the good sql statement? Thanks!
|
|
#2
May 13th, 2004, 09:35 AM
|
||||
|
||||
|
You can create a dynamic connectionstring in a function.
Code:
Dynamic Database Function
Function ConnectToDatabase(strDatabaseName)
myConnectionString = "Provider=sqloledb;Data Source='" & strDatabaseName & "';Initial Catalog=Customer;User Id=sa;Password=password;"
End Function
Dynamic Database and Table Function
Function ConnectToDatabase(strDatabaseName, strTableName)
myConnectionString = "Provider=sqloledb;Data Source='" & strDatabaseName & "';Initial Catalog='" & strTableName & "';User Id=sa;Password=password;"
End Function
Then call them like this
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open ConnectToDatabase(DatabaseName)
or
Conn.Open ConnectToDatabase(DatabaseName, TableName)
|
|
#3
May 14th, 2004, 06:37 PM
|
|||
|
|||
|
If you want to use just straight sql, you can use the system stored procedure SP_EXECUTESQL to execute a dynamic sql statement.
Code:
DECLARE @dbname nvarchar(100), @sqlstm nvarchar(500), @sqlparams nvarchar(200), @error int SELECT @dbname = N'LoadStatsPremium01' SET @sqlstm = N'select * from ' + @dbname + N'.dbo.Customer' /* In this example, @sqlparams is not used since there are no parameters */ @error = EXECUTE SP_EXECUTESQL @sqlstm, @sqlparams If you use SP_EXECUTESQL you can use arguments. The arguments can not be identifiers of database objects (i.e. table names, database names, column names, etc), but it is useful if you have other variables that change such as the customer id. Code:
/* ASSUMING YOU ALREADY HAVE AN INPUT VARIABLE FOR CUSTOMER ID CALLED @CID */ DECLARE @dbname nvarchar(100), @sqlstm nvarchar(500), @sqlparams nvarchar(200) SELECT @dbname = N'LoadStatsPremium01' SELECT @sqlparams = N'@customerId int' SET @sqlstm = N'select * from ' + @dbname + N'.dbo.Customer WHERE CustomerId=@customerId' /* Here @sqlparams has the parameter list/declaration, then the arguments follow (@CID) */ @error = EXECUTE SP_EXECUTESQL @sqlstm, @sqlparams, @CID You can read more about SP_EXECUTESQL at http://msdn.microsoft.com/library/d..._ea-ez_2h7w.asp. I hope this helps. 
|
|
| Viewing: ASP Free Forums > Database > Microsoft SQL Server > Dynamic Database Name |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
|
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
