Using a string as a table variable

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 General Discussion » Using a string as a table variable

Using a string as a table variable

Rate Topic

Display Mode

Topic Options

Author

Message

abinder-682132

Posted Wednesday, June 18, 2008 3:11 PM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, August 26, 2008 2:25 PM
Points: 10, Visits: 30

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

Post #519473

Matt Miller (#4)

Posted Wednesday, June 18, 2008 3:24 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:45 PM
Points: 7,002, Visits: 13,999

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?

Post #519481

abinder-682132

Posted Thursday, June 19, 2008 10:31 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, August 26, 2008 2:25 PM
Points: 10, Visits: 30

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

Post #520008

Matt Miller (#4)

Posted Thursday, June 19, 2008 10:33 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:45 PM
Points: 7,002, Visits: 13,999

abinder (6/19/2008)

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?

Post #520011

« Prev Topic | Next Topic »

Permissions