Sql: Script to substitute stored procedure calls with stored procedure code

Question

I'd like at tool/script that would automatically substitute the calls to stored procedures with the code. It is better to explain with an example.

I have a code like this where a stored procedure is called twice

declare @x int, @y int
set @x = 10 
exec @y = calculate_and_insert @x

set @x = @y 
exec @y = calculate_and_insert @x

the called procedure has a code like:

create stored procedure calculate and insert @in int
as 
   declare @x int
   set @x = 10
   return @x + @in

See that it has a variable with the same name of one in the outer scope

I'd like to generate something like:

declare @x int, @y int
set @x = 10 
   declare @cai1_in int
   set @cai1_in = @x

   declare @cai1_x int
   set @cai1_x = 10
   set @y = @cai1_x + @cai1_in

set @x = @y 
   declare @cai2_in int
   set @cai2_in = @x

   declare @cai2_x int
   set @cai2_x = 10
   set @y = @cai2_x + @cai2_in

Maybe somebody already needed to do something like it. The reasoning is that I have a lot of stored procedures that I don't want to change in production, but I'd like to execute a new version of them to see the result. I'd recode them in test and generate a script.

I don't have to be done through SQL, can be done in another language. It also don't have to cover all cases.

You're trying to replace calls to stored procs with the logic inside the stored procs? — Andrew
– Andrew, Commented Oct 18, 2013 at 21:30
Wow. I certainly don't know of any way you do that through T_SQL. I guess you could write a java program or something to parse through all the scripts and make appropriate changes, but that sounds way complex. Plus, you'd have to generate all the appropriate scripts and save them as files. Sounds horrible to me. — Andrew
– Andrew, Commented Oct 18, 2013 at 21:47
I think you can do something like this with selects from the msdb tables, but you are going to hit errors pretty quickly using that route. In your example you were able to change the variable @x to @cai1_x...I don't know how you'd do that programatically. This is one of those times where I'd have to recommend re-evaluating doing it this way and see what other routes you have. — Twelfth
– Twelfth, Commented Oct 18, 2013 at 21:47
This is called a "compiler". You will find it quite hard to write one in SQL. — RBarryYoung
– RBarryYoung, Commented Oct 18, 2013 at 22:58

acfrancis · Accepted Answer · 2013-10-18 22:13:45Z

1

Why don't you create a new stored procedure called calculate_and_insert2 and just alter your script to call the new procedure. That will be a lot less error-prone than your initial suggestion.

answered Oct 18, 2013 at 22:13

acfrancis

3,70128 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

neves Over a year ago

Because this is just a simple example. There are a lot of calls. Also because I don't have permission to create nothing in the database, just to execute (don't ask).

RBarryYoung Over a year ago

@neves The more calls there are, the better acfrancis's suggestion becomes and the worse your approach is. You would be far better off to address your permissions issue. And I promise that it will take less time and effort.

Collectives™ on Stack Overflow

Sql: Script to substitute stored procedure calls with stored procedure code

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related