locked
set parameter to other parameters RRS feed

  • Question

  • User632751675 posted

    hi,

    In my stored procedure, I have three parameters all of which are varchar(2).  I would like to declare a local variable and set that variable to these parameters.

    I've tried this below, but it doesn't work.  here's the code from the SP:

    @ContactCodeC3 varchar(2)='c3',
    @ContactCodec5 varchar(2) = 'c5',
    @ContactCodeo varchar(2) = 'o4' ,
    @ContactCoded varchar(2)= 'd3',
    
    
    
    declare @ContactCodes varchar(15) = null
     
     
    set @ContactCodes = @ContactCodeC3 + ' ' + @ContactCodec5 + ' ' + @ContactCodeo4 + ' ' + @Contactcoded
    
    then this:
    
    and isnull(v.ContactCode, '') in (select value from fn_Split(@ContactCodes, ','))
    
    
    
    I get no error messages, but zero results. What am I doing wrong? 
    Friday, December 4, 2015 11:43 PM

Answers

  • User-62323503 posted

    Change

    set @ContactCodes = @ContactCodeC3 + ' ' + @ContactCodec5 + ' ' + @ContactCodeo4 + ' ' + @Contactcoded

    To

    set @ContactCodes = @ContactCodeC3 + ',' + @ContactCodec5 + ',' + @ContactCodeo4 + ',' + @Contactcoded

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, December 5, 2015 7:38 AM
  • User-1716253493 posted

    Try this

    and isnull(v.ContactCode, '') in (select value from fn_Split(@ContactCodes, ' '))

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, December 7, 2015 2:14 AM
  • User-595703101 posted

    As an addition to Sandeep's solution, you can add ISNULL() as follows

    set @ContactCodes = ISNULL(@ContactCodeC3 + ',','') + ISNULL(@ContactCodec5 + ',','') + ISNULL(@ContactCodeo4 + ',','') + ISNULL(@Contactcoded,'')

    I assume your split function uses "," as seperator

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, December 7, 2015 3:05 PM

All replies

  • User-62323503 posted

    Change

    set @ContactCodes = @ContactCodeC3 + ' ' + @ContactCodec5 + ' ' + @ContactCodeo4 + ' ' + @Contactcoded

    To

    set @ContactCodes = @ContactCodeC3 + ',' + @ContactCodec5 + ',' + @ContactCodeo4 + ',' + @Contactcoded

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, December 5, 2015 7:38 AM
  • User-1716253493 posted

    Try this

    and isnull(v.ContactCode, '') in (select value from fn_Split(@ContactCodes, ' '))

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, December 7, 2015 2:14 AM
  • User-595703101 posted

    As an addition to Sandeep's solution, you can add ISNULL() as follows

    set @ContactCodes = ISNULL(@ContactCodeC3 + ',','') + ISNULL(@ContactCodec5 + ',','') + ISNULL(@ContactCodeo4 + ',','') + ISNULL(@Contactcoded,'')

    I assume your split function uses "," as seperator

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, December 7, 2015 3:05 PM