SQL Server - How to create IF EXISTS…SELECT AND SELECT
3
I am attempting to create an IF/ELSE check on my code in order to ensure that the parameters @BookTitle and @GenreName are records within the the tables BookGenre and Genre before attempting to delete them. I attempted to write this line of code, but received an error,
An expression of type non-Boolean type specified where condition is expected.
IF EXISTS (SELECT Id FROM Library.Book WHERE Title = @BookTitle AND
SELECT Id FROM Library.Genre WHERE Name = @GenreName)
ELSE
I need some direction on how to create an IF EXISTS statement expression that includes two separate select statements as part of the IF check.
Here is the entire Store Procedure below (Without the IF statement):
PROCEDURE [Library].[DeleteDateFromBookGenre]
-- Parameters to select which record delete
@BookTitle nvarchar(200),
@GenreName nvarchar(50)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
IF EXISTS (SELECT Id FROM Library.Book WHERE Title = @BookTitle)
IF EXISTS (SELECT Id FROM Library.Book WHERE Title = @BookTitle)
-- Deletes the specified and selected data from the Genre and Book table.
DELETE FROM Library.BookGenre
WHERE BookId IN (
SELECT Id
FROM Library.Book
WHERE Title = @BookTitle
) AND
GenreId IN (
SELECT Id
FROM Library.Genre
WHERE Name = @GenreName
)
END
sql
sql-server tsql dml edited Nov 12 at 5:50
Zohar Peled
52.3k73273
asked Nov 12 at 1:52
Ethan Hart
234
add a comment |
3
I am attempting to create an IF/ELSE check on my code in order to ensure that the parameters @BookTitle and @GenreName are records within the the tables BookGenre and Genre before attempting to delete them. I attempted to write this line of code, but received an error,
An expression of type non-Boolean type specified where condition is expected.
IF EXISTS (SELECT Id FROM Library.Book WHERE Title = @BookTitle AND
SELECT Id FROM Library.Genre WHERE Name = @GenreName)
ELSE
I need some direction on how to create an IF EXISTS statement expression that includes two separate select statements as part of the IF check.
Here is the entire Store Procedure below (Without the IF statement):
PROCEDURE [Library].[DeleteDateFromBookGenre]
-- Parameters to select which record delete
@BookTitle nvarchar(200),
@GenreName nvarchar(50)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
IF EXISTS (SELECT Id FROM Library.Book WHERE Title = @BookTitle)
IF EXISTS (SELECT Id FROM Library.Book WHERE Title = @BookTitle)
-- Deletes the specified and selected data from the Genre and Book table.
DELETE FROM Library.BookGenre
WHERE BookId IN (
SELECT Id
FROM Library.Book
WHERE Title = @BookTitle
) AND
GenreId IN (
SELECT Id
FROM Library.Genre
WHERE Name = @GenreName
)
END
sql
sql-server tsql dml edited Nov 12 at 5:50
Zohar Peled
52.3k73273
asked Nov 12 at 1:52
Ethan Hart
234
It isn't clear why you are trying to perform the check since you don't do anything exciting, e.g.throwan exception, if the rows aren't found.. If you simply go ahead and execute thedeletestatement then you can check@@RowCount. If it is0then no rows were deleted indicating either the title, genre or both were not found. If it's1then the book/genre pair was unique. If> 1you may be surprised.
– HABO
Nov 12 at 2:10
I apologize for not being more clear. I wanted to purely know more about IF statements and was attempting to test it on this procedure, even though I don't do anything "exciting" in it. I couldn't find the answer I was looking for, but was able to get my answer down below.
– Ethan Hart
Nov 12 at 2:36
add a comment |
3
3
3
I am attempting to create an IF/ELSE check on my code in order to ensure that the parameters @BookTitle and @GenreName are records within the the tables BookGenre and Genre before attempting to delete them. I attempted to write this line of code, but received an error,
An expression of type non-Boolean type specified where condition is expected.
IF EXISTS (SELECT Id FROM Library.Book WHERE Title = @BookTitle AND
SELECT Id FROM Library.Genre WHERE Name = @GenreName)
ELSE
I need some direction on how to create an IF EXISTS statement expression that includes two separate select statements as part of the IF check.
Here is the entire Store Procedure below (Without the IF statement):
PROCEDURE [Library].[DeleteDateFromBookGenre]
-- Parameters to select which record delete
@BookTitle nvarchar(200),
@GenreName nvarchar(50)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
IF EXISTS (SELECT Id FROM Library.Book WHERE Title = @BookTitle)
IF EXISTS (SELECT Id FROM Library.Book WHERE Title = @BookTitle)
-- Deletes the specified and selected data from the Genre and Book table.
DELETE FROM Library.BookGenre
WHERE BookId IN (
SELECT Id
FROM Library.Book
WHERE Title = @BookTitle
) AND
GenreId IN (
SELECT Id
FROM Library.Genre
WHERE Name = @GenreName
)
END
sql
sql-server tsql dml edited Nov 12 at 5:50
Zohar Peled
52.3k73273
asked Nov 12 at 1:52
Ethan Hart
234
I am attempting to create an IF/ELSE check on my code in order to ensure that the parameters @BookTitle and @GenreName are records within the the tables BookGenre and Genre before attempting to delete them. I attempted to write this line of code, but received an error,
An expression of type non-Boolean type specified where condition is expected.
IF EXISTS (SELECT Id FROM Library.Book WHERE Title = @BookTitle AND
SELECT Id FROM Library.Genre WHERE Name = @GenreName)
ELSE
I need some direction on how to create an IF EXISTS statement expression that includes two separate select statements as part of the IF check.
Here is the entire Store Procedure below (Without the IF statement):
PROCEDURE [Library].[DeleteDateFromBookGenre]
-- Parameters to select which record delete
@BookTitle nvarchar(200),
@GenreName nvarchar(50)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
IF EXISTS (SELECT Id FROM Library.Book WHERE Title = @BookTitle)
IF EXISTS (SELECT Id FROM Library.Book WHERE Title = @BookTitle)
-- Deletes the specified and selected data from the Genre and Book table.
DELETE FROM Library.BookGenre
WHERE BookId IN (
SELECT Id
FROM Library.Book
WHERE Title = @BookTitle
) AND
GenreId IN (
SELECT Id
FROM Library.Genre
WHERE Name = @GenreName
)
END
sql
sql-server tsql dml sql
sql-server tsql dml edited Nov 12 at 5:50
Zohar Peled
52.3k73273
asked Nov 12 at 1:52
Ethan Hart
234
edited Nov 12 at 5:50
Zohar Peled
52.3k73273
asked Nov 12 at 1:52
Ethan Hart
234
edited Nov 12 at 5:50
Zohar Peled
52.3k73273
edited Nov 12 at 5:50
Zohar Peled
52.3k73273
edited Nov 12 at 5:50
Zohar Peled
52.3k73273
52.3k73273
asked Nov 12 at 1:52
Ethan Hart
234
asked Nov 12 at 1:52
Ethan Hart
234
asked Nov 12 at 1:52
Ethan Hart
234
234
It isn't clear why you are trying to perform the check since you don't do anything exciting, e.g.throwan exception, if the rows aren't found.. If you simply go ahead and execute thedeletestatement then you can check@@RowCount. If it is0then no rows were deleted indicating either the title, genre or both were not found. If it's1then the book/genre pair was unique. If> 1you may be surprised.
– HABO
Nov 12 at 2:10
I apologize for not being more clear. I wanted to purely know more about IF statements and was attempting to test it on this procedure, even though I don't do anything "exciting" in it. I couldn't find the answer I was looking for, but was able to get my answer down below.
– Ethan Hart
Nov 12 at 2:36
add a comment |
It isn't clear why you are trying to perform the check since you don't do anything exciting, e.g.throwan exception, if the rows aren't found.. If you simply go ahead and execute thedeletestatement then you can check@@RowCount. If it is0then no rows were deleted indicating either the title, genre or both were not found. If it's1then the book/genre pair was unique. If> 1you may be surprised.
– HABO
Nov 12 at 2:10
I apologize for not being more clear. I wanted to purely know more about IF statements and was attempting to test it on this procedure, even though I don't do anything "exciting" in it. I couldn't find the answer I was looking for, but was able to get my answer down below.
– Ethan Hart
Nov 12 at 2:36
It isn't clear why you are trying to perform the check since you don't do anything exciting, e.g.
throw an exception, if the rows aren't found.. If you simply go ahead and execute the delete statement then you can check @@RowCount. If it is 0 then no rows were deleted indicating either the title, genre or both were not found. If it's 1 then the book/genre pair was unique. If > 1 you may be surprised.– HABO
Nov 12 at 2:10
It isn't clear why you are trying to perform the check since you don't do anything exciting, e.g.
throw an exception, if the rows aren't found.. If you simply go ahead and execute the delete statement then you can check @@RowCount. If it is 0 then no rows were deleted indicating either the title, genre or both were not found. If it's 1 then the book/genre pair was unique. If > 1 you may be surprised.– HABO
Nov 12 at 2:10
I apologize for not being more clear. I wanted to purely know more about IF statements and was attempting to test it on this procedure, even though I don't do anything "exciting" in it. I couldn't find the answer I was looking for, but was able to get my answer down below.
– Ethan Hart
Nov 12 at 2:36
I apologize for not being more clear. I wanted to purely know more about IF statements and was attempting to test it on this procedure, even though I don't do anything "exciting" in it. I couldn't find the answer I was looking for, but was able to get my answer down below.
– Ethan Hart
Nov 12 at 2:36
add a comment |
1 Answer
1
active
oldest
votes
4
Your parentheses are off; you need two EXISTS conditions:
IF EXISTS (SELECT Id FROM Library.Book WHERE Title = @BookTitle
) AND
EXISTS (SELECT Id FROM Library.Genre WHERE Name = @GenreName
)
BEGIN
. . .
END
answered Nov 12 at 1:59
Gordon Linoff
755k35290398
Thanks for the quick response. This was exactly was I was looking for.
– Ethan Hart
Nov 12 at 2:33
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
});
}
});
draft saved
draft discarded
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%2f53255078%2fsql-server-how-to-create-if-exists-select-and-select%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
4
Your parentheses are off; you need two EXISTS conditions:
IF EXISTS (SELECT Id FROM Library.Book WHERE Title = @BookTitle
) AND
EXISTS (SELECT Id FROM Library.Genre WHERE Name = @GenreName
)
BEGIN
. . .
END
answered Nov 12 at 1:59
Gordon Linoff
755k35290398
Thanks for the quick response. This was exactly was I was looking for.
– Ethan Hart
Nov 12 at 2:33
add a comment |
4
Your parentheses are off; you need two EXISTS conditions:
IF EXISTS (SELECT Id FROM Library.Book WHERE Title = @BookTitle
) AND
EXISTS (SELECT Id FROM Library.Genre WHERE Name = @GenreName
)
BEGIN
. . .
END
answered Nov 12 at 1:59
Gordon Linoff
755k35290398
Thanks for the quick response. This was exactly was I was looking for.
– Ethan Hart
Nov 12 at 2:33
add a comment |
4
4
4
Your parentheses are off; you need two EXISTS conditions:
IF EXISTS (SELECT Id FROM Library.Book WHERE Title = @BookTitle
) AND
EXISTS (SELECT Id FROM Library.Genre WHERE Name = @GenreName
)
BEGIN
. . .
END
answered Nov 12 at 1:59
Gordon Linoff
755k35290398
Your parentheses are off; you need two EXISTS conditions:
IF EXISTS (SELECT Id FROM Library.Book WHERE Title = @BookTitle
) AND
EXISTS (SELECT Id FROM Library.Genre WHERE Name = @GenreName
)
BEGIN
. . .
END
answered Nov 12 at 1:59
Gordon Linoff
755k35290398
edited Nov 12 at 2:01
answered Nov 12 at 1:59
Gordon Linoff
755k35290398
answered Nov 12 at 1:59
Gordon Linoff
755k35290398
answered Nov 12 at 1:59
Gordon Linoff
755k35290398
755k35290398
Thanks for the quick response. This was exactly was I was looking for.
– Ethan Hart
Nov 12 at 2:33
add a comment |
Thanks for the quick response. This was exactly was I was looking for.
– Ethan Hart
Nov 12 at 2:33
Thanks for the quick response. This was exactly was I was looking for.
– Ethan Hart
Nov 12 at 2:33
Thanks for the quick response. This was exactly was I was looking for.
– Ethan Hart
Nov 12 at 2:33
add a comment |
draft saved
draft discarded
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.
draft saved
draft discarded
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%2f53255078%2fsql-server-how-to-create-if-exists-select-and-select%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
It isn't clear why you are trying to perform the check since you don't do anything exciting, e.g.
throwan exception, if the rows aren't found.. If you simply go ahead and execute thedeletestatement then you can check@@RowCount. If it is0then no rows were deleted indicating either the title, genre or both were not found. If it's1then the book/genre pair was unique. If> 1you may be surprised.– HABO
Nov 12 at 2:10
I apologize for not being more clear. I wanted to purely know more about IF statements and was attempting to test it on this procedure, even though I don't do anything "exciting" in it. I couldn't find the answer I was looking for, but was able to get my answer down below.
– Ethan Hart
Nov 12 at 2:36