sql server - Sharing data between stored procedures -

i have stored procedure called dbo.match. looks :

create procedure [dbo].[match] @parameterfromuser  nvarchar(30), @checkbool int  begin --some code select rowid,         percentmatch  @matches end 

this procedure being called stored procedure :

create procedure matchmotherfirstname  @motherfn nvarchar(20) , @checkbool int begin  select @constval = functionweight     dbo.functionweights    functionweights.functionid = 20;  /*     code execute `dbo.match` procedure in above procedure called `matchmotherfirstname` , retrieve `rownumber` , `percentmatch`,     insert #temp in respective fields , , calculate `percentmatch * constval`,     , insert in corresponding column called `percentage` in `#temp`  */ end  

i need execute dbo.match stored procedure in above procedure, retrieve rowid , pecrntmatch value, @constval value have above, multiply @constval , percentmatch , store in percentage column of #temp , insert results dbo.match procedure in temporary table. dbo.match returns rowid , percentmatch.

structure of temporary table:

create table #temp ( rownumber int not null, valfromuser nvarchar(30), columnname nvarchar(30), valfromfunc decimal(18, 4), funcweight decimal(18, 4),    -- @constval here percentage decimal(18, 4) not null, -- calculated value here i.e (funcweight * valfromfunc)  ); 

in #temp, need insert value of @constval calculate column , insert i.e percentmatch * contval rows inserted in execution call only. how can in above procedure in efficient way ?

edit : purpose of clarity , here doing if dbo.match function , not procedure:

  if @motherfn not null    begin      select @constval = functionweight        dbo.functionweights       functionweights.functionid = 20;     insert #temp2                    (rownumber,valfromuser,columnname,valfromfunc,funcweight,percentage)   select rowid,           @motherfn  ,         'mothersfirstname'         ,percentmatch,          @constval,          percentmatch * @constval     dbo.matchmatch(@motherfn, 0)      end 

like can retrieve value of percentmatch, @constval , multiply them both insert in #temp , how may while execute dbo.match procedure instead of calling dbo.match function ?

you have several options, ranging incredibly easy overly complicated. easiest (and efficient) ways of doing describe are:

1. don't it: include calculation in query. why need in table definition?

2. add computed column temp table when created. requires include field store "constant value" can referenced computed column. if calculation expensive and/or there lots of rows , selected (and possibly used in and/or order clauses), can make computed column persisted calculated upon insert , update updates fields referenced in computed column.

3. add computed column temp table after table has been created. allows embedding "constant value" computed column there no need [constantvalue] column. if calculation expensive and/or there lots of rows , selected (and possibly used in and/or order clauses), can make computed column persisted calculated upon insert , update updates fields referenced in computed column.

   p.s. in case find asking "why not create temp table dynamically in 1 step instead of 2 steps?": local temporary table created in dynamic sql cease exist after exec of dynamic sql. global temp table survive execution of dynamic sql, table name shared across sessions session executing code @ same time error on name conflict. in case need generate guid via newid() use global temp table name , concatenate value dynamic sql, stuck being required use dynamic sql references global temp table (including insert...exec) , more work no benefit.

test setup

if (object_id(n'tempdb..#innerproc') not null) begin   drop procedure #innerproc; end; go  if (object_id(n'tempdb..#tempresults1') not null) begin   drop table #tempresults1; end; if (object_id(n'tempdb..#tempresults2') not null) begin   drop table #tempresults2; end; if (object_id(n'tempdb..#tempresults3') not null) begin   drop table #tempresults3; end; go  create procedure #innerproc set nocount on;    select top 20 so.[object_id], so.[modify_date]     [master].[sys].[objects]   order so.[modify_date] desc; go 

option 1

create table #tempresults1 (   [objectid] int not null,   [modifydate] datetime not null );  declare @constantvalue1 int; set @constantvalue1 = 13;  insert #tempresults1 ([objectid], [modifydate])   exec #innerproc;  select 1 [test], *, dateadd(day, @constantvalue1, [modifydate]) [somecalculation] #tempresults1; 

option 2

create table #tempresults2 (   [objectid] int not null,   [modifydate] datetime not null,   [constantvalue] int null, -- added via update   [somecalculation] (dateadd(day, [constantvalue], [modifydate])) -- persisted ?? );  insert #tempresults2 ([objectid], [modifydate])   exec #innerproc;  select 2 [test], * #tempresults2;  update #tempresults2 set    [constantvalue] = 13;  select 2 [test], * #tempresults2; 

option 3

declare @constantvalue3 int; set @constantvalue3 = 13;  create table #tempresults3 (   [objectid] int not null,   [modifydate] datetime not null );  insert #tempresults3 ([objectid], [modifydate])   exec #innerproc;  select 3 [test], * #tempresults3;  -- next 3 lines done after create table , before insert, -- doing allows seeing "before" , "after" data. declare @sql nvarchar(max); set @sql = n'alter table #tempresults3 add [somecalculation] (dateadd(day, '            + convert(nvarchar(10), @constantvalue3) + n', [modifydate])); --persisted'; exec (@sql);  select 3 [test], * #tempresults3;