narendra9559

 Profile Answers by narendra9559 

• May 24th, 2011

 

If you find yourself using the same query over and over again, it would put into a stored procedure.

jayshree13

 Profile Answers by jayshree13 Questions by jayshree13

• May 25th, 2011

 

Hi Narendra,

Thanks for answering the question.But I would like to knw in which scenario we will go for function?

narendra9559

 Profile Answers by narendra9559 

• May 25th, 2011

 

Hi,

suppose you want top 3 salaries from emp table.

syntax for top 3 salaries:

select distinct top (3) (sal) from emp order by sal desc

we have to use this query dialy or monthly.Means have to use more times. That's why i am putting this query into stored procedure.

create proc usp_topsal
as
select
distinct top (3) (sal) from emp order by sal desc


exec usp_totalsal

we get top3 salaries from emp table. But no one want either top3 or top4

i want top n salaries, on that time i pass one input variable into stored procedure

create proc usp_topsal(@n int)
as
select
distinct top (@n) (sal) from emp order by sal desc


exec usp_totalsal 4

ptmich

 Profile Answers by ptmich 

• May 28th, 2012

 

It is best to go for a function when a value needs to be computed but a stored procedure is useful when you need to execute business logic.

krishna

• Dec 17th, 2015

 

When there is return value then we use function else we use procedure and for DMLs also