help for a search procedure

hello,
please help me to give me solution
i have a table
CREATE TABLE T_STUDENT_MT (ID INT PRIMARY KEY, NAME VARCHAR2(50) NOT
NULL, FATHERNAME VARCHAR2(50), COUNTRY VARCHAR2(50), STATE VARCHAR2(20),
CITY VARCHAR2(30), STREET VARCHAR2(40))
MY DATA IS
INSERT INTO T_STUDENT_MT VALUES (1,
'ABC1','FABC1','CCC1','SSS1','CTT1','STT
1');
INSERT INTO T_STUDENT_MT VALUES (2,
'ABC2','FABC2','CCC2','SSS2','CTT2','STT
2');
INSERT INTO T_STUDENT_MT VALUES (3,
'ABC3',NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO T_STUDENT_MT VALUES (4,
'XXBC3','FCDD','ABDD',NULL,NULL,NULL,NUL
L);
PLEASE SUGGEST TO WRITE A PROCEDURE FOR
'CONDITION 1: If i pass iNAME to null it should NOT check in where
condition
'CONDITION 2:if i pass iNAME Parameter as 'A' IT HAS TO CHECK AS NAME
LIKE iNAME || '%'
CREATE PROCEDURE STUDENTSEARCH(iNAME VARCHAR2, iFATHERNAME VARCHAR2 ,
COUNTRY VARCHAR2, STATE VARCHAR2, CITY VARCHAR2, STREET VARCHAR2)
AS
BEGIN
--SINGE QUERY TO RETURN STUDENTT DATA
END;
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!kamal hussain wrote:
> hello,
> please help me to give me solution
> i have a table
> CREATE TABLE T_STUDENT_MT (ID INT PRIMARY KEY, NAME VARCHAR2(50) NOT
> NULL, FATHERNAME VARCHAR2(50), COUNTRY VARCHAR2(50), STATE VARCHAR2(20),
> CITY VARCHAR2(30), STREET VARCHAR2(40))
>
> MY DATA IS
> INSERT INTO T_STUDENT_MT VALUES (1,
> 'ABC1','FABC1','CCC1','SSS1','CTT1','STT
1');
> INSERT INTO T_STUDENT_MT VALUES (2,
> 'ABC2','FABC2','CCC2','SSS2','CTT2','STT
2');
> INSERT INTO T_STUDENT_MT VALUES (3,
> 'ABC3',NULL,NULL,NULL,NULL,NULL,NULL);
> INSERT INTO T_STUDENT_MT VALUES (4,
> 'XXBC3','FCDD','ABDD',NULL,NULL,NULL,NUL
L);
>
> PLEASE SUGGEST TO WRITE A PROCEDURE FOR
> 'CONDITION 1: If i pass iNAME to null it should NOT check in where
> condition
> 'CONDITION 2:if i pass iNAME Parameter as 'A' IT HAS TO CHECK AS NAME
> LIKE iNAME || '%'
>
> CREATE PROCEDURE STUDENTSEARCH(iNAME VARCHAR2, iFATHERNAME VARCHAR2 ,
> COUNTRY VARCHAR2, STATE VARCHAR2, CITY VARCHAR2, STREET VARCHAR2)
> AS
> BEGIN
> --SINGE QUERY TO RETURN STUDENTT DATA
> END;
--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
I'm assuming condition 1 means if the variable @.Name is NULL, then you
want all the rows.
Try this:
CREATE PROCEDURE STUDENTSEARCH(
@.NAME VARCHAR(50) = NULL
)
AS
SELECT [ID]
,[NAME]
,FATHERNAME
,COUNTRY
,STATE
,CITY
,STREET
FROM T_STUDENT_MT
WHERE ((@.NAME IS NOT NULL AND [NAME] = @.NAME)
OR (@.NAME IS NULL))
OR (LEN(@.NAME)=1 AND [NAME] LIKE @.NAME + '%')
Note: In SQL Server there isn't a data type of VARCHAR2 and the
concatenation symbol is the plus sign (+).
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQi0xPoechKqOuFEgEQJmOwCg1I3RunB5ASmn
7rtRnj7EZDpoKTkAoMrL
wTkd98HDDTfkao35SpHRNH7S
=WkyP
--END PGP SIGNATURE--