inetserver asp db: 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

[quoted text, click to view]

First, read this:
http://www.google.com/groups?hl=en&lr=&c2coff=1&selm=OgYK94SgEHA.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=OVlfw%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.

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.




[quoted text, click to view]

[quoted text, click to view]

Whoops, typo

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