We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now


Execute stored procedure from VBScript

Medium Priority
Last Modified: 2012-05-06
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
'  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
  Set Rs = Nothing
  'Msgbox "Not Connected"
End If
Set cnnSQL = Nothing

Open in new window

Watch Question

Kyle AbrahamsSenior .Net Developer

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>

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Top Expert 2012
'   Set Rs=CreateObject("ADODB.Recordset")           Not needed before or now
strSQL = "exec YourStoredProcedure '123', '123'"
Set Rs=CnnSQL.Execute(strSQL)


Thanks guys for the solutions. That has been a great help.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.