stored procedure and "IN" statement

I'm trying to do a stored procedure which include a WHERE clause using a "IN" statement.  How can I provide value to this "IN" statement using a parameter.

ie. sp_pendingsubmission 'form1', '1,2'


CREATE PROCEDURE sp_pendingsubmission
@sSubmissionForm_Id  varchar(20),
@sPartner_ID varchar(20)
AS


SELECT submission_ID, result_value
FROM tbl_result
WHERE
(submissionForm_ID = @sSubmissionForm_Id) AND
(section_ID = 'projectinfo') AND
(question_ID = 'projecttitle') AND
(submission_ID IN
     (SELECT submission_ID
       FROM tbl_result
       WHERE
     (submissionForm_ID = @sSubmissionForm_Id) AND
     (
     ((section_ID = 'partneradmin') AND
     (question_ID = 'statusid') AND
     (result_value = 'pending'))
     OR
     ((section_ID = 'gbl') AND
     (question_ID = 'partnerid') AND
     (result_value in  (@sPartner_ID)))
     )
      GROUP BY submission_ID
     HAVING COUNT(submission_ID) >= 2))
martalexAsked:
Who is Participating?
 
appariConnect With a Mentor Commented:
CREATE PROCEDURE sp_pendingsubmission
@sSubmissionForm_Id  varchar(20),
@sPartner_ID varchar(20)
AS


exec( " SELECT submission_ID, result_value
       FROM tbl_result WHERE
      (submissionForm_ID = @sSubmissionForm_Id) AND
      (section_ID = 'projectinfo') AND
      (question_ID = 'projecttitle') AND
      (submission_ID IN
     (SELECT submission_ID
      FROM tbl_result
      WHERE
    (submissionForm_ID = @sSubmissionForm_Id) AND
    (
    ((section_ID = 'partneradmin') AND
    (question_ID = 'statusid') AND
    (result_value = 'pending'))
    OR
    ((section_ID = 'gbl') AND
    (question_ID = 'partnerid') AND
    (result_value in  (" + @sPartner_ID + ")))
    )
     GROUP BY submission_ID")

   
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
replace
(result_value in  (@sPartner_ID))
by
(',' + @sPartner_ID  +',' + like '%,'+ result_value + ',%')

CHeers
0
All Courses

From novice to tech pro — start learning today.