0

I was reading this post Click and I realize that we can't use a stored procedure in SELECT / WHERE / HAVING statements/clauses, but we can use them in an UDF, so why does this stored procedure work and return data?

I'm using select and having in this stored procedure:

create proc person_sp (@id int)
as
begin
    select id, [name], lastname 
    from person
    group by id, [name], lastname
    having sum(id) > 3
end

and then I called

exec person_sp 1
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user3453159
  • 151
  • 1
  • 3
  • 13
  • 3
    You can't call the stored procedure _from_ a SELECT query. Eg `select * from person_sp 1` is illegal. But you can call a UDF from a SELECT query eg `select * from personfunction(1) p` – David Browne - Microsoft May 15 '20 at 16:28
  • You can't call a SP within a query, but you can issue any query within a SP (note the reversed terminology). Is that what's the question is about? – Alejandro May 15 '20 at 16:28
  • "I'm using select and having in this sp?" Is this a question??? If it is, what is the actual question? – Eric May 15 '20 at 17:14
  • @Eric I mean if we can't use SELECT/WHERE/ HAVING statement in Sp so why in my code I can use having And I didn't get an error؟ – user3453159 May 15 '20 at 17:27
  • 1
    Who says you can't use SELECT/WHERE/HAVING in sp? – Eric May 15 '20 at 17:43
  • @Eric look this https://stackoverflow.com/a/12900448/3453159 – user3453159 May 15 '20 at 17:54

1 Answers1

1

It's too long to put in comment, so I put it here. In the link, it says " We can't use SP in SELECT/WHERE/ HAVING statement." It means you can't call sp in your select/where/having statement. The statement below will error out. 

select id, [name], lastname, exec person_sp 1
from person
where sp_another > 2
group by id, [name], lastname
having sum(id) > 3
Eric
  • 3,165
  • 1
  • 19
  • 25