Stored procedure not returning any data

Question

Here is the issue

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
ALTER   PROCEDURE [dbo].[errors_master](
    @search varchar(100) = null,
    @orderColumn varchar(50) = null,
    @dir int = null,
    @columnsPassed varchar(max) = null,
    @param varchar(100) = null,
    @group int = 0
)              
AS              
BEGIN

    DECLARE @whereClause NVARCHAR(MAX);
    SET @whereClause = 
    CASE
        WHEN @search != null  
            THEN 'and template like ''%' + @search + '%''  or message like ''%' + @search + '%'
        ELSE NULL
    END

    DECLARE @whereClause2 NVARCHAR(MAX);
    SET @whereClause2 = 
    CASE
        WHEN @param != null  
            THEN 'and errorid =' + @param
        ELSE NULL
    END

    DECLARE @orderByStatement NVARCHAR(MAX);
    IF (isNull(@orderColumn, '') <> '')
    BEGIN
        SET @orderByStatement = ' order by ' + @orderColumn + ' '
    END
    ELSE
    BEGIN
        SET @orderByStatement = ' order by max(etime) '
    END

    DECLARE @orderType VARCHAR(5);
    IF (isNull(@dir, 1) <> 0)
    BEGIN
        SET @orderType = ' asc '
    END
    ELSE
    BEGIN
        SET @orderType = ' desc '
    END

    DECLARE @groupBy VARCHAR(50);
    SET @groupBy = 
        CASE 
            WHEN @group = 1 THEN ' group by errorid,template,message, type, line' 
        ELSE NULL 
        END

    DECLARE @Sql NVARCHAR(MAX);
    SET @Sql =  N' select ' + @columnsPassed + ' from dbo.errors where 1=1 ' + @whereClause + ' ' + @whereClause2 + ' '
                + @groupBy + ' ' + @orderByStatement + ' ' + @orderType

    EXECUTE sp_executesql @Sql

END

GRANT EXECUTE ON [dbo].[errors_master] TO [x] AS [dbo]
GRANT EXECUTE ON [dbo].[errors_master] TO [xa] AS [dbo]
GO

When I try to

exec dbo.errors_master

Its says command completed successfully but does not show any query results .

What is wrong here, not sure because if there is a syntactical error, it should show me or tell me but it just does not nothing and returns me nothing.

Answer 1

You are getting nulls for some of the values, and + will null out the result if any of the inputs are null. Instead use CONCAT.

Also, to compare to null you need IS NOT NULL not <> NULL

CREATE OR ALTER PROCEDURE [dbo].[errors_master](
    @search varchar(100) = null,
    @orderColumn sysname = null,
    @dir int = null,
    @columnsPassed nvarchar(max) = null,
    @param varchar(100) = null,
    @group int = 0
)              
AS              
BEGIN

    DECLARE @whereClause NVARCHAR(MAX) = 
        CASE
        WHEN @search IS NOT NULL
            THEN ' and (template like ''%'' + @search + ''%''  or message like ''%'' + @search + ''%'')'
        END;

    DECLARE @whereClause2 NVARCHAR(MAX) =
        CASE
        WHEN @param is not null  
            THEN ' and errorid = @param'
        END;

    DECLARE @groupBy VARCHAR(50) = 
        CASE 
        WHEN @group = 1
            THEN ' group by errorid, template, message, type, line' 
            ELSE ''
        END;

    DECLARE @orderByStatement NVARCHAR(MAX) =
        ' order by ' + 
        CASE WHEN ISNULL(@orderColumn, '') <> '')
            THEN QUOTENAME(@orderColumn) + ' '
            ELSE 'max(etime) '
        END;

    DECLARE @orderType VARCHAR(5) =
        CASE WHEN ISNULL(@dir, 1) <> 0
            THEN ' asc'
            ELSE ' desc'
        END;

    DECLARE @Sql NVARCHAR(MAX) =
      CONCAT(
        N'
select 
  ',
        @columnsPassed,
        '
from dbo.errors
where 1=1 ',
        @whereClause,
        @whereClause2,
        @groupBy,
        @orderByStatement,
        @orderType
      );

    PRINT @Sql; -- your friend

    EXECUTE sp_executesql
        @Sql
        N'@search varchar(100), @param varchar(100)',
        @search = @search,
        @param = @param;

END

Note also the use of proper parameterization of the dynamic SQL, as well as using PRINT for debugging.

@columnsPassed is still wide open to SQL injection, I suggest you fix that also.