Passing a stored procedure parameter into an IN clause

Hi All :)

I have a stored procedure which, initially, I had passed a single parameter into a WHERE clause (e.g ...WHERE CustomerCode = @.CustCode). The parameter is passed using a DECommand object in VB6.

I now require the sp to return values for more than one customer and would like to use an IN clause (e.g ...WHERE CustomerCode IN(@.CustCode). I know I could create multiple parameters (e.g. ...WHERE CustomerCode in (@.CustCode1, @.CustCode2,...etc), but do not want to limit the number of customers.

If I set CustCode to be KA1001, everything works fine. If I set CustCode to be KA1001, KA1002 it does not return any records.

I think the problem is in the way SQL Server concatenates the stored procedure before execution. Is what I am attempting to do possible? Is there any particular format I need to set the string parameter to? I've tried:

KA1001', 'KA1002 (in the hope SQL Server just puts single quotes either side of the string)

and

'KA1001', 'KA1002'

Both fail :(

Any ideas?

Regards

Xoyou need to parse your list into a table variable and then join to that table variable.|||How about

WHERE
','+@.CustCode+','
LIKE
'%,'+CustomerCode+',%'

perhaps?

It's crap (won't use an index) but does work and is easy (might need some trims in there).
These requirements are really dodgy but sadly very common. You shoudl really try using an array of some kind. I would stay away from dynamic SQL which is the other way.|||You can also do this by passing the list of parameters as nText and using XML to add as many options you want. In the sproc you will need to use sp_xml_PrepareDocument to force that into a local temp table.

It's all kinda tricky, but crafty at the same time.|||LoztInSpace: Don't quite understand the syntax! Can you post an example! Thanks

alex8675: Thanks but no thanks!!! :)

Thrasymachus: Thanks!! Have used your solution for now :)|||Bookmark this

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=25830&SearchTerms=udf,csv,string|||LoztInSpace: Don't quite understand the syntax! Can you post an example! Thanks

What's to not understand? It's just a where statement. Did you try it?|||Hi Xogon

A bit of a bumpy ride for your second post methinks - don't take it personal, sometimes people forget what it's like to not fully understand the basics.

One easy Option for you - but not the most efficient is to build you SQL Statement in a VarChar and then EXECUTE it.

Heres an example


Declare @.SQL VarChar(1000)

SELECT @.SQL = ' SELECT [something]'
SELECT @.SQL = @.SQL + ' FROM [Table]'
SELECT @.SQL = @.SQL + ' WHERE CustomerCode IN (' + @.CustCode + ')'

EXECUTE (@.SQL)

And watch out for the Single Quotes in your @.CustCode these will have to be doubled up

GW|||ummmmmmmmmmm...did you read the link I posted?|||thanks GWilliy - worked a treat!! :)|||UUummmmmmmmmmmmmmmmmm - LOL

I Obviously did Brett & found the link in the Thread to another thread which had this Code.CREATE Procedure sp_dynamic_test
@.TableName varChar(100),
@.ID1 varchar(100),
@.ID2 varchar(100),
@.ID3 varchar(100),
@.DX varchar(100),
@.family varchar(100)
AS

Declare @.SQL VarChar(1000)

SELECT @.SQL = ' SELECT FIRSTNAME, LASTNAME, @.ID1, @.ID2, @.ID3, @.DX '
SELECT @.SQL = @.SQL + ' FROM '
SELECT @.SQL = @.SQL + @.TableName
SELECT @.SQL = @.SQL + ' WHERE Family = COALESCE((NULLIF(@.Family,0)),Family) '

Exec ( @.SQL)

Probably Naughty of me to do the Leg work for xogon, Plagarize & Dumb the code down.

I don't know what Skill level xogon is @. (2nd Post) but I thought a Concise & Clear example is often a good place to start. Complexities & doing his own Googles will organically follow.

DBForums is a Fantastic Site but I think sometimes posting a link to a related thread can frustrate New members, Although I will accept a point about too much hand holding.

hopefuly I've not upset anyone

GW|||Still, the use of the table variable is MUCH preferred over building dynamic SQL like you are suggesting and as I fear xogon has implemented.

We actually have built a function that takes as input the CSV string of (in this case, customer numbers), parses it, and returns the required table. That way I offer some reasonable methodology to the unwashed masses of developers here that need to do what you are doing in some form or fashion.

I didn't check Brett's link, but I think this function was derived from an earlier query I made on this very subject eons ago.CREATE FUNCTION [dbo].[fn_CSVList_FilteredPortfolioTable] (@.CsvList varchar(4000))
RETURNS table
AS

RETURN ( SELECT TOP 100 PERCENT *
FROM dbo.Portfolio (nolock)
WHERE ((CHARINDEX(',' + CAST(PortfolioID AS VARCHAR) + ',', ',' + @.CsvList + ',') > 0)
AND (isInactive <> 1))
ORDER BY PortfolioID)this code is probably even more complex than yours would need to be (you really would only need the CHARINDEX line), as it "verifies" the entries in the input CSV string against a table of valid codes.

Still, the use of a function lets you do the conversion on the fly in selects by join, such as: SELECT DISTINCT CL.PortfolioID, CL.StockOSID, @.CreateDate AS CreateDate, BuyDate, SellDate, CL.StockSymbol, Weight
FROM dbo.CurrentList CL (nolock)
INNER JOIN dbo.fn_CSVList_FilteredPortfolioTable(@.PortfoliosT oProcess) FP ON
CL.PortfolioID = FP.PortfolioID
WHERE ((@.CreateDate > CL.Buydate) AND
(@.CreateDate <= CL.SellDate)) AND
(CL.Active = 1)

errrr...and forgive the use of the fn_ prefix on the function name...it was created before I knew better, and at the suggestion of the senior SQL Server developer here at the time. *blush*

Pass a variable to a DataReader in a DataFlow task

How can I pass a variable to a DataReader in a DataFlow task?

My SqlCommand for the DataReader is:
SELECT CustName, CustCode FROM Customers WHERE CustCode = '?'

The DataFlow task is nested in a ForEach loop. I confirmed that the variable is changing with each loop by using a ScriptTaks and a message box. However, the DataReader SqlCommand does not seem to be updating.

You need to put the query into a variable, let's say @.myquery.

@.myquery would use an expression to make it dynamic on each iteration. Then you have to assign @.myquery variable to the data reader via expression. For doing that, go to the control flow; select the data flow task go to properties; select expression and add an expression to SQLCommand proepty. The proeprty should be just: @.myquery.

|||

This article by Jamie Thomson gave me more detail to accomplish what Rafael described:

http://blogs.conchango.com/jamiethomson/archive/2005/12/09/SSIS_3A00_-Using-dynamic-SQL-in-an-OLE-DB-Source-component.aspx

Note: the SQLCommand property of the DataReader gets renamed to [Name of DataReader Source]

|||

Well,

That article applies for OLE DB source component; for a data reader is slightly different.

Good that it worked for you!