PDA

View Full Version : Solved: Stored procedure w/ w/out parameters



doubtfire
08-28-2009, 06:35 PM
I have the following SUB which is reading SQL to Excel. When I include parameters it is working but w/o parameters I get "Type name is invalid".
Please suggest.: pray2:

Code for Stored procedure dbo.GetStore1 w/ parameter
create procedure dbo.GetStore @Store int
AS
select * from Sales.Store where SalesPersonID=@Store

Code for Stored procedure dbo.GetStore2 w/O parameter
create procedure dbo.GetStore
AS
select * from Sales.Store

Code for SUB


Sub testing()

Dim Connection As New ADODB.Connection
Dim RecordSet As New ADODB.RecordSet

Const ConnectionString As String = _
"Provider=SQLOLEDB.1;" & _
"Data Source=Server01\Instance01;" & _
"Initial Catalog=AdventureWorks;" & _
'Integrated Security=SSPI"

On Error Goto ShowError

Call Connection.Open(ConnectionString)

Connection.GetStore1 282, RecordSet '****This is working
'Connection.GetStore2, RecordSet '***THIS DOES NOT work***

Call Sheet1.Range("A1").CopyFromRecordset(RecordSet)

ShowError:
If (Err.Number<>0) Then Debug.Print Err.Description
If (Connection.State=ObjectStateEnum.adStateOpen) Then Connection.Close
If (RecordSet.State=ObjectStateEnum.adStateOpen) Then RecordSet.Close

End Sub

Bob Phillips
08-29-2009, 02:30 AM
We would have to see the code in GetStore1 and 2.

doubtfire
08-29-2009, 05:08 AM
Please refer to the following

Code for Stored procedure dbo.GetStore1 w/ parameter
create procedure dbo.GetStore @Store int
AS
select * from Sales.Store where SalesPersonID=@Store

Code for Stored procedure dbo.GetStore2 w/O parameter
create procedure dbo.GetStore
AS
select * from Sales.Store

Thanks.

Bob Phillips
08-29-2009, 12:44 PM
Are you naming those SPs correctly, the code both look like GetStore to me, not number.

doubtfire
08-29-2009, 03:24 PM
Thanks.
The two SPs - GetStore1 needs parameters, which I feed with an Integer, and the GetStore2 has NO parameters.
My issue is once running the GetStore1 asking for parameters work, and whenever running with a SP that NO parameters needed raised an error.

doubtfire
08-30-2009, 06:12 AM
Please refer to the following for the correct SPs. Thanks.

Code for Stored procedure dbo.GetStore1 w/ parameter
create procedure dbo.GetStore1 @Store int
AS
select * from Sales.Store where SalesPersonID=@Store

Code for Stored procedure dbo.GetStore2 w/O parameter
create procedure dbo.GetStore2
AS
select * from Sales.Store

Thanks.

doubtfire
08-31-2009, 06:07 PM
Anyone out there has the correct answer for the error displayed ?
Thanks.:banghead:

CreganTur
09-09-2009, 07:36 AM
Even with my google-fu I cannot find any examples of calling a stored procedure that does not require parameters to be defined. In fact, the entire purpose of a stored procedure is so that you can quickly and easily execute a parameterized query.

That being said, in the case where you want to get all of the records in your table I would suggest creating a normal ADO connection to your database, and then create a recordset that pulls in all of the table data. Then you can easily use the CopyFromRecordset() method to load the records into Excel.

HTH:thumb

doubtfire
09-09-2009, 09:04 AM
Thanks for the suggestion.
Strongly agree with what you say. BUT in terms of programming "grammar"/syntax is it possible to fix my issue with the provided code ? (I just want to know what is missing)
Salute. :(

CreganTur
09-09-2009, 09:35 AM
BUT in terms of programming "grammar"/syntax is it possible to fix my issue with the provided code ? (I just want to know what is missing)


What you're missing are parameters within the stored procedure. You might be able to get away with using your stored procedure #1 and providing it with a wildcard parameter (*), or maybe a 'Like *'- you'd have to play around and see which one works... that should pull all records.

doubtfire
09-09-2009, 09:46 AM
Thanks.

doubtfire
09-09-2009, 10:44 AM
Agree with you for the #1 with wildcard for fulfilling the NO error display. BUT I still want for curiosity IF you (or anyone) can think of anything missing ("" / '' punctuation) on the #2 SP (w/0 parameters) PLEASE let me know!. :friends:

CreganTur
09-09-2009, 11:16 AM
Agree with you for the #1 with wildcard for fulfilling the NO error display. BUT I still want for curiosity IF you (or anyone) can think of anything missing ("" / '' punctuation) on the #2 SP (w/0 parameters) PLEASE let me know!. :friends:

I am confident that the error is caused because a stored procedure must have at least one parameter. Since your second stored procedure does not have a parameter, it is failing. Either VBA is causing it to fail because it is expecting a parameter, or SQL is causing the failure because it is expecting at least one parameter. No way to tell without a stack trace.

doubtfire
09-09-2009, 11:50 AM
First, thank you for the extra efforts in responding.
Second, your comments are interesting and I need to dig deeper. I have done a research on the net that there is a site (could not find anymore ..) mentioning for SP with NO parameters quotes (single/double) has(ve) to be added in order to feed the format requirements under ADO.
I definitely keep an eye and keep you informed next time I luckily come across this site again. Or may be anything comes up to your mind please notify me/us.
Salute!!! :beerchug:

doubtfire
09-09-2009, 11:50 AM
First, thank you for the extra efforts in responding.
Second, your comments are interesting and I need to dig deeper. I have done a research on the net that there is a site (could not find anymore ..) mentioning for SP with NO parameters quotes (single/double) has(ve) to be added in order to feed the format requirements under ADO.
I definitely keep an eye and keep you informed next time I luckily come across this site again. Or may be anything comes up to your mind please notify me/us.
Salute!!! :beerchug: