Avatar of ieg
iegFlag for United Kingdom of Great Britain and Northern Ireland asked on

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
VB Script

Avatar of undefined
Last Comment
ieg

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Scott Fell

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
ieg

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

ASKER
ieg

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

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy