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

  • texpic

    SSCertifiable

    Points: 5882

    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: 1751

    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

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply