> ... use ExecReader to retrieve records, works fine when there is no input parameters but once I call a stored procedure with input parameters I get following message Line 1: Incorrect syntax near '_Search_Provider_ZipCode'.

Not sure, but I think you're being hit by SQL Server or ADO.NET being "helpful".

Here's the short answer: you shouldn't use ExecReader(..) to call stored procedures. Use ExecSprocReader(..)

The long answer is that your code should actually fail even when there are no parameters.

There is something somewhere than can detect when a stored procedure is being called if you specify just its name. Compare these in Query Analyzer (or SQL Mgmt studio)

(note sp_server_info is a system stored procedure that takes one optional parameter)

sp_server_info



print 'Calling sproc now'
sp_server_info


The first version works, the second doesn't. However change to the correct syntax for calling a stored procedure and it always works:

print 'calling sproc now'
exec sp_server_info


Anyway, switching to ExecSproc / ExecSprocReader etc should fix the problem. Look up "Calling Stored Procedures" in the MM.NET Developer guide.

Hope this helps - Walter