Optional Multivalue Parameters

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Optional Multivalue Parameters

Author

Topic

hwood125
Starting Member

12 Posts

Posted - 2008-07-17 : 15:24:38

I am curious how anyone whould set up a select statement that is based off of two optional table parameters? The user will have the option to enter in multiple records for each parameter. In some situations a users will enter in both single parameters while in other situations enter in multiple values for each of them or even one parameter with 5 values and the other parameter with nothing. I decided to put them in a variable table but I am having a hard time selecting the records if one or both of the table parameters are null. I have tried various things and I am stuck. If anyone has any ideas or suggestions on what to do with creating a select statment based off of optional parameters that each parameter could have multiple values would be great.

Sorry, I don't have any code to paste in here. The process is pretty lengthly and I'm not even sure if I am on track.

Thanks in advance!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-19 : 02:14:21

let the parameters be @param1 & @Param2

then in your procedure define them with default value of null

CREATE PROC ProcName
@Param1 varchar(8000)=NULL,
@Param2 varchar(8000)=NULL
AS
SELECT fields
FROM table1 t1
join table2 t2
....
WHERE (','+@Param1+',' LIKE '%,'+CAST(tablealias.Field1 AS varchar(10))+',%' OR @Param1 IS NULL)
AND (','+@Param2+',' LIKE '%,'+CAST(tablealias.Field2 AS varchar(10))+',%' OR @Param2 IS NULL)
...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources