Need help For Using Conditions inside the Stored Procedure?

Dear All,

Let me Explain the Process First:

I have Four parameters to send To Sql server Stored Procedure.

I have Recived all the parameters in the stored procedure successfully.

In that five parameters, i have some parameters as null and some parameters have the value.

I have to select the values from data base table using that values.


when i am using the query in the front end, i can use like below.

(ex:

deptid,desigid,sectionid,gradeid are parameters to select a record from the database.

Sql = "Select * from tblEmployee where EmpCd='Emp001'"
if txtDeptId.Value <> "" then
sql = sql & " and Deptid = "' & txtDeptId.Value & '"
end if
if txtDesigId.Value <> "" then
sql = sql & " and Deptid = "' & txtDesigId.Value & '"
end if
if txtSectionId.Value <> "" then
sql = sql & " and Deptid = "' & txtSectionId.Value & '"
end if
if txtGradeId.Value <> "" then
sql = sql & " and Deptid = "' & txtGradeId.Value & '"
end if

So finally the string sql will have all the combinations of query.

So it is easy to select the records in any combination.

So, I want to do this in stored procedure. i have to send all parameters to stored procedure and i have to do query for all Combinations.

Can i write the stored procedure for that, if yes please give the SP.


Pls, It's very urggently needed for me.


Thank you,
by,
Kumaresan

Loook at this website:

http://vyaskn.tripod.com/passing_ar..._procedures.htm

which contains several examples of dynamically building your SQL in an stored procedure. You should be able to modify one of these for your needs.

CREATE PROCEDURE dbo.spSelect @paramDeptID Char(10), @paramDesigId Char(10),

@paramSectionId Char(10), @paramGradeId Char(10)

As

Select * from tblEmployee where EmpCd='Emp001'" And

(CASE WHEN @paramDeptID Is Not Null and Deptid= @paramDeptID THEN 1

WHEN @paramDeptID Is Null THEN 1

ELSE 0

END) = 1 AND

(CASE WHEN @paramDesigId Is Not Null and desigid = @paramDesigId THEN 1

WHEN @paramDesigId Is Null then 1

ELSE 0

END) = 1 AND

(CASE WHEN @paramSectionId Is Not Null and sectionid= @paramSectionId THEN 1

WHEN @paramSectionId Is Null then 1

ELSE 0

END) = 1 AND

(CASE WHEN @paramGradeId Is Not Null and gradeid= @paramGradeId THEN 1

WHEN @paramGradeId Is Null then 1

ELSE 0

END) = 1