Output from SP (sql)

This is a multi-part message in MIME format.

------=_NextPart_000_011A_01C519C8.A39D5D40
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Hello,

I use some sp:s in a ms sql, and so far it looks like (this works fine):

-----------------------------------------------------
sql =3D "sp_NameOfSp @IDno =3D 1, @Name =3D 'Mikael'" (and 15 more =
values...)

cmd.CommandText =3D sql
cmd.Execute
-----------------------------------------------------
But know I want something in return from the SP.=20
I must use: RETURN 1001 in the SP (not use: @returnvalue int OUTPUT).


I have tried:
-----------------------------------------------------
.ActiveConnection =3D rs
.CommandText =3D "up_CreateAbsMessage"
.CommandType =3D 4

.Parameters.Append .CreateParameter("RETURN_VALUE", 3, 4)
.Parameters.Append .CreateParameter("@IDno", 3, 1, 4, "1")
.Parameters.Append .CreateParameter("@Name", 3, 1, 4, "Mikael")

.Execute

lngAdded =3D .Parameters("RETURN_VALUE")
-----------------------------------------------------

And this works as well, but is much more inconvenient to use, and the =
"CreateParameter-list" must be in the exact order as in the SP and I =
have to set the data-type and length. In my first example I can put them =
in any order I want - I don't use every value every time.


Best regards,

/Mikael Sörensson




------=_NextPart_000_011A_01C519C8.A39D5D40
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable



charset=3Diso-8859-1">

Re: Output from SP (sql)

Mikael S wrote:
> Hello,
>
> I use some sp:s in a ms sql, and so far it looks like (this works
> fine):
>
> -----------------------------------------------------
> sql = "sp_NameOfSp @IDno = 1, @Name = 'Mikael'" (and 15 more

It is not a good idea to use "sp_" when naming a non-system stored
procedure. There is a slight performance penalty for doing so, since the
query engine will look in the Master database for a procedure with that
prefix before looking in the current database.

> values...)
>
> cmd.CommandText = sql
> cmd.Execute

There is no reason to use an explicit command object to run this sql string.
This sql string could be run using the connection's Execute method (assuming
cn is an open connection object):

cn.Execute sql,,129

(the 129 is the combination of two constants: adCmdText (1) and
adExecuteNoRecords (128). You should always tell ADO what the command type
is, and whether or not the command needs to create a recordset to retrieve
data, which it does by default unless you tell it not to)

Since it has no output parameters, and you are not interested in seeing the
return value, you can do this:

cn.sp_NameOfSp 1, "Mikael", ..., param17


You can even use this technique to open a recordset:

set rs=createobject("adodb.recordset")
cn.sp_NameOfSp 1, "Mikael", ..., param17, rs


I prefer this technique over the dynamic sql technique you've chosen since
it prevents sql injection, and eliminates the need to worry about delimiters
in the values I'm passing to the procedure.

> -----------------------------------------------------
> But know I want something in return from the SP.
> I must use: RETURN 1001 in the SP (not use: @returnvalue int OUTPUT).

Why not? There's nothing wrong with using output parameters. There's nothing
wrong with using RETURN either, it's just that most programmers use output
parameters to return data, and RETURN values to return status or error
codes.

>
>
> I have tried:
> -----------------------------------------------------
> .ActiveConnection = rs
> .CommandText = "up_CreateAbsMessage"
> .CommandType = 4
>
> .Parameters.Append .CreateParameter("RETURN_VALUE", 3, 4)
> .Parameters.Append .CreateParameter("@IDno", 3, 1, 4, "1")
> .Parameters.Append .CreateParameter("@Name", 3, 1, 4, "Mikael")
>
> .Execute
>
> lngAdded = .Parameters("RETURN_VALUE")
> -----------------------------------------------------
>
> And this works as well, but is much more inconvenient to use, and the
> "CreateParameter-list" must be in the exact order as in the SP and I
> have to set the data-type and length. In my first example I can put
> them in any order I want - I don't use every value every time.
>
I'm not sure what your question is. You have hit upon the best way to pass
arguments to a stored procedure if you need to get a RETRURN or output value
back. You could use Refresh to avoid coding the parameters collection
yourself, but that forces your code to make an extra trip to the database to
retrieve the parameter information, so Refresh is avoided.

I have written a code generator to relieve myself of this onerous task. It's
available here if you want to try it out:
http://www.thrasherwebdesign.com/index.asp?pi=links&hp=links .asp&c=&a=clear


HTH,
Bob Barrows
--
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"

Re: Output from SP (sql)

Thank you for your reply. It was very helpful.
I will look through all my sp-connections.


Best regards,

/Mikael Sörensson



"Bob Barrows [MVP]" skrev i meddelandet
news:e5CITXdGFHA.3444@TK2MSFTNGP14.phx.gbl...
> Mikael S wrote:
> > Hello,
> >
> > I use some sp:s in a ms sql, and so far it looks like (this works
> > fine):
> >
> > -----------------------------------------------------
> > sql = "sp_NameOfSp @IDno = 1, @Name = 'Mikael'" (and 15 more
>
> It is not a good idea to use "sp_" when naming a non-system stored
> procedure. There is a slight performance penalty for doing so, since the
> query engine will look in the Master database for a procedure with that
> prefix before looking in the current database.
>
> > values...)
> >
> > cmd.CommandText = sql
> > cmd.Execute
>
> There is no reason to use an explicit command object to run this sql
string.
> This sql string could be run using the connection's Execute method
(assuming
> cn is an open connection object):
>
> cn.Execute sql,,129
>
> (the 129 is the combination of two constants: adCmdText (1) and
> adExecuteNoRecords (128). You should always tell ADO what the command type
> is, and whether or not the command needs to create a recordset to retrieve
> data, which it does by default unless you tell it not to)
>
> Since it has no output parameters, and you are not interested in seeing
the
> return value, you can do this:
>
> cn.sp_NameOfSp 1, "Mikael", ..., param17
>
>
> You can even use this technique to open a recordset:
>
> set rs=createobject("adodb.recordset")
> cn.sp_NameOfSp 1, "Mikael", ..., param17, rs
>
>
> I prefer this technique over the dynamic sql technique you've chosen since
> it prevents sql injection, and eliminates the need to worry about
delimiters
> in the values I'm passing to the procedure.
>
> > -----------------------------------------------------
> > But know I want something in return from the SP.
> > I must use: RETURN 1001 in the SP (not use: @returnvalue int OUTPUT).
>
> Why not? There's nothing wrong with using output parameters. There's
nothing
> wrong with using RETURN either, it's just that most programmers use output
> parameters to return data, and RETURN values to return status or error
> codes.
>
> >
> >
> > I have tried:
> > -----------------------------------------------------
> > .ActiveConnection = rs
> > .CommandText = "up_CreateAbsMessage"
> > .CommandType = 4
> >
> > .Parameters.Append .CreateParameter("RETURN_VALUE", 3, 4)
> > .Parameters.Append .CreateParameter("@IDno", 3, 1, 4, "1")
> > .Parameters.Append .CreateParameter("@Name", 3, 1, 4, "Mikael")
> >
> > .Execute
> >
> > lngAdded = .Parameters("RETURN_VALUE")
> > -----------------------------------------------------
> >
> > And this works as well, but is much more inconvenient to use, and the
> > "CreateParameter-list" must be in the exact order as in the SP and I
> > have to set the data-type and length. In my first example I can put
> > them in any order I want - I don't use every value every time.
> >
> I'm not sure what your question is. You have hit upon the best way to pass
> arguments to a stored procedure if you need to get a RETRURN or output
value
> back. You could use Refresh to avoid coding the parameters collection
> yourself, but that forces your code to make an extra trip to the database
to
> retrieve the parameter information, so Refresh is avoided.
>
> I have written a code generator to relieve myself of this onerous task.
It's
> available here if you want to try it out:
>
http://www.thrasherwebdesign.com/index.asp?pi=links&hp=links .asp&c=&a=clear
>
>
> HTH,
> Bob Barrows
> --
> 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"
>
>