Where myParm IN myTableType ignore if empty

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 2008 Forums

Transact-SQL (2008)

Where myParm IN myTableType ignore if empty

Author

Topic

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2014-03-11 : 11:54:11

I have a simple select to bring back records according to status:


SELECT ID, Comment
FROM myTable
WHERE StatusID = @StatusID OR @StatusID IS NULL OR @StatusID = -1

Straight forward, the param @StatusID being sent in is ignored if it's NULL or -1 then it will bring back all records no matter the status.
Otherwise it brings back only the records with the requested status.

What about a user defined table type?

CREATE TYPE [dbo].[int_list_tbltype] AS TABLE(
	[ID] INT NOT NULL
)
GO

Then the params being sent into the stored procedure are:

	@StatusID INT,
	@POSLst dbo.int_list_tbltype READONLY

Easy to use if it's populated.


SELECT ID, Comment
FROM myTable
WHERE (StatusID = @StatusID OR @StatusID IS NULL OR @StatusID = -1)
AND(POSID IN (SELECT ID FROM @POSLst))

I've been trying to find a way to add this to the where clause so it will ignore it if it's empty. If it's empty, bring back all POSID's and if it's not empty, do the IN statement.

Sure, an if statement with two sql statements would work but the actual sql statement is quite long.

Thanks for any input.

sqlsaga
Yak Posting Veteran

93 Posts

Posted - 2014-03-11 : 12:51:13

How about building the where clause with a dynamic string and executing it on the whole?

Visit www.sqlsaga.com for more t-sql snippets and BI related how to's.

sqlsaga
Yak Posting Veteran

93 Posts

Posted - 2014-03-11 : 12:53:55

Instead of the IN statement , why not use a JOIN??? so it would return results faster than they way it is now...

SELECT ID, Comment
FROM MyTable a
INNER JOIN @PostList b ON a.POSID = b.ID
WHERE (StatusID = @StatusID OR @StatusID IS NULL OR @StatusID = -1)

Visit www.sqlsaga.com for more t-sql snippets and BI related how to's.

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2014-03-11 : 13:18:35

Thanks for the input.
I adjusted it like you suggested using an INNER JOIN.
Made a few more adjustments and got it working just fine.

Thanks again!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources