InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 10:14:13 GMT20http://www.sqlservercentral.com/Forums/Topic519473-149-1.aspx[quote][b]abinder (6/19/2008)[/b][hr]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[/quote]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.....Thu, 19 Jun 2008 10:33:45 GMTMatt Miller (#4)http://www.sqlservercentral.com/Forums/Topic519473-149-1.aspxYou'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.AlfonsThu, 19 Jun 2008 10:31:02 GMTabinder-682132http://www.sqlservercentral.com/Forums/Topic519473-149-1.aspxFrom 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:[code]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][/code]would probably have to be rewritten as :[code] 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);[/code]That entails declaring a @SQL variable outside of the loop.Wed, 18 Jun 2008 15:24:38 GMTMatt Miller (#4)http://www.sqlservercentral.com/Forums/Topic519473-149-1.aspxI 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, Alfonsabinder@hps.mdCREATE TABLE #aatemp(Employer varchar(50), [Provider TIN] int, [Provider Name] varchar(50), [Total Charge] money)declare @employer varchar(50)declare C1 cursor read_only forselect name from sys.tables where name like '%$%'open C1fetch next from C1 into @employerwhile (@@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 @employerendclose C1deallocate C1SELECT [Employer], a.[Provider TIN], a.[Provider Name], a.[Total Charge]FROM #aatemp order by Employerdrop table #aatempWed, 18 Jun 2008 15:11:09 GMTabinder-682132