none
Help with SP

    Question

  • I have writen a SP to pass data for my web, I cannot think of how to connect the parameters with the select statements. I would appreciate if soneone help me.

    ALTER

    PROCEDURE [SVY].[SP_GET_SSV_SCORE_BY_OUTLETS]

    @START_DATE

    DATETIME,

    @END_DATE

    DATETIME,

    @OUTLET_CODE

    NVARCHAR(1000)=NULL,

    @PROVINCE_CODE

    NVARCHAR(1000)=NULL

    AS

    DECLARE

    @STATEMENT NVARCHAR(4000)

    DECLARE

    @PARAMETERS NVARCHAR(1000)

    DECLARE

    @STATEMENT1 NVARCHAR(4000)

    DECLARE

    @PARAMETERS1 NVARCHAR(1000)

    BEGIN

    SET @STATEMENT = ' SELECT * INTO #MYTEMP FROM '

    SET @STATEMENT = ' SELECT OUTLET_CODE, OUTLET_NAME, PROVINCE_NAME, PROVINCE_CODE, PHONE, AVG(LTCS) AS CS, AVG(RESULT) AS RESULT, '

    SET @STATEMENT = @STATEMENT + ' AVG([01.01.01]) AS [01.01.01], AVG([02.01.02b]) AS [02.01.02b], AVG([02.02.01b]) AS [02.02.01b], AVG([02.03.01b]) AS [02.03.01b], AVG([01.01.02]) AS [01.01.02] '

    SET @STATEMENT = @STATEMENT + ' FROM SVY.SQL_SSV_SUMMARY_SCORES_BY_OUTLETS '

    SET @STATEMENT = @STATEMENT + ' WHERE (START_DATE BETWEEN @START_DATE AND @END_DATE) '

    SET @STATEMENT = @STATEMENT + ' GROUP BY OUTLET_CODE, OUTLET_NAME, PROVINCE_NAME, PROVINCE_CODE, PHONE '

    SET @STATEMENT1 = ' SELECT OUTLET_CODE, OUTLET_NAME, PROVINCE_NAME, PROVINCE_CODE, PHONE, CS, RESULT, '

    SET @STATEMENT1 = @STATEMENT1 + ' [01.01.01], [02.01.02b], [02.02.01b], [02.03.01b], [01.01.02] '

    SET @STATEMENT1 = @STATEMENT1 + ' FROM #MYTEMP '

    -- SET @STATEMENT1 = @STATEMENT1 + ' WHERE ' HERE IS WHERE THE ERROR START AS I CANNOT THE WHERE STATEMENT WITH THE TWO PARAMETERS BELOW!!

    IF NOT @OUTLET_CODE IS NULL BEGIN

    SET @STATEMENT1 =@STATEMENT1 + ' AND ( #MYTEMP.OUTLET_CODE IN (' + @OUTLET_CODE + '))'

    END

    IF NOT @PROVINCE_CODE IS NULL BEGIN

    SET @STATEMENT1 =@STATEMENT1 + ' AND ( #MYTEMP.PROVINCE_CODE IN (' + @PROVINCE_CODE + '))'

    END

    SET @PARAMETERS = '@START_DATE DATETIME, @END_DATE DATETIME '

    EXECUTE SP_EXECUTESQL @STATEMENT, @PARAMETERS, @START_DATE = @START_DATE, @END_DATE = @END_DATE

    SET @PARAMETERS1 = '@START_DATE DATETIME, @END_DATE DATETIME, @OUTLET_CODE NVARCHAR(1000), @PROVINCE_CODE NVARCHAR(1000) '

    EXECUTE SP_EXECUTESQL @STATEMENT1, @PARAMETERS1, @START_DATE = @START_DATE, @END_DATE = @END_DATE, @OUTLET_CODE =@OUTLET_CODE, @PROVINCE_CODE = @PROVINCE_CODE

    END

    Wednesday, June 13, 2012 3:42 AM

Answers

  • Use a dummy first where clause such as '1=1' so you can then simply append the other clauses with an 'AND'

    SET @STATEMENT1 = @STATEMENT1 + ' WHERE 1=1 ' 
    IF NOT @OUTLET_CODE IS NULL BEGIN
    SET @STATEMENT1 =@STATEMENT1 + ' AND ( #MYTEMP.OUTLET_CODE IN (' + @OUTLET_CODE + '))'
    END
    IF NOT @PROVINCE_CODE IS NULL BEGIN
    SET @STATEMENT1 =@STATEMENT1 + ' AND ( #MYTEMP.PROVINCE_CODE IN (' + @PROVINCE_CODE + '))'
    END

    • Marked as answer by SSamnang Friday, June 15, 2012 10:21 AM
    Wednesday, June 13, 2012 7:28 AM

All replies

  • IF @OUTLET_CODE IS  NOT NULL 
    BEGIN
    SET @STATEMENT1 =@STATEMENT1 + 'WHERE #MYTEMP.OUTLET_CODE IN (' + @OUTLET_CODE  + ')'
    END
    IF @PROVINCE_CODE IS NOT NULL 
    BEGIN
    SET @STATEMENT1 =@STATEMENT1 + 'WHERE #MYTEMP.PROVINCE_CODE IN (' + @PROVINCE_CODE + ')'
    END

    You can also use single declare to declare variables.

    DECLARE @STATEMENT NVARCHAR(4000),@PARAMETERS NVARCHAR(1000),
    @STATEMENT1 NVARCHAR(4000),@PARAMETERS1 NVARCHAR(1000)


    ESHANI. Please click "Mark As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you


    • Edited by Eshani Rao Wednesday, June 13, 2012 4:06 AM
    Wednesday, June 13, 2012 4:03 AM
  • Hi,

    I think you need to modify your code little bit. You are already concatenating the @OUTLET_CODE & @PROVINCE_CODE to statement1, so you may need not pass them as parameters to the sp_executeSQL

    ALTER PROCEDURE [SVY].[SP_GET_SSV_SCORE_BY_OUTLETS] @START_DATE DATETIME, @END_DATE DATETIME, @OUTLET_CODE NVARCHAR(1000)=NULL, @PROVINCE_CODE NVARCHAR(1000)=NULL AS DECLARE @STATEMENT NVARCHAR(4000) DECLARE @PARAMETERS NVARCHAR(1000) DECLARE @STATEMENT1 NVARCHAR(4000) DECLARE @PARAMETERS1 NVARCHAR(1000) BEGIN SET @STATEMENT = ' SELECT * INTO #MYTEMP FROM ' SET @STATEMENT = ' SELECT OUTLET_CODE, OUTLET_NAME, PROVINCE_NAME, PROVINCE_CODE, PHONE, AVG(LTCS) AS CS, AVG(RESULT) AS RESULT, ' SET @STATEMENT = @STATEMENT + ' AVG([01.01.01]) AS [01.01.01], AVG([02.01.02b]) AS [02.01.02b], AVG([02.02.01b]) AS [02.02.01b], AVG([02.03.01b]) AS [02.03.01b], AVG([01.01.02]) AS [01.01.02] ' SET @STATEMENT = @STATEMENT + ' FROM SVY.SQL_SSV_SUMMARY_SCORES_BY_OUTLETS ' SET @STATEMENT = @STATEMENT + ' WHERE (START_DATE BETWEEN @START_DATE AND @END_DATE) ' SET @STATEMENT = @STATEMENT + ' GROUP BY OUTLET_CODE, OUTLET_NAME, PROVINCE_NAME, PROVINCE_CODE, PHONE ' SET @STATEMENT1 = ' SELECT OUTLET_CODE, OUTLET_NAME, PROVINCE_NAME, PROVINCE_CODE, PHONE, CS, RESULT, ' SET @STATEMENT1 = @STATEMENT1 + ' [01.01.01], [02.01.02b], [02.02.01b], [02.03.01b], [01.01.02] ' SET @STATEMENT1 = @STATEMENT1 + ' FROM #MYTEMP ' -- SET @STATEMENT1 = @STATEMENT1 + ' WHERE ' HERE IS WHERE THE ERROR START AS I CANNOT THE WHERE STATEMENT WITH THE TWO PARAMETERS BELOW!! IF NOT @OUTLET_CODE IS NULL BEGIN SET @STATEMENT1 =@STATEMENT1 + ' AND ( #MYTEMP.OUTLET_CODE IN (' + @OUTLET_CODE + '))' END IF NOT @PROVINCE_CODE IS NULL BEGIN SET @STATEMENT1 =@STATEMENT1 + ' AND ( #MYTEMP.PROVINCE_CODE IN (' + @PROVINCE_CODE + '))' END SET @PARAMETERS = '@START_DATE DATETIME, @END_DATE DATETIME ' EXECUTE SP_EXECUTESQL @STATEMENT, @PARAMETERS, @START_DATE = @START_DATE, @END_DATE = @END_DATE

    --SET @PARAMETERS1 = '@START_DATE DATETIME, @END_DATE DATETIME, @OUTLET_CODE NVARCHAR(1000), @PROVINCE_CODE NVARCHAR(1000) '

    SET @PARAMETERS1 = '@START_DATE DATETIME, @END_DATE DATETIME'
    --EXECUTE SP_EXECUTESQL @STATEMENT1, @PARAMETERS1, @START_DATE = @START_DATE, @END_DATE = @END_DATE,@OUTLET_CODE =@OUTLET_CODE, @PROVINCE_CODE = @PROVINCE_CODE
    EXECUTE SP_EXECUTESQL @STATEMENT1, @PARAMETERS1, @START_DATE = @START_DATE, @END_DATE = @END_DATE
    END


    Regards
    Satheesh

    Wednesday, June 13, 2012 6:32 AM
  • Use a dummy first where clause such as '1=1' so you can then simply append the other clauses with an 'AND'

    SET @STATEMENT1 = @STATEMENT1 + ' WHERE 1=1 ' 
    IF NOT @OUTLET_CODE IS NULL BEGIN
    SET @STATEMENT1 =@STATEMENT1 + ' AND ( #MYTEMP.OUTLET_CODE IN (' + @OUTLET_CODE + '))'
    END
    IF NOT @PROVINCE_CODE IS NULL BEGIN
    SET @STATEMENT1 =@STATEMENT1 + ' AND ( #MYTEMP.PROVINCE_CODE IN (' + @PROVINCE_CODE + '))'
    END

    • Marked as answer by SSamnang Friday, June 15, 2012 10:21 AM
    Wednesday, June 13, 2012 7:28 AM
  • Thank you all for the key tricks that work well in my case.

    Sam

    Friday, June 15, 2012 10:21 AM