存储过程未返回任何数据

Question

这是的问题

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

当我尝试时

exec dbo.errors_master

它表示命令已成功完成，但没有显示任何查询结果。

这里出了什么问题，不确定，因为如果有语法错误，它应该向我显示或告诉我，但它并没有什么结果，也没有给我任何回报。

Answer 1

您将获得某些值的null，如果任何输入为null，+将使结果为null。请改用CONCAT。

此外，要与null进行比较，您需要IS NOT NULL而不是<> 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

还要注意使用正确的动态SQL参数化，以及使用PRINT进行调试。

@columnsPassed仍然对SQL注入开放，我建议您也解决这个问题。