output parameters in stored procedures

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

output parameters in stored procedures

Author

Topic

Pumpkins_Man
Starting Member

20 Posts

Posted - 2002-04-15 : 19:48:27

hey guys,
i've got a bit of a problem. i asked one of the guys at work to write me a few stored procedures and he's set them up to return an output parameter. the problem is i dont' know how ot a) run the procedure in the query analyzer to see how it works and b)i don't know how to read an output parameter into a variable in asp.
the guy who wrote the stored procedures has gone away for three days so i'm getting no where. i was hoping you guys could show me how to use this procedure:

Create Procedure Forum_Access
	@forumID int,
	@userID int,
	@code int output
As 
	declare @access varchar(50)

	select @access = s.[Name] from
	Security_Assignments a inner join Security_Groups s on a.Group_ID = s.Group_ID
	where a.[User_ID] = @userID

	if @access <> 'Administrator' begin
		Select @access = s.[Name]
		from Forum_Security f inner join Security_Levels s on f.Security_ID = s.Security_ID
		where f.Forum_ID = @forumID and
		f.[User_ID] = @userID
	end

	Select @code = 
	CASE @access
		when 'Administrator' then 4
		when 'Moderator' then 3
		when 'Contributor' then 2
		when 'Viewer' then 1
		else 0
	end


GO

http://www.users.bigpond.com/thefullmontybronty

yoursfriend
Starting Member

5 Posts

Posted - 2002-04-16 : 02:31:37

Hi,

Try to do like this in SQL-Server(Query Analyzer), It will help to you.

Declare @CodeVal Int
Exec Forum_Access 1, 10, @CodeVal
Select @CodeVal

jackstow
Posting Yak Master

160 Posts

Posted - 2002-04-16 : 04:45:40

There's some ASP/VBScript code here that will do it for you - http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=12682

Pumpkins_Man
Starting Member

20 Posts

Posted - 2002-04-16 : 20:53:45

thanks heaps so far guys. i think i'm get the idea of how to run the stored procedure but once i've run it do i have to return the results in a reocordset? this is what i have done so far and there's an error when i try a response.write

<%@ language = vbscript %>
<% 
dim objcomm, dataconn
set dataconn = server.createobject("ADODB.Connection")
dataconn.open "DSN=intranet;uid=intranet;pwd=intranet"
Set objcomm = Server.CreateObject("ADODB.Command")
objcomm.ActiveConnection = dataconn
objcomm.CommandText = "Forum_Access" 
objcomm.CommandType = adCmdStoredProc
objcomm.Parameters.Append objcomm.CreateParameter("@forumID", adInteger, adParamInput, 4, 1) 
objcomm.Parameters.Append objcomm.CreateParameter("@userID", adInteger, adParamInput, 4, 1) 
objcomm.Parameters.Append objcomm.CreateParameter("@code", adInteger, adParamOutput, 1) 
objcomm.Execute 
%>
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body bgcolor="#FFFFFF" text="#000000">
<%=@code %>
</body>
</html>

http://www.users.bigpond.com/thefullmontybronty

rksingh024
Yak Posting Veteran

56 Posts

Posted - 2002-04-17 : 01:50:07

Hi,

Use: <%=objcomm.Parameters("@code").value%>

since it can't be accessed directly.

Ramesh Singh

jackstow
Posting Yak Master

160 Posts

Posted - 2002-04-17 : 04:18:59

You don't need a recordset if you're using output parameters
<%=objComm.Parameters("@code")%> should work
Also, remember to close the connection and kill the objects;

Set objComm = Nothing
dataconn.Close
Set dataconn = nothing

You can use the command object to fill a recordset as well like so;
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open objComm.Execute

.. but you don't need to in this situation

Pumpkins_Man
Starting Member

20 Posts

Posted - 2002-04-18 : 18:34:45

well i still haven't figured out how to do this properly yet. this is the code that i'm using just to test to see if it will work:

<%@ language = vbscript %>
<% 
dim objcomm, dataconn
set dataconn = server.createobject("ADODB.Connection")
dataconn.open "DSN=intranet;uid=intranet;pwd=intranet"
Set objcomm = Server.CreateObject("ADODB.Command")
objcomm.ActiveConnection = dataconn
objcomm.CommandText = "Forum_Access" 
objcomm.CommandType = adCmdStoredProc
objcomm.Parameters.Append objcomm.CreateParameter("@forumID", adInteger, adParamInput, 4, 1) 
objcomm.Parameters.Append objcomm.CreateParameter("@userID", adInteger, adParamInput, 4, 1) 
objcomm.Parameters.Append objcomm.CreateParameter("@code", adInteger, adParamOutput, 1) 
objcomm.execute
%>
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body bgcolor="#FFFFFF" text="#000000">
<%=objComm.Parameters("@code")%> 
</body>
</html>

i keep getting errors on these lines:

 objcomm.CommandText = "Forum_Access" 
objcomm.CommandType = adCmdStoredProc

could you guys please tell me what is incorrect with my code? this is really bugging me.

thanks heaps.

http://www.users.bigpond.com/thefullmontybronty

Pumpkins_Man
Starting Member

20 Posts

Posted - 2002-04-18 : 19:23:08

woot! i figured it out. cancel my last post. thanks for the other help guys

http://www.users.bigpond.com/thefullmontybronty

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources