Tuesday, 15 November 2011

Create Store Procedure Search Record in Table

TABLE RECORD 


DistCode
DistName
1
VirudhuNagar
2
Madurai
3
Theni

Search Record


CREATE PROCEDURE DistMast_Search_SP
(
@DistCode INT,
@DistName VARCHAR(30)


)
AS
BEGIN

SELECT @DistName  = LTRIM(RTRIM(@DistName ))

IF @DistCode=0 
SELECT @DistCode= NULL

IF @DistName  =''
SELECT @DistName =NULL

SELECT @DistName   = ISNULL(@DistName,'%')
SELECT @DistName   = @DistName  +'%'


SELECT DistCode,DistName FROM DistMast 
WHERE DistCode LIKE ISNULL(@DistCode,DistCode)
AND DistName LIKE @DistName

END
                                                            ------------


Output :

exec  DistMast_Search_SP 1,''

DistCode
DistName
1
VirudhuNagar

exec  DistMast_Search_SP 0,'M'

DistCode
DistName
2
Madurai
exec  DistMast_Search_SP 0,''

DistCode
DistName
1
VirudhuNagar
2
Madurai
3
Theni

(Note : Display All Record Pass Parameter Value Empty . Numeric then 0 string then '')


Friendly Learn Translate to Tamil

Smilely

Services

© 2014