June 18, 2008 at 3:11 pm
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
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
June 18, 2008 at 3:24 pm
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?
June 19, 2008 at 10:31 am
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
June 19, 2008 at 10:33 am
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