Sadaqat Jeelani

Sadaqat Jeelani

  • NA
  • 20
  • 15.2k

How to create stored procedure for searching

Feb 12 2016 12:59 AM
I have table in this records are 1 to 20 i want to perform searching with for example  id , name , city , salary 
i please tell me how to create stored procedure of that 

Answers (2)

0
Amit Kumar Singh

Amit Kumar Singh

  • 380
  • 4k
  • 203.9k
Feb 12 2016 5:07 AM


CREATE TABLE EMPLOYEE
(
@ID INT,
@NAME VARCHAR(50),
@GENDER VARCHAR(50),
@SALARY VARCHAR(50),
@COUNTRY VARCHAR(50)
)


Insert Value into Table

Then Execute Procedure


Alter PROCEDURE Employee_proc
(
@ID INT,
@NAME VARCHAR(50),
@GENDER VARCHAR(50),
@SALARY VARCHAR(50),
@COUNTRY VARCHAR(50),
@Opt varchar(2)
)
AS
BEGIN
IF(@Opt='S')
BEGIN
SELECT NAME from Employee WHERE NAME like @NAME + '%'
END
END



Employee_proc 0,'A','','','','S'


0
Shubham Kumar

Shubham Kumar

  • 0
  • 5.2k
  • 855.8k
Feb 12 2016 1:14 AM
for that you have to use dynamic stored procedure according to your condition ,I just give you the idea how to do that don't expect full code
 
CREATE PROC ABC_Search
@Id VARCHAR(50),
@Name VARCHAR(50)
AS
BEGIN
DECLARE @SqlQuery varchar(max), @Condition VARCHAR(max);

IF(@Id<>'')
BEGIN
SET @Condition='id='+@id;
END
ELSE IF(@Id<>'' AND @Name<>'')
BEGIN
SET @Condition='id='+@id +' and name='''+@name+'''';
END

SET @SqlQuery='select * from yourtable where'+ @Condition
exec(@SqlQuery);
END