Using Output Parameters from SQL Server Stored Procedure to ASP.NET SQLCommand

  • Thread starter Thread starter Mr Not So Know It All
  • Start date Start date
M

Mr Not So Know It All

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.varchar)
sqlParameter.size = 20
sqlParameter.direction = parameterdirection.output
'execute command
dtrEList = sqlComm.executeReader(CommandBehavior.CloseConnection)
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...
 
Hi,

You can return result sets from stored procedure. You can assign one value
to the output parameter not a set of values as you're doing it now. Please
also note that when you use DataReader, value of the out parameter becomes
available after you closing it. Modify stored proc as follows:

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

Then process the result set:
sqlComm = new sqlCommand("_getRCList",conn)
sqlComm.commandtype = commandtype.storedprocedure

'add input parameter
sqlComm.parameters.add("@in_rc","lxkqchy")
'execute command
dtrEList = sqlComm.executeReader(CommandBehavior.CloseConnection)
response.Write("<br> rows : " & dtrEList.hasrows & "<br>")
while dtrEList.read()
response.Write("<li>")
response.Write("CUID " & dtrEList(0) & "<br>")
end while
dtrEList.close()

If you want to use output parameters here's an example

CREATE PROCEDURE [safety].[_getRCList]
@in_rc varchar(8),
@Count int OUTPUT -- it's just an example :)
@
AS
set @Count = (SELECT Count(IdColumn) FROM _organization where rccode = @in_rc
select ecuid from _organization where rccode = @in_rc
GO

and vb.net code

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

'add input parameter
sqlComm.parameters.add@in_rc","lxkqchy")
'add output parameter
sqlParameter = sqlComm.parameters.add("@Count",sqlDBtype.Int)
sqlParameter.direction = parameterdirection.output
'execute command
dtrEList = sqlComm.executeReader(CommandBehavior.CloseConnection)
response.Write("<br> rows : " & dtrEList.hasrows & "<br>")
while dtrEList.read()
response.Write("<li>")
response.Write("CUID " & dtrEList(0) & "<br>")
end while
dtrEList.close()
' obtain output value after reader closing the reader
dim OutputValue as Integer = Convert.ToInt32(dtrEList("@Count"))


Hope my example is not so messy as it looks like :]
--
Milosz Skalecki
MCP, MCAD


Mr Not So Know It All said:
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.varchar)
sqlParameter.size = 20
sqlParameter.direction = parameterdirection.output
'execute command
dtrEList = sqlComm.executeReader(CommandBehavior.CloseConnection)
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...
Click to expand...
 
Back
Top