[dba-SQLServer]Calling a Sproc with Parameter

Both points are valid, but in this case the Sproc must be able to handle the
'%'.  Assuming that you are not executing Dynamic Sql in your sproc you
maybe manipulating the sproc to select all Customers not just the active
ones...  Do you have access to Query Analyzer? if so I would test the sproc
in there to make sure it executes w/o errors.  I have found certain errors
to be Access Only.  Such as selecting data from a linked table on another
server.


-Francisco
http://rcm.netfirms.com
----- Original Message -----
From: "Djabarov, Robert" <Robert.Djabarov at usaa.com>
To: <dba-sqlserver at databaseadvisors.com>
Sent: Wednesday, March 05, 2003 7:13 AM
Subject: RE: [dba-SQLServer]Calling a Sproc with Parameter


I will have to respectfully disagree with the previous recommendations.
LIKE will prevent your sp from compiling in the first place, while the usage
of EXEC is very proper in the original code.  Although the suggested form is
still valid, specifying named parameters is actually encouraged for the
clarity of the code, and MS fully supports EXEC sp_PROC @Parm1='<value>',
@Parm2=<value>, etc.

Robert Djabarov
Senior SQL Server DBA
USAA IT/DBMS
* (210) 913-3148 - phone
* (210) 753-3148 - pager

-----Original Message-----
From: Mike and Doris Manning [mailto:mikedorism at ntelos.net]
Sent: Wednesday, March 05, 2003 7:25 AM
To: dba-sqlserver at databaseadvisors.com
Subject: RE: [dba-SQLServer]Calling a Sproc with Parameter

Two things.

1)  Make sure your sproc is looking for LIKE @AccStatus and not = @AccStatus

2)  When you use EXEC, you don't add the name of the parameter.
"EXEC spfrmCustomers 'Active'"

Doris Manning
Database Administrator
Hargrove Inc.
www.hargroveinc.com
-----Original Message-----
From: dba-sqlserver-admin at databaseadvisors.com
[mailto:dba-sqlserver-admin at databaseadvisors.com] On Behalf Of David Emerson
Sent: Tuesday, March 04, 2003 07:01 PM
To: dba-SQLServer at databaseadvisors.com
Subject: [dba-SQLServer]Calling a Sproc with Parameter
I have a button on a form which is used to filter the records.  The code
is -

Private Sub cmdCustomerFilter_Click()

    On Error GoTo Err_cmdCustomerFilter_Click

    If Me!cmdCustomerFilter.Caption = "Show Active" Then
        Me.RecordSource = "EXEC spfrmCustomers @AccStatus = 'Active'"  <-
Problem line
        Me!cmdCustomerFilter.Caption = "Show All"
    Else
        Me.RecordSource = "EXEC spfrmCustomers @AccStatus='%'"
        Me!cmdCustomerFilter.Caption = "Show Active"
    End If
.....

End Sub

In the sproc the parameter is -

@AccStatus varchar(20) = '%'

When I click on the button I get error 2353 - Bad query parameter.  I can
run the query in Query analyser and it runs ok.

If I leave the @AccStatus = 'Active' off then a message appears telling me
that the procedure expects the parameter (so this tells me it is calling the
correct sproc)

I think the problem is in the call but can't put my finger on it.  Any help?



Regards

David Emerson
DALYN Software Ltd
25b Cunliffe St, Johnsonville
Wellington, New Zealand
Ph/Fax (877) 456-1205