Database Name as a Variable in a Stored Proc? 1

[hoialmen]

Greetings All!

Wondering if anyone knows if it is possible to pass a database name into a Stored Proc and assign it to a variable to be used in a SQL statement. I am writing a Stored Proc that needs to reference a group of databases that are the same structurally but contain different data. The specific database being accessed with depend on what is being requested by a user through a ColdFusion form. The following is an example of what I am trying to do:


CREATE PROCEDURE dbo.vspTestProc
(
@CurrentDB (Data Type?)
)

AS

INSERT INTO TempTable (Col1,Col2,Col3)
SELECT CurrentTable.Col1, CurrentTable.Col2, CurrentTable.Col3
FROM @CurrentDB.Table1 AS CurrentTable


Any suggestions will be greatly appreciated

Regards,

Nathan C. Hoialmen
President
Encephalon Business Solutions, LLC

http://www.encephalon.net

 

[donutman]

Someone is going to suggest using dynamic SQL. I don't. If the SP isn't just a simple SQL statement, then I'd write a separate SP for each DB. Then write one SP that calls the appropriate SP based upon a case statement (or series of if statements). That way each specific SP can get optimized and the SP that does the calling is trivial so it doesn't need optimization.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[mrdenny]

And here is your dynamic SQL suggestion.

CREATE PROCEDURE dbo.vspTestProc
    (
        @CurrentDB    (Data Type?)
    )
 
AS
BEGIN
    DECLARE @CMD varchar(1000)
    set @CMD = 'INSERT INTO TempTable (Col1,Col2,Col3)
    SELECT CurrentTable.Col1, CurrentTable.Col2, CurrentTable.Col3
    FROM ' + @CurrentDB + '.Table1 AS CurrentTable'
    EXEC (@CMD)
END

It is a little more resourse intensive on the SQL Server, but it is easier to scale if you add more databases as you don't have to remember to go and create more stored procedures.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[donutman]

And it can't be optimized.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[SQLSister]

One thing to remember when using dynamic SQL is that you have to set the permissions at the table/view level. Giving the sp exec permissions won;t be neough when using dynamic SQL.

Questions about posting. See faq183-874

 

[donutman]

Oh, and while we're at it, also remember that since this is a web app, you should consider the possibility of SQL injection attacks. I don't know how ColdFusion works, but if someone can bypass your form and slip this into your variable, then you have security problems.

@CurrentDB='SomeTable Drop Table AnotherTable Select * from dbo'

-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[hoialmen]

Thanks for all the prompt feedback!

Unfortunately the coding standards implemented by our DBA group don't permit dynamic SQL so it looks like multiple SPs is going to have to be the answer.

Thanks again for the feedback.

Sincerely,

Nathan C. Hoialmen
President
Encephalon Business Solutions, LLC

http://www.encephalon.net

 

[SQLSister]

YOu can put all in one sp if you want using if statments. But Ithink you are better off creating the multiple sps and then one master sp which will run the proper individual sp depending on the situation. OR you can write the alternative choosing code within your application and then just run one sp.Your dbas are smart - dynamic SQL is bad and should be avoided if at all possible, especially in a web interface.


Questions about posting. See faq183-874

 

[tlbroadbent]

Another option is to have the DBAs create a system stored procedure in the master database. You could then execute the SP in a selected database by using the three part object name rather than passing the database name as a parameter.

Exec mydatabase.dbo.sp_TestProc

Here is some code you can use to see how to use this technique.

--create a test table in db1
use db1 
create table temptable(col1 int, col2 int, col3 int)
go

--create a test table in db2
use db2
create table temptable1(col1 int, col2 int, col3 int)
insert temptable1 values (1,1,1)
insert temptable1 values (1,2,2)
insert temptable1 values (1,3,2)
insert temptable1 values (1,4,2)
go

--create a test table in db3
use db3
create table temptable1(col1 int, col2 int, col3 int)
insert temptable1 values (2,1,1)
insert temptable1 values (2,2,2)
insert temptable1 values (2,3,2)
insert temptable1 values (2,4,2)
go

use master
go
--create the SP in the master database
--note the name begins with "sp_"
CREATE PROCEDURE dbo.sp_TestProc
AS
    INSERT INTO db1..TempTable (Col1,Col2,Col3)
    SELECT t.Col1, t.Col2, t.Col3
    FROM dbo.temptable1 t
go

--mark the SP as a system object.
exec sp_ms_marksystemobject sp_TestProc
go

--Go to db1 and exec the stored procedure in db2 and db3
use db1
exec db3.dbo.sp_TestProc
exec db2.dbo.sp_TestProc

--select from table in db1 to show results of execution
Select * from temptable
Order by 1,2,3

Let me know if you have any questions.

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.comcast.net/sql/sql_articles.htm

 

[SQLSister]

Elegant solution, Terry.

Questions about posting. See faq183-874

 

[hoialmen]

Thanks to all for the good advice!

This particular stored proc is becoming more complex than I imagined. There are some additional problems but I am going to create a new post for them as they are really not related to this topic.

Thanks again!

Nathan C. Hoialmen
President
Encephalon Business Solutions, LLC

http://www.encephalon.net