December 14, 2007 at 7:25 am
Does anyone have a clever way of dynamically defining the schema for a table through a parameter in a stored procedure?
Suppose I have 3 tables with the same name that are separated by schema:
Sales.tbl
Marketing.tbl
Finance.tbl
I want a single stored procedure to handle my requests for these tables but I don't want to build a string and use execute(@command)....there are several reasons so let's just say for now that this isn't an option.
so let's look at the following example proc: (which I already know can't work but I'm just setting the stage)
create procedure myDynamicSchemaSproc
@schema_name varchar(50)
as
insert into [@schema_name].tbl (myCols)
values (1)
go
Is it possible to dynamically assign the schema without doing this?
create procedure myDynamicSchemaSproc
@schema_name varchar(50)
as
declare @command varchar(max)
set @command = '
insert into [' + @schema_name + '].tbl (myCols)
values (1)'
execute(@command)
go
December 14, 2007 at 7:47 am
To the best of my knowledge this cannot be done without dynamic T-SQL. I would love for someone to prove me wrong though 😉
December 14, 2007 at 7:47 am
not sure why you cannot use dynamic SQL, but another alternative is to use synonyms (which I'd normally avoid).
An example is:
create schema foo1
go
create schema foo2
go
create table foo1.mytable(a int)
go
insert into foo1.mytable values (1)
go
create table foo2.mytable(a int)
go
insert into foo2.mytable values (2)
go
create synonym aliasedtable for foo1.mytable
go
select * from aliasedtable
go
drop synonym aliasedtable
go
create synonym aliasedtable for foo2.mytable
go
select * from aliasedtable
Regards,
Andras
December 14, 2007 at 8:01 am
Andras,
Cool solution, I did not even know about this feature. It seems like managing objects with synonyms can become confusing and messy, if you do not know that the object name is aliased.
December 14, 2007 at 8:06 am
Adam Haines (12/14/2007)
Andras,Cool solution, I did not even know about this feature. It seems like managing objects with synonyms can become confusing and messy, if you do not know that the object name is aliased.
Hi Adam,
I cannot agree with you more about the messiness of using synonyms. They are resolved at execution time, so probably forget statement caching 🙁 But in emergency cases they do come in handy 🙂
Andras
December 14, 2007 at 8:10 am
Dynamic tsql is difficult because in my situation, I have a rather long procedure with other local table variables that I need to access. When you use dynamic tsql, it executes outside the context of the current procedure and cannot see the table variable structures that are in place. Not to mention, there is some overhead with dynamic tsql that I would love to avoid.
Your example would still keep me in the same place when the synonym name would be passed as a parameter in the procedure....
December 14, 2007 at 8:16 am
Chris Gierlack (12/14/2007)
Dynamic tsql is difficult because in my situation, I have a rather long procedure with other local table variables that I need to access. When you use dynamic tsql, it executes outside the context of the current procedure and cannot see the table variable structures that are in place. Not to mention, there is some overhead with dynamic tsql that I would love to avoid.Your example would still keep me in the same place when the synonym name would be passed as a parameter in the procedure....
One thing to note, that you cannot use the variable in the create synonym statement directly. So you could either use dynamic SQL only for the create synonym statement, or, if the set of possible schema names are small and fixed, use an if else statement to set up the relevant synonym.
Concerning the dynamic SQL being difficult, you could create a single string with the contents, use a dummy value for the schema (like AAAAAAAAAAAA), and then just do a replace.
Regards,
Andras
December 14, 2007 at 8:23 am
If you can add a column which differentiates which table is which in some fashion, something as simple as a column that directly lists a table name or perhaps giving them each a fixed block of an identifier range, then you can create an updatable view over all the tables and use the where clause to ensure it is inserted into the right table. Depending on on exactly how you have the tables structured, you will probably have to create instead of triggers on the view to show the compiler exactly how to handle those updates/inserts/deletes, but that is normally not hard.
That is probably more awkward than dynamic sql or aliases in most cases, but once implemented it may be more flexible than aliases and it avoids the dynamic sql.
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply