vbscript - getting a recordset back from a stored procedure

Hi
Can anyone advise how best to get a recordset back from a stored procedure using VBScript?
I need to pass a stock code as an input parameter and I'd like to get a recordset that I can then iterate over and process in my vbscript.

I want to pass the start of the stock code to the stored procedure (e.g. B1%) and get a list back.

My test stored procedure looks like this.
ALTER PROCEDURE [dbo].[ALtest]
      -- Add the parameters for the stored procedure here
      @p_StockCode varchar(50)
      
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

    -- Insert statements for procedure here
      SELECT StockCode, Description from InvMaster where StockCode like @p_StockCode
END


Many thanks
iegAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
See if this works.  You are creating a function to call the SP that passes the stockCode.  Edit the MyConnectionString to your own


response.write(ALtest("abc123"))

Function ALtest(stockCode)


	Set cmd = Server.CreateObject("ADODB.Command")
With cmd
   ' Set up DB connection to use, set the type of SQL command
   	.ActiveConnection = MyConnectionString
   	.CommandType = adCmdStoredProc
   	.CommandText = "ALtest" 
   
   	.Parameters.Append .CreateParameter("@p_StockCode",adVarChar, adParamInput,50)
   	.Parameters("@p_StockCode") = stockCode

 
   set rs = .Execute
End With
 
  ALtest= rs(0)

set cmd = nothing
set rs = nothing

end Function

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
iegAuthor Commented:
Many thanks for the reply - it worked a treat
This is the code I ended up with for my test.
Just a simple .vbs file but it shows the basics.
Cheers
Andy

'
'Filename : CallSQLStoredProcedure
'Author : 
'Date : 9/9/2013
'
'Example of calling a SQL stored procedure 
'The script will run a simple stroed proecedure that returns a recordset
'then iterates over it to create and XML string.
'
'Stored procedure for test
'==========================================================
'ALTER PROCEDURE [dbo].[ALtest] 
'	-- Add the parameters for the stored procedure here
'	@p_StockCode varchar(50)
'	
'AS
'BEGIN
'	-- SET NOCOUNT ON added to prevent extra result sets from
'	-- interfering with SELECT statements.
'	SET NOCOUNT ON;
'
'    -- Insert statements for procedure here
'	SELECT StockCode, Description from InvMaster where StockCode like @p_StockCode
'End
'===========================================================
'
Option Explicit

'see http://www.w3schools.com/ado/ado_datatypes.asp
Const adParamInput = 1
Const adParamOutput = 2
Const adParamInputOutput = 3
Const adParamReturnValue = 4

Const adChar = 129
Const adVarChar = 200

Dim connstr
Dim cmd
Dim SP_Name
Dim par1
Dim par2
Dim rst
Dim result
Dim name

SP_Name = "ALtest"
connstr = "Provider=sqloledb;Data Source=(local);Initial Catalog=SysproOutdoors61;Integrated Security=SSPI"

Set par1 = CreateObject("ADODB.Parameter")
par1.Direction=adParamInput
par1.Name="@p_StockCode"
par1.Size=30
par1.Type=adVarChar
par1.Value="B1%"


'set up sql command object
Set cmd = CreateObject("ADODB.Command")
With cmd
.ActiveConnection = connstr
	.CommandType = 4
	.CommandText = SP_Name
	.Parameters.Append par1
End With

'execute command obeject and set local variable rst to equal result of select statement in stored procedure
Set rst = cmd.Execute

'loop over recordset and pick up stockcode
result = ""
Do Until rst.eof
 result = result & rst.Fields("StockCode").Value & vbCrLf
 rst.movenext
Loop



MsgBox result
'tidy up and remove objects
Set par1 = Nothing
Set rst = Nothing
Set cmd = Nothing

Open in new window

0
iegAuthor Commented:
And just in case anyone is looking for this here is an example of picking up a named parameter from a stored procedure

'
'Filename : CallSQLStoredProcedure_GetReturnValue
'Author : 
'Date : 9/9/2013
'
'Example of calling a SQL stored procedure 
'The script will run a simple stored procedure and picks up a named parameter
'
'Stored procedure for test
'==========================================================
'ALTER PROCEDURE [dbo].[ALtest2] 
'	-- Add the parameters for the stored procedure here
'	@p_StockCode varchar(30),
'	@o_StockDescription varchar(45) OUTPUT
'	
'AS
'BEGIN
'	-- SET NOCOUNT ON added to prevent extra result sets from
'	-- interfering with SELECT statements.
'	SET NOCOUNT ON;
'
'    -- Insert statements for procedure here
'	SELECT @o_StockDescription = Description from InvMaster where StockCode = @p_StockCode
'End
'===========================================================
'
Option Explicit

'see http://www.w3schools.com/ado/ado_datatypes.asp
Const adParamInput = 1
Const adParamOutput = 2
Const adParamInputOutput = 3
Const adParamReturnValue = 4

Const adChar = 129
Const adVarChar = 200

Dim connstr
Dim cmd
Dim SP_Name
Dim par1
Dim par2
Dim rst
Dim result
Dim StkDescription

SP_Name = "ALtest2"
connstr = "Provider=sqloledb;Data Source=(local);Initial Catalog=SysproOutdoors61;Integrated Security=SSPI"

Set par2 = CreateObject("ADODB.Parameter")
par2.Direction=adParamOutput
par2.Name = "@o_StockDescription"
par2.Size=45
par2.Type=adVarChar

Set par1 = CreateObject("ADODB.Parameter")
par1.Direction=adParamInput
par1.Name="@p_StockCode"
par1.Size=30
par1.Type=adVarChar
par1.Value="B100"


'set up sql command object
Set cmd = CreateObject("ADODB.Command")
With cmd
.ActiveConnection = connstr
	.CommandType = 4
	.CommandText = SP_Name
	.NamedParameters = True
	.Parameters.Append par1
	.Parameters.Append par2
	.Execute
End With


'pick up named parameter from stored procedure
StkDescription = cmd.Parameters("@o_StockDescription")


MsgBox StkDescription
'tidy up and remove objects
Set par1 = Nothing
Set par2 = Nothing
Set rst = Nothing
Set cmd = Nothing

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.