quick SPROC report? - SQL Server Forums

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

Transact-SQL (2005)

quick SPROC report?

Author

Topic

tech
Starting Member

32 Posts

Posted - 2010-08-12 : 07:22:27

how would one write a SPROC to return results of this query:

params: companyID, userID, audittype (int), datefrom, dateto.

companyID can be null as can userID. if anyone of these are null then we arent interested in them (for a where query). AuditType will ALWAYS have a value.

so I want to return records based on those parameters, joining the following tables:

if companyID is not null then inner join company
if userID is not null then inner join users

[then where clause here]

any ideas?

Kristen
Test

22859 Posts

Posted - 2010-08-12 : 08:13:08

[code]
FROM MyTable AS T
LEFT OUTER JOIN CompanyTable AS C
ON T.CompanyID = C.CompanyID
LEFT OUTER JOIN UserTable AS U
ON T.userID = U.userID
WHERE (@companyID IS NULL OR (T.CompanyID = @companyID AND C.CompanyID IS NOT NULL0))
AND (@userID IS NULL OR (T.userID = @userID AND U.userID IS NOT NULL))
AND T.audittype = @audittype
AND (@datefrom IS NULL OR T.datefrom = @datefrom)
AND (@dateto IS NULL OR T.dateto = @dateto)
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources