Solved

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

Posted on 2011-09-29
7
295 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
7 Comments
 
LVL 15

Assisted Solution

by:Eyal
Eyal earned 50 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 450 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
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 utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

758 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now