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
ASKER CERTIFIED SOLUTION
Avatar of Scott Fell
Scott Fell
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of ieg
ieg
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

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

Avatar of ieg
ieg
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

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

VB Script
VB Script

VBScript (Visual Basic Scripting Edition) is an interpreted scripting language developed by Microsoft that is modeled on Visual Basic, but with some important differences. VBScript is commonly used for automating administrative and other tasks in Windows operating systems (by means of the Windows Script Host) and for server-side scripting in ASP web applications. It is also used for client-side scripting in Internet Explorer, specifically in intranet web applications.

39K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo