Automatically return an Empty Row if condition fails

Question

Automatically return an Empty Row if condition fails

SqlUser-529296

SSCarpal Tunnel

Points: 4072
More actions
November 27, 2008 at 5:35 am

#128857

Hi All,
Please find the below code. Here the "IF EXISTS" Condition is not satisfied then the Last "SELECT" Statement will work.
Fine.
Here my Question is, i don't want the Last "SELECT" Statement(SELECT @fldTableName). If the Condition is not
Satisfied it should automatically return an Empty Row. Is it possible here?
Pl advice.
DECLARE @Table TABLE
(
fldIDINT,
fldNamesVARCHAR(16)
)
DECLARE @fldTableName NVARCHAR(16)
SET @fldTableName = ''
INSERT INTO @Table SELECT 1,'First' UNION ALL SELECT 2,'Second'
IF EXISTS(SELECT 1 FROM @Table WHERE fldID = 2)
BEGIN
SELECT @fldTableName = COALESCE(@fldTableName + ',','') + fldNames
FROM @Table WHERE fldID = 1
SELECT @fldTableName = SUBSTRING(@fldTableName,2,LEN(@fldTableName))
SELECT @fldTableName
RETURN
END
SELECT @fldTableName
---

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Vladan SSC-Insane Points: 22072 More actions · Answer 1

What do you mean by "empty row"?

Your procedure - as far as I can see - returns just one value. If the BEGIN-END block is skipped, returned value is '' (empty string). What is the difference between returning empty string and "empty row"? Is something missing from the posted example, or do you need to return NULL or is it something else?

Maybe you can just delete the last SELECT? I don't really see why it is there at all... The block ends with RETURN, which ends the procedure or whatever it is - so... when do you plan to reach this last select, if it should not be executed when block is skipped, and it can't be executed otherwise?

You can better observe what is happenning if you add something to that last select, so you can tell whether the returned value(s) comes from this select, or from the one before RETURN. Try this:

DECLARE @Table TABLE

(

fldID INT,

fldNames VARCHAR(16)

)

DECLARE @fldTableName NVARCHAR(16)

SET @fldTableName = ''

INSERT INTO @Table SELECT 1,'First' UNION ALL SELECT 2,'Second'

IF EXISTS(SELECT 1 FROM @Table WHERE fldID = 2)

BEGIN

SELECT @fldTableName = COALESCE(@fldTableName + ',','') + fldNames

FROM @Table WHERE fldID = 1

SELECT @fldTableName = SUBSTRING(@fldTableName,2,LEN(@fldTableName))

SELECT @fldTableName, 'in_the_block'

RETURN

END

SELECT @fldTableName, 'last_row'

Now if you change the query so that there is just 1 row in @table, empty string and 'last_row' will be in the result. If @table has 2 rows, result is 'First' and 'in_the_block' - i.e. it comes from inside the BEGIN-END block.