Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How do I run the stored proc from vbscript and rea

Author  Topic 

jan.adolfsson
Starting Member

1 Post

Posted - 2013-10-01 : 16:46:19
I have a stored procedure that I run in Management studio just fine.
I need to provide 2 integer variables @id and @version.

How do I run the stored proc from vbscript and reading the output?

SP:
USE [ProjectBudget]
GO

DECLARE @return_value int,
@name varchar(500),
@maconomy varchar(100),
@creator varchar(100),
@created datetime,
@active bit



EXEC @return_value = [dbo].[sp_project_get]
@id = 2,
@version = 2,
@name = @name OUTPUT,
@maconomy = @maconomy OUTPUT,
@creator = @creator OUTPUT,
@created = @created OUTPUT,
@active = @active OUTPUT

SELECT @name as N'@name',
@maconomy as N'@maconomy',
@creator as N'@creator',
@created as N'@created',
@active as N'@active'

SELECT 'Return Value' = @return_value

GO



SP Returns:
@name @maconomy @creator @created @active
test 3 122112 Håkan Mattsson 2013-09-27 12:54:57.580 1




ASP Code:

Set Conn = CreateObject("ADODB.Connection")
Conn.Open "Provider=SQLOLEDB;Server=XXXX;database=XXXX;network=DBMSSOCN;uid=XXXX;pwd=XXXX"

Set objCommandSec = CreateObject("ADODB.Command")
objCommandSec.ActiveConnection = Conn
objCommandSec.CommandType = 4
objCommandSec.CommandText = "sp_project_get"

objCommandSec.Parameters.Refresh

objCommandSec.parameters("@id") = 2
objCommandSec.parameters("@version") = 2

objCommandSec.execute

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2013-10-02 : 03:53:56
Not sure what the question is - what happens when you run the code - does it fail? Can't you access the parameters?
To get the resultset you need to set a resultset object to the execute result.

Have a look at these for calling a stored proc and accessing a resultset
http://www.nigelrivett.net/aSP/DBAccess.inc.html
http://www.nigelrivett.net/VB/VBExecSPSimple.html


==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page
   

- Advertisement -