Database Discussion - Simple Member Search

Print Page | Close Window

Simple Member Search

Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: Database Discussion
Forum Description: Discussion and chat on database related topics.
URL: https://forums.webwiz.net/forum_posts.asp?TID=5015
Printed Date: 30 March 2026 at 10:06am
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com

Topic: Simple Member Search

Posted By: gazeth
Subject: Simple Member Search
Date Posted: 14 August 2003 at 6:47am

I have created a simple stored procedure to which i send the following values

@strfirstname

@strsurname

@knownas

I want to search the members table for records matching any of the three using the LIKE statement however i don't want to have mailto:'@strSurname - @strsurname LIKE '%%' how do i get around this in the stored procedure

Replies:

Posted By: Morgan
Date Posted: 14 August 2003 at 7:38pm

So if "Smith" is passed in, you do not want "Smitherman" to be pulled up?

-------------
Morgan

Posted By: gazeth
Date Posted: 15 August 2003 at 2:32am

yes but if nothing is passed in then i don't want all records to be pulled up.

I need some checking in the stored procedure to see if the value is null or not. It's when the value is null i'm not sure what to replace it with.

Posted By: Flamewave
Date Posted: 25 August 2003 at 8:20am

at the beginig of the sp:

if @strsurname is null
Goto END

at end of sp:

END:
Select 0 as NumRecords
Return 1

something along those lines

-------------
- Flamewave

They say the grass is greener on the other side, but if you really think about it, the grass is greener on both sides.

Posted By: Morgan
Date Posted: 26 August 2003 at 5:27pm

CREATE PROC spTEST
   @strfirstname VARCHAR(30) = NULL,
   @strsurname VARCHAR(30) = NULL,
   @knownas VARCHAR(30) = NULL
AS
IF @strfirstname IS NULL
BEGIN
    RAISERROR('Must supply value for First Name', 16, 1)
    RETURN -1
END

IF @strsurname IS NULL
BEGIN
    RAISERROR('Must supply value for Surname', 16, 1)
    RETURN -1
END

IF @knownas IS NULL
BEGIN
    RAISERROR('Must supply value for Known AS', 16, 1)
    RETURN -1
END

...the rest of your stored proc here...
GO

-------------
Morgan