Zulfiqar's web

TSQL: Conditional WHERE clause

There are different ways of programming conditional WHERE clause.

Method 1
========

Dynamic sql
------------

declare @select varchar(7000)
declare @where varchar(7000)
declare @i int

set @select = "select * from mytable"
set @i = 1

if @i = 1 then
    set @where = " where mycol = 1 "
else
    set @where = " where mycol = 2 "

set @select = @select + @where
exec(@select)

Method 2
========

if else conditions
------------------

if @i = 1
    select * from mytable where mycol = 1
else
    select * from mytable where mycol = 2

Method 3
==========

Case instruction
-----------------

select * from mytable
where
mycol =  case when @i = 1 then 1 else 2 end

You have to make choice pending your situation but I tend to stay away from
dynamic sql as much as possible and try to use better techniques such as case
instruction (in my opinion).

Some time ago I ran into the following situation where I had to choose between the
above 3 approaches and I was able to solve it using case statement. Here is the
scenario. Lets say you have @custid and it could be matched to primary customer id
pcustid or secondary customer id scustid depending upon status parameter
@statusid in table called mytable. So if @statusid is 1 then primary matching
else secondary matching.

Our input parameters are declared as follows

declare @custid int
declare @statusid int

Here are the use cases

1. When none of the params are initialized then return all the rows.
2. When only custid is passed then return all the rows for that customer.
3. When @custid is passed along with the @statusid then only the related row
would show up.

Dynamic sql way is very easy to implement so I will not address that.
Lets look at the if then else statement.

    if (@custid is not null)
    begin
        if (@statusid = 1)
            select
                *
            from mytable t
            where t.pcustid  = @custid

        else if (@statusid= 2)
            select
                *
            from mytable t
            where t.scustid = @custid
        else
            select
                *
            from mytable t
            where
            (t.pcustid = coalesce( @custid,t.pcustid) )
            or
            (t.scustid = coalesce( @custid, t.scustid) )

    end
    else
            select
                *
            from mytable

This could be implemented as follws in terms of case statement.

    select * from mytable t
    where coalesce(t.pcustid,0) = (
                        case when (@custid is not null) then
                            case when (@pcustid = 1) then
                                @custid
                            else coalesce(t.pcustid,0)
                            end
                        else
                            coalesce(t.pcustid,0)
                        end
        )
        and coalesce(t.scustid,0) = (
                        case when (@custid is not null) then
                            case when (@pcustid = 2) then
                                @custid
                            else coalesce(t.scustid,0)
                            end
                        else
                            coalesce(t.scustid,0)
                        end
        )
        and (
            (coalesce(t.pcustid,0) = coalesce( @custid, coalesce(t.pcustid,0)) )
            or
            (coalesce(t.scustid,0)= coalesce( @custid, coalesce(t.scustid,0)) )
        )

Coaslesce is heaven sent function. In case pcustid column is null, I will not
be able to do exact match with it self if all else fails to render it as
TRUE result. So the following match would return true

coalesce(t.pcustid,0)  = coalesce(t.pcustid,0)

Otherwise null column would not result as equal

t.pcustid = t.pcustid does not return true if column is null.

So see if you could revisit your dynamic sql statements and see if you
could REFACTOR them into nice CASE driven where clause.

Thanks

ZULFIQAR SYED

SSIS: stored proc and ado.net

Here is another way to pass value out of stored procedure. This one is using ado.net as Connection type for EXECUTE SQL TASK.

variables are declared as follows:

Name: inp
Scope: Package1
Datatype : Int32
value: 1

Name: outp
Scope: Package1
Datatype : Int32
value: 0

Hers are the settings for the task.

Genereal tab:
    Resultset  = none
    Connection type = ado.net
    Connection = localhost.AdventureWorks
    SqlSourceType = DirectInput
    SQL Statement =

declare @o int
exec testproc @in=@i1,@out = @o output
select @o1 = @o

    IsQueryStoredProcedure = False

Parameter mapping tab
    Variable Name = User::outp
    Direction = Output
    Datatype = Int32
    Parameter Name = @o1

    Variable Name = User::inp
    Direction = Input
    Datatype = Int32
    Parameter Name = @i1

Resultset and Expressions tabs are left emtpy.

The stored proc looks like this..

create PROCEDURE [dbo].[testproc]
    @in int ,     @out int output
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    select 
        @out = count(*)
        from a_cust where customerid = @in
    set nocount off
END

You can connect the output of this sql execute task to script task

Script tab
    ReadOnlyVariables = User::outp

Inside the script add the following line right under neatch public sub main so the whole method looks like this..

    Public Sub Main()
        '
        ' Add your code here
        '
        MsgBox(Dts.Variables(0).Value)

        Dts.TaskResult = Dts.Results.Success
    End Sub

Feel free to change the values of inp in the variable section and check out the output via messagebox. The variable inp could be set using any mechanism such as a database table, file , or any other source.

I hope this helps answering some of the questions regarding stored procs and sql execute task.

Thanks

ZULFIQAR SYED