How do you check if a stored procedure returns records?

4 Answers. Use RETURN @@rowcount in the inner stored proc immediately after the SELECT. And call like this: EXEC @rtncount = dbo.

Can we use return in stored procedure?

You can use one or more RETURN statements in a stored procedure. The RETURN statement can be used anywhere after the declaration blocks within the SQL-procedure-body. To return multiple output values, parameters can be used instead. Parameter values must be set prior to the RETURN statement being executed.

Does stored procedure always return value?

Stored Procedures should always indicate failure with an error (generated with THROW/RAISERROR if necessary), and not rely on a return code to indicate the failure. Also you should avoid using the return code to return application data.

Which SQL function can be used to check if a SELECT statement returns nothing?

The IS NULL condition is used in SQL to test for a NULL value. It returns TRUE if a NULL value is found, otherwise it returns FALSE. It can be used in a SELECT, INSERT, UPDATE, or DELETE statement.

When does stored procedure return no rows, run a second query?

If stored procedure returns no rows, run a second query ? I have a stored procedure that returns a bunch of rows of data. However, in a couple situations, a “backup” query needs to run if the number of rows in the 1st query is 0.

How to check if a SQL SELECT statement returns no rows?

BEGIN PRINT ‘No matching row exists’ END If this is SQL Server, try @@ROWCOUNT. If you get 0, you got 0. 🙂 You can use @@ROWCOUNT. For e.g. SELECT QBalance FROM dbo.CustomerBalance WHERE (CustomerID = 1) AND (MarchentID = @MerchantId) –This will return no of rows returned by above statement. SELECT @@ROWCOUNT

How do you return data from a procedure?

Returning Data Using a Return Code. A procedure can return an integer value called a return code to indicate the execution status of a procedure. You specify the return code for a procedure using the RETURN statement.

When to run a second query with no rows?

The problem with this approach is sp will return 2 rowsets if @@rowcount is 0 – one empty rowset from 1st select and another one from 2nd select. You can do eventualy (if result of 1st select is not huge)