SQL parameterized query, where you can also pass “all”?
Having a tough time finding any info on this out there, but is it possible to pass an "all" or "*" like value to a parameterized query's ?, so as to get all results:
AND st.SomeColumn = ?
where ? would be RETURN ALL MATCHES? So then you could in theory pass any possible individual matches or retrieve all without some string concatenation shenanigans (which I know is totally possible and is my plan B).
sql
sql-server query-parameters edited Nov 13 '18 at 17:16
Thilina Nakkawita
9331228
asked Nov 13 '18 at 17:11
Matt LarsonMatt Larson
348212
add a comment |
Having a tough time finding any info on this out there, but is it possible to pass an "all" or "*" like value to a parameterized query's ?, so as to get all results:
AND st.SomeColumn = ?
where ? would be RETURN ALL MATCHES? So then you could in theory pass any possible individual matches or retrieve all without some string concatenation shenanigans (which I know is totally possible and is my plan B).
sql
sql-server query-parameters edited Nov 13 '18 at 17:16
Thilina Nakkawita
9331228
asked Nov 13 '18 at 17:11
Matt LarsonMatt Larson
348212
are you using SQL or MYSQL?
– Thilina Nakkawita
Nov 13 '18 at 17:14
SQL Server actually.
– Matt Larson
Nov 13 '18 at 17:15
I think it would be helpful to see the query you want to write. From your description it is really hard to tell what you want.
– T Gray
Nov 13 '18 at 17:17
Instead of using?why don't you use named and strongly-typed parameters? Or (gasp) a stored procedure?
– Aaron Bertrand
Nov 13 '18 at 17:18
add a comment |
Having a tough time finding any info on this out there, but is it possible to pass an "all" or "*" like value to a parameterized query's ?, so as to get all results:
AND st.SomeColumn = ?
where ? would be RETURN ALL MATCHES? So then you could in theory pass any possible individual matches or retrieve all without some string concatenation shenanigans (which I know is totally possible and is my plan B).
sql
sql-server query-parameters edited Nov 13 '18 at 17:16
Thilina Nakkawita
9331228
asked Nov 13 '18 at 17:11
Matt LarsonMatt Larson
348212
Having a tough time finding any info on this out there, but is it possible to pass an "all" or "*" like value to a parameterized query's ?, so as to get all results:
AND st.SomeColumn = ?
where ? would be RETURN ALL MATCHES? So then you could in theory pass any possible individual matches or retrieve all without some string concatenation shenanigans (which I know is totally possible and is my plan B).
sql
sql-server query-parameters sql
sql-server query-parameters edited Nov 13 '18 at 17:16
Thilina Nakkawita
9331228
asked Nov 13 '18 at 17:11
Matt LarsonMatt Larson
348212
edited Nov 13 '18 at 17:16
Thilina Nakkawita
9331228
asked Nov 13 '18 at 17:11
Matt LarsonMatt Larson
348212
edited Nov 13 '18 at 17:16
Thilina Nakkawita
9331228
edited Nov 13 '18 at 17:16
Thilina Nakkawita
9331228
edited Nov 13 '18 at 17:16
Thilina Nakkawita
9331228
9331228
asked Nov 13 '18 at 17:11
Matt LarsonMatt Larson
348212
asked Nov 13 '18 at 17:11
Matt LarsonMatt Larson
348212
asked Nov 13 '18 at 17:11
Matt LarsonMatt Larson
348212
348212
are you using SQL or MYSQL?
– Thilina Nakkawita
Nov 13 '18 at 17:14
SQL Server actually.
– Matt Larson
Nov 13 '18 at 17:15
I think it would be helpful to see the query you want to write. From your description it is really hard to tell what you want.
– T Gray
Nov 13 '18 at 17:17
Instead of using?why don't you use named and strongly-typed parameters? Or (gasp) a stored procedure?
– Aaron Bertrand
Nov 13 '18 at 17:18
add a comment |
are you using SQL or MYSQL?
– Thilina Nakkawita
Nov 13 '18 at 17:14
SQL Server actually.
– Matt Larson
Nov 13 '18 at 17:15
I think it would be helpful to see the query you want to write. From your description it is really hard to tell what you want.
– T Gray
Nov 13 '18 at 17:17
Instead of using?why don't you use named and strongly-typed parameters? Or (gasp) a stored procedure?
– Aaron Bertrand
Nov 13 '18 at 17:18
are you using SQL or MYSQL?
– Thilina Nakkawita
Nov 13 '18 at 17:14
are you using SQL or MYSQL?
– Thilina Nakkawita
Nov 13 '18 at 17:14
SQL Server actually.
– Matt Larson
Nov 13 '18 at 17:15
SQL Server actually.
– Matt Larson
Nov 13 '18 at 17:15
I think it would be helpful to see the query you want to write. From your description it is really hard to tell what you want.
– T Gray
Nov 13 '18 at 17:17
I think it would be helpful to see the query you want to write. From your description it is really hard to tell what you want.
– T Gray
Nov 13 '18 at 17:17
Instead of using
? why don't you use named and strongly-typed parameters? Or (gasp) a stored procedure?– Aaron Bertrand
Nov 13 '18 at 17:18
Instead of using
? why don't you use named and strongly-typed parameters? Or (gasp) a stored procedure?– Aaron Bertrand
Nov 13 '18 at 17:18
add a comment |
1 Answer
1
active
oldest
votes
No, you need to build this into the query logic. One simple way is (if the columns contain no NULL values) is to pass in the parameter as NULL and do:
st.SomeColumn = COALESCE(?, st.SomeColumn)
Otherwise:
(st.SomeColumn = ? OR (? IS NULL))
This requires passing the parameter twice -- which is why named parameters are handy.
answered Nov 13 '18 at 17:14
Gordon LinoffGordon Linoff
768k35300402
Ah, that works. Thanks!
– Matt Larson
Nov 13 '18 at 17:15
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',
autoActivateHeartbeat: false,
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%2f53286275%2fsql-parameterized-query-where-you-can-also-pass-all%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
No, you need to build this into the query logic. One simple way is (if the columns contain no NULL values) is to pass in the parameter as NULL and do:
st.SomeColumn = COALESCE(?, st.SomeColumn)
Otherwise:
(st.SomeColumn = ? OR (? IS NULL))
This requires passing the parameter twice -- which is why named parameters are handy.
answered Nov 13 '18 at 17:14
Gordon LinoffGordon Linoff
768k35300402
Ah, that works. Thanks!
– Matt Larson
Nov 13 '18 at 17:15
add a comment |
No, you need to build this into the query logic. One simple way is (if the columns contain no NULL values) is to pass in the parameter as NULL and do:
st.SomeColumn = COALESCE(?, st.SomeColumn)
Otherwise:
(st.SomeColumn = ? OR (? IS NULL))
This requires passing the parameter twice -- which is why named parameters are handy.
answered Nov 13 '18 at 17:14
Gordon LinoffGordon Linoff
768k35300402
Ah, that works. Thanks!
– Matt Larson
Nov 13 '18 at 17:15
add a comment |
No, you need to build this into the query logic. One simple way is (if the columns contain no NULL values) is to pass in the parameter as NULL and do:
st.SomeColumn = COALESCE(?, st.SomeColumn)
Otherwise:
(st.SomeColumn = ? OR (? IS NULL))
This requires passing the parameter twice -- which is why named parameters are handy.
answered Nov 13 '18 at 17:14
Gordon LinoffGordon Linoff
768k35300402
No, you need to build this into the query logic. One simple way is (if the columns contain no NULL values) is to pass in the parameter as NULL and do:
st.SomeColumn = COALESCE(?, st.SomeColumn)
Otherwise:
(st.SomeColumn = ? OR (? IS NULL))
This requires passing the parameter twice -- which is why named parameters are handy.
answered Nov 13 '18 at 17:14
Gordon LinoffGordon Linoff
768k35300402
answered Nov 13 '18 at 17:14
Gordon LinoffGordon Linoff
768k35300402
answered Nov 13 '18 at 17:14
Gordon LinoffGordon Linoff
768k35300402
answered Nov 13 '18 at 17:14
Gordon LinoffGordon Linoff
768k35300402
768k35300402
Ah, that works. Thanks!
– Matt Larson
Nov 13 '18 at 17:15
add a comment |
Ah, that works. Thanks!
– Matt Larson
Nov 13 '18 at 17:15
Ah, that works. Thanks!
– Matt Larson
Nov 13 '18 at 17:15
Ah, that works. Thanks!
– Matt Larson
Nov 13 '18 at 17:15
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.
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%2f53286275%2fsql-parameterized-query-where-you-can-also-pass-all%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
are you using SQL or MYSQL?
– Thilina Nakkawita
Nov 13 '18 at 17:14
SQL Server actually.
– Matt Larson
Nov 13 '18 at 17:15
I think it would be helpful to see the query you want to write. From your description it is really hard to tell what you want.
– T Gray
Nov 13 '18 at 17:17
Instead of using
?why don't you use named and strongly-typed parameters? Or (gasp) a stored procedure?– Aaron Bertrand
Nov 13 '18 at 17:18