Clash Royale CLAN TAG#URR8PPP

Stuck on SQL Server procedure code

I'm getting an error from this code

DECLARE @existingEmail AS INT

SET NOCOUNT ON;

@existingEmail = COUNT(*) AS total FROM table WHERE email = @email;

INSERT INTO table2 (email)
VALUES (CASE WHEN @existingEmail IS 1 THEN 'Yes' ELSE 'No' END);


That is the error I am getting

Msg 102, Level 15, State 1, Procedure sp_counts, Line 21 [Batch Start Line 7]

Incorrect syntax near '@existingEmail'.

insert

update

@existingEmail = COUNT(*) AS total FROM table WHERE email = @email;

SELECT

3 Answers
3

Using COUNT to check existence may not be best idea from performance point of view:

COUNT

INSERT INTO table2 (email)
SELECT CASE WHEN EXISTS (SELECT 1 FROM table WHERE email = @email) THEN 'Yes'
ELSE 'No' END


Instead of this:

@existingEmail = COUNT(*) as total from table where email = @email;


You need to do this:

SELECT @existingEmail = COUNT(*) as total from table where email = @email;


or this:

SET @existingEmail = (SELECT COUNT(*) as total from table where email = @email);


Msg 156, Level 15, State 1, Procedure sp_counts, Line 21 [Batch Start Line 7] Incorrect syntax near the keyword 'from'. Msg 102, Level 15, State 1, Procedure sp_counts, Line 25 [Batch Start Line 7] Incorrect syntax near '1'.

FROM

1

= 1

IS 1

On the line where you are trying to set your variable @existingEmail you are missing the key word set. if you update that line to the following this should resolve that error.

@existingEmail

set

set @existingEmail = (SELECT COUNT(*) from table where email = @email);


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.