With issue.

I implement a stored procedure like this:

Code:

---

ELSE IF (@Command = 'GETUSERS_NEWS') --newsletter
                ;With Emails As (SELECT u.Email, ROW_NUMBER() OVER (ORDER BY u.Email) AS RowNumber
                        FROM Users u LEFT JOIN Notifications n ON u.Username = n.Username WHERE
                        u.Suspend IS NULL AND u.IsDelete IS NULL AND (n.NewsLetter = @Alert OR n.NewsLetter IS NULL))
                        SELECT * FROM Emails WHERE RowNumber BETWEEN @RowStart AND @RowEnd

---

BUt it keeps saying that there's an error syntax ';' the semicolon in front of With. If I ran it in the sql query, it's fine.

Semicolons are used in a SqlCommand but not in SQL Server Management Studio. Just start a new statement on a new line.

Can you provide an example? If i put that with statement outside of the IF statement, it works, but if not, then it throws that error - how do I resolve that?

Code:

---

ELSE IF @Command = 'GETUSERS_NEWS'
                With Emails As (SELECT u.Email, ROW_NUMBER() OVER (ORDER BY u.Email) AS RowNumber
                        FROM Users u LEFT JOIN Notifications n ON u.Username = n.Username WHERE
                        u.Suspend IS NULL AND u.IsDelete IS NULL AND (n.NewsLetter = @Alert OR n.NewsLetter IS NULL))
                        SELECT * FROM Emails WHERE RowNumber BETWEEN @RowStart AND @RowEnd

---

I think that what you're trying to do is associate a single block of code with the ELSE:

Code:

---

ELSE IF @Command = 'GETUSERS_NEWS'
BEGIN
    -- blah, blah, blah
END

---