?
Solved

MS SQL 2008 - How to : IN(@parameter)

Posted on 2011-09-29
7
Medium Priority
?
313 Views
Last Modified: 2012-05-12
Attached is the Procedure using 3 parameters
.
I am trying to pass a string parameter for @CageCodes :
'31788','44185','50507','1cay9','595fd'

I want those cage codes to be used in the  :   IN(@CageCodes)

SQL processes the string as:
@CageCodes = N'''31788'',''44185'',''50507'',''1cay9'',''595fd'''

As you can see, these are double apostrophes and do not properly delimit the cage codes passed to the procedure.
The idea is that any number of cage codes can be passed (so we need the IN to be parameterized)
.
.
CREATE PROCEDURE [dbo].[procWarehouseReceived] 
	-- PARAMETERS
	@StartDate DateTime,
	@EndDate DateTime,
	@CageCodes nvarchar(50)
AS
BEGIN

SET NOCOUNT ON;

SELECT     dbo.tblProjects_Acct.SalesOrderNum,  dbo.tblProjects_Warehouse.ProductRcvdDate, dbo.tblProjects_Acct.VendorPOIssueDate, dbo.tblProjects_RFQ.CageCode
	FROM         dbo.tblProjects_Acct INNER JOIN
                      dbo.tblProjects_RFQ ON dbo.tblProjects_Acct.ProjectID = dbo.tblProjects_RFQ.ProjectID LEFT OUTER JOIN
                      dbo.tblProjects_Warehouse ON dbo.tblProjects_RFQ.ProjectID = dbo.tblProjects_Warehouse.ProjectID
    WHERE (VendorPOIssueDate Between @StartDate and @EndDate) And  Cagecode In (@CageCodes)
    ORDER BY VendorPOIssueDate
END

Open in new window

0
Comment
Question by:PAGANED
[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
7 Comments
 
LVL 15

Assisted Solution

by:Eyal
Eyal earned 200 total points
ID: 36818364
well...

you can't do it like so

first the parameter value you should send needs to be something like this:
@CageCodes = N'31788,44185,50507,1cay9,595fd'


inside the SP you should spilt it with userdefined function like this:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648

and then use the temporary table holding the splited string in the query you have posted
0
 
LVL 2

Expert Comment

by:akku101
ID: 36818560
Try this

------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[procWarehouseReceived]
      -- PARAMETERS
      @StartDate DateTime,
      @EndDate DateTime,
      @CageCodes nvarchar(50)
AS
BEGIN

/*
      --Akku101 Execute statement
      Exec procWarehouseReceived @StartDate ='2011-01-01',@EndDate ='2011-01-05',@CageCodes ='31788,44185,50507,1cay9,595fd'
*/


-- Akku101 Creating Temporary Table for CageCode
      
      CREATE TABLE  #CageCodes (CageCodes int)
      SELECT @CageCodes = @CageCodes + ','  
   
             

-- Akku101 Inserting cagecode into temp table
      
            WHILE (CHARINDEX(',',@CageCodes) >0)
      BEGIN
            INSERT INTO #CageCodes (CageCodes)
            SELECT SUBSTRING(@CageCodes ,1,CHARINDEX(',',@CageCodes)-1)
            SELECT @CageCodes = STUFF(@CageCodes,1,CHARINDEX(',',@CageCodes),'')
      END



SET NOCOUNT ON;

SELECT     dbo.tblProjects_Acct.SalesOrderNum,  dbo.tblProjects_Warehouse.ProductRcvdDate, dbo.tblProjects_Acct.VendorPOIssueDate, dbo.tblProjects_RFQ.CageCode
      FROM         dbo.tblProjects_Acct INNER JOIN
                      dbo.tblProjects_RFQ ON dbo.tblProjects_Acct.ProjectID = dbo.tblProjects_RFQ.ProjectID LEFT OUTER JOIN
                      dbo.tblProjects_Warehouse ON dbo.tblProjects_RFQ.ProjectID = dbo.tblProjects_Warehouse.ProjectID
    WHERE (VendorPOIssueDate Between @StartDate and @EndDate)
-- Akku101 Using Cagecodes for where clause
And  Cagecode In (Select CageCodes from #CageCodes)
    ORDER BY VendorPOIssueDate
END
0
 
LVL 2

Accepted Solution

by:
akku101 earned 1800 total points
ID: 36818564
Try This, its exactly same code as above, just copied code in Code Area
CREATE PROCEDURE [dbo].[procWarehouseReceived] 
	-- PARAMETERS
	@StartDate DateTime,
	@EndDate DateTime,
	@CageCodes nvarchar(50)
AS
BEGIN

/*
	--Akku101 Execute statement
	Exec procWarehouseReceived @StartDate ='2011-01-01',@EndDate ='2011-01-05',@CageCodes ='31788,44185,50507,1cay9,595fd'
*/


-- Akku101 Creating Temporary Table for CageCode
	
	CREATE TABLE  #CageCodes (CageCodes int) 
	SELECT @CageCodes = @CageCodes + ','  
    
		  

-- Akku101 Inserting cagecode into temp table
	
		WHILE (CHARINDEX(',',@CageCodes) >0)
	BEGIN
	      INSERT INTO #CageCodes (CageCodes)
	      SELECT SUBSTRING(@CageCodes ,1,CHARINDEX(',',@CageCodes)-1)
	      SELECT @CageCodes = STUFF(@CageCodes,1,CHARINDEX(',',@CageCodes),'')
	END



SET NOCOUNT ON;

SELECT     dbo.tblProjects_Acct.SalesOrderNum,  dbo.tblProjects_Warehouse.ProductRcvdDate, dbo.tblProjects_Acct.VendorPOIssueDate, dbo.tblProjects_RFQ.CageCode
	FROM         dbo.tblProjects_Acct INNER JOIN
                      dbo.tblProjects_RFQ ON dbo.tblProjects_Acct.ProjectID = dbo.tblProjects_RFQ.ProjectID LEFT OUTER JOIN
                      dbo.tblProjects_Warehouse ON dbo.tblProjects_RFQ.ProjectID = dbo.tblProjects_Warehouse.ProjectID
    WHERE (VendorPOIssueDate Between @StartDate and @EndDate) 
-- Akku101 Using Cagecodes for where clause
And  Cagecode In (Select CageCodes from #CageCodes)
    ORDER BY VendorPOIssueDate
END

Open in new window

0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36819032
And we can only hope no one recommends you resort to using Dynamic SQL, but I will not be surprised...
0
 
LVL 1

Author Comment

by:PAGANED
ID: 36893242
Let me clarify the @CageCodes parameter
.
These are not static numbers.
There could be 1 or 100 of them passed into the parameter
.
I cannot hardcode the Parameter in the Procedure
.
or perhaps I don't understand
0
 
LVL 1

Author Comment

by:PAGANED
ID: 36893258
Sorry ... I apologize ...
.
I see you are creating a temp table to take the passed @CageCodes and then break it out into however many are in the nvarchar
.
Looks Pretty Cool !   ...  didnt realize that SQL couldnt handle an IN() clasue with a parameter
0
 
LVL 1

Author Closing Comment

by:PAGANED
ID: 36893695
Thank You AKKU101  __  Cagecodes changes to nvarchar

Works great !
..................................................................

-- Akku101 Creating Temporary Table for CageCode
CREATE TABLE  #CageCodes (CC int,CageCodes nvarchar(50))
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

762 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