SELECT * FROM (GET TABLENAME HERE)? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SELECT * FROM (GET TABLENAME HERE)?

Author

Topic

svdelle
Starting Member

3 Posts

Posted - 2008-01-10 : 14:32:29

I don't know the name of the table I'll query on before hand, but have to get it from another table dynamically.

Can this be done (well it can't, but just to illustrate the idea and get a solution):

SELECT * FROM
(SELECT DBTable
FROM PageTemplates
WHERE ID IN
(SELECT PageTemplateID
FROM Pages
WHERE ID = 1))
WHERE ID = 1

Marioi
Posting Yak Master

132 Posts

Posted - 2008-01-10 : 14:40:03

Concat your query statement and execute it with EXEC

quote:
Originally posted by svdelle

I don't know the name of the table I'll query on before hand, but have to get it from another table dynamically.

Can this be done (well it can't, but just to illustrate the idea and get a solution):

SELECT * FROM
(SELECT DBTable
FROM PageTemplates
WHERE ID IN
(SELECT PageTemplateID
FROM Pages
WHERE ID = 1))
WHERE ID = 1

svdelle
Starting Member

3 Posts

Posted - 2008-01-10 : 15:07:37

OK, black talk.

I'll explain a little clearer this time.

I want to pull all columns from a table of which I do not know the name up front. I'll get the name from another table, where it's put during design time.

SELECT * FROM (another select statement here to pull the table name from another table) WHERE ID = 1

1 is only used as an example, as I'll get this ID from a QueryString.

So never mind the lacking CONCAT, it was just to make it clearer to read, but if you insist:
SELECT * FROM (SELECT DBTable FROM PageTemplates WHERE ID IN (SELECT PageTemplateID FROM Pages WHERE ID = 1)) WHERE ID = 1

Marioi
Posting Yak Master

132 Posts

Posted - 2008-01-10 : 15:31:52

If each query that feeds the main query returns just one value (ensure that it does with TOP 1 as in SELECT TOP 1 DBTable ...), SET that value into a varchar or nvarchar variable, then concatenate the main query text with the variable you just set in a yet another varchar variable, and EXEC that variable:

EXEC @strQuery

It is recommended to use sp_executesql (for ex: EXEC executesql @strQuery), but I think you have to use the letter N before each literal string as in:

SET @strQuery = N'SELECT * FROM (' + @strSubquery + N') ...'

It may be easier to first get it going with plain EXEC.

quote:
Originally posted by svdelle

OK, black talk.

I'll explain a little clearer this time.

I want to pull all columns from a table of which I do not know the name up front. I'll get the name from another table, where it's put during design time.

SELECT * FROM (another select statement here to pull the table name from another table) WHERE ID = 1

1 is only used as an example, as I'll get this ID from a QueryString.

So never mind the lacking CONCAT, it was just to make it clearer to read, but if you insist:
SELECT * FROM (SELECT DBTable FROM PageTemplates WHERE ID IN (SELECT PageTemplateID FROM Pages WHERE ID = 1)) WHERE ID = 1

tm
Posting Yak Master

160 Posts

Posted - 2008-01-10 : 15:37:37

The answer was given by Marioi.

You need to use EXEC/EXECUTE function.
Concatenate your statement into a variable and then use EXEC/EXECUTE.

Refer to SQL Books On Line ~ EXEC / EXECUTE.

OR
search for "Dynamic SQL".

Marioi
Posting Yak Master

132 Posts

Posted - 2008-01-10 : 15:41:20

Be careful to put two single quotes wherever you would have one single quote in a regular query.

quote:
Originally posted by Marioi

If each query that feeds the main query returns just one value (ensure that it does with TOP 1 as in SELECT TOP 1 DBTable ...), SET that value into a varchar or nvarchar variable, then concatenate the main query text with the variable you just set in a yet another varchar variable, and EXEC that variable:

EXEC @strQuery

It is recommended to use sp_executesql (for ex: EXEC executesql @strQuery), but I think you have to use the letter N before each literal string as in:

SET @strQuery = N'SELECT * FROM (' + @strSubquery + N') ...'

It may be easier to first get it going with plain EXEC.

quote:
Originally posted by svdelle

OK, black talk.

I'll explain a little clearer this time.

I want to pull all columns from a table of which I do not know the name up front. I'll get the name from another table, where it's put during design time.

SELECT * FROM (another select statement here to pull the table name from another table) WHERE ID = 1

1 is only used as an example, as I'll get this ID from a QueryString.

So never mind the lacking CONCAT, it was just to make it clearer to read, but if you insist:
SELECT * FROM (SELECT DBTable FROM PageTemplates WHERE ID IN (SELECT PageTemplateID FROM Pages WHERE ID = 1)) WHERE ID = 1

svdelle
Starting Member

3 Posts

Posted - 2008-01-10 : 16:06:53

This EXEC and CONCAT talk is all new to me ... I'm not an SQL man. As you probably guessed by now.

This is for use in an asp.net application . I've tried to set the query for the DBTable in one SqlDataSource and use the result as a ControlParameter in another SqlDataSource like this:

SELECT * FROM @DBTable WHERE PageID = @PageID

Unfortunately without any luck as I can't seem to get a ControlParameter (@DBTable) from a data source but only from some kind of ... well ... control (GridView, DetailsView etc.).

Marioi
Posting Yak Master

132 Posts

Posted - 2008-01-10 : 16:32:48

If it's easier for you, although this is terrible from the performance and resource standpoint, you could execute a query to get the table and the Where condition from .net first and load results into .net variables, then concat a statement as string and execute it as dynamic SQL code.

quote:
Originally posted by svdelle

This EXEC and CONCAT talk is all new to me ... I'm not an SQL man. As you probably guessed by now.

This is for use in an asp.net application . I've tried to set the query for the DBTable in one SqlDataSource and use the result as a ControlParameter in another SqlDataSource like this:

SELECT * FROM @DBTable WHERE PageID = @PageID

Unfortunately without any luck as I can't seem to get a ControlParameter (@DBTable) from a data source but only from some kind of ... well ... control (GridView, DetailsView etc.).

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-01-10 : 16:42:00

quote:
Originally posted by svdelle

I don't know the name of the table I'll query on before hand, but have to get it from another table dynamically.

Can this be done (well it can't, but just to illustrate the idea and get a solution):

SELECT * FROM
(SELECT DBTable
FROM PageTemplates
WHERE ID IN
(SELECT PageTemplateID
FROM Pages
WHERE ID = 1))
WHERE ID = 1

The answer is yes but you should know that you may be opening your application to a very serious security risk by coding it this way. The following article can help explain what Im talking about and contains a section on the exact question you are asking.

http://www.sommarskog.se/dynamic_sql.html

jholovacs
Posting Yak Master

163 Posts

Posted - 2008-01-10 : 16:43:23

quote:

I think you have to use the letter N before each literal string as in:

SET @strQuery = N'SELECT * FROM (' + @strSubquery + N') ...'

if you DECLARE your var as NVARCHAR, you can forego the "N" as the text will be implicitly converted. I wouldn't recommend looping implicit conversions, but in this sort of case it could save time and question marks.

___________________________
Geek At Large

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-01-10 : 21:22:00

Are you sure you need to do this? Unless you are writing a database tool of some kind, it's a terrible design to need to do this. Sounds like you need one table with a tag column on it (currently @DBTable).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources