Adapt SQL query in a while loop

Question

I have been spending a fair amount of time researching a method to adapt an sql query, while in a loop in order to bring back from multiple tables.

The one method I came across that makes this possible would be executing the query as a loadstring, then you could adapt the query each time the loop runs ( as explained via this link: https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql ).

To be more specific, I am attempting to run a rather large query, which loops through multiple databases - however each database has a branch number, such as A, B, C, D, E etc.. So each time I execute the query I am using joins to go to all the databases I need from A. In order to make this work, I would need to copy and paste this entire 500 line query, over 5 times in order to cover every branch.

The method using loadstring would end up being similar to this:

DECLARE process varchar(max) = 'select * from Vis_' + Branch[i] + '_Quotes' exec(process)

Is there a better method to adapt your query search while its running?

mysql != sql-server, please don't tag products not involved. Why do you think "the varchar max limit is likely to be exceeded"? What do you think the limit is? — HoneyBadger
– HoneyBadger, Commented Oct 12, 2017 at 8:56
You're aware that the limit in varchar(max) is around 2gb of data in one cell right? — Rich Benner
– Rich Benner, Commented Oct 12, 2017 at 8:57
not sure if this would be of any use: EXECUTE master.sys.sp_MSforeachdb details here:weblogs.sqlteam.com/joew/archive/2008/08/27/60700.aspx, possibly not as you want to adapt the query each time, but this allows you to run the same query on each db on your server — Tanner
– Tanner, Commented Oct 12, 2017 at 9:02
I'm not getting the statements "however each database has a branch number" and "So each time I execute the query I am using joins to go to all the databases I need from A". Are all used tables in one database named xx_A_xx and the next database xx_B_xx, ... etc? Do you join between databases or is one query just using tables in one database? — mortb
– mortb, Commented Oct 12, 2017 at 9:19
In one database, there are many tables like "Vis_A_Quote, Vis_A_Pol, Vis_A_Client" and "Vis_B_Quote, Vis_B_Pol, Vis_B_Client", so for each branch there are many tables in the said database. — Hex
– Hex, Commented Oct 12, 2017 at 9:39

pcdev · Accepted Answer · 2017-10-13 04:03:03Z

Here is one example of how this might be used. It's not clear if this fits your requirements, but it appears that dynamic SQL is new to you so I've supplied an example that includes both looping and passing in parameters safely. This is untested, but hopefully should get you on the right track.

This assumes you have an existing table of branches with the corresponding branch codes (ideal, as then the script doesn't need updating when adding/disabling/removing a branch). If you don't, then you could always create a table variable and insert branches at the top of your script:

declare @sql varchar(max),
        @BranchCode nvarchar(10) = '',
        @param1 int,
        @param2 nvarchar(10);

while 1=1 begin
  set @BranchCode = 
    (select top 1 Code from Branch where Active = 1 and Code > @BranchCode order by Code)
  if @BranchCode is null break;
  set @sql = @sql + 'select * from Vis_' + @BranchCode + '_Quotes
                     where col1 = @param1 and @col2 like @param2
' -- notice extra linebreak (or space) added to separate each query
end

exec sp_executesql @sql, 
  '@param1 int, @param2 nvarchar(10), ...', -- parameter definitions
  @param1, @param2, ... -- any additional parameters you need to safely pass in

Collectives™ on Stack Overflow

Adapt SQL query in a while loop

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related