How to return stored procedure"s result

Hi

I have a stored procedure assigns different variables and want to return to
asp page. How can I do it?

Here is part of my sp.
------------------------------------------
IF Exists(Select * from CFSecurity..Secure where UserID = @UserID)
Select @SuntronAccount = 1
Else
Select @SuntronAccount = 0

IF Exists(Select * from CIA_Group where SuntronSite = @SuntronSite AND
CIACoordinatorUserID LIKE '%'+@UserID+'%')
Select @SiteCoordinator = 1
Else
Select @SiteCoordinator = 0

IF Exists(Select * from CIA_Department where SuntronSite = @SuntronSite AND
CIADepartment = @CIADepartment AND CIADeptManagerID LIKE '%'+@UserID+'%')
Select @SiteDeptManager = 1
Else
Select @SiteDeptManager = 0

----------------------------------------------------------

In my asp, I would like to display these three results. Anyone know the asp
code?

I did something like this but not successful:

ado.Open strConn
set rs = ado.execute("EXEC dbo.mysp")

response.write rs("@SuntronAccount ")
response.write rs("@SiteCoordinator ")
response.write rs("@SiteDeptManager")

Thanks,



--


Lin Ma

Re: How to return stored procedure"s result

Lin Ma wrote:
> Hi
>
> I have a stored procedure assigns different variables and want to
> return to asp page. How can I do it?
>
> Here is part of my sp.
> ------------------------------------------
> IF Exists(Select * from CFSecurity..Secure where UserID = @UserID)
> Select @SuntronAccount = 1
> Else
> Select @SuntronAccount = 0
>
> IF Exists(Select * from CIA_Group where SuntronSite = @SuntronSite AND
> CIACoordinatorUserID LIKE '%'+@UserID+'%')
> Select @SiteCoordinator = 1
> Else
> Select @SiteCoordinator = 0
>
> IF Exists(Select * from CIA_Department where SuntronSite =
> @SuntronSite AND CIADepartment = @CIADepartment AND CIADeptManagerID
> LIKE '%'+@UserID+'%') Select @SiteDeptManager = 1
> Else
> Select @SiteDeptManager = 0
>
> ----------------------------------------------------------
>
> In my asp, I would like to display these three results. Anyone know
> the asp code?
>
> I did something like this but not successful:
>
> ado.Open strConn
> set rs = ado.execute("EXEC dbo.mysp")
>
> response.write rs("@SuntronAccount ")
> response.write rs("@SiteCoordinator ")
> response.write rs("@SiteDeptManager")
>

First, read this:
http://www.google.com/groups?hl=en&lr=&c2coff=1&selm=OgYK94S gEHA.4092%40TK2MSFTNGP10.phx.gbl

Then, after you decide which method you wish to use to return the results,
read this:
http://groups.google.com/groups?hl=en&lr=&c2coff=1&selm=OVlf w%235sDHA.1060%40TK2MSFTNGP12.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.

Re: How to return stored procedure"s result

Several problems here.

(a) SELECT @... = something only assigns values, it does not return results
anywhere (what do you see when you run the procedure in Query Analyzer???)

(b) you cannot reference @... variables from ASP. These are local to the
stored procedure only.

(c) if you were doing it this way, you would need to use rs.nextrecordset()
to retrieve the results from each SELECT. But I have a better suggestion.


CREATE PROCEDURE dbo.doStuff
@userID INT
AS
BEGIN
SET NOCOUNT ON

DECLARE @SuntronAccount BIT, @SiteCoordinator BIT, @SiteDeptManager BIT

SELECT @SuntronAccount=0, @SiteCoordinator=0, @SiteDeptManage=0

IF EXISTS (SELECT 1 FROM CFSecurity ... ) SET @SuntronAccount = 1

IF EXISTS (SELECT 1 FROM CIA_Group ... ) SET @SiteCoordinator = 1

IF EXISTS (SELECT 1 FROM CIA_Department ... ) SET @SiteDeptManager = 1

SELECT suntronAccount = @suntronAccount,
siteCoordinator = @SiteCoordinator,
siteDeptManager = @siteDeptManager

END
GO

Now from ASP:

set rs = ado.execute("EXEC dbo.doStuff @userID=" & userID)
' why not be like normal people here and use "conn" not "ado"
response.write rs("suntronAccount ")
response.write rs("siteCoordinator ")
response.write rs("siteDeptManager")

or just

response.write rs(0)
response.write rs(1)
response.write rs(2)

--
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.




"Lin Ma" wrote in message
news:u1OyAXjNFHA.1040@TK2MSFTNGP12.phx.gbl...
> Hi
>
> I have a stored procedure assigns different variables and want to return
to
> asp page. How can I do it?
>
> Here is part of my sp.
> ------------------------------------------
> IF Exists(Select * from CFSecurity..Secure where UserID = @UserID)
> Select @SuntronAccount = 1
> Else
> Select @SuntronAccount = 0
>
> IF Exists(Select * from CIA_Group where SuntronSite = @SuntronSite AND
> CIACoordinatorUserID LIKE '%'+@UserID+'%')
> Select @SiteCoordinator = 1
> Else
> Select @SiteCoordinator = 0
>
> IF Exists(Select * from CIA_Department where SuntronSite = @SuntronSite
AND
> CIADepartment = @CIADepartment AND CIADeptManagerID LIKE '%'+@UserID+'%')
> Select @SiteDeptManager = 1
> Else
> Select @SiteDeptManager = 0
>
> ----------------------------------------------------------
>
> In my asp, I would like to display these three results. Anyone know the
asp
> code?
>
> I did something like this but not successful:
>
> ado.Open strConn
> set rs = ado.execute("EXEC dbo.mysp")
>
> response.write rs("@SuntronAccount ")
> response.write rs("@SiteCoordinator ")
> response.write rs("@SiteDeptManager")
>
> Thanks,
>
>
>
> --
>
>
> Lin Ma
>
>

Re: How to return stored procedure"s result

> SELECT @SuntronAccount=0, @SiteCoordinator=0, @SiteDeptManage=0

Whoops, typo

SELECT @SuntronAccount=0, @SiteCoordinator=0, @SiteDeptManageR=0