Changing the name of the Table dynamically inside the SP.

hi All,

I get a daily dump of some data in a table like DumpTable_07182007, where the suffix is the date of the data dumped. Everyday a new table is created with the date as suffix as according to the date like DumpTable_07192007, DumpTable_07202007 etc.We get day before yesterday data on today.i.e. on 20 July we get the data for 18 july in the table DumpTable_07182007 and so on. Similarly on 21 July we will get the data in the table DumpTable_07192007 . I have to create a SP (to include in a job) that pulls some data from these dump tables in such a way that if the SP is fired by a job on 20July , it shud pull the data from DumpTable_07182007 . Similarly if the SP is fired on 21 july , it shud pull the data from DumpTable_07192007 table only . etc.

So my problem is how to change the table name dynamically inside the SP so that whenever the SP is fired on a particular date , it shud pull data from the appropriate table.

I hv something like : select count(*) from DumpTable_07182007 where abcd =1 ,

query inside my SP.

Plz guide me in dynamically changing the tablename in the SP .

Thanks in advance.

Hi,

You can not have a dynamic table name in a query. One way to do this is to generate your query as a text each time, and then execute it using sp_executesql.

If you want to use its output as a table, then you can also create a table-value function, and pass the date to it. It should create a table in script and execute it and return that result.

Zafar|||

Hi,

You have to frame the select query as a string (VARCHAR), and then execute the statement.

By using this logic, you can dynamically modify not only your table name but the entire query.

example: ( similar to this )

DECLARE @.myQuery VARCHAR(200)

SET @.myQuery = 'SELECT COUNT(*) FROM DumTable_' + @.DateParameter

EXECUTE (@.myQuery)

Regards,

Perumal.R,

Prelude Solution Providers India Pvt. Ltd.,

Kotturpuram,

Chennai.

|||

You don't need to use dynamic SQL. You can try the approach below which is more easy to debug and maintain. This requires the caller to have create view permission and I assume this is probably ok in this case since this looks like a batch job on the server. So you can run it under dbo account.

1. Create a SP that create a view dynamically. The view will refer to the DumpTable being loaded. For example:

Code Snippet

create procedure CreateDumpTableView (@.date varchar(10))

as

begin

declare @.tablename nvarchar(130);

set @.tablename = quotename(N'DumpTable_' + @.date);

exec('create view DumpTableRef as select .... from ' + @.tablename);

end

2. Now, in your main SP write your queries against the view and call the the create view SP first when the date changes like:

Code Snippet

create procedure YourSp (@.date varchar(10))

as

begin

exec CreateDumpTableView @.date;

if @.@.error....

select count(*) from DumpTableRef;

end

sql