Arnab Arnab - 1 month ago 8
SQL Question

SQL Server stored procedure compile error

Stored procedure is:

CREATE PROCEDURE CountUtily
@domain varchar(50),
@count int,
@totalCount int OUT
AS
BEGIN
SET NOCOUNT ON
SET @totalCount=0

IF (EXISTS (SELECT @totalCount = count
FROM FormFillerAuto2_DomainCount
WHERE domain = @domain))
BEGIN
SET @totalCount = @totalCount + @count

UPDATE FormFillerAuto2_DomainCount
SET count = @totalCount
WHERE domain = @domain
END
ELSE
BEGIN
INSERT INTO FormFillerAuto2_DomainCount (domain, count)
VALUES (@domain, @count)
END
END


Error:


Incorrect syntax near '='. Incorrect syntax near the keyword 'ELSE'.

Answer

See EXISTS

Specifies a subquery to test for the existence of rows. it Returns TRUE if a subquery contains any rows,it accept restricted SELECT statement, The INTO keyword is not allowed.

the problem here is you can't set the value inside the Exists.

Try

alter PROCEDURE CountUtily
    @domain varchar(50),
    @count int,
    @totalCount int OUT
AS BEGIN    
    SET NOCOUNT ON
    SET @totalCount=0;

    IF (EXISTS (SELECT [count] FROM FormFillerAuto2_DomainCount WHERE domain=@domain))
    begin
        SELECT @totalCount=[count] FROM FormFillerAuto2_DomainCount WHERE domain=@domain
        UPDATE FormFillerAuto2_DomainCount SET count=@totalCount WHERE domain=@domain
    end
    ELSE
    begin
        INSERT INTO FormFillerAuto2_DomainCount (domain, count) VALUES (@domain, @count)
    end
end
Comments