Solved

stored procedure and "IN" statement

Posted on 2001-08-13
2
208 Views
Last Modified: 2009-07-29
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))
0
Comment
Question by:martalex
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 143

Expert Comment

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

CHeers
0
 
LVL 39

Accepted Solution

by:
appari earned 50 total points
ID: 6382965
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

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how the fundamental information of how to create a table.

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question