exec sp in a select statement - 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)

exec sp in a select statement

Author

Topic

kling
Starting Member

12 Posts

Posted - 2003-08-28 : 15:58:53

Hello,

Can I have a statement in my stored procedure sp_A as following?

select a.col1, b.col3, (exec sp_B b.col2)
from tableA a joint tableB b on a.col1=b.col1

sp_B always return only one field. Any workaround, suggestions are appreciated.

- Kevin

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-08-28 : 16:03:02

No you can not do that. You could change sp_B, so that it is a UDF:

SELECT a.col1, b.col3, dbo.udf_B(b.col2)
FROM TableA a
JOIN TableB b ON a.col1 = b.col1

BTW, don't name any objects with sp_. SQL Server will check in the master database first to see if the object exists there before checking the current database. The recommendation is to use usp_, which stands for user stored procedure. For UDFs, I recommend udf_.

Tara

TSQLMan
Posting Yak Master

160 Posts

Posted - 2003-08-28 : 23:25:45

Does SP_B have parameters? If so then use a temporary table, and select the results of QueryB, and then join it with query a. If not then just use a view for one of the queriies. Or you could use queryB as a nested query.

Eg.1

create table #temp

(
column1 char(10),
column2 char(10),
column3 char(10) -- or whatever data type
)

insert into #temp

select Column1,Column2,column3
from tableB

Select * from tableA a

inner join (select column1,column2,column3
from #temptable t
) dt

on a.column1 = dt.column1

--end

It is a little late but that should work. If I am missing something feel free to e-mail, or re-post.

--Always a student

nr
SQLTeam MVY

12543 Posts

Posted - 2003-08-29 : 01:34:10

Even if you could do that it wouldn't be a good idea for anything but small resultsets as it would have to call the SP for each row returned (similar arguement for a function - but check the query plan, it may work on distinct values, also a function is limitted in functionality).

I take it you have an SP which works on a single value and returns the same.
If you want to keep this processing in the SP then maybe get it to work on a temp table instead. Thcalling SP populates a temp table with Col1, Col3, Col2 and an extra column for the subsidiary SP result. It then calls the SP which uses Col2 to update the extra column. On return the control SP just selects from the temp talble.

You could maybe call an SP as you wish to in your example by using openquery or openrowset but I would not advise it.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

kling
Starting Member

12 Posts

Posted - 2003-08-29 : 21:37:29

Thank you very much for your help!

I was just worried that temp tables will take up too much memory. Sounds like it is a common way to handle this kind of operations!

BTW, I would like to get some input on whether my sp_B is done right or if it make sense! What stored procedure sp_B does is that it will query a table and return the first column of the first two rows.

The query is based on a sequence of three conditions/where clauses. Return rows from the first condition need to be at top of the result set, followed by the second result set, and followed by the third one.

My statement is like following:

set @@rowcount 2
declare cur cursor for
select col1 from tableA where conditionA
union
select col1 from tableA where conditionB and not conditionA
union
select col1 from tableA whee conditionC and not conditionB
open cur
fetch next from cur into @output1
fetch next from cur into @output2
close cur
deallocate cur
select (output1+@output2)
set @@rowcount 0

Any input would be greatly appreciated!

-Kevin

nr
SQLTeam MVY

12543 Posts

Posted - 2003-08-30 : 01:30:58

!!!
try
set rowcount 2
select @output = coalesce(@output,'') + col1
from
(select col1 from tableA where conditionA
union
select col1 from tableA where conditionB and not conditionA
union
select col1 from tableA whee conditionC and not conditionB
) a
set rowcount 0
select @output

This will not produce reliable results though as you do not have an order by clause so the 2 rows selected will depend on how the server retrieves records which can change from query to query.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

kling
Starting Member

12 Posts

Posted - 2003-08-31 : 02:39:37

Great! It is so helpful! You all are so informative!

Sorry, I did not mention that I have a column to help me to get the result in the order I wanted ...

(select 0 as outedorder, col1 from tableA where conditionA
union
select 1 as outedorder, col1 from tableA where conditionB and not conditionA
union
select 2 as outedorder, col1 from tableA whee conditionC and not conditionB
)

Does it make more sense?

- Kevin

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-08-31 : 11:39:19

use union all instead of union for performance boost since there are no duplicates in your three sets.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources