How can i change the table name dynamically in SP

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

How can i change the table name dynamically in SP

Author

Topic

reddymade
Posting Yak Master

165 Posts

Posted - 2006-02-08 : 17:32:09

How can i dynamically change the table name: in the stored procedure depending on the @Module
In the following stored procedure under the select query where i use from table names: the table name i hardcoded now is Tab_CCSNETCN m

please, i want to make that dynamic so that i can pass the table name using the parameter @Module

using a case:
CASE @Module
WHEN 'CN' THEN 'Tab_CCSNETCN m'
WHEN 'SU' THEN 'TAB_CCSNETSU m'
WHEN 'CO' THEN 'TAB_CCSNETCO m'



CREATE PROCEDURE dbo.USP_SendEmailActionsSUM
		(@ModuleID			int,
		 @Module			Varchar(50))
 AS

INSERT INTO Tab_ccsNetEmailsum
		(toemail,
		alerttime,
		body,
		emailsubject)
Select  u.Email, getdate(), 
'Program: ' + pg.progno + char(13)+char(13)+ 'Project: ' + pj.projno + char(13)+char(13)  + 'Contract: ' + ct.contractno + char(13) +char(13)
+ 'Due Date: ' + CONVERT(varchar(10),a.DueDate,101) + char(13) +char(13)+ 'Company: ' + rtrim(isnull(a.company,'')) + char(13)+char(13) 
+ 'Action Description: ' + a.actiondescription + char(13)+char(13) + 'Status: ' + rtrim(isnull(a.status,'')) + char(13)+char(13)
+ 'Comment: ' + rtrim(isnull(a.comment,'')) + char(13)+char(13)
+  'Assigned To: ' + u.UserName + char(13)+char(13) as body,'You have been assigned a Task on CCSNet: '+a.actiondescription
from tab_ccsnetactions a, tab_ccsnetprograms pg, tab_ccsnetprojects pj,
tab_ccsnetcontracts ct, TAB_ccsNetUsers u, Tab_ccsNETCN m 
where a.ModuleRecordID  = m.cnid and
a.assignedto = u.UserID and
m.progid = pg.progid and 
m.projid = pj.projid and
m.contractid = ct.contractid and
a.modulerecordid = @ModuleID
GO

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-02-08 : 19:21:46

U can create the query dynamically

eg.

Declare @sSQL varchar(10000)
Set @s = 'Select .... from ' + @Module + ' Where .....'
Execute (@s)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-02-08 : 19:22:43

But doing that is not recommended due to performance and security reasons. Why do you need to change the table name dynamically? It's usually due to bad design.

Tara Kizer
aka tduggan

reddymade
Posting Yak Master

165 Posts

Posted - 2006-02-08 : 21:04:59

This query is used to get all the tasks assigened to members, which are actionitems. this actionitems table is a common table between multiple modules. each module has its own table.

so now when i want to get the actionitems from module1 i use the same SP using that modules tablename in it, like that if i want to fetch the action items related to module2 i can use the same query by calling the module2's main table in it, like that i have six modules in the project, all of those modules does use the actionitems child table as a common table.

quote:
Originally posted by tkizer

But doing that is not recommended due to performance and security reasons. Why do you need to change the table name dynamically? It's usually due to bad design.

Tara Kizer
aka tduggan

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-02-09 : 12:58:41

quote:

each module has its own table.

And there's the possible design problem.

If you really want to do this, search the main site for dynamic SQL. It'll show you how to do this. But it certainly wouldn't be recommended.

Tara Kizer
aka tduggan

X002548
Not Just a Number

15586 Posts

Posted - 2006-02-09 : 13:00:55

Add a module column.

Done, next

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources