Click here to Skip to main content
14,494,494 members
Rate this:
Please Sign up or sign in to vote.
See more:
Hi there,

I have created a Function called split string which splits the delimited characters into row by row. This was done because I have created a stored procedure where it will not supports IN operator. And this function in the stored procedure is working fine.

Now, I am planning to use the same function in another stored proc, this time I  passed the parameters dynamically (keeping those where clause columns in the single quotes as shown below with EXEC(@parameter), but it is not working.

                                         MARKET IN (SELECT Item 
FROM dbo.SplitString( ' + @stringcolumn + ',' + ''''') 
here dbo.SplitString is a split function.

I tried to print my parameters, in that the where clause is showing NULL. I removed that expression from the where clause and ran, then it is working fine with no errors.

Please help me.


What I have tried:

Hi

If I select col1 column (check box) then this should be displayed in the report along with columns in the select list. Like wise for col2, col3, etc

here is my code:
ALTER PROC [dbo].[spGetanswer]
(
@a         VARCHAR(10) = NULL,  
@b         NVARCHAR(200) = NULL,
@b     VARCHAR(15) = NULL,
@d      NVARCHAR(MAX)= NULL,
@StartDate     VARCHAR(50) = NULL,
@EndDate       VARCHAR(50) = NULL,
@col1       BIT = 0,
@col2   BIT = 0,
@col3   BIT = 0,
@col4     BIT = 0,
@col5    BIT = 0

)

AS
SET NOCOUNT ON
SET DATEFORMAT MDY


DECLARE    @SQLSELECT        NVARCHAR(4000)
DECLARE    @SQLFROM          NVARCHAR(100)
DECLARE    @SQLWHERE         NVARCHAR(1000)
DECLARE    @SQLGROUP         NVARCHAR(3000)
DECLARE    @SQLORDER         NVARCHAR(3000)
DECLARE    @StringUnion      NVARCHAR(MAX)


SET @SQLSELECT = ' SELECT  SUM(IsNull(SALES,0)) , 
IsNull(CUSTOMER,'''') as Customer  
'
SET @SQLFROM =    ' FROM dbo.VW_getanswer '
SET @SQLWHERE=    ' WHERE 
                    
                     (CONVERT(datetime,[MONTH]) BETWEEN  '  +  '''' + @StartDate + '''' +  ' AND ' + '''' + @EndDate + '''' + ' )  AND
                      COMPANY =''' + @COMPANY + ''' 
                       'AND    b IN (SELECT Item 
FROM dbo.SplitString( ' + @b + ',' + ''''') 

AND

c IN (SELECT Item 
FROM dbo.SplitString( ' + @c + ',' + ''''') )            
 AND
                       
                                           d IN ( SELECT Item 
 FROM dbo.SplitString( ' + @d + ',' + ''''')) '


SET @SQLGROUP =    ' GROUP BY CUSTOMER '
SET @SQLORDER =    ' ORDER By SUM(IsNull(SALES,0)) DESC , CUSTOMER '


IF (@col1 = 1 )
BEGIN

SET @SQLSELECT =      @SQLSELECT + ' ,col1 '
SET @SQLGROUP = @SQLGROUP + ' ,col1'
SET @SQLORDER = @SQLORDER + ' ,col1 '
END
 
IF (@col2 = 1  )
BEGIN
SET @SQLSELECT =      @SQLSELECT + ' , col2 '
SET @SQLGROUP = @SQLGROUP + ' , col2 '
SET @SQLORDER = @SQLORDER + ' , col2 '
END

IF (@col3 = 1 )
BEGIN
SET @SQLSELECT =      @SQLSELECT + ' , col3 '
SET @SQLGROUP = @SQLGROUP + ' , col3 '
SET @SQLORDER = @SQLORDER + ' , col3 '
END

IF (@col4 = 1 )
BEGIN
SET @SQLSELECT =      @SQLSELECT + ' , col4 '
SET @SQLGROUP = @SQLGROUP + ' , col4 '
SET @SQLORDER = @SQLORDER + ' , col4 '
END

IF (@col5 = 1 )
BEGIN
SET @SQLSELECT =      @SQLSELECT + ' , col5 '
SET @SQLGROUP = @SQLGROUP + ' , col5 '
SET @SQLORDER = @SQLORDER + ' , col5 '
END
SET @StringUnion =    @SQLSELECT +   @SQLFROM +   @SQLWHERE +  @SQLGROUP +   @SQLORDER

EXECUTE (@StringUnion) 
Posted
Updated 10-Aug-16 3:08am
v2

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

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
        -- If we have a filter, replace the first "And" with "Where":
        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;
   
v2
Comments
Member 10914736 11-Aug-16 3:46am
   
Thanks for the solution Richard.
This works fine but Small problem with where clause items.
While executing the stored proc, if I pass NULL for any one value in the where clause, the entire result is Showing NULL. Means, I passed company,month, b and d, but not c, which implies none of the results are displayed.

and secondly, I cannot able to input 2 values in the one variable in the where clause even I used a function 'dbo.SplitString' which delimited by ','.
It is allowing one value for one where clause item.
Any suggestions please?
Richard Deeming 11-Aug-16 7:56am
   
You either need to change the Where clause to account for Null values, or build it up based on the parameters passed in.

And you're currently passing an empty string to the SplitString function as the separator. If your parameter is a comma-separated list, then you need to pass in a comma as the separator.
Richard Deeming 11-Aug-16 8:05am
   
I've updated my answer with an example.
Member 10914736 11-Aug-16 9:48am
   
Thank you very much Richard.
It's working now.

Thank you very much.
Member 10914736 16-Aug-16 6:03am
   
For Suppose, if I don't want to include all columns in the Group by clause but only few, I am getting the following error;
col2 is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

can you please suggest something.
Richard Deeming 16-Aug-16 7:50am
   
As the error message says, when you use GROUP BY, every column in the SELECT list either needs to be in the GROUP BY list as well, or needs to be contained in an aggregate function[^].

So, for example, this is not valid:
SELECT A, B FROM table GROUP BY A

But this is:
SELECT A, Max(B) As BiggestB FROM table GROUP BY A

And so is this:
SELECT A, B FROM table GROUP BY A, B
Member 10914736 16-Aug-16 9:55am
   
Thanks Richard.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100