[dba-SQLServer]Calling a Sproc with Parameter

Wed Mar 5 12:57:14 CST 2003

In situtaions like this I do lots of testing eliminating one thing at a
time.  Given what you have already tried, what I would do is change the
sproc so it doesn't require an input parameter and then hard code a
value (active or %) into your sproc.  Try running it from the app to see
if it works.  If it does, you know for sure the problem is in how you
are passing the parameter.  If the problem is in passing the parameter,
I might put the parameter in a variable in your FE and then do something
like:

Me.RecordSource = "EXEC spfrmCustomers @AccStatus = " & strAccStatus

--Susan

	-----Original Message-----
	From: David Emerson [mailto:davide at dalyn.co.nz] 
	Sent: Wednesday, March 05, 2003 12:44 PM
	To: dba-sqlserver at databaseadvisors.com
	Subject: Re: [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 <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 <http://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
<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/20030305/20ce0c86/attachment.html>