[dba-SQLServer]Calling a Sproc with Parameter

I have used query analyser and run the sproc both with entering % and 
Active as the parameter.  Both work OK.

I can't seem to find any information on "error 2353 - Bad query 
parameter."  Anyone know of a source that will explain this?  (I tried 
Access help, BOL, Google).

Here is the sproc in case I have missed something -

ALTER PROCEDURE spfrmCustomers

         (
                 @AccStatus varchar(20) = '%'
         )

AS
         SET NOCOUNT ON

         SELECT dbo.tblCustomers.*,
                 case
                         when [Residential]=1
                                 then CSurname + C1stName
                         else TradingName
                 end
                 as SortOrder
         FROM dbo.tblCustomers
         WHERE AccStatus like @AccStatus
         ORDER BY SortOrder

         If @@Error <> 0
                 Begin
                         Select 'Unexpected error occurred: ', @@Error
                         Return @@Error
                 End
         else
                 return 0



David

At 5/03/2003, you wrote:
>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
>
>_______________________________________________
>dba-SQLServer mailing list
>dba-SQLServer at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>http://www.databaseadvisors.com

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/20030306/95483793/attachment.html>