Solved

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

Posted on 2011-09-29
7
303 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

809 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