[SOLVED] How not to return select statements in SQL Server stored procedure if there is raiseerror below?

How not to return select statements in SQL Server stored procedure if there is raiseerror below?

Is it possible in SQL Server some way not to return select statements in stored procedure if there is raiseerror or throw below?

I have select statement, and if the row count is bigger than some number, I raiseerror. In my application I use ado.net, and so error is in second result ( I must do NextResult(), so before it I must process my first result). Is it possible to know if there is second result or not? Or is it possible not to return select ? Or how either can I solve this problem: not to process select if its rows count is bigger than some particular number?

Solution

You can't check how many rows a statement will return without it being run, no. If you have a (simple) statement like SELECT * FROM MyTable1 MT1 JOIN MyTable2 MT2 on MT1.ID = MT2.fID; until that statement is run, SQL Server won't know how many rows will be returned.

If you, however, don't want to return that dataset if it has more the x rows, you could use a IF statement:

IF (SELECT COUNT(*) FROM MyTable1 MT1 JOIN MyTable2 MT2 on MT1.ID = MT2.fID) <= 1024 BEGIN

    SELECT *
    FROM MyTable MT1
         JOIN MyTable MT2 ON MT1.ID = MT2.fID;

    SELECT ... --Your other SELECT statement
END ELSE BEGIN
    RAISERROR(...
END

If there are more than 1024 rows, then no dataset will be returned. This option will (probably) be less costly than a process like inserting all your data into a temporary table, especially if you're working with a large dataset. If you're inserting everything into a temporary table, then every row needs to be read and written to tempdb, and then read back when you return the data at the end of your statement(s); that's not a cheap operation with a lot of data.