1

I want to loop through a bunch of different functions and execute them inside a stored procedure. For example, if I have the function names:

  • Function1
  • Function2
  • Function3

And I want to call each using the variable name, @fn, how do I do that? I already know how to store the functions inside @fn and loop using a cursor, but do not know how to execute it. The following is what I've tried:

declare @fn nvarchar(30) = 'Function1',
        @sql varchar(500);

set @sql = N'select * from dbo.' + @fn + '(101)';

exec @sql;

I've also tried exec sp_executesql @sql;

Thanks!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
lw493
  • 13
  • 1
  • 4
  • Why can't you just call the functions and forget about looping and dynamic for this? Cursors are horrible for performance. My guess is your entire procedure could stand a rewrite so it isn't stuck using a cursor. It should use set based logic whenever possible. – Sean Lange Jul 11 '16 at 20:55
  • http://stackoverflow.com/questions/5967035/using-function-as-a-parameter-when-executing-a-stored-procedure – PM 77-1 Jul 11 '16 at 20:57
  • When you tried that what happened? Did you get an error? You're selecting something and not doing anything with it. SQL really doesn't work well this way - it needs fixed metadata, i.e. columns, data types etc. should be known beforehand. Reconsider what you're trying to do it might cause far more trouble than you save. – Nick.Mc Jul 11 '16 at 23:20
  • Sorry guys, I tried to cut out all the "extras" to show the core of my problem and evidently made the question more confusing. Aleksey's suggestion solved my issue (it was trying to execute it as a stored procedure and not a function) – lw493 Jul 12 '16 at 12:35

2 Answers2

0

To execute dynamic SQL with EXEC you have to wrap EXEC's argument into parenthesis or it would think you are trying to execute stored procedure:

exec (@sql);
Aleksey Ratnikov
  • 569
  • 3
  • 11
0

You can write a dynamic query and pass that dynamic query in a paramater and call in EXEC @parameter

Follow the link it will help EXECUTE (Transact-SQL)

call another sp with output inside stored procedures sql server

Maverick
  • 1,167
  • 1
  • 8
  • 16
  • 1
    While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes. - [From Review](/review/low-quality-posts/12975777) – Rich Benner Jul 12 '16 at 07:34
  • The link that I provided is clear and will help to achieve the goals. – Maverick Jul 12 '16 at 19:24