Stored Procedure Help - 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 2000 Forums

SQL Server Development (2000)

Stored Procedure Help

Author

Topic

BukovanJ
Starting Member

2 Posts

Posted - 2005-09-11 : 15:17:50

How can I turn the below basic procedure into one that will accept the various options (below) and return results. Obviously, as it stands now, if I leave a variable blank it will not return results, same if I set it to null.

@searchterm must have at lease one character or word
@Category must accept an integer or return all records if left blank.
@Subcategory same as above
@Submitter same as above

Thanks in advance for your help!

CREATE PROCEDURE SearchDocument

(
@SearchTerm char (200),
@Category int,
@Subcategory int,
@SubmitterID int
)

as

SELECT dbo.tblDocument.DocumentID,
dbo.tblDocument.Title,
dbo.tblSubmitter.SubmitterName

FROM dbo.tblDocument join dbo.tblSubmitter
on (tbldocument.submitterid = tblsubmitter.submitterid)

where freetext (document, @searchterm) and
dbo.tblDocument.SubCategoryID = @subcategory AND
dbo.tblDocument.CategoryID = @category AND
dbo.tblDocument.SubmitterID = @submitterID

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-09-11 : 16:22:23

(dbo.tblDocument.SubCategoryID = @subcategory or @subcategory is null) AND
(dbo.tblDocument.CategoryID = @category or @category is null) AND
(dbo.tblDocument.SubmitterID = @submitterID or @submitterID is null)

Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."

BukovanJ
Starting Member

2 Posts

Posted - 2005-09-11 : 20:56:54

Oh Oh Oh...yes...dah... Then I'll just change my delcare's at the top to:

@SearchTerm char (200),
@Category int = null,
@Subcategory int = null,
@SubmitterID int = null

Thanks !!

Kristen
Test

22859 Posts

Posted - 2005-09-12 : 01:23:52

You may want to put the NULL tests first - I seem to remember reading that that gave better performance:

(@subcategory is null OR dbo.tblDocument.SubCategoryID = @subcategory) AND
...

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2005-09-12 : 03:08:21

quote:
You may want to put the NULL tests first - I seem to remember reading that that gave better performance:

If I remember rightly SQL server is smart enough to work this out without rearranging- but don't quote me on that

steve

Alright Brain, you don't like me, and I don't like you. But lets just do this, and I can get back to killing you with beer.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources