mcraig 42832 - Friday, October 20, 2017 2:24 PM

patrickmcginnis59 10839 - Friday, October 20, 2017 1:38 PM

mcraig 42832 - Tuesday, October 17, 2017 7:09 AM

So, the basics of what I want to accomplish is simple...I have a stored procedure that has some really complex input to work.  Currently, the SP just manages by itself but the "code base" is completely re-usable only requiring a set of temporary tables and cursors to loop through them to do the work.  The problem is that latter part.  There are about 6 temporary table structures and data to go with them and they all get looped through so cursors and variables for their fields for fetching are required.  To make the stored procedure "generic", I'd rather not code these things in the stored procedure and passing them all as variables seems...well...improbably.  So my idea, like many other languages, is to find a way to "include" or "import" one script into another so I could create all the cursor variables, consumer variables and temporary tables in one script, then execute my stored procedure by passing the script name of the "configuration" settings needed and "poof".

Totally wrong track?  Teach me what I don't know?  Go back to chewing gum and programming GWBasic?

Thanks for any insight,  Mike

I have on at least one occasion had one of those "catch all" queries that would do stuff like this, so I put the particular code in one procedure that would do stuff based on a mix of parameters and then another procedure that would do the same thing each time with the results. A "main" procedure called both. The nice thing is that the "main" procedure that called the other two, before doing any calling did the temp table creation.

So its like this:

main procedure:
- accepted varying list of parameters and creates temp tables, we'll call this "main".
- call particular stored procedure with mix of parameters (lets call this "particular1"), and this particular stored procedure filled temp tables with work data depending on what parameters were passed
- call generic stored procedure (lets call this "generic1") that did the same thing each time with results using the previously generated work data

create procedure test1_variant1
as
begin
 insert #t1
 select 1, 'hi from test1_variant1'
end
go
create procedure test1_variant2
as
begin
 insert #t1
 select 1, 'hi from test1_variant2'
end
go
create procedure test1_variant3
as
begin
 insert #t1
 select 1, 'hi from test1_variant3'
end
go

create procedure test2
as
begin
 select * from #t1
end
go

create procedure testmain
as
begin
 create table #t1
  (
  testkey int,
  testdata varchar(100)
  );
 exec sp_executesql @stmt = N'exec test1_variant2';
 exec test2;
end
go


ah HA!  Exactly what I'm looking for...thanks for that !