[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 
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/dba-sqlserver/attachments/20030305/9ec6e2af/attachment.html>