SP in select - 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

SQL Server Development (2000)

SP in select

Author

Topic

khalik
Constraint Violating Yak Guru

443 Posts

Posted - 2002-05-13 : 03:52:22

can we call a stored procedure in a select statment...
i have a example...

create procedure abc @a numeric, @b numeric , @c numeric output
as
begin
select @c=@a + @b
end

declare @d as numeric
select @d= abc 1,2,@d ---**** problem
select abc(1,2,@d) ---****problem

thanks

======================================
Ask to your self before u ask someone

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2002-05-13 : 04:31:36

-- Declare the variable to receive the output value of the procedure.
DECLARE @theoutput numeric

-- Execute the procedure and save the output value in a variable.
EXECUTE abc 1,2, @theoutput OUTPUT
PRINT @theoutput

Lookup "Returning Data Using OUTPUT Parameters" in BOL.

Nazim
A custom title

1408 Posts

Posted - 2002-05-13 : 04:47:54

khalik, you have been visiting SQLTeam from so long. and have 194 posts. but you dont ever use Forum Search . Dont get me wrong , but i never see you putting any effort from your side .

This particular question have been answered couple of times.

Herez the most recent link http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=15384 .

Dont just Ask yourself before you ask others. put some efforts to get things done.

--------------------------------------------------------------

khalik
Constraint Violating Yak Guru

443 Posts

Posted - 2002-05-13 : 06:20:16

[quote]but i never see you putting any effort from your side /quote]

thanks nazim but this time u are not correct.. Dont get me wrong u cant just blame someone.. may be i was not able to express the whole thing it depends....

i do use search , but if i not able to find then only i post....

i have seen the post but what i was looking for as i posted above..
i need to access a stored procedure by query...

the comple one i was trying was the data from sp and something else together.....

In Oracle, we can call functions in a query.
example, select schema.payday(ts_date) from pay_table.
some thing simlar by which we can combine data from sp and table (physical or derived)

thanks

======================================
Ask to your self before u ask someone

khalik
Constraint Violating Yak Guru

443 Posts

Posted - 2002-05-13 : 06:24:46

if i am not wrong i think i found it.. my early posting i talk about function... i think the function will do it.. right..
i have link if any one wanna check and confirm it...
http://www.sqlteam.com/item.asp?ItemID=979

thanks

======================================
Ask to your self before u ask someone

Nazim
A custom title

1408 Posts

Posted - 2002-05-13 : 08:42:48

ok, Am glad that i was wrong

. My intention wasnt hurting your feeling but wanted just to make you realise that its always better therez something called forum search(Anywayz you said you already use it) .

quote:

thanks nazim but this time u are not correct.. Dont get me wrong u cant just blame someone.. may be i was not able to express the whole thing it depends....

Coming to your question , if you dont use Sql2000 you can still call a sp in a select

SELECT a.*
FROM OPENROWSET('SQLOLEDB','servername';'sa';'password',
'exec mydatabase.dbo.sp_yoursp') AS a

--------------------------------------------------------------

khalik
Constraint Violating Yak Guru

443 Posts

Posted - 2002-05-13 : 22:56:50

thanks nazim...

quote:
SELECT a.*
FROM OPENROWSET('SQLOLEDB','servername';'sa';'password',
'exec mydatabase.dbo.sp_yoursp') AS a

yea if i am not using sql 2k i can call sp in select...
but that is similar to exec... what is was looking for is share data between the stored procedure and table...
in a single select statment using a table and sp is it possible..
i think using function it possible... if i am not wrong.. but is the same possible with stored procedure... in sql 2k and 7

======================================
Ask to your self before u ask someone

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-05-14 : 00:21:01

The other way of doing it is inserting into a temp table.

I.E.



Create Table #mytemp (
 col1 int,
 col2 int
)
--whatever the expected output is

Insert into #mytemp
Exec MyProcedure

Select * From #mytemp

Drop table #mytemp

That will work in SQL 7

Damian

khalik
Constraint Violating Yak Guru

443 Posts

Posted - 2002-05-14 : 04:35:22

thanks Merkin

if we inserting the output into a temp table. we cannot link with the existing table data...

is somthing like calling
Insert into #mytemp
Exec MyProcedure

early i my posting the link talk about functions
instead of use sp in select can we use function by passing the fileds paramater ... something like this

select custid,name,comm(custid) from customer

where comm is function which takes custid as parameter and return the commision for every row....

this is just to confirm is this possible.... did try out yet need some time..

======================================
Ask to your self before u ask someone

Nazim
A custom title

1408 Posts

Posted - 2002-05-14 : 08:46:43

AFAIK you can ONLY do this with Functions. Sp's wouldnt work here.

quote:

select custid,name,comm(custid) from customer

--------------------------------------------------------------

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-05-14 : 08:53:18

quote:

if we inserting the output into a temp table. we cannot link with the existing table data...

Are you smoking drugs ??????

You are calling a STORED PROCEDURE! Of course you can not "link with" table data. A stored procedure executes some code and (potentially) returns some results. They are already removed from base table data by the time you get the results.

I think you might be better off using a view or something. But as usual, you haven't given the proper background so I am left guessing. Well, trying to guess anyway.

Damian

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources