Possible Duplicate:
SELECT FROM stored procedure?
How should I include a stored procedure in a T-SQL syntax?
select * from CITY_TABLE C where
COUNT_PEOPLE_PROCEDURE(C.ID) > 3
3 Answers
You cannot include stored procedure into query like this.
Instead, you can make scalar function
create function COUNT_PEOPLE_FUNCTION
(
@ID int
)
returns int
as
begin
declare @Result int
<... your code here ...>
return @Result
end
and then
select * from CITY_TABLE C where COUNT_PEOPLE_FUNCTION(C.ID) > 3
But, actually, it may slow your query, because scalar function will be called for each row independently.
6 Comments
serhio
I don't see any difference in select with my variant...
roman
the only difference is that you have to create
function and not procedure
Aaron Bertrand
@serhio look closer... that's a function not a procedure.
serhio
ok, bold function is better )
serhio
To remark, there is a need to specify
DBO. FUNCTION_NAME |
As per your comment, if you need to get the only records which have n occurrencies, you could use a subquery like this:
SELECT * FROM CITY_TABLE WHERE IDPerson IN (
SELECT IDPerson FROM CITY_TABLE GROUP BY IDPerson HAVING COUNT(*) > 3
)
Obviously i cannot know the exact column name, but this is the idea.
5 Comments
serhio
thank you, but I ask about stored procedures, not about a way to bypass it...
LittleSweetSeas
IMHO you can't use SP that way... you should use a function as stated above, with a big performance hit on large data sets.
serhio
I have a complicated select with multiple JOINs and I use this in multiple places in the sql... So I need to store it somewhere to use.
LittleSweetSeas
Ok, but your SP needs a parameter that is unique for each row of your initial select, is it correct? If so, you need an inline function, or a subquery.
LittleSweetSeas
However, playing with temporary tables & subqueries you could get a big performance improvement if compared to a scalar function.
As others already said you cannot use a stored procedure like that, i suggest you to use a function if you can; If you can't use a function (and even if you can) i suggest you this page as a useful reading:
2 Comments
serhio
fortunately, I don't need to share data between SP, but thank you anyway for the article.
fnurglewitz
actually it could be useful, it says "How can I use the result set from a stored procedure in a SELECT statement?", by the way, as everybody says, you can't use SPs like the way you are using in your example
SELECT ... WHERE EXEC someproc...