CODEWITHSUNDEEP
t-sqlstored-procedurestemp-tables
user1440675
user1440675

Reputation: 343

TSQL - Passing fields from table into stored procedure then storing result in temp table

I'm working with a client who has a stored procedure with about a dozen parameters.

I need to get the parameter values from tables in the database, then feed these into the stored procedure to get a number value. I then need to join this value to a SELECT statement.

I know that I have to build a temp table in order to join the SP results with my select statement, but this is all new to me and could use some help. Mostly focusing on how to feed field values into the SP. I would also like the Temp table to contain a couple of the parameters as fields so I can join it to my select statement.

any and all help is appreciated.

Thank You

Upvotes: 0

Views: 1514

Answers (2)

HABO
HABO

Reputation: 15816

This is a comment that is better formatted as an answer.

You don't need to create a temporary table, or table variable, to be able to join a numeric result with other data. The following demonstrates various curiosities using SELECTs without explicitly creating any tables:

declare @Footy as VarChar(16) = 'soccer'
select * from (
  select 'a' as Thing, 42 as Thingosity
  union all
  select *
    from ( values ( 'b', 2 ), ( 'c', 3 ), ( @Footy, Len( @Footy ) ) ) as Placeholder ( Thing, Thingosity )
  ) as Ethel cross join
  ( select 42 as TheAnswer ) as Fred

Upvotes: 0

GilM
GilM

Reputation: 3761

You can capture the parameter values in declared variables. Something like:

DECLARE @Parm1 int, @Parm2 varchar(50) -- Use appropriate names and datatypes

SELECT @Parm1 = Parm1ColumnName, @Parm2=Parm2ColumnName
FROM TableWithParmValues
-- Include a WHERE condition if appropriate

DECLARE @ProcOutput TABLE(outputvalue int) -- use appropriate names and datatypes to match output

INSERT @ProcOuptut
EXECUTE MyProc @ProcParm1 = @Parm1, @ProcParm2 = @Parm2 -- Use appropriate names

Then use the @ProcOutput temp table, and parameter variables as you need with your SELECT.

Upvotes: 2

Related Questions