CODEWITHSUNDEEP
sql-servert-sqlstored-proceduressql-server-2012
SVS
SVS

Reputation: 103

Calling procedure inside a function

I'm trying to combine procedures and functions to learn more about them.

I have a table called customer with few columns (the column I'll be using is sal).

I created a function to check who all have salary less then 25000. If the above case renders rows then I call the procedure from my function.

The procedure updates the sal (sal = sal + 1000) and returns rowcount.

create procedure Taxrefund2(@taxr int) as
begin
    update customer 
    set Balance=Balance + @taxr

    return @@rowcount
end

create function taxfunc()
as
begin
   declare @salary table(sal decimal(10,2))

   set @salary = (select sal from customer)

   declare @x int=0

   if @salary < 25000
      exec @x = taxrefund2(1000)

   return @x

   print 'the no of customers who got tax redeemption is :' +cast(@x as varchar(10))

When I compile my function I am getting errors:

Msg 156, Level 15, State 1, Procedure taxfunc, Line 2
Incorrect syntax near the keyword 'as'.

Msg 102, Level 15, State 1, Procedure taxfunc, Line 8
Incorrect syntax near '1000'.

Msg 178, Level 15, State 1, Procedure taxfunc, Line 9
A RETURN statement with a return value cannot be used in this context.

Msg 102, Level 15, State 1, Procedure taxfunc, Line 10
Incorrect syntax near ')'.

Can someone explain me what wrong have I done either in the code or the concept?

Upvotes: 1

Views: 3594

Answers (1)

goofyui
goofyui

Reputation: 3492

You cannot call a stored procedure directly from User defined Function.

How to call a stored procedure from a user defined function In SQL Server 2000

Upvotes: 1

Related Questions