0

I have quite a few tables which contain some common columns (same name and datatype). On these columns I need to perform the same operations and I would like to do that via a stored procedure. However the only way I can make it work at the moment is using dynamic sql, which I'd rather avoid.

Is there any way to use a stored procedure for different tables (where the table name is handed over as parameter) without using dynamic sql?

Improve this question
  • 3
    Why do you want to avoid dynamic SQL? – Jon Seigel Aug 22 '14 at 17:35
  • @JonSeigel Because I find it much more difficult to maintain than "normal" sql: Intellisense won't work etc. Basically I don't want to use dynamic SQL as long as there is another approach that's still ok. Good question though. – ManOnAMission Aug 24 '14 at 16:35
7

The other thing you could do is create a view that unions the like tables together, and include a defined column with the table name, then just do a select against that view with the tablename in the where clause.

CREATE VIEW tablesviews AS
SELECT columns, 'TABLE1' as tablename
FROM Table1
UNION ALL
SELECT columns, 'TABLE2' as tablename
FROM Table2
...


SELECT {some columns}
FROM  tableviews
WHERE Tablename=@table and [other where clauses]
Improve this answer
3

one way i could think of is below..

create proc dynamictable   
(     
@name varchar(100)    
)     
as     
begin    
if @name ='tablename1'    
begin    
select columns from tablename1    
end     
if@name='tablename2'    
begin   
select columns from tablename2    
end   
end
Improve this answer
  • It is often seen for event distribution, journals, logs, that the name of a table is selected from another control table, allowing rotation. – mckenzm Mar 14 '19 at 21:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.