469,307 Members | 1,999 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,307 developers. It's quick & easy.

SQL Server Stored Procedure with Output Parameter and ASP.NET SQLCommand

im new to SQL Server and ASP.Net. Here's my problem. I have this SQL
Server stored procedure with an input parameter and output parameter

CREATE PROCEDURE [safety].[_getRCList]
@in_rc varchar(8)
@out_eList varchar(7) output
AS
select @out_eList = ecuid from _organization where rccode = @in_rc
GO

im trying to get the values from the select statement. for example, if
@in_rc is a value that has mutiple ecuids, i would like to have the
@out_eList equal to those values.

here is the asp.net code im using to get the output parameters from the
SQL Server stored procedure.

sqlComm = new sqlCommand("_getRCList",conn)
sqlComm.commandtype = commandtype.storedprocedure

'add input parameter
sqlComm.parameters.add("@in_mcuid","lxkqchy")
'add output parameter
sqlParameter = sqlComm.parameters.add("@out_eList",sqlDBtype.varc har)
sqlParameter.size = 20
sqlParameter.direction = parameterdirection.output
'execute command
dtrEList = sqlComm.executeReader(CommandBehavior.CloseConnect ion)
response.Write("<br> rows : " & dtrEList.hasrows & "<br>")
while dtrEList.read()
response.Write("<li>")
response.Write("CUID " & dtrEList(0) & "<br>")
end while
dtrEList.close()

is this the right way to get values from a stored procedure? please
help...

Jan 17 '06 #1
4 50027
Welcome Mr Not So Know it All to this newsgroup
Try looking at this article at :-
http://aspnet.4guysfromrolla.com/articles/062905-1.aspx
It should help you
Patrick

"Mr Not So Know It All" <dj****************@yahoo.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...
im new to SQL Server and ASP.Net. Here's my problem. I have this SQL
Server stored procedure with an input parameter and output parameter

CREATE PROCEDURE [safety].[_getRCList]
@in_rc varchar(8)
@out_eList varchar(7) output
AS
select @out_eList = ecuid from _organization where rccode = @in_rc
GO

im trying to get the values from the select statement. for example, if
@in_rc is a value that has mutiple ecuids, i would like to have the
@out_eList equal to those values.

here is the asp.net code im using to get the output parameters from the
SQL Server stored procedure.

sqlComm = new sqlCommand("_getRCList",conn)
sqlComm.commandtype = commandtype.storedprocedure

'add input parameter
sqlComm.parameters.add("@in_mcuid","lxkqchy")
'add output parameter
sqlParameter = sqlComm.parameters.add("@out_eList",sqlDBtype.varc har)
sqlParameter.size = 20
sqlParameter.direction = parameterdirection.output
'execute command
dtrEList = sqlComm.executeReader(CommandBehavior.CloseConnect ion)
response.Write("<br> rows : " & dtrEList.hasrows & "<br>")
while dtrEList.read()
response.Write("<li>")
response.Write("CUID " & dtrEList(0) & "<br>")
end while
dtrEList.close()

is this the right way to get values from a stored procedure? please
help...

Jan 17 '06 #2
thanks patrick
i read the article and it was helpful. it didn't mention how to return
multiple values with an output parameter. for example, say my select
statement returned four employee id numbers, how would i use an output
variable to return them? or maybe an output variable can only return
one value (as in the example from the article, the output parameter
returns one aggregate function result).

i found this in microsoft's web site

ExecuteReader Executes commands that return rows. For increased
performance, ExecuteReader invokes commands using the Transact-SQL
sp_executesql system stored procedure. As a result, ExecuteReader may
not have the desired effect if used to execute commands such as
Transact-SQL SET statements.

http://msdn.microsoft.com/library/de...classtopic.asp

maybe that is my problem. thanks again patrick for the help and further
assistance.

Jan 17 '06 #3
sql output parameters (like input) are scalar, so you can not return
mutilple values, short of concating the values into a string. you want to
return a result set containg rows.

CREATE PROCEDURE [safety].[_getRCList]
@in_rc varchar(8)
@out_eList varchar(7) output
AS
select ecuid from _organization where rccode = @in_rc
GO

this will return multiple values. in .net you can access them with a
datareader or datatable.

-- bruce (sqlwork.com)

"Mr Not So Know It All" <dj****************@yahoo.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...
im new to SQL Server and ASP.Net. Here's my problem. I have this SQL
Server stored procedure with an input parameter and output parameter

CREATE PROCEDURE [safety].[_getRCList]
@in_rc varchar(8)
@out_eList varchar(7) output
AS
select @out_eList = ecuid from _organization where rccode = @in_rc
GO

im trying to get the values from the select statement. for example, if
@in_rc is a value that has mutiple ecuids, i would like to have the
@out_eList equal to those values.

here is the asp.net code im using to get the output parameters from the
SQL Server stored procedure.

sqlComm = new sqlCommand("_getRCList",conn)
sqlComm.commandtype = commandtype.storedprocedure

'add input parameter
sqlComm.parameters.add("@in_mcuid","lxkqchy")
'add output parameter
sqlParameter = sqlComm.parameters.add("@out_eList",sqlDBtype.varc har)
sqlParameter.size = 20
sqlParameter.direction = parameterdirection.output
'execute command
dtrEList = sqlComm.executeReader(CommandBehavior.CloseConnect ion)
response.Write("<br> rows : " & dtrEList.hasrows & "<br>")
while dtrEList.read()
response.Write("<li>")
response.Write("CUID " & dtrEList(0) & "<br>")
end while
dtrEList.close()

is this the right way to get values from a stored procedure? please
help...

Jan 17 '06 #4
thx bruce. that makes a lot of sense considering my results. i
appreciate the time and effort.

Jan 17 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Dino L. | last post: by
3 posts views Thread by Bilbo | last post: by
10 posts views Thread by J. S. EDV | last post: by
7 posts views Thread by ashtek | last post: by
2 posts views Thread by jed | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.