|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
SQL Syntax QuestionIs it possible to pass a table name to a stored procedure?
I have a ton of stored procedures that do the same basic thing, just on different tables. Can I pass a table name, and, if so can you give me an example? Ant help is greatly appreciated. This can be acheived with Dynamic SQL. There are pros and cons of this
approach... http://www.sommarskog.se/dynamic_sql.html -- Show quoteHTH. Ryan "Greg Smith" <g**@umn.edu> wrote in message news:ug6TLmFGGHA.3000@TK2MSFTNGP14.phx.gbl... > Is it possible to pass a table name to a stored procedure? > > I have a ton of stored procedures that do the same basic thing, just on > different tables. > > Can I pass a table name, and, if so can you give me an example? > > > Ant help is greatly appreciated. > Please read:
http://www.sommarskog.se/dynamic_sql.html Show quote "Greg Smith" <g**@umn.edu> wrote in message news:ug6TLmFGGHA.3000@TK2MSFTNGP14.phx.gbl... > Is it possible to pass a table name to a stored procedure? > > I have a ton of stored procedures that do the same basic thing, just on > different tables. > > Can I pass a table name, and, if so can you give me an example? > > > Ant help is greatly appreciated. > Greg Smith (g**@umn.edu) writes:
> Is it possible to pass a table name to a stored procedure? Can. But probably shouldn't do.> > I have a ton of stored procedures that do the same basic thing, just on > different tables. > > Can I pass a table name, and, if so can you give me an example? When you are in this situation, there are two likely causes: 1) Someone split up one table into may, so you have Sales04, Sales05 etc. Bad. But build a view over them, and access the view instead. 2) The tables are lookup tables, that indeed has a similar structure, but describe apart entities. Each table should then have its own procedure. They may be similar today - but tomorrow it may be different. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Think about this. What would call such a procedure? The Britney
Spears, automobiles, squid or anything in the Entire Universe procedure? >> I have a ton of stored procedures that do the same basic thing, just on different tables. << So what? Have you ever had a basic Software Engineering course inCollege? If not, you are not going to learn the basics on a Newsgroup. If you really do not care about Software Engineering, software quaity, etc. you can kludge this with some crappy dynamic code. Read what Erland wrote as a source for possible problems in the schema. I read your post that you advocate using many stored procedures when in we
could actually normalise this into a single more maintainable stored procedure. We don't get paid by lines of code anymore, we get paid for a timely result, supportability and performance. The only kludge here is your in ability to get your head round real world problems and techniques - get out of the class room and wake up! Show quote "--CELKO--" <jcelko***@earthlink.net> wrote in message news:1137470750.548127.166350@g49g2000cwa.googlegroups.com... > Think about this. What would call such a procedure? The Britney > Spears, automobiles, squid or anything in the Entire Universe > procedure? > >>> I have a ton of stored procedures that do the same basic thing, just on >>> different tables. << > > So what? Have you ever had a basic Software Engineering course in > College? If not, you are not going to learn the basics on a Newsgroup. > > > If you really do not care about Software Engineering, software quaity, > etc. you can kludge this with some crappy dynamic code. > > Read what Erland wrote as a source for possible problems in the schema. >  |
|||||||||||||||||||||||
Other interesting topics