capture SP result - SQL Server Forums

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

Transact-SQL (2000)

capture SP result

Author

Topic

henrikop
Constraint Violating Yak Guru

280 Posts

Posted - 2005-02-28 : 06:26:59

I have an application (ASP.NET) which does a lot with Stored Procedures.
I cannot change these procedures. Some of these SP's return a value (and not as an OUTPUT parameter). They return a value like

SELECT 'xxxxx@planet.nl'

How can I catch this output in a stored procedure parameter?


DECLARE @Email VARCHAR (100), @UserUid NUMERIC

EXEC spGetEmail @UserUid

Now I want to put the result scalar of spGetEmail into @Email.

How do I do that?

Henri
~~~~
Anyone who can walk to the welfare office can walk to work.
- Al Capone

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-28 : 06:38:16

use SqlCommand.ExecuteScalar()
cmd.CommandText = "EXEC spGetEmail @UserUid";
string mail = cmd.ExecuteScalar().ToString();

and then put mail into a paramter.

Go with the flow & have fun! Else fight the flow

henrikop
Constraint Violating Yak Guru

280 Posts

Posted - 2005-02-28 : 06:43:02

Hiya Mladen,

I know how to do it in ASP.NET, how do I do it in T-SQL?
Sorry for not being clear.

Henri
~~~~
Anyone who can walk to the welfare office can walk to work.
- Al Capone

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-28 : 07:33:30

ohh.... well without modifying the sproc itself you can't.
you must specify the parameter in sproc as OUTPUT and the set it a value
select @email = email from MyTable where ....

Go with the flow & have fun! Else fight the flow

henrikop
Constraint Violating Yak Guru

280 Posts

Posted - 2005-02-28 : 07:44:29

Are you sure? There must be some workaround (or something with global variables), I don't know the answer, else I would'nt be asking. But changing my SPROC by adding parameters is difficult. All the calling (ASP.NET) procedures have to be changed as well...

Henri
~~~~
Anyone who can walk to the welfare office can walk to work.
- Al Capone

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-28 : 08:03:18

ok now i'm lost...
you have a sproc that returns an e-mail into a resultset.
instead of that you want the same sproc to return that email via output parameter, yet you don't want to modify the
paramteres of the sproc???
if you add the output parameter you don't need to specify it everywhere.
i guess you could put it into a global temp table and read it from there... there's no such thing a global user defined variable in sql server.

Go with the flow & have fun! Else fight the flow

henrikop
Constraint Violating Yak Guru

280 Posts

Posted - 2005-02-28 : 08:20:24

In ASP.NET if you have a command that is a sqlCommand.CommandType = 'StoredProcedure' AND you declare an output PARAMETER which isn't included in the sqlCommand you'll get the error 'Stored procedure expects parameter @ResultScalar which is not provided'.

So if I add an OUTPUT parameter I need to add this parameter everywhere where this procedure is called.

In ASP.NET I'm not using any SELECT statements, I use the output from stored procedures and work with that and I'm using a lot of SCALAR functions.

Now I'm programming an T-SQL engine (a job which runs overnight) and I want to use these SCALAR functions. The output of this scalar stored procedures is SELECT Something rather than SET @ScalarValue = Something and declare @ScalarValue as an output parameter. (the result of a stored procedure is sometime a scalar, sometimes a recordset, sometime multiple records sets).

The thing is; I want to use the function in ASP.NET as well as in T-SQL.

My question was not asked to get an email address or something (an instance of my problem) , but to use the result of a stored procedure when this is one single value, because I need this functionality in a lot of cases (and don't want to change all these procedures).

Thx for helping anyway! I don't want to sound un-thankful, I'm just can't deal with "No, you can't"

.

Henri
~~~~
Anyone who can walk to the welfare office can walk to work.
- Al Capone

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-28 : 08:44:51

so you want something like this??


use northwind
go
create proc blah
@OrderId int
as
select 	customerid 
from 	orders 
where	orderId = @OrderId
go

create table #temp (value VARCHAR(100))
insert into  #temp
exec blah 10250
select value from #temp
go
drop proc blah