Does sql parser support MS SQL IF ELSE statements?

[bruceus]

Hi,
first of all big thanks for the existence of SQLPage.

Basically, the initial target what I wanted to achieve was to display Datagrid component and fill it with SQL data as description. I wanted to have there 4 columns each having its title and the description of each would be a number as a result of SQL query.
This worked for me perfectly until I found out that one SQL query returns zero rows. In this case the whole column of datagrid disappeared - this is OK.
I wanted to overcome it by SELECTing of a dummy value '0' when no data (zero rows) is returned.
This can be easily achieved in MS SQL by running IF / ELSE statements, so I tried this:

select 
    'table' as component;
IF EXISTS (
	SELECT * FROM TalkgroupManipulationLocks
)
BEGIN
SELECT '1'
END
ELSE 
BEGIN
SELECT '0'
END;

The table component is there for debugging purposes only.

I got back sqlparser error:
sql parser error: Expected an SQL statement, found: IF at Line: 39, Column 1

Did my test prove that SQL parser searches only for SELECT statements and does not support IF / ELSE statements or am I doing something wrong?

[lovasoa]

Hello! Indeed, top-level each statements are not supported. But in your case it sounds like you can use a simple standard case expression, or even a coalesce statement with a subquery:

• https://learn.microsoft.com/en-us/sql/t-sql/language-elements/case-transact-sql?view=sql-server-ver16
• https://learn.microsoft.com/en-us/sql/t-sql/language-elements/coalesce-transact-sql?view=sql-server-ver16

[lovasoa]

If you post your query that returned zero rows here, we may be able to help you change it to return a single NULL value instead when there is no data.

Here is an example of datagrid with real data: https://replit.com/@pimaj62145/SQLPage-datagrid-with-fallback#index.sql

[bruceus]

Thank you for the answer. I don't think it makes sense to add a complete datagrid part from our production system here but I can at least start with following simple query (of one datagrid column) which returns no results.

select 
    'datagrid'               as component;
SELECT 'Total CCE ports used' as title,
		SUM(ISNULL(CAST(InUse as int),0)) as description
FROM TetraPorts 
WHERE InUse = 1 
GROUP BY InUse;

To add more sense to it, the tables are dynamically filled in with data from front-end system which I don't have access to. There can be data in one moment and no data in other moment when I refresh SQLPage.
I think it can be just more simple to read for user when there's 0 as datagrid description when no rows are returned instead of nothing.
Unfortunately, CASE and COALESCE work with existing table rows with NULL values.

[lovasoa]

It looks like what you need is just

SELECT 'Total CCE ports used' as title, COUNT(*) as description
FROM TetraPorts 
WHERE InUse = 1;

right ?

https://sqlfiddle.com/sql-server/online-compiler?id=9a919ccf-3309-4112-b828-389c4705b0e8

[bruceus]

I'm marking this as the right answer, assuming when you fix the issue in SQLPage it will show the result - which is 0.
Thank you for your help!

[bruceus]

I tested your code and it works. It shows me dash when no rows are returned which is OK.
Just out of curiosity, how would you change the code if I want 0 to be returned instead of dash in case of no rows?
SQLPage with dash returned:

It looks like what you need is just

SELECT 'Total CCE ports used' as title, COUNT(*) as description
FROM TetraPorts 
WHERE InUse = 1;

right ?

https://sqlfiddle.com/sql-server/online-compiler?id=9a919ccf-3309-4112-b828-389c4705b0e8

[lovasoa]

Hello ! This is a bug in SQLPage. I'll fix it, but in the meantime, you can work around it by converting the number to a string if you'd like to see a literal zero instead of a dash:

SELECT 'Total CCE ports used' as title, CAST(COUNT(*) AS VARCHAR) as description
FROM TetraPorts 
WHERE InUse = 1;

https://sqlfiddle.com/sql-server/online-compiler?id=af08bcc2-9cd0-424c-beee-269f0468f3aa