Returning Values from a SP to asp

[Harag]

Hi all

Win 2kpro, sql 2k, vbscrip asp iis 5

I'm currently doing something along the lines of:

dim exists
msExecutedSQL = "sp_memberexists '" & strName & "' "
lOptions
set rs = moConnection.execute (msExecutedSQL, ,lOptions)

exists=false
if not rs.eof then
if rs(0) = 1 then
exists=true
response.write "exists"
end if
end if
if not exists then
response.write "NOT exists!!!"
end if


MY SP is as follows:
CREATE PROCEDURE dbo.sp_memberexists @TestName VARCHAR(30)
AS
SET NOCOUNT ON
IF EXISTS (SELECT UserName FROM test WHERE UserName = @TestName)
select 1
else
select 0
go


now this works fine... but I've read that using "RETURN 1/0" in the SP
is better. as it doesn't create a SP.

how can I change the above asp so I can do something like:

msExecutedSQL = "sp_memberexists '" & strName & "' "
if moConnection.execute (msExecutedSQL, ,lOptions) = 1 then
response.write "exists"
else
response.write "dont exists"
end if

thanks for any help & tips.

Al

[Bob Barrows]

Harag wrote:

> Hi all
>
> Win 2kpro, sql 2k, vbscrip asp iis 5
>
> I'm currently doing something along the lines of:
>
> dim exists
> msExecutedSQL = "sp_memberexists '" & strName & "' "
> lOptions
> set rs = moConnection.execute (msExecutedSQL, ,lOptions)
>
> exists=false
> if not rs.eof then
> if rs(0) = 1 then
> exists=true
> response.write "exists"
> end if
> end if
> if not exists then
> response.write "NOT exists!!!"
> end if
>
>
> MY SP is as follows:
> CREATE PROCEDURE dbo.sp_memberexists @TestName VARCHAR(30)

Don't use "sp_" as your stored procedure prefix unless you are creating a
system stored procedure. I've seen people use "usp_". I use "np_" because
"n" is the first letter of my company's name.

> AS
> SET NOCOUNT ON
> IF EXISTS (SELECT UserName FROM test WHERE UserName = @TestName)
> select 1
> else
> select 0
> go
>
>
> now this works fine... but I've read that using "RETURN 1/0" in the SP
> is better. as it doesn't create a SP.

You mean: "it doesn't create a recordset". This is true. It is more
efficient to pass a single value back to the client than it is to assemble a
resultset and pass it to the client, which has to transform it to an ADO
recordset to allow it to be used by the caller.

>
> how can I change the above asp so I can do something like:
>
> msExecutedSQL = "sp_memberexists '" & strName & "' "
> if moConnection.execute (msExecutedSQL, ,lOptions) = 1 then
> response.write "exists"
> else
> response.write "dont exists"
> end if
>
> thanks for any help & tips.
>

You can't. To use a Return or output parameter (these are two different
things), you have to use an explicit ADO Command object. I've created a code
generator that creates the Command object code for you. You can get it at
[url]http://www.thrasherwebdesign.com/index.asp?pi=links&hp=links.asp&c=&a=clear[/url]

You'll have the source code, so you can modify it if you want it to do
anything differently (such as use different variable names for the ADO
objects).

Anyways, after the Execute method is called, the first parameter will
contain the value returned via the Return statement in the SP. So your code
would look like this"

<Command object code from code generator>
If cmd.Parameters(0) = 1 then
response.write "exists"
else
response.write "dont exists"
end if

HTH,
Bob Barrows