yas

Hello,

I have a Stored procedure on a MS SQL DB. Which simply takes the value
of a Parameter and returns the Value of that Parameter... I would like
to call this stored procedure to assign values to various variables in
my VBscript code..

Example get the parameter value for parameter "runScript"... This
should return True or False from the stored procedure...
Can someone please help me with the code to get this working? I don't
know how to execute the stored procedure from VBScript everytime I
want to get the value for a parameter..

Pseudo code would be strRunScript = getParemValue("runScript")

I have something like...

Const adCmdStoredProc = 4

Set adoSQLCmdParam = CreateObject("ADODB.Command")
Set adoSQLCmdParam.ActiveConnection = adoSQLConnection
adoSQLCmdParam.CommandText = "sp_getParam"
adoSQLCmdParam.CommandType = adCmdStoredProc
adoSQLCmdParam.Parameters(0) = "runScript"
adoSQLCmdParam.Execute

Thanks! :-)

Yas

yas

Hello,

I have a Stored procedure on a MS SQL DB. Which simply takes the NAME
of a Parameter and returns the Value of that Parameter... I would like
to call this stored procedure to assign values to various variables in
my VBscript code..

Example get the parameter value for parameter name "runScript"... This
should return True or False from the stored procedure...
Can someone please help me with the code to get this working? I don't
know how to execute the stored procedure from VBScript everytime I
want to get the value for a parameter from a parameter Name..

Pseudo code would be strRunScript = getParemValue(paramName)

I have something like...

Const adCmdStoredProc = 4

Set adoSQLCmdParam = CreateObject("ADODB.Command")
Set adoSQLCmdParam.ActiveConnection = adoSQLConnection
adoSQLCmdParam.CommandText = "sp_getParam"
adoSQLCmdParam.CommandType = adCmdStoredProc
adoSQLCmdParam.Parameters(0) = "runScript"
adoSQLCmdParam.Execute

Thanks! :-)

Yas

bob barrows [mvp]

Don not use the "sp_" prefix for your stored procedures. That prefix is
reserved for system stored procedures, which mean that the query engine
by default will look for them in the master database, only looking in
the current database when it is not found in master. This is not only a
performance hit: it is also a source of a very hard-to-debug error if
you happen to give one of your procedures the same name as a system procedure.

I am not clear if you have the terminology correct. Show us the first
part of the CREATE PROCEDURE script for this procedure, just the part
where the parameters are declared:
CREATE PROCEDURE sp_getParam (
params ) AS

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

bob barrows [mvp]

This may help you with the proper terminology:
http://www.google.com/groups?hl=en&l...TNGP10.phx.gbl
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

yas

Hi, actually I changed/corrected the Procedure name but forgot to
update it in the post. :-)
Anyway, its a simple stored procedure that takes 1 parameter and
returns the value for that Parameter. It searches for the given
parameter name in a table and returns just 2 columns, the parameter
name and parameter value.

CREATE PROCEDURE my_getParam
@paramName nvarchar(50)
AS

Thanks again

yas

Hi, actually I changed/corrected the Procedure name but forgot to
update it in the post. :-)

Anyway, its a simple stored procedure that takes 1 parameter and
returns the value for that Parameter. It searches for the given
parameter name in a table and returns just 2 columns, the parameter
name and parameter value.

CREATE PROCEDURE my_getParam
@paramName nvarchar(50)
AS

I just want to call this procedure in my VBScript to assign values to
couple of variables.... so Dim i: i = <returned value fro SP for a
given parameter name>

Thanks again

bob barrows [mvp]

Did you read my post about how to return data from procedures? Is your
intent to return the value using @paramName? Or are you returning the
results as a Select statement? If you don't understand my question, you had
best post the rest of your procedure script.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

yas

Hi, thanks for your quick reply. In my stored procedure I'm using the
@param.... something like select paramValue from tbl where
parameterName = @param

CREATE PROCEDURE my_getParam
@paramName nvarchar(50)
AS
SELECT parameter_name, parameter_value FROM tbl
WHERE parameter_name = @paramName
GO

so really it would be nice if I could easily return the Values from
the SP and assign them to each Variable in VBScript for each given
parameterName passed to the SP.

Thanks
Yas

bob barrows [mvp]

So open a recordset and loop through it. I don't understand the problem.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

bob barrows [mvp]

First of all, there is no point to returning parameter_name in this
query: you already know the parameter name which is contained in
@paramName.
What is the datatype of parameter_value?
Is parameter_name the primary key of tbl, thus guaranteeing only a
single row will be retrieved?

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.