locked
Dynamic Search procudure RRS feed

  • Question

  • Hi ALL

    I need to create a Procedureon Attandence object based on

    Dynamic Search : EmployeeID, AttendanceDate

    How can we Create it .. Like, If we enter employeer id then it reture answer If we enter AttendanceDate then it return answer.\

    We have following columns;

    AttendanceID, EmployeeID, AttendanceDate, CreatedON

    Kindly help .


    Monday, November 11, 2013 1:12 PM

Answers

  • If its just two optional parameters then you could use static sql like this

    SELECT AttendanceID,EmployeeID,AttendanceDate,CreatedON
    FROM YourTable
    WHERE (EmployeeID = @EmpID OR @EmpID IS NULL)
    AND (AttendanceDate = @Date OR @Date IS NULL)

    By default if you not pass a value for parameter it will default to NULL and above condition will make sure filter gets bypassed for the NULL value

    • Marked as answer by BI_Support Tuesday, November 12, 2013 6:50 AM
    Monday, November 11, 2013 1:18 PM

All replies

  • If its just two optional parameters then you could use static sql like this

    SELECT AttendanceID,EmployeeID,AttendanceDate,CreatedON
    FROM YourTable
    WHERE (EmployeeID = @EmpID OR @EmpID IS NULL)
    AND (AttendanceDate = @Date OR @Date IS NULL)

    By default if you not pass a value for parameter it will default to NULL and above condition will make sure filter gets bypassed for the NULL value

    • Marked as answer by BI_Support Tuesday, November 12, 2013 6:50 AM
    Monday, November 11, 2013 1:18 PM
  • However if your dataset is large or you may many parameters using method like above known as catch all method can cause bad execution plan to be used. In that case you need to use dynamic sql like

    DECLARE @SQL nvarchar(max)
    SET @SQL= N'SELECT  AttendanceID,EmployeeID,AttendanceDate,CreatedON
    FROM YourTable
    WHERE 1 = 1'
    
    IF @EmployeeID > 0
    SET @SQL=@SQL + N' AND EmployeeID = ' + @EmployeeID
    
    IF @AttendanceDate > ''
    SET @SQL=@SQL + N' AND AttendanceDate = ''' + @AttendanceDate + ''''
    
    DECLARE @Params nvarchar(2000)
    
    SET @Params= N'@EmployeeID int,@AttenanceDate datetime'
    
    EXEC sp_executeSQL @SQL,@Params,@EMployeeID = @EmployeeID,@AttendanceDate = @AttendanceDate

    Monday, November 11, 2013 1:23 PM
  • If you have only 2 conditions (which are basically different queries), I suggest executing one or the other static SQL statements conditionally:

    IF @EmployeeID IS NOT NULL
    BEGIN
    	SELECT
    		  AttendanceID
    		, EmployeeID
    		, AttendanceDate
    		, CreatedON
    	FROM dbo.Attendance
    	WHERE EmployeeID = @EmployeeID;
    END
    ELSE
    BEGIN
    	SELECT
    		  AttendanceID
    		, EmployeeID
    		, AttendanceDate
    		, CreatedON
    	FROM dbo.Attendance
    	WHERE AttendanceDate = @AttendanceDate;
    END;
    

    But if you have many possible permuations, dynamic SQL is a more manegeable approach.  See http://www.sommarskog.se/dyn-search.html for details and examples.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Monday, November 11, 2013 1:28 PM
  • thanks for reply.

    If we have something like this then,

    Dynamic Search : EmployeeID, AttendanceDate,employeeName

    Monday, November 11, 2013 2:01 PM
  • http://www.sommarskog.se/dyn-search.html

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, November 11, 2013 2:12 PM
    Answerer