Using coalesce with stored procedures

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Using coalesce with stored procedures

Author

Topic

BigMan2001
Starting Member

8 Posts

Posted - 2002-11-15 : 07:19:32

Hi,

I have a stored procedure which uses the COALESCE function to filter on
fields dynamically. The SP looks like the following (there is nothing wrong
with it by the way, it is just to show you how I am using it):

CREATE PROCEDURE [sp_GetOrders]
(@OrderID int = NULL,
@CustomerID int = NULL,
@ProductID int = NULL)
AS
SELECT * FROM Orders
WHERE OrderID = COALESCE(@OrderID, OrderID) AND
CustomerID = COALESCE(@CustomerID, CustomerID) AND
ProductID = COALESCE(@ProductID, ProductID)
GO

The idea is, if you run EXEC sp_GetOrders you return all rows, if you run
EXEC sp_GetOrders @CustomerID = 20 you return all rows with a customer id of
20. This works fine from SQL Analyser. However, when I call the SP from VB
I am having a bit of a problem. I want to use the command object to run the
procedure. I append only the parameters that have been selected by the
client, so that within the collection of parameters associated with the
command object I have just the one: whose name is CustomerID whose value is
20. However, when I execute the command object, I don't get any results
back. Now, the funny thing is, I have also run the SP using the execute
method of the connection object, passing in a string I build based on the
parameters to be used in the query. This works fine. But, I don't want to
use this method, as it means creating a complex algorithm to build the query
string, it would be much simpler to just append the necessary parameters to
the command object.

So to summarise:

This does NOT work:

With objCMD
.CommandText = "sp_GetOrders"
.ActiveConnection = cn
.CommandType = adCmdStoredProc
If OrderID <> 0 Then
.Parameters.Append .CreateParameter("OrderID", adInteger,
adParamInput, 4, OrderID)
End If
If ProductID <> 0 Then
.Parameters.Append .CreateParameter("ProductID", adInteger,
adParamInput, 4, ProductID)
End If
If CustomerID <> 0 Then
.Parameters.Append .CreateParameter("CustomerID", adInteger,
adParamInput, 4, CustomerID)
End If
Set GetOrders = .Execute
End With

But this DOES work (but is very clunky):

strQuery = "sp_GetOrders "
If OrderID <> 0 Then
strQuery = "@OrderID=" & OrderID
End If
If ProductID <> 0 Then
If OrderID <> 0 Then
strQuery = strQuery & ", @ProductID=" & ProductID
Else
strQuery = strQuery & "@ProductID=" & ProductID
End If
End If
If CustomerID <> 0 Then
If OrderID <> 0 Or ProductID <> 0 Then
strQuery = strQuery & ", @CustomerID=" & CustomerID
Else
strQuery = strQuery & "@CustomerID=" & CustomerID
End If
End If
Set GetOrders = cn.Execute(strQuery)

Cheers

Glenn

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-11-15 : 07:31:55

OK, you are going to think this is stupid, as do I..... but... in ADO, even though the parameters are named, you still need to pass them in order. If you run profiler and watch what your VB app does, you will see what is going on.

So, if you only have one parameter that will be left blank, put it last. Then you can leave it out. Or, pass "nothing" to it.

Damian

BigMan2001
Starting Member

8 Posts

Posted - 2002-11-15 : 07:53:12

Cheers Damian,

That worked, and yes, it is ruddy stupid! Do you know whether ADO.NET has the same issue?

Glenn

nr
SQLTeam MVY

12543 Posts

Posted - 2002-11-15 : 07:55:17

No .net unfortunately forces you to name the parameters - it won't even allow you to leave them blank.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

BigMan2001
Starting Member

8 Posts

Posted - 2002-11-15 : 10:38:24

Hi Damian,

For your information, I have just discovered that you can pass the parameters in a different order. You do this by setting the NamedParameters property of the Command object to True (it's default is false). I had to make a slight change to the code in VB so that the parameter names begin with "@". This works fine.

Glenn

SamC
White Water Yakist

3467 Posts

Posted - 2002-11-15 : 10:43:51

Would you post a snippet of this ADO parameter??

Sam

BigMan2001
Starting Member

8 Posts

Posted - 2002-11-15 : 11:04:15

From the ADO SDK download:

"NamedParameters Property
Indicates whether parameter names should be passed to the provider.

Remarks
When this property is true, ADO passes the value of the Name property of each parameter in the Command’s Parameter collection. The provider uses a parameter name to match parameters in the CommandText or CommandStream properties. If this property is false (the default), parameter names are ignored and the provider uses the order of parameters to match values to parameters in the CommandText or CommandStream properties."

My stored procedure is unchanged from the one I posted at the start of this thread.

The VB I use now looks like this:

     With objCMD
        .CommandText = "sp_GetOrders"
        .ActiveConnection = cn
        .CommandType = adCmdStoredProc
        .NamedParameters = True
        If CustomerID <> 0 Then
            .Parameters.Append .CreateParameter("@CustomerID", adInteger, adParamInput, 4, CustomerID)
        Else
            .Parameters.Append .CreateParameter("@CustomerID", adInteger, adParamInput, 4, Empty)
        End If
        If OrderID <> 0 Then
            .Parameters.Append .CreateParameter("@OrderID", adInteger, adParamInput, 4, OrderID)
        Else
            .Parameters.Append .CreateParameter("@OrderID", adInteger, adParamInput, 4, Empty)
        End If
        If ProductID <> 0 Then
            .Parameters.Append .CreateParameter("@ProductID", adInteger, adParamInput, 4, ProductID)
        Else
            .Parameters.Append .CreateParameter("@ProductID", adInteger, adParamInput, 4, Empty)
        End If
        Set GetOrders = .Execute
    End With

Glenn

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources