hi,
i am a learner of ms -sql server 2000, i had a doubt in stored
procedures

suppose i have a data base having 20 tables, all the tables have a
column named--DATE

can we write a store procdure to find out the data ---i mean i want
the data entered
between two days ---- if i call the stored procedure in any one of the
table i need to get the answer

pls help me how to write the stored procedure

satishkumar.g

Do you mean calling the procedure for any one table and then getting the
data from the table with a date filter? If yes, then you would need to use
dynamic string and send the table name and two dates to the stored procedure
as parameters. Something like

create procedure dbo.GetData
(
@tablename varchar(100),
@dateFrom datetime,
@dateTo datetime
)

as

execute
('
select * from '+@tablename +' where date >= ' + @dateFrom +' and date <= '+
@dateTo
)

go

offcourse, you should check the part with dates and see what would you
actually want to get back. Do you want to filter by time, should it include
or exclude datefrom data in the results and so on. You should also consider
using sp_executesql instead of execute.



MC



"satish" <satishkumar.gourabathina (AT) gmail (DOT) com> wrote

MC's answer is awesome IMO.

Only thing I might add is that this is called "Dynamic SQL."

For sure has its uses. Erland has some excellent writeups regarding how
easy it is to overuse this technique.

Obvious questions come up such as "Why do you have 20 tables all with
the same data?"