table name as variable in sql
-
Is it possible to store a tabel name in a varibale and pass it f.ex. to a stored procedure to use it in a query. Somehow I only get error messages like @tablename has to be declared
f.ex
declare @tablename sysname
set @tablename = 'someTable'
select * from @tablename
Any idea?
Thanx
-
Before you go down that route, go here http://www.sommarskog.se/dynamic_sql.html and read (everything)
This (dynamic SQL) isn't as easy or innocent as it may seem at first glance. Be fully aware of the consequences of this technique.
/Kenneth
-
ok, I got it, no dynamic SQL.
Yes I know it is not best practice and maybe worst but I had the idea of designing a single access API - if you could call it an API.
But idea trashed!
thanx for your help
-
Hi,
We use dynamic SQL a lot, and find it a very useful tool for many purposes. The article mentioned above is however very useful reading - it is absolutely good to know about the problems that might arise when using dynamic SQL. But I wouldn't let that prevent me from creating a general purpose procedure - in some cases that is a very good thing.
Here's a suggestion for how your initial question could be solved:
create procedure pr_get_all
@tablename sysname
as
begin
-- Verify that input is a tablename
if not exists (select 1 from sysobjects
where type = 'U'
and id = object_id(@tablename))
begin
raiserror('Not a tablename!', 0, 1)
goto proc_exit
end
-- Execute select
exec('select * from ' + @tablename)
proc_exit:
end
go
exec pr_get_all 'dtproperties'
An obvious problem with this example, is of course that you will have a very hard time using the output programmatically, since there are no well-defined output...
-
You´ll need use dynamic sql to processes a query.
You have Openquery(@sqlStmt) command or create dymanic sql and exec(@sqlStmt)
Hildevan O Bezerra -
Before posting this, let me first disclose that of course there are better design methods...there is more than one way to skin a cat. However, in the "real world" sometimes we have to use a solution that works for the problem, no matter how "flawed" the design is. This doesn't mean that you should code against the grain of good design, but you should remember that not all of life's problems fit neatly into a textbook design. With that being said, here's a real world solution that is being used:
create procedure dbo.MASTER_PROCESS
@PROCESS nvarchar(255),
@CLIENTNAME nvarchar(255),
@SOURCENAME nvarchar(255)
as
declare @ROW int
declare @SOURCEID varchar(100)
declare @SOURCETABLE varchar(255)
declare @CREATEELIG varchar(100)
declare @SQL_EXEC1 nvarchar(4000)
declare @SQL_EXEC2 nvarchar(4000)
declare @SQL_EXEC3 nvarchar(4000)
declare @TMPINFO table
(ROW_ID Int)
insert into @TMPINFO
select ROW_ID from dbo.ELIG_MASTER_MAPPING
where CLIENTNAME=@CLIENTNAME and <A href="mailto
ROCESS=@PROCESS">PROCESS=@PROCESSset @ROW = (select min(ROW_ID) from @TMPINFO)
while @ROW <= (select max(ROW_ID) from @TMPINFO)
begin
set @CLIENTNAME = (select CLIENTNAME from dbo.ELIG_MASTER_MAPPING WHERE Row_ID = @Row)
set @SOURCEID = (select SOURCEID from dbo.ELIG_MASTER_MAPPING WHERE Row_ID = @Row)
set @SOURCETABLE= (select SOURCETABLE from dbo.ELIG_MASTER_MAPPING WHERE Row_ID = @Row)
/*-----------------------------------------------------------------
the variables are set based upon the values that are associated with the selected row_id
from a mapping table
---------------------*/
set @SQL_EXEC1=
'insert into MASTER_'+@CLIENTNAME+'
(CLIENT_NAME,SOURCE_NAME,ELIG_ID,
SOURCE_ID,SOURCE_FIELD,ADD_DATE,ACTIVE)
select distinct
cum.ELIG_ID,
GETDATE(),
''1''
from
'+@SOURCETABLE+' as cum
'
print @SQL_EXEC1
exec sp_executeSQL @SQL_EXEC1
set @Row = (select min(ROW_ID) from @tmpinfo where Row_ID > @Row)
end
select '1' as status
---------------------------------------------------------------------
The table name(s) is placed in a "holding" table (i.e. dbo.ELIG_MASTER_MAPPING) and pulled
by the procedure. This table can be loaded and updated by a controlled user group, which
cuts down on your chance for input error. The mapping table will have the following fields
for this example:
ROW_ID | CLIENTNAME | SOURCENAME | SOURCETABLE
1 |ABC_Corp |XYZ Inc. |server.database.dbo.YourTableName
The begin looping is added to the code in case there is more than one table you want to execute the query against.
Again, we could go on for days about the "right" way to do something, but when it's all said
and done, which do you want, the "right" way, or a way that works?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply