Member 10914736 Ответов: 1

Как передать параметры для динамической функции и динамически выполнить хранимую процедуру с помощью EXEC()

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.

Что я уже пробовал:

Привет

Если я выберу столбец col1 (флажок), то он должен отображаться в отчете вместе со столбцами в списке выбора. Как мудрый для col2, col3 и т. д

вот мой код:

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)

SQL-Server-2008R2 Источник

1 Ответов

Рейтинг:
7

Richard Deeming

Воспользуйся процедуры sp_executesql[^], передавая параметры в качестве параметров.

Кроме того, не передавайте даты в виде строк. Используйте один из типы даты и времени[^].

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;

Member 10914736

Спасибо за решение, Ричард.
Это прекрасно работает,но небольшая проблема с пунктами предложения where.
При выполнении сохраненного proc, если я передаю NULL для любого одного значения в предложении where, весь результат показывает NULL. Значит, я прошел компанию, месяц, b и d, но не c, что означает, что ни один из результатов не отображается.

а во-вторых, я не могу ввести 2 значения в одну переменную в предложении where, даже если я использовал функцию 'dbo.SplitString', который разделяется на ','.
Он допускает одно значение для одного элемента предложения where.
Есть какие-нибудь предложения, пожалуйста?

Richard Deeming

Вам либо нужно изменить Where п. к ответственности за Null значения или построить его на основе переданных параметров.

И в данный момент Вы передаете пустую строку в SplitString функция как разделитель. Если ваш параметр представляет собой список, разделенный запятыми, то вам нужно передать запятую в качестве разделителя.

Richard Deeming

Я обновил свой ответ примером.

Member 10914736

Большое спасибо, Ричард.
Теперь это работает.

Большое спасибо.

Member 10914736

Например, если я не хочу включать все столбцы в предложение Group by, а только несколько, я получаю следующую ошибку;
col2 недопустим в списке выбора, поскольку он не содержится ни в агрегатной функции, ни в предложении GROUP BY.

не могли бы вы предложить что-нибудь?

Richard Deeming

Как говорится в сообщении об ошибке, при использовании GROUP BY, каждая колонка в SELECT список либо должен быть в GROUP BY список, ну или должен содержаться в статистическая функция[^].

Так, например, это недопустимо:
SELECT A, B FROM table GROUP BY A

Но это:
SELECT A, Max(B) As BiggestB FROM table GROUP BY A

И это тоже:
SELECT A, B FROM table GROUP BY A, B

Member 10914736

Спасибо, Ричард.

Как передать параметры для динамической функции и динамически выполнить хранимую процедуру с помощью EXEC()

1 Ответов

Richard Deeming

Member 10914736

Richard Deeming

Richard Deeming

Member 10914736

Member 10914736

Richard Deeming

Member 10914736

Категории

Недавние ответы

Изменение источника данных (базы данных) программно

Проблема при попытке вставить данные: ошибка при преобразовании типа данных nvarchar в числовой.

Может ли кто-нибудь, пожалуйста, сказать мне, как я практичен в жизненном цикле страницы .NET

C# get и set ? любая помощь ценится!

Как получить список ip-адресов пользователей, подключенных к моему Wi-Fi