Optional IN functions in Stored Procedure

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

SQL Server Development (2000)

Optional IN functions in Stored Procedure

Author

Topic

ChadLewis
Starting Member

4 Posts

Posted - 2000-12-01 : 04:34:13

This is a tricky one. I want to pass a stored procedure three comma separated strings of primary key ids to search on. Any of these can be blank.

I use an sp called spSys_ReturnTokens to convert the string into a temporary table. This all works, if i specify both @search1 and @search2. but if i dont then i have to set the temporary table to contain every record in the database.

There must be an easier way. And preferably not using temporary tables

My code so far is this:

declare @search1 varchar(50)
declare @search2 varchar(50)

IF @search1 is null BEGIN SELECT @search1='' END
IF @search2 is null BEGIN SELECT @search2='' END

CREATE TABLE #search1 (MID int NULL)
CREATE TABLE #search2 (MID int NULL)

set nocount on

BEGIN INSERT INTO #search1 exec spSys_ReturnTokens searchtable1 END

BEGIN INSERT INTO #search2 exec spSys_ReturnTokens searchtable2 END

SELECT DISTINCT
*
FROM Searchtable1 INNER JOIN
Searchtable2 ON
Searchtable1.UniqueID = Searchtable2.UniqueID
WHERE
Searchtable1.UniqueID IN (select UniqueID from #search1
AND
Searchtable2.UniqueID IN (select UniqueID from #search2

DROP TABLE #search1
DROP TABLE #search2

SET NOCOUNT OFF

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources