SELECT * FROM @Table not using Dynamic SQL (if exists)
in [Programming SQL]
|
From: LUIS on 4 Jul 2008 18:23 I have asked in some question before about use a select by choosing table you want, this is like: DECLARE @Table VARCHAR(20) SELECT @Table = "tblSome" SELECT * FROM @Table But NOT using Dynamic SQL (if exists) Please, answer me if you have some way or theory to do so. Beforehand, thank you very much. -- Luis Garcia IT Consultant
From: Dan Guzman on 4 Jul 2008 19:13 >I have asked in some question before about use a select by choosing table >you > want, this is like: > > DECLARE @Table VARCHAR(20) > SELECT @Table = "tblSome" > SELECT * FROM @Table > > But NOT using Dynamic SQL (if exists) > > Please, answer me if you have some way or theory to do so. You can use a SQLCMD variable that will be replaced with the desired value at run time. For example: SQLCMD -S ServerName -d DatabaseName -E -Q"SELECT * FROM $(Table)" -v Table=dbo.MyTable Barring SQLCMD variables, I believe you will need to resort to dynamic SQL. -- Hope this helps. Dan Guzman SQL Server MVP http://weblogs.sqlteam.com/dang/ "LUIS" <LUIS(a)discussions.microsoft.com> wrote in message news:654C8309-7AB8-400F-BAC3-693C85B8017D(a)microsoft.com... >I have asked in some question before about use a select by choosing table >you > want, this is like: > > DECLARE @Table VARCHAR(20) > SELECT @Table = "tblSome" > SELECT * FROM @Table > > But NOT using Dynamic SQL (if exists) > > Please, answer me if you have some way or theory to do so. > > Beforehand, thank you very much. > > -- > Luis Garcia > IT Consultant
From: Andrew J. Kelly on 4 Jul 2008 19:17 If you asked this before you most likely got the same answer you will get again. It simply can't be done with the current versions of SQL Server, period, end of story. You have to either use dynamic sql or build the statement dynamically from the client and submit it that way. -- Andrew J. Kelly SQL MVP Solid Quality Mentors "LUIS" <LUIS(a)discussions.microsoft.com> wrote in message news:654C8309-7AB8-400F-BAC3-693C85B8017D(a)microsoft.com... >I have asked in some question before about use a select by choosing table >you > want, this is like: > > DECLARE @Table VARCHAR(20) > SELECT @Table = "tblSome" > SELECT * FROM @Table > > But NOT using Dynamic SQL (if exists) > > Please, answer me if you have some way or theory to do so. > > Beforehand, thank you very much. > > -- > Luis Garcia > IT Consultant
From: Erland Sommarskog on 5 Jul 2008 14:57 LUIS (LUIS(a)discussions.microsoft.com) writes: > I have asked in some question before about use a select by choosing > table you want, this is like: > > DECLARE @Table VARCHAR(20) > SELECT @Table = "tblSome" > SELECT * FROM @Table > > But NOT using Dynamic SQL (if exists) If @Table = 'thattable' SELECT * FROM thattable ELSE IF @Table = 'thistable' SLEECT * FROM thistable ELSE IF ... Another approach is to create a view over all possible tables: CREATE VIEW luis_view AS SELECT tbl = 'thistable', * FROM thistable UNION ALL SELECT tbl = 'thattable', * FROM thattable ... Then you do SELECT col1, col2, col3, ... FROM luis_view WHERE tbl = @Table Both solutions presumes that you have a finite number of tables, and the latter solutions also presumes that you return the same columns from all. But if you don't do that, you desire to have a dynamic table name is really suspect. -- Erland Sommarskog, SQL Server MVP, esquel(a)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
From: Hugo Kornelis on 5 Jul 2008 16:37
On Fri, 4 Jul 2008 15:23:00 -0700, LUIS wrote: >I have asked in some question before about use a select by choosing table you >want, this is like: > >DECLARE @Table VARCHAR(20) >SELECT @Table = "tblSome" >SELECT * FROM @Table > >But NOT using Dynamic SQL (if exists) > >Please, answer me if you have some way or theory to do so. > >Beforehand, thank you very much. Hi LUIS, If the actual table is one of a limited list, you can use IF @Table = 'tblSome' SELECT * FROM tblSome IF @Table = 'tblOther' SELECT * FROM tblOther (...) Oh, and you do of course know that SELECT * should not be used in production code, right? -- Hugo Kornelis, SQL Server MVP My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis |