Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2011-09-29
7
Medium Priority
?
318 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

618 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