Passing multiple values to a select statement
-
Hi, not an expert SQL programmer so apologies if this seems like a novice question... Is there a way to pass multiple parameters to a select statement in an SQL stored procedure? For example: Say you wish to select all addresses in a certain county, where the counties table is foreign-keyed to the Address table on ID. So you would write a stored proc to accept a single county ID, and select on that basis. But say you want to pass in a range of counties, and select all addresses with a county ID in that range? It's easy to do in inline SQL, using the IN operator, so I could build a SQL string in my data access class and pass that for direct execution to the DB engine, but I'd rather stay away from that approach for obvious reasons. The other option is to not select on county, pass all results to my .NET class, and filter the data using my list of selection values in code before passing the resultset to the client. I think I'd prefer the latter approach but if it were possible I'd like to do the whole lot in SQL to enhance performance. It's an MS SQL 2000 database. Does anyone out there know of a way to get around this?
All the dude ever wanted... was his rug back.
-
Hi, not an expert SQL programmer so apologies if this seems like a novice question... Is there a way to pass multiple parameters to a select statement in an SQL stored procedure? For example: Say you wish to select all addresses in a certain county, where the counties table is foreign-keyed to the Address table on ID. So you would write a stored proc to accept a single county ID, and select on that basis. But say you want to pass in a range of counties, and select all addresses with a county ID in that range? It's easy to do in inline SQL, using the IN operator, so I could build a SQL string in my data access class and pass that for direct execution to the DB engine, but I'd rather stay away from that approach for obvious reasons. The other option is to not select on county, pass all results to my .NET class, and filter the data using my list of selection values in code before passing the resultset to the client. I think I'd prefer the latter approach but if it were possible I'd like to do the whole lot in SQL to enhance performance. It's an MS SQL 2000 database. Does anyone out there know of a way to get around this?
All the dude ever wanted... was his rug back.
Hi, You can do some thing like this. Create Procedure @CountyID varchar(100) Begin Exec ('Select * from CountyTable where countyId in ('+@CountyID+')') End Where @CountyID contains the comma separated list of countyId's Hope this helps you.
rAm i Think, i Wait, i Fast -- Siddartha
-
Hi, not an expert SQL programmer so apologies if this seems like a novice question... Is there a way to pass multiple parameters to a select statement in an SQL stored procedure? For example: Say you wish to select all addresses in a certain county, where the counties table is foreign-keyed to the Address table on ID. So you would write a stored proc to accept a single county ID, and select on that basis. But say you want to pass in a range of counties, and select all addresses with a county ID in that range? It's easy to do in inline SQL, using the IN operator, so I could build a SQL string in my data access class and pass that for direct execution to the DB engine, but I'd rather stay away from that approach for obvious reasons. The other option is to not select on county, pass all results to my .NET class, and filter the data using my list of selection values in code before passing the resultset to the client. I think I'd prefer the latter approach but if it were possible I'd like to do the whole lot in SQL to enhance performance. It's an MS SQL 2000 database. Does anyone out there know of a way to get around this?
All the dude ever wanted... was his rug back.
Yes, it is possible. There are 3 ways to do this as mentioned here : Click Here[^]
Regards, Arun Kumar.A