VBSCript execute MS SQL Stored Procedure Return Value

[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]]

Yas wrote:
> 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"

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.

> adoSQLCmdParam.CommandType = adCmdStoredProc
> adoSQLCmdParam.Parameters(0) = "runScript"
> adoSQLCmdParam.Execute
>

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]]

Yas wrote:
> Hello,
>

This may help you with the proper terminology:
http://www.google.com/groups?hl=en&lr=&c2coff=1&selm=OgYK94SgEHA.4092%40TK2MSFTNGP10.phx.gbl

[Yas]

On 17 Sep, 20:59, "Bob Barrows [MVP]" <reb01...@NOyahoo.SPAMcom>
wrote:
> Yas wrote:
> > Hello,
>
> This may help you with the proper terminology:http://www.google.com/groups?hl=en&lr=&c2coff=1&selm=OgYK94SgEHA.4092...

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

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]]

Yas wrote:
> On 17 Sep, 20:59, "Bob Barrows [MVP]" <reb01...@NOyahoo.SPAMcom>
> wrote:
>> Yas wrote:
>>> Hello,
>>
>> This may help you with the proper
>> terminology:http://www.google.com/groups?hl=en&lr=&c2coff=1&selm=OgYK94SgEHA.4092...
>> --
>> 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.
>
> 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
>

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]

On 18 Sep, 12:30, "Bob Barrows [MVP]" <reb01...@NOyahoo.SPAMcom>

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.

[Bob Barrows [MVP]]

Yas wrote:
> 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

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?

[Yas]

On 18 Sep, 16:10, "Bob Barrows [MVP]" <reb01...@NOyahoo.SPAMcom>
wrote:

> Yas wrote:
> > 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
>
> 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?

Hi, indeed no point in returning parameter name.

I just need the paremeter value. Data type of parameter_Value is
nvarchar(100) and yes parameter_name is Primary key. (no duplicates,
not nullable etc) essentially the table only has 3 columns. 3rd being
description of the parameter.

So how can I return this paremeter value from this SP by providing the
paremeterName?

Many thanks

Yas

[Bob Barrows [MVP]]

You could use a recordset without modifying the procedure, like this:

CREATE PROCEDURE my_getParam
@paramName nvarchar(50)
AS

SET NOCOUNT ON
SELECT parameter_value FROM tbl
WHERE parameter_name = @paramName
GO

Dim rs,parmname,parmval
parmname="runScript"
Set rs=createobject("adodb.recordset")
adoSQLConnection.my_getParam parmName, rs
if not rs.EOF then
parmval = rs(0)
else
parmval="No parameter named " & parmname & " was found"
end if

Or, you could do this a little more efficiently by using a Command
object with an output parameter:

CREATE PROCEDURE my_getParam
@paramName nvarchar(50).
@paramValue nvarchar(100) output)
AS
SET NOCOUNT ON
SET @paramValue = (SELECT parameter_value FROM tbl
WHERE parameter_name = @paramName)
GO

Const adCmdStoredProc = 4
Const adInteger = 3
Const adVarWChar = 202
Const adParamInput = &H0001
Const adParamOutput = &H0002
Const adParamReturnValue = &H0004

Dim parmname,parmval
parmname="runScript"

Set adoSQLCmdParam = CreateObject("ADODB.Command")

With adoSQLCmdParam
Set .ActiveConnection = adoSQLConnection
.CommandText = "my_getParam"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("RETURN_VALUE", _
adInteger, adParamReturnValue )
.Parameters.Append .CreateParameter("@paramName", _
adVarWChar, adParamInput,50,parmname)

.Parameters.Append .CreateParameter("@paramValue", _
adVarWChar, adParamOutput,100)
.Execute
parmval = .Parameters(2).Value
End With

Even though there is more lines of code, the second version is more
efficient, since a bulky recordset is not marshalled across processes.

[Yas]

On 18 Sep, 17:23, "Bob Barrows [MVP]" <reb01...@NOyahoo.SPAMcom>

Hi, yes second version seems better. Thanks so much for your help!

Yas