How to pass pass parameters to the IN clause

How to pass pass parameters to the IN clause

Post by Joe Far » Sun, 27 Feb 2000 04:00:00



Can anybody help me.

I need to pass a list of params into a stored procedure for use with an IN
clause

i.e.

select * from customers where age IN (30,40,43,50)

why dosn't

work.

Anybody know how I can do this.

I know I can use an Execute statement and build the SQL up on the fly in the
stored proc, but that seems to defeat the point.

Thanks,
Joe.

 
 
 

How to pass pass parameters to the IN clause

Post by George Padvora » Mon, 28 Feb 2000 04:00:00


Here's a sample that will show you what you need and a little more.  Recently I had the same problem, but soon after I figured out
how to use a parameter for the IN clause, I needed to add more criteria in the WHERE clause which required more help.

Note:  each parameter used as criteria in the WHERE clause needs to be converted using CAST.  for divverent data types you need to

Alter Procedure zSample_CAST_integer_AND_IN_Statement
As
  set nocount on





UPDATE     tbProjectDetail




simple sample:


SELECT       *
FROM          tbProjectDetail


hope this helps
George Padvorac


> Can anybody help me.

> I need to pass a list of params into a stored procedure for use with an IN
> clause

> i.e.

> select * from customers where age IN (30,40,43,50)

> why dosn't

> work.

> Anybody know how I can do this.

> I know I can use an Execute statement and build the SQL up on the fly in the
> stored proc, but that seems to defeat the point.

> Thanks,
> Joe.


 
 
 

How to pass pass parameters to the IN clause

Post by joe_ce.. » Tue, 29 Feb 2000 04:00:00


Quote:>> I need to pass a list of params into a stored procedure for use with

an IN  clause .. <<

You might find it faster and easier to load a table with the list and
ththen use this compiled statement:

 BEGIN
 DELETE FROM Parm_list;
 {{ whatever you need to do to get the list fromteh user }}
 SELECT *
   FROM Customers
  WHERE age IN (SELECT age_parm FROM Parm_list);
 END;

--CELKO--

Sent via Deja.com http://www.deja.com/
Before you buy.

 
 
 

1. Pass-Through Queries from Access - How to pass form-based parameters

Hi,

I'm just diving into MSSQL7 (SS) by attempting to modify an existing Access frontend/backend
application to use SS as the data provider and continue using Access as the front end.

I've successfully used ODBC to "link" to the SS tables for the application.  However, I believe
that a pass-through query (ptq) would provide better performance.  As a trial, I converted a rather
simple query I was using to feed a form into a ptq, and, upon specifying the ptq as the data source
for the form, performance did increase.

Now, to extend this approach, I have attempted to convert a query that feeds a form-launched report
(getting parameters from the form such as "SedStatus", "phase", "areacode" by module-level functions
that examine textboxes and checkboxes) to a ptq.  This approach works perfectly in the query by
placing "Like getSedStatus()" in the criteria field for the "SedStatus" field, for example.

My first error (although the SS error messages are anything by informative) involved my use of
DISTINCTROW, then, after changing that to just DISTINCT (just to keep going through the SQL
statement), I get an error:
[Microsoft][ODBC SQL Server Driver][SQL Server]'getSedStatus' is not a recognized function name
(#195)

How do I pass the form-based parameters to this ptq?

TIA,

John McKee

2. How can I capture SQL going to Sybase?

3. passing parameters for SP to use in IN clause

4. HELP ! Information about integration WWW and DB's

5. Passing numeric parameters for IN(....) clause

6. Easy KILL question

7. How to pass parameter for an order by clause in stored procedure

8. VB4 & SQL Server 6.0

9. Can IN Clause be passed in QueryDef Parameter?

10. Passing the Where Clause as an input parameter to a stored procedure

11. Include WHERE clause in Stored Procedure depending on Parameter Passed

12. passed parameter in order clause

13. Passing Parameters down to a buried view or stored procedure using parameters