August 1, 2012 at 4:45 am
DECLARE @Sample Varchar(Max)
DECLARE @EXISTS INT
SELECT @EXISTS=0
--SET @Sample='mymail
SET @Sample='mymail@mymail.com'
;WITH SAMPLEDATA (UserName,Email) AS
(Select U.UserName,M.Email From Membership as M
INNER JOIN Users as U
ON M.UserId=U.UserId and M.IsApproved=1
Where U.UserName=@Sample OR M.Email=@Sample
)
IF EXISTS(SELECT UserName,Email From SAMPLEDATA Where UserName=@Sample OR Email=@Sample)
BEGIN
SELECT @EXISTS=1
END
SELECT @EXISTS AS UserName_OR_Email_EXISTS
Error:
Msg 156, Level 15, State 1, Line 11
Incorrect syntax near the keyword 'IF'
What was the mistake i did would you please rectify it...
Thanks In Advance
August 1, 2012 at 5:37 am
I believe "if exists" has to be used in the where clause and that's causing the issue.
Remove everything after your CTE and try this:
declare @intCount int
select @intCount = (SELECT count(*) From SAMPLEDATA Where UserName=@Sample OR Email=@Sample)
IF @intCount > 0
BEGIN
set @EXISTS=1
END
SELECT @EXISTS AS UserName_OR_Email_EXISTS
Mark
August 1, 2012 at 9:02 am
Mark Eckeard (8/1/2012)
I believe "if exists" has to be used in the where clause and that's causing the issue.
You can't use IF EXISTS in a where clause. The IF statement is used as a control flow, not part of a condition.
Remove everything after your CTE and try this:
declare @intCount int
select @intCount = (SELECT count(*) From SAMPLEDATA Where UserName=@Sample OR Email=@Sample)
IF @intCount > 0
BEGIN
set @EXISTS=1
END
SELECT @EXISTS AS UserName_OR_Email_EXISTS
Mark
This will certainly work but the IF EXISTS would actually be the preferred method here. I agree that the cte is not needed and just adds unnecessary complexity.
Something like this should work.
IF EXISTS
(
Select * --using * inside an exists is the ONLY time it is ok
From Membership as M
INNER JOIN Users as U ON M.UserId = U.UserId
and M.IsApproved = 1
Where U.UserName = @Sample
OR M.Email = @Sample
)
SET @EXISTS = 1
Here is another way of doing this using case. It is shorter but not quite as readily apparent what is going on.
SELECT Case count(*) when 0 then 0 else 1 end
From Membership as M
INNER JOIN Users as U ON M.UserId = U.UserId
and M.IsApproved = 1
Where U.UserName = @Sample
OR M.Email = @Sample
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply