build a stored procedure using multiple row/fields from a table

• texpic

  SSCertifiable

  Points: 5887

  More actions

  January 6, 2015 at 11:47 am

  #296407

  This is an oversimplified example of something I'm trying to build. The real #Dynamic table might have 10 to 20 rows of formulas. What I'm trying to do is build a stored procedure using multiple row/fields from a table. Thanks.

  --assume these tables exist

  CREATE TABLE #Desired (CustomerId varchar(20), NetIncome numeric(11,2), Ratio numeric(11,2), NotUsed int)

  CREATE TABLE #Dynamic (NewField varchar(20), MyFormula varchar(99))

  INSERT INTO #Dynamic

  SELECT 'NetIncome', 'Revenue - Expense' UNION ALL

  SELECT 'Ratio', 'Revenue / Expense'

  --desired result is to build a stored procedure using multiple row/fields from a table

  INSERT INTO #Desired (CustomerId, NetIncome, Ratio)

  SELECT CustomerId, Revenue - Expense as NetIncome, Revenue / Expense as Ratio FROM #RawData

• stevenb 14609

  SSCommitted

  Points: 1832

  More actions

  January 6, 2015 at 1:10 pm

  #1768225

  It looks like you're talking about building some dynamic SQL.

  The general outline is to dynamically build a string that can be executed to build your result table. Here is a link that will get you started.

  http://msdn.microsoft.com/query/dev10.query?appId=Dev10IDEF1&l=EN-US&k=k(SP_EXECUTESQL_TSQL);k(SQL11.SWB.TSQLRESULTS.F1);k(SQL11.SWB.TSQLQUERY.F1);k(MISCELLANEOUSFILESPROJECT);k(DevLang-TSQL)&rd=true