Getting Record Based On condition

Posted by Shoyebaziz123 under Sql Server on 1/6/2012 | Points: 10 | Views : 875 | Status : [Member] | Replies : 1
Hi All,
i have written this query in sql

alter PROCEDURE Sp_SearchReviewOfReq
-- Add the parameters for the stored procedure here
@iClientId int =null
,@sBillNo nvarchar(250)=null
,@sReqNumber nvarchar(250)=null
,@dtReceivedDate datetime=null
AS
BEGIN
Declare @str nvarchar(Max)=null;
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
SELECT tReqMas.ClientId,tReqMas.ReqId,tbl_Client.ClientName,
tReqMas.ReqNumber, tReqBD.BillNo,
CONVERT(char(15),tReqMas.ReceivedDate,103)ReceivedDate ,
CONVERT (char(15), tReqMas.ReportDate,103)ReportDate ,
case tReqMas.ReqStatus
when 1 then 'Open'
when 2 then 'Requested'
when 3 then 'Closed'
when 4 then 'Invoiced'
end Status

FROM tbl_RequisitionMaster tReqMas INNER JOIN
tbl_Client ON tReqMas.ClientId = tbl_Client.ClientId INNER JOIN
tbl_RequisitionBillDetails tReqBD ON tReqMas.ReqId = tReqBD.ReqId

--where tReqMas.ClientId= @iClientId
--or tReqBD.BillNo=@sBillNo
--or tReqMas.ReqNumber=@sReqNumber
--or tReqMas.ReceivedDate=@dtReceivedDate

now my problem is that how to getting record if i send only some parameter or sending nothing from my code
based on those condition the value must be written.... i am try this query with sending no parameter query does not return any value

Thanks




Responses

Posted by: Sksamantaray on: 1/6/2012 [Member] Silver | Points: 25

Up
0
Down


Either you can use if and else to check nullabilty of your input parameter and write respective query

or

you can use case when <condition> then <dotask> end ; in your where clause

Thanks,
Sanjay

Shoyebaziz123, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response