Procedure problem, please help!

[philiplcp]

Recently, my job need to design a generic application to query a lot of same format table within the sql server, in order to enhance the efficiency, I try to use procedure, however I don't know how to dynamic to input the table name parameter into the procedure. Have anyone can help me to solve it!

 

the following procedure code can't work, have any good idea to solve:

 

CREATE PROCEDURE TOTAL_TOPX_HANDLE AS SELECT COUNT(*) AS TOTAL FROM [color=Sienna]**Table_name** [/color] WHERE (PROFILE_STATUS <> 'A') AND (DAY(START_TIME) = DAY(GETDATE())) AND (MONTH(START_TIME) = MONTH(GETDATE())) AND (YEAR(START_TIME) = YEAR(GETDATE()))

Edited January 8, 2005 by philiplcp

[mocella]

(assuming SQL2K here):

You'll have to have your stored procedure query text (the actual SELECT statement) as a VarChar field in which you append the table name from your param passed into the proc.

 

Once you have the string build, you can EXEC that:

EXEC( @yourVarCharQueryStringGoesHere )

 

On a side note, this isn't really improving efficiency as far as sql performance as this will "compile" every time you run this proc, but it will increase maintenance efficiency for your developer/dba teams. To each, his own though. ;)

[philiplcp]

(assuming SQL2K here):

You'll have to have your stored procedure query text (the actual SELECT statement) as a VarChar field in which you append the table name from your param passed into the proc.

 

Once you have the string build, you can EXEC that:

EXEC( @yourVarCharQueryStringGoesHere )

 

On a side note, this isn't really improving efficiency as far as sql performance as this will "compile" every time you run this proc, but it will increase maintenance efficiency for your developer/dba teams. To each, his own though. ;)

 

 

thx mocella :D