How to handle this query in an sp? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

How to handle this query in an sp?

Author

Topic

Andrew V
Starting Member

1 Post

Posted - 2006-03-28 : 10:58:08

Hello, friends,

I am programming a stored procedure for a search query as the follows. A user may search by appID, and/or typeID, and/or statusID.

So, I know my WHERE clause is NOT right since typeID (and/or statusID and/or appID) could be NULL.

What is the correct way to do this? Any sample source code or reference papers? Thans a lot.

CREATE PROCEDURE dbo.sp_GetInfoByAppIDTypeIDStatusID
@appID INT,
@typeID INT,
@statusID INT
AS

BEGIN
SELECT t.ticketID,
t.summary,
t.appID,
t.typeID,
t.sevName,
t.statusID,
t.addDT

FROM ticket AS t (NOLOCK)

WHERE t.appID = @appID
AND t.typeID = @typeID
AND t.statusID = @statusID
END

nr
SQLTeam MVY

12543 Posts

Posted - 2006-03-28 : 11:07:06

WHERE t.appID = coalesce(@appID, t.appID)
AND t.typeID = coalesce(@typeID, t.typeID)
AND t.statusID = coalesce(@statusID, t.statusID)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-03-28 : 11:09:33

-- My methods may be inefficient --

Method 1.

Create the SP to accept 2 parameters :
ParameterName (eg. Status) & ParameterData (eg. 'Resigned')

Write the code of SP so that, it checks the Parameter Name and
depending on that, select the appropriate Query within a If - Else if Structure

eg.

If @appID = 'VB' 
Begin 
   Select ... where t.appID = @ParameterData 
End
Else If @parameterName = 'Status' 
Begin 
   Select ... where t.statusID = @ParameterData 
End

Method 2.
Leave ur parameters list as it is
Check for Value of each parameter and depending on the value (some value or '') select the appropriate Query within a If - Else if Structure

Srinika

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources