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

x

Execute stored procedure from VBScript

Medium Priority
716 Views
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
  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

Comment
Watch Question

Kyle AbrahamsSenior .Net Developer
CERTIFIED EXPERT
Commented:

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
CERTIFIED EXPERT
Top Expert 2012
Commented:
'   Set Rs=CreateObject("ADODB.Recordset")           Not needed before or now
strSQL = "exec YourStoredProcedure '123', '123'"
Set Rs=CnnSQL.Execute(strSQL)

Author

Commented:
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.

OR

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.