With issue.

[vbbit]

I implement a stored procedure like this:

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.

[jmcilhinney]

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

[vbbit]

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?

[kaliman79912]

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

[jmcilhinney]

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

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