Using a string as a table variable

  • I need to repeat a portion of a procedure for many tables in a database where nothing changes but the name of the table. Rather than hardcode the procedure 100's of times, I thought I'd use a WHILE statement as below. However, I can't get this to work. Is there any way I can do this? When I run the script below, I get an error stating that I need to DECLARE a table variable. When I do this, I get an error that states that I need to DECLARE a scalar variable. Is hard coding the only solution? It doesn't seem very elegant (and beside, it's very time consuming ... very, very): Thanks, Alfons

    abinder@hps.md

    CREATE TABLE #aatemp(Employer varchar(50), [Provider TIN] int, [Provider Name] varchar(50), [Total Charge] money)

    declare @employer varchar(50)

    declare C1 cursor read_only for

    select name from sys.tables where name like '%$%'

    open C1

    fetch next from C1 into @employer

    while (@@fetch_status <> -1)

    begin

    if (@@fetch_status <> -2)

    begin

    insert into #aatemp(Employer, [Provider TIN], [Provider Name], [Total Charge])

    SELECT @employer, [Provider TIN], [Provider Name], sum([Total Charge]) [Total Charge]

    FROM quotename(@employer) inner JOIN

    tblProviderTaxIdEntity ON [Provider TIN] = tblProviderTaxIdEntity.vcTaxId_Number

    where cast([Provider TIN] as int)like '39%'

    and [Total Charge] > 0

    group by [Provider TIN], [Provider Name]

    order by [Provider TIN]

    end

    fetch next from C1 into @employer

    end

    close C1

    deallocate C1

    SELECT [Employer], a.[Provider TIN], a.[Provider Name], a.[Total Charge]

    FROM #aatemp order by Employer

    drop table #aatemp

  • From what I can tell - you're trying to do dynamic SQL without actually setting it up as actually dynamic SQL.

    Your insert is supposed to pull dynamically fron a different table each time, right?

    This entire thing would need to become a dynamic SQL call:

    insert into #aatemp(Employer, [Provider TIN], [Provider Name], [Total Charge])

    SELECT @employer, [Provider TIN], [Provider Name], sum([Total Charge]) [Total Charge]

    FROM quotename(@employer) inner JOIN

    tblProviderTaxIdEntity ON [Provider TIN] = tblProviderTaxIdEntity.vcTaxId_Number

    where cast([Provider TIN] as int)like '39%'

    and [Total Charge] > 0

    group by [Provider TIN], [Provider Name]

    order by [Provider TIN]

    would probably have to be rewritten as :

    set @SQL='insert into #aatemp(Employer, [Provider TIN], [Provider Name], [Total Charge])

    SELECT @employer, [Provider TIN], [Provider Name], sum([Total Charge]) [Total Charge]

    FROM ' + quotename(@employer)+' inner JOIN

    tblProviderTaxIdEntity ON [Provider TIN] = tblProviderTaxIdEntity.vcTaxId_Number

    where cast([Provider TIN] as int)like '''39%'''

    and [Total Charge] > 0

    group by [Provider TIN], [Provider Name]

    order by [Provider TIN]'

    Exec(@SQL);

    That entails declaring a @SQL variable outside of the loop.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • You're correct, Matt. I have this aversion to writing dynamic SQL although I have used it in the past. It is awkward at best and I have read many reasons why not to use it (although I feel some of the negatives are overstated). I thought there might be some kind of magic I have overlooked. In retrospect, I will use it since I don't think I have a real alternative.

    BTW, you have one too many apostrophes around the %39% on each side.

    Thanks for your response to this question and also my last posting on Aging Reports.

    Alfons

  • abinder (6/19/2008)


    You're correct, Matt. I have this aversion to writing dynamic SQL although I have used it in the past. It is awkward at best and I have read many reasons why not to use it (although I feel some of the negatives are overstated). I thought there might be some kind of magic I have overlooked. In retrospect, I will use it since I don't think I have a real alternative.

    BTW, you have one too many apostrophes around the %39% on each side.

    Thanks for your response to this question and also my last posting on Aging Reports.

    Alfons

    Very likely on the apostrophes - I didn't try to put anything together to validate.

    Otherwise - you're welcome! Good to hear that it helped.....

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Viewing 4 posts - 1 through 3 (of 3 total)

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