search by both or any of the criteria

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

General SQL Server Forums

New to SQL Server Programming

search by both or any of the criteria

Author

Topic

nikoo56
Starting Member

26 Posts

Posted - 2015-04-07 : 13:19:39

I have a store procedure that search by Firstname and Lastname. I want it search by either both (Firstname and Lastname) or any of them. For example if only FirstName passes to it shows all the record with that Fistname.
Currently I have to pass both Firstname and Lastname to my store proc to get the result.

This is my stor proc:

USE [CustomerPortal]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[CSA_Search_Customer_By_Name]

@First_Name varchar(500)=''

,@Last_Name varchar(500)=''

AS

BEGIN

SET NOCOUNT ON;

SELECT

cc.[Customer_ID]

, [Account_Number]

, [First_Name]

, [Middle_Name]

, [Last_Name]

, isRegistered]

, isActivated]

,Zip

FROM [CustomerPortal].[dbo].[Customer] cc WITH (NOLOCK)

left join [CustomerPortal].[dbo].Customer_Payment cp WITH (NOLOCK) on cc.Customer_ID = cp.Customer_ID

WHERE
[First_Name] = @First_Name
And [Last_Name] = @Last_Name

END

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-04-07 : 13:26:21

[code]
USE [CustomerPortal]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[CSA_Search_Customer_By_Name] @First_Name VARCHAR(500) = ''
, @Last_Name VARCHAR(500) = ''
AS
BEGIN
SET NOCOUNT ON;

SELECT cc.[Customer_ID]
, [Account_Number]
, [First_Name]
, [Middle_Name]
, [Last_Name]
, isRegistered]
, isActivated]
, Zip
FROM [CustomerPortal].[dbo].[Customer] cc
LEFT JOIN [CustomerPortal].[dbo].Customer_Payment cp
ON cc.Customer_ID = cp.Customer_ID
WHERE [First_Name] = @First_Name
OR [Last_Name] = @Last_Name
END
[/code]

Note that you will still have to pass two parameters to the procedure, though either of them can be NULL

nikoo56
Starting Member

26 Posts

Posted - 2015-04-07 : 13:39:38

This is not working I tried before as if I pass both firstname and last name it suppose to bring only one but with this is bring every one that hast that last name with different firstname and that firstname with different last name.

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2015-04-07 : 13:46:15

USE [CustomerPortal]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[CSA_Search_Customer_By_Name] @First_Name VARCHAR(500) = ''
, @Last_Name VARCHAR(500) = ''
AS
BEGIN
SET NOCOUNT ON;

SELECT cc.[Customer_ID]
, [Account_Number]
, [First_Name]
, [Middle_Name]
, [Last_Name]
, isRegistered]
, isActivated]
, Zip
FROM [CustomerPortal].[dbo].[Customer] cc
LEFT JOIN [CustomerPortal].[dbo].Customer_Payment cp
ON cc.Customer_ID = cp.Customer_ID
WHERE ([First_Name] = @First_Name or @First_Name IS NULL)
AND([Last_Name] = @Last_Name or @Last_Name IS NULL)
END

Just took GB's code and changed the Where clause. This should work for both or either or

nikoo56
Starting Member

26 Posts

Posted - 2015-04-07 : 14:11:22

@MichaelJSQL Thanks it is working now.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources