HOW TO CODE FOR THE THIS STOREDPROCEDURE

Posted by Parthibansk under ASP.NET on 5/28/2013 | Points: 10 | Views : 737 | Status : [Member] | Replies : 1
I GOT A STORED PROCEDURE FOR RETRIEVING DATA FROM FILTERED SEARCH.. A DROPDOWN WITH FIRSTNAME,LASTNAME AND EMAIL AND A TEXTBOX WHICH FILTERS WITH EXACT SEARCH..

BELOW IS SP.. HOW TO CODE FOR THIS...

IF I CHOOSE FIRTSNAME FROM DDL AND ENTER FIRSTNAME IN TEXTBOX.. IT HAS TO FILTER AND BIND WITH EXACT DETAILS IN GRIDVIEW...SAME AS FOR LASTNAME AND EMAIL...


Create Procedure [dbo].[spSearch]
(
@firstName nvarchar(50) = null,
@lastName nvarchar(50) = null,
@email nvarchar(50) = null
)
AS
BEGIN

If @firstName is not null and Len(@firstName )=0 Set @firstName = null
If @lastName is not null and Len(@lastName )=0 Set @lastName = null
If @email is not null and Len(@email )=0 Set @email = null

Select
*
From userinf
Where
( @firstName is null or firstName Like @firstName )
and ( @lastName is null or lastName Like @lastName )
and ( @email is null or email Like @email )
END

GO




Responses

Posted by: lakhansin-22735 on: 7/4/2013 [Member] Starter | Points: 25

Up
0
Down
Hi,

Please try this

CREATE PROCEDURE [dbo].[spSearch]

(
@firstName nvarchar(50) = null,
@lastName nvarchar(50) = null,
@email nvarchar(50) = null
)
AS
BEGIN

--Declare a local variable to hold dynamic Sql query
DECLARE @sqlQuery NVARCHAR(2000)

IF(@firstName != '')
SET @firstName = 'AND firstName LIKE' + @firstName
ELSE
SET @firstName = ''

IF(@lastName != '')
SET @lastName = 'AND lastName LIKE' + @lastName
ELSE
SET @lastName = ''

IF(@email != '')
SET @email = 'AND email LIKE' + @email
ELSE
SET @email = ''

SET @sqlQuery = 'Select * From userinf Where (1=1) ' + @firstName + @lastName + @email

EXEC (@sqlQuery )

END


Lakhan Singh
Tech Lead
BeyondKey System Pvt. Ltd.
Indore, M.P.
India

Parthibansk, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response