Stored procedure for dynamic query in SQL Server

Question

I want to create a procedure which returns a table on the basis of input of parmeters as fields and operator ...how can I use operator selection which is passed as a parameter string

ALTER PROCEDURE dbo.sp_getStaffRecord
(
   @deptname varchar(50),
   @dob date,
   @active bit,
   @salary int,
   @firstname varchar(50),
   @lastname varchar(50),
   @OperatorDob varchar(2),
   @OperatorSalary varchar(2)
)
AS
    select 
        st.id, firstname, lastname, deptname, salary, dob,
        (select firstname + ', ' + lastname from StaffTable 
         where firstname = @firstname and lastname = @lastname) as [Reporting To],  
        doj, active 
    from 
        StaffTable st 
    inner join 
        DepartmentTable dt on dt.id = st.dept 
    where 
        dt.deptname = @deptname 
        and 
        (
            if (@OperatorDob = '>=')
               st.dob >= @dob  
            else if (@OperatorDob = '<=') 
               st.dob <= @dob
            else if (@OperatorDob = '=')
               st.dob = @dob
            else if (@OperatorDob = '>')
               st.dob > @dob
            else if (@OperatorDob = '<=')
               st.dob = @dob
         ) 
    and st.active = @active 
    and st.salary >= @salary

    RETURN

Just so this can be properly tagged, which version of SQL Server are you working with? — Yuck
– Yuck, Commented Mar 8, 2012 at 17:11

Yuck · Accepted Answer · 2012-03-08 17:07:24Z

2

You can't use IF..ELSE the way you want to. What you can do is combine the value of the @OperatorDob with the logic it should execute:

WHERE
   dt.deptname = @deptname 
 AND
(
  (@OperatorDob = '>=' AND st.dob >= @dob)
  OR
  (@OperatorDob = '<=' AND st.dob <= @dob)
  OR
  (@OperatorDob = '=' AND st.dob = @dob)
  OR
  (@OperatorDob = '>' AND st.dob > @dob)
  OR
  (@OperatorDob = '<=' AND st.dob = @dob)
)
 AND st.active = @active 
 AND st.salary >= @salary

If the @OperatorDob value happens not to be any of those specified above (e.g. '!=') then the query won't produce any results. This might be a desirable side-effect.

answered Mar 8, 2012 at 17:07

Yuck

51.1k13 gold badges108 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Lamak · Accepted Answer · 2012-03-08 17:07:46Z

You can do this with dynamic SQL but it's a dangerous thing to do (big risk of SQL injection). Besides, it looks like yo can have those conditions on a simple CASE, so I recommend you to do something like this:

ALTER PROCEDURE dbo.sp_getStaffRecord
        (
            @deptname varchar(50),
            @dob date,
            @active bit,
            @salary int,
            @firstname varchar(50),
            @lastname varchar(50),
            @OperatorDob varchar(2),
            @OperatorSalary varchar(2)
        )
AS
    select  st.id,
            firstname,
            lastname,
            deptname,
            salary,
            dob,
            (select firstname+', '+lastname 
             from StaffTable 
             where firstname = @firstname and lastname = @lastname) 
             as [Reporting To], --Why aren't you just doing @firstname+', '+@lastname 
            doj,
            active 
    from StaffTable st 
    inner join DeapartmentTable dt 
    on dt.id = st.dept 
    where dt.deptname = @deptname and
    case when @OperatorDob = '>=' AND st.dob >= @dob  THEN 1
    when @OperatorDob = '<=' AND st.dob <= @dob THEN 1
    when @OperatorDob = '=' AND st.dob = @dob THEN 1
    when @OperatorDob = '>' AND st.dob > @dob THEN 1
    when @OperatorDob = '<=' AND st.dob = @dob THEN 1 ELSE 0 END = 1
    and st.active = @active 
    and st.salary >= @salary

Jeff · Accepted Answer · 2012-03-08 17:09:26Z

0

Try this:

where dt.deptname = @deptname  
        and
        (@OperatorDob = '>=' AND st.dob >= @dob) OR
        (@OperatorDob = '=' AND st.dob = @dob) OR
        (@OperatorDob = '>' AND st.dob > @dob) OR 
        (@OperatorDob = '<=' AND st.dob <= @dob) OR
        (@OperatorDob = '<' AND st.dob < @dob) OR

Alternatively, you can put the entire query into a CASE .. WHEN block.

answered Mar 8, 2012 at 17:09

Jeff

14.3k12 gold badges61 silver badges107 bronze badges

Collectives™ on Stack Overflow

Stored procedure for dynamic query in SQL Server

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related