I need Search Criteria for this values

Posted by Jayakumars under ASP.NET AJAX on 2/29/2012 | Points: 10 | Views : 1016 | Status : [Member] [MVP] | Replies : 2
I need Search criteria Stored Proceduere following fields
FromDate,ToDate,Department,Name

when i put fromdate and todate match date only show
when i put Department,name same
only match records i need show

Mark as Answer if its helpful to you


Responses

Posted by: Sksamantaray on: 2/29/2012 [Member] Silver | Points: 25

Up
0
Down
create a procedure with parameters having default value set to null
then in the procedure body
if you want single query user case conditions else you can use if condition .

Thanks,
Sanjay

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

Posted by: Hmanjarawala on: 2/29/2012 [Member] Bronze | Points: 25

Up
0
Down
Hi heer is the answer:


CREATE PROCEDURE <my-proc-name>
{
@FromDate DATE=NULL,
@ToDate DATE=NULL,
@DeptID INT=0,
@Name VARCHAR(20)=NULL
}
BEGIN
DECLARE @@Query VARCHAR(MAX)
DECLARE @@Date VARCHAR(100)
DECLARE @@DeptID VARCHAR(50)
DECLARE @@Name VARCHAR(50)

IF ISNULL(@FromDate) AND ISNULL(@ToDate)
BEGIN
SET @@Date = '1=1 '
END
ELSEIF NOT ISNULL(@FromDate) AND ISNULL(@ToDate)
BEGIN
SET @@Date = 'CONVERT(VARCHAR(8),MYDate,'''yyyyMMdd'') >= CONVERT(VARCHAR(8),' + @FromDate + ','''yyyyMMdd'')'
END
ELSEIF ISNULL(@FromDate) AND NOT ISNULL(@ToDate)
BEGIN
SET @@Date = 'CONVERT(VARCHAR(8),MYDate,'''yyyyMMdd'') <= CONVERT(VARCHAR(8),' + @ToDate + ','''yyyyMMdd'')'
END
ELSE
BEGIN
SET @@Date = 'CONVERT(VARCHAR(8),MYDate,'''yyyyMMdd'') >= CONVERT(VARCHAR(8),' + @FromDate + ','''yyyyMMdd'') AND CONVERT(VARCHAR(8),MYDate,'''yyyyMMdd'') <= CONVERT(VARCHAR(8),' + @ToDate + ','''yyyyMMdd'')'
END

IF @DeptID = 0
BEGIN
SET @@DeptID = '1=1 '
END
ELSE
BEGIN
SET @@DeptID = 'DEPTID = ' + @DeptID
END

IF ISNULL(@Name)
BEGIN
SET @@Name = '''z'' = ''z'' '
END
ELSE
BEGIN
SET @@DeptID = 'NAME = ''' + @Name + '''
END

SET @@Query = '<your-select-statement> ' + 'WHERE ' + @@Date + ' AND ' + @@DeptID + ' AND ' + @@Name

EXEC(@@Query)
END


Himanshu Manjarawala
Sr. Software Engineer@AutomationAnywhere
http://fieredotnet.wordpress.com/

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

Login to post response