SELECT * FROM @Table not using Dynamic SQL (if exists)
in [Programming SQL]
|
From: Hugo Kornelis on 5 Jul 2008 16:40 On Sat, 05 Jul 2008 22:37:05 +0200, Hugo Kornelis wrote: (the same as Erland) Sorry. I hadn't refreshed the group in a while, so I was not aware that Erland already wrote the same suggestion. -- Hugo Kornelis, SQL Server MVP My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
From: LUIS on 5 Jul 2008 18:45 Dan Thank you very much for answering, and link you send me. -- Luis Garcia IT Consultant "Dan Guzman" 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. > > 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: LUIS on 5 Jul 2008 18:48 Andrew Thank you very much. I would like to receive an answer from Mi Lambda(MVP) who told me he knows a way to do so besides Dynamic SQL, but probably it will be something like other colleages have send to me. Onece again, thank you very much. -- Luis Garcia IT Consultant "Andrew J. Kelly" wrote: > 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: LUIS on 5 Jul 2008 18:50 Erland: Thank you very much for answering. -- Luis Garcia IT Consultant "Erland Sommarskog" wrote: > 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: LUIS on 5 Jul 2008 18:51
Hugo: Thank you very much for answering. Good try, any way!!! -- Luis Garcia IT Consultant "Hugo Kornelis" wrote: > On Sat, 05 Jul 2008 22:37:05 +0200, Hugo Kornelis wrote: > > (the same as Erland) > > Sorry. I hadn't refreshed the group in a while, so I was not aware that > Erland already wrote the same suggestion. > > -- > Hugo Kornelis, SQL Server MVP > My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis > |