I need Search Criteria for this values

Posted by Jayakumars under ASP.NET AJAX on 2/29/2012 | Points: 10 | Views : 1961 | Status : [Member] [MVP] | Replies : 2

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Kumaraspcode2009@gmail.com

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

0	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

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

Latest Posts