Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Using a string as a table variable Expand / Collapse
Author
Message
Posted Wednesday, June 18, 2008 3:11 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Wednesday, June 18, 2008 3:24 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:41 PM
Points: 7,119, Visits: 15,009
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
Posted Thursday, June 19, 2008 10:31 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Thursday, June 19, 2008 10:33 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:41 PM
Points: 7,119, Visits: 15,009
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?
Post #520011
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse