How to check if a SELECT has any rows matching the WHERE conditions in SQL Server-CodePudding

The question is:

Create a procedure GetOrderDetails that takes OrderID as input parameter and returns all the records for that OrderID. If no records are found in Order Details table, then it should print the line: “The OrderID XXXX does not exits”, where XXX should be the OrderID entered by user and the procedure should RETURN the value 1.

My solution:

ALTER PROC GetOrderDetails
@InputOrderID int
AS
BEGIN
    SELECT CASE
        WHEN SalesOrderID != @InputOrderID 
            THEN 'The OrderID '   CONVERT(VARCHAR,@InputOrderID)   ' does not exists'
            ELSE SalesOrderID
        END
        FROM SalesLT.SalesOrderDetail
END

EXEC GetOrderDetails 71770

CodePudding user response：

SQL Server specific way:

In SQL Server, you can select the data and when check how many records were selected using @@ROWCOUNT. You can also use the PRINT statement to print an error message (which will not be part of the resultset).

ALTER PROCEDURE GetOrderDetails
        @InputOrderID int
AS
BEGIN
    DECLARE @SelectedRowCount INT;

    SELECT
        * /* Don't use * in production code, list all, but only the required columns! This is just for demonstration purposes */
    FROM
        SalesLT.SalesOrderDetail
    WHERE
        SalesOrderID = @InputOrderID
    ;

    SELECT @SelectedRowCount = @@ROWCOUNT;

    IF (@SelectedRowCount = 0)
    BEGIN
        PRINT 'Not found';
        RETURN 1; /*Return with an error code*/
    END;

    RETURN 0; /* Return with no error */
END;

EXEC GetOrderDetails 71770;

In other products, you may need to check if there is at least one record fulfils your where condition, this can be done by selecting the COUNT of the result in a separate query or by checking it with EXISTS or with more complex and exotic ways, like selecting into a temp table then counting the records in said temp table, then deciding to output the data or printing a message.

CodePudding user response：

Just use ROWCOUNT on the immediately following line to check if there were any records.

CREATE OR ALTER PROC GetOrderDetails
  @InputOrderID int
AS

SET NOCOUNT ON;

SELECT od.SalesOrderID
FROM SalesLT.SalesOrderDetail od
WHERE od.SalesOrderID == @InputOrderID; 

IF (@@ROWCOUNT = 0)
BEGIN
    SELECT CONCAT('The OrderID ', @InputOrderID, ' does not exists');
    RETURN 1;
END;

GO

EXEC GetOrderDetails 71770