• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 705
  • Last Modified:

Execute stored procedure from VBScript

Hi guys,

As you can see from the code attached I am currenlty creating a connection to a SQL Server DB and then executing two SQL statements 1 to insert some data and the other to return. This is the proof of concept and is working well.

I now want to move the code into stored procedures as the SQL needs to be rolled out to many scripts and I want to centrally manage the code. I have created the stored procedures with the input parameters and they work fine.

How do I now change the code below to execute the stored procedures attaching the parameters?

Any help will be greatfully recieved.
Str_Connect = "Provider=SQLOLEDB;Data Source=srv02\sqlexpress;Initial Catalog=IPPbxDynamic"
Set CnnSQL=CreateObject("ADODB.Connection")
CnnSQL.Open Str_Connect,"***", "*****"
 
If CnnSQL.State=1 Then	'** Check if the connection is alive
 
'********
'  Insert the current call into the DB
'********
 
  strInsertSQL = "Insert INTO tblDynamicCallDetails (CallId, ScriptName) VALUES ('123','456')"	' ** Build the dynamic SQL Statement
  CnnSQL.Execute(strInsertSQL)
 
'********
'  Insert the current call into the DB
'********
 
  strReturnSQL = "Select * from tblDynamicCallDetails"	' ** Build the dynamic SQL Statement
 
  Set Rs=CreateObject("ADODB.Recordset")
  Set Rs=CnnSQL.Execute(strSQL)
  Do while not Rs.EOF
	'write the responses back to variables
	Rs.MoveNext
  Loop
 
  Rs.Close
  Set Rs = Nothing
 
else
  'Msgbox "Not Connected"
End If
 
cnnSQL.Close
Set cnnSQL = Nothing

Open in new window

0
Nigel_Taylor
Asked:
Nigel_Taylor
2 Solutions
 
Kyle AbrahamsSenior .Net DeveloperCommented:

strInsertSQL ="
"declare @p1 int;declare @p2 varchar(100);select @p1 = 1, @p2='test'; exec mystoredproc @p1, @p2"

if you need the parameters instead of test you can call a replace or just break it down.

select @p1 = " & myParam &  <rest of query>
0
 
Anthony PerkinsCommented:
'   Set Rs=CreateObject("ADODB.Recordset")           Not needed before or now
strSQL = "exec YourStoredProcedure '123', '123'"
Set Rs=CnnSQL.Execute(strSQL)
0
 
Nigel_TaylorAuthor Commented:
Thanks guys for the solutions. That has been a great help.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now