SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Using a string as a table variable


Using a string as a table variable

Author
Message
abinder-682132
abinder-682132
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 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
Matt Miller (4)
Matt Miller (4)
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29347 Visits: 19002
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?
abinder-682132
abinder-682132
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 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
Matt Miller (4)
Matt Miller (4)
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29347 Visits: 19002
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?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search