How to create stored procedure for searching

Question

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

Amit Singh · Answer

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'

Shubham Kumar · Answer

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