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 dont 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 cant 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()))

 

[mocella]

(assuming SQL2K here):
Youll 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 isnt 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):
Youll 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 isnt 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