Use
sp_executesql[
^], passing the parameters as parameters.
Also, don't pass dates as strings. Use one of
the Date and Time types[
^].
ALTER PROC [dbo].[spGetanswer]
(
@a VARCHAR(10) = NULL,
@b NVARCHAR(200) = NULL,
@c VARCHAR(15) = NULL,
@d NVARCHAR(MAX)= NULL,
@StartDate datetime = NULL,
@EndDate datetime = NULL,
@col1 bit = 0,
@col2 bit = 0,
@col3 bit = 0,
@col4 bit = 0,
@col5 bit = 0
)
AS
BEGIN
DECLARE @statement nvarchar(max), @params nvarchar(max);
DECLARE @SqlSelect nvarchar(max), @SqlFrom nvarchar(max), @SqlWhere nvarchar(max), @SqlGroup nvarchar(max), @SqlOrder nvarchar(max);
SET NOCOUNT ON;
SET @SqlSelect = N'SELECT Sum(IsNull(SALES, 0)) As Sales, IsNull(Customer, '''') As Customer';
SET @SqlFrom = N' FROM dbo.VW_getanswer ';
SET @SqlGroup = N' GROUP BY Customer ';
SET @SqlOrder = N' ORDER BY Sales DESC, Customer ';
SET @SqlWhere = N'';
If @StartDate Is Not Null And @EndDate Is Not Null
BEGIN
SET @SqlWhere = @SqlWhere + N'And Convert(datetime, [MONTH]) Between @StartDate And @EndDate ';
END
Else If @StartDate Is Not Null
BEGIN
SET @SqlWhere = @SqlWhere + N'And Convert(datetime, [MONTH]) >= @StartDate ';
END
Else If @EndDate Is Not Null
BEGIN
SET @SqlWhere = @SqlWhere + N'And Convert(datetime, [MONTH]) <= @EndDate ';
END;
If @Company Is Not Null
BEGIN
SET @SqlWhere = @SqlWhere + N'And Company = @Comany ';
END;
If @a Is Not Null
BEGIN
SET @SqlWhere = @SqlWhere + N'And a In (SELECT Item FROM dbo.SplitString(@a, '','')) ';
END;
If @b Is Not Null
BEGIN
SET @SqlWhere = @SqlWhere + N'And b In (SELECT Item FROM dbo.SplitString(@b, '','')) ';
END;
If @c Is Not Null
BEGIN
SET @SqlWhere = @SqlWhere + N'And c In (SELECT Item FROM dbo.SplitString(@c, '','')) ';
END;
If @d Is Not Null
BEGIN
SET @SqlWhere = @SqlWhere + N'And d In (SELECT Item FROM dbo.SplitString(@d, '','')) ';
END;
If @SqlWhere != N''
BEGIN
SET @SqlWhere = STUFF(@SqlWhere, 1, 3, N' WHERE');
END;
If @col1 = 1
BEGIN
SET @SqlSelect = @SqlSelect + N', col1';
SET @SqlGroup = @SqlGroup + N', col1';
SET @SqlOrder = @SqlOrder + N', col1';
END;
If @col2 = 1
BEGIN
SET @SqlSelect = @SqlSelect + N', col2';
SET @SqlGroup = @SqlGroup + N', col2';
SET @SqlOrder = @SqlOrder + N', col2';
END;
If @col3 = 1
BEGIN
SET @SqlSelect = @SqlSelect + N', col3';
SET @SqlGroup = @SqlGroup + N', col3';
SET @SqlOrder = @SqlOrder + N', col3';
END;
If @col4 = 1
BEGIN
SET @SqlSelect = @SqlSelect + N', col4';
SET @SqlGroup = @SqlGroup + N', col4';
SET @SqlOrder = @SqlOrder + N', col4';
END;
If @col5 = 1
BEGIN
SET @SqlSelect = @SqlSelect + N', col5';
SET @SqlGroup = @SqlGroup + N', col5';
SET @SqlOrder = @SqlOrder + N', col5';
END;
SET @statement = @SqlSelect + @SqlFrom + @SqlWhere + @SqlGroup + @SqlOrder;
SET @params = N'@a VARCHAR(10), @b NVARCHAR(200), @c VARCHAR(15), @d NVARCHAR(MAX), @StartDate datetime, @EndDate datetime';
EXEC sp_executesql @statement, @params, @a = @a, @b = @b, @c = @c, @d = @d, @StartDate = @StartDate, @EndDate = @EndDate;
END;