Solved

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

Posted on 2011-09-29
7
305 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 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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql query help 15 55
Checking for column changes SQL 2014 4 24
What Is an Error? 2 27
SQL Server XML Select sub tables 4 30
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…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

726 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