why I need a dynamic query for “column value from a query”
up vote
0
down vote
favorite
For example this returns a value from a query, which I will then use as a column name.
@A=Select top 1 productid from productlist order by timestamp desc
then I would like this "productid" A to be used in the other table
Select @A from customerlist
then the result is @A value instead of field value in customerlist.
When I use dynamic query, I can get right result.
Why?
(I know I can use join but because this productlist table is dynamic, so let's assume it is a sub query)
sql
edited Nov 12 at 4:10
Homam
192
asked Nov 12 at 1:31
NewPy
705
add a comment |
up vote
0
down vote
favorite
For example this returns a value from a query, which I will then use as a column name.
@A=Select top 1 productid from productlist order by timestamp desc
then I would like this "productid" A to be used in the other table
Select @A from customerlist
then the result is @A value instead of field value in customerlist.
When I use dynamic query, I can get right result.
Why?
(I know I can use join but because this productlist table is dynamic, so let's assume it is a sub query)
sql
edited Nov 12 at 4:10
Homam
192
asked Nov 12 at 1:31
NewPy
705
(1) You have a problem with your data model if you have columns that are really data fields. (2) This requires dynamic SQL, so you need to specify the database you are using.
– Gordon Linoff
Nov 12 at 2:00
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
For example this returns a value from a query, which I will then use as a column name.
@A=Select top 1 productid from productlist order by timestamp desc
then I would like this "productid" A to be used in the other table
Select @A from customerlist
then the result is @A value instead of field value in customerlist.
When I use dynamic query, I can get right result.
Why?
(I know I can use join but because this productlist table is dynamic, so let's assume it is a sub query)
sql
edited Nov 12 at 4:10
Homam
192
asked Nov 12 at 1:31
NewPy
705
For example this returns a value from a query, which I will then use as a column name.
@A=Select top 1 productid from productlist order by timestamp desc
then I would like this "productid" A to be used in the other table
Select @A from customerlist
then the result is @A value instead of field value in customerlist.
When I use dynamic query, I can get right result.
Why?
(I know I can use join but because this productlist table is dynamic, so let's assume it is a sub query)
sql
sql
edited Nov 12 at 4:10
Homam
192
asked Nov 12 at 1:31
NewPy
705
edited Nov 12 at 4:10
Homam
192
asked Nov 12 at 1:31
NewPy
705
edited Nov 12 at 4:10
Homam
192
edited Nov 12 at 4:10
Homam
192
edited Nov 12 at 4:10
Homam
192
192
asked Nov 12 at 1:31
NewPy
705
asked Nov 12 at 1:31
NewPy
705
asked Nov 12 at 1:31
NewPy
705
705
(1) You have a problem with your data model if you have columns that are really data fields. (2) This requires dynamic SQL, so you need to specify the database you are using.
– Gordon Linoff
Nov 12 at 2:00
add a comment |
(1) You have a problem with your data model if you have columns that are really data fields. (2) This requires dynamic SQL, so you need to specify the database you are using.
– Gordon Linoff
Nov 12 at 2:00
(1) You have a problem with your data model if you have columns that are really data fields. (2) This requires dynamic SQL, so you need to specify the database you are using.
– Gordon Linoff
Nov 12 at 2:00
(1) You have a problem with your data model if you have columns that are really data fields. (2) This requires dynamic SQL, so you need to specify the database you are using.
– Gordon Linoff
Nov 12 at 2:00
add a comment |
1 Answer
1
active
oldest
votes
up vote
1
down vote
accepted
You need "dynamic SQL" because SQL will NOT allow you to use a parameter as a column name or a table name. You can only use parameters for data values such as in a where clause where column1 = @val
set @A = 'çolumn1'
Select @A from customerlist -- this fails because it is not allowed
Dynamic SQL is a "hack" to get around those restrictions as the SQL statement is placed into a string along with any value held by parameters.
set @A = 'çolumn1'
set @SQL = 'Select ' + @A + ' from customerlist;'
execute @SQL -- this works, the SQL statement is valid with no parameters as column names
The string formed as @SQL is a complete sql statement without needing any parameters as column names.
Note: the syntax I used here is incomplete and is based on MS SQL Server, different databases will use a different, but similar, syntax.
answered Nov 12 at 5:08
Used_By_Already
22.2k21838
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53254951%2fwhy-i-need-a-dynamic-query-for-column-value-from-a-query%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
1
down vote
accepted
You need "dynamic SQL" because SQL will NOT allow you to use a parameter as a column name or a table name. You can only use parameters for data values such as in a where clause where column1 = @val
set @A = 'çolumn1'
Select @A from customerlist -- this fails because it is not allowed
Dynamic SQL is a "hack" to get around those restrictions as the SQL statement is placed into a string along with any value held by parameters.
set @A = 'çolumn1'
set @SQL = 'Select ' + @A + ' from customerlist;'
execute @SQL -- this works, the SQL statement is valid with no parameters as column names
The string formed as @SQL is a complete sql statement without needing any parameters as column names.
Note: the syntax I used here is incomplete and is based on MS SQL Server, different databases will use a different, but similar, syntax.
answered Nov 12 at 5:08
Used_By_Already
22.2k21838
add a comment |
up vote
1
down vote
accepted
You need "dynamic SQL" because SQL will NOT allow you to use a parameter as a column name or a table name. You can only use parameters for data values such as in a where clause where column1 = @val
set @A = 'çolumn1'
Select @A from customerlist -- this fails because it is not allowed
Dynamic SQL is a "hack" to get around those restrictions as the SQL statement is placed into a string along with any value held by parameters.
set @A = 'çolumn1'
set @SQL = 'Select ' + @A + ' from customerlist;'
execute @SQL -- this works, the SQL statement is valid with no parameters as column names
The string formed as @SQL is a complete sql statement without needing any parameters as column names.
Note: the syntax I used here is incomplete and is based on MS SQL Server, different databases will use a different, but similar, syntax.
answered Nov 12 at 5:08
Used_By_Already
22.2k21838
add a comment |
up vote
1
down vote
accepted
up vote
1
down vote
accepted
You need "dynamic SQL" because SQL will NOT allow you to use a parameter as a column name or a table name. You can only use parameters for data values such as in a where clause where column1 = @val
set @A = 'çolumn1'
Select @A from customerlist -- this fails because it is not allowed
Dynamic SQL is a "hack" to get around those restrictions as the SQL statement is placed into a string along with any value held by parameters.
set @A = 'çolumn1'
set @SQL = 'Select ' + @A + ' from customerlist;'
execute @SQL -- this works, the SQL statement is valid with no parameters as column names
The string formed as @SQL is a complete sql statement without needing any parameters as column names.
Note: the syntax I used here is incomplete and is based on MS SQL Server, different databases will use a different, but similar, syntax.
answered Nov 12 at 5:08
Used_By_Already
22.2k21838
You need "dynamic SQL" because SQL will NOT allow you to use a parameter as a column name or a table name. You can only use parameters for data values such as in a where clause where column1 = @val
set @A = 'çolumn1'
Select @A from customerlist -- this fails because it is not allowed
Dynamic SQL is a "hack" to get around those restrictions as the SQL statement is placed into a string along with any value held by parameters.
set @A = 'çolumn1'
set @SQL = 'Select ' + @A + ' from customerlist;'
execute @SQL -- this works, the SQL statement is valid with no parameters as column names
The string formed as @SQL is a complete sql statement without needing any parameters as column names.
Note: the syntax I used here is incomplete and is based on MS SQL Server, different databases will use a different, but similar, syntax.
answered Nov 12 at 5:08
Used_By_Already
22.2k21838
answered Nov 12 at 5:08
Used_By_Already
22.2k21838
answered Nov 12 at 5:08
Used_By_Already
22.2k21838
answered Nov 12 at 5:08
Used_By_Already
22.2k21838
22.2k21838
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53254951%2fwhy-i-need-a-dynamic-query-for-column-value-from-a-query%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
(1) You have a problem with your data model if you have columns that are really data fields. (2) This requires dynamic SQL, so you need to specify the database you are using.
– Gordon Linoff
Nov 12 at 2:00