Jump to content

How to return a string from empty table


Jack McKalling
 Share

Recommended Posts

Hey you there! :mellow: Is it possible to return a string with a SELECT statement, when the table in question is empty?Like with this PHP-like conditional structure:

IF (COUNT(MyTable.UniqueID) = 0){	SELECT 'table is empty!';}ELSE{	SELECT * FROM MyTable ORDER BY UniqueID;}

This is no valid code, but just to show what I would like to have :)Is this possible, in one SQL query? If so, how would it be?Thanks in advance :)PS: I am using this for printing the contents of a table, from which I directly want to return a string when the table is empty, instead of leaving an empty placeholder on the page. It could easily be done with code on the page, but that is not possible for my own reasons. So this is why I would like to do it with SQL on before hand if possible.

Link to comment
Share on other sites

In SQL Server, you can do something like this in a query:

DECLARE @TableCount intSELECT @TableCount = COUNT(UniqueID) FROM MyTableIF @TableCount = 0BEGIN	SELECT 'table is empty!'ENDELSEBEGIN	SELECT * FROM MyTable ORDER BY UniqueIDEND

Link to comment
Share on other sites

That is not fully possible either in my situation, I forgot to tell.The resulting SQL should be only one SELECT statement, it cannot be a script.Actually, my example is misleading; it consists of a conditional structure, which is precisely not possible. This is more accurate:

SELECT IF(   COUNT(UniqueID)=0,   'empty',   (SELECT * FROM MyTable ORDER BY UniqueID)) AS ReturnDataFROM MyTable

But this doesnt work obviously :)If the COUNT is zero, only the string should return, else, the tablecontent should rerturn.In reality this IF always results in false, and this embedding query is not valid SQL :)

Link to comment
Share on other sites

Okay, now I have a modification about my previous question.What I actually wanted was to select just the content of a table and show column names above it, or nothing at all if it is empty.I print the column names above the content without using SQL, so when the table was empty, the column names would show up with no data below them. So I tried and asked how to return an empty table string in that case, to avoid the column names to be left allone. However, what about including the column names in the SQL statement? If I could get that to work properly, I won't be needing such empty table string because if the table was empty, just nothing will be returned (and no column names either for that matter)I must be using this instead, and it works!!

SELECT 'DisplayColumn1' AS tbl_col1, 'DisplayColumn2' AS tbl_col2 FROM MyTableUNION(SELECT tbl_col1, tbl_col2 FROM MyTable ORDER BY tbl_col1)

It will show the following if the table has 1 row with values "Test" and "Testing":

DisplayColumn1  DisplayColumn2Test			Testing

And this when empty:

 

All I wanted is to leave off the column names when the returned content was empty after all.Now, my question remaining is, actually, I answered it already... :)

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...