Trying to Avoid Dynamic SQL

  • I am constructing a query. It will pull data from 10 different databases. Rather than write the code then different times, I am using a cursor to build the query. However, the only was I can think of to do it is to use dynamic SQL. I don't mind using it, but it has its limitations in this application. Here is a sample:

    IF OBJECT_ID('TmpVendDBName', 'U') IS NOT NULL DROP TABLE TmpVendDBName

    CREATE TABLE TmpVendDBName (BusinessUnit NVARCHAR(100), VendDBName NVARCHAR(100))

    INSERT INTO TmpVendDBName SELECT 'Australia','AUS_DB';

    INSERT INTO TmpVendDBName SELECT 'Brazil','BRL_DB';

    INSERT INTO TmpVendDBName SELECT 'United States','US_DB';

    DECLARE @BUName NVARCHAR(100)

    DECLARE @DBName NVARCHAR(100)

    DECLARE VendInvCursor CURSOR FOR

    SELECT BusinessUnit, VendDBName FROM TmpVendDBName

    OPEN VendInvCursor

    FETCH NEXT FROM VendInvCursor INTO @BUName, @DBName

    WHILE @@FETCH_STATUS = 0

    BEGIN

    DECLARE @SQLExecA NVARCHAR(MAX)

    SET @SQLExecA ='

    SELECT '' + @BUName + '' AS BusinessUnit

    , Item

    , ItemDescription

    FROM ' + @DBName + '.ProductTable

    WHERE ItemStatus = ''Active''

    '

    PRINT(@SQLExecA)

    FETCH NEXT FROM VendInvCursor INTO @BUName, @DBName

    END

    CLOSE VendInvCursor

    DEALLOCATE VendInvCursor

    Is there a way to write this in such a way that I don't have to use dynamic SQL?

    Thanks.

    Steve

  • Either you write the 10 queries or you use Dynamic SQL. You could use a procedure called sp_MSforeachdb (which is undocumented) or Aaron Bertrand's version http://sqlblog.com/blogs/aaron_bertrand/archive/2010/12/29/a-more-reliable-and-more-flexible-sp-msforeachdb.aspx

    What limitations are you concerned about?

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • There are two limitations that I see. First, the queries are fairly large, so I bump up against the max. I know I can split it up, but it's a hassle. The second is that it makes it difficult to modify. When I extract a piece of code to modify and test it, I have to take out the extra quote marks and replace the parameters with objects, then reverse the process when I need to reinsert it. It takes time, and opens it up for mistakes.

  • sdownen05 (4/13/2015)


    There are two limitations that I see. First, the queries are fairly large, so I bump up against the max. I know I can split it up, but it's a hassle. The second is that it makes it difficult to modify. When I extract a piece of code to modify and test it, I have to take out the extra quote marks and replace the parameters with objects, then reverse the process when I need to reinsert it. It takes time, and opens it up for mistakes.

    There is virtually no max for dynamic SQL upto NVARCHAR(MAX). That's a billion characters. And if you write it correctly, you won't have any problems modifying it. The way to write this one correctly is to make a view against the tables in the different databases and then just query the view. The underlying indexes will be used. Your query would then not need to be dynamic at all.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff,

    I guess I was confused about NVARCHAR(MAX). I thought the max was 4000 characters, because when I print the results of my dynamic query it will cut it off. I guess it is a problem with the PRINT function and not the maximum value of NVARCHAR.

    As far as views, if I understand right, I should create ten views, then use my stored procedure to query those views. Is that correct?

    Thanks.

    Steve

  • sdownen05 (4/14/2015)


    Jeff,

    I guess I was confused about NVARCHAR(MAX). I thought the max was 4000 characters, because when I print the results of my dynamic query it will cut it off. I guess it is a problem with the PRINT function and not the maximum value of NVARCHAR.

    As far as views, if I understand right, I should create ten views, then use my stored procedure to query those views. Is that correct?

    Thanks.

    Steve

    NP. And, no... not 10 views. 1 view with 10 SELECT/UNION ALLs. It'll be treated like one table. You could actually take it one step further and add a constraint to the table for the "vendor" in each database and you would have a "partitioned view" that would also allow for "partition elimination" in queries with criteria for the vendor that don't need to address all the databases.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • And to be sure, the query for the view would just be...

    SELECT columnlist FROM databaseA.dbo.table UNION ALL

    SELECT columnlist FROM databaseB.dbo.table UNION ALL

    SELECT columnlist FROM databaseC.dbo.table UNION ALL

    etc...

    Don't use "*" for the column list here. Too much of a chance of something going haywire later in life. You can easily get the column list for the tables by clicking and dragging the "columns" "folder" from the Object Explorer into the code window. Keep in mind that each table must have the same number and order of columns for all the SELECTs in this view.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Thank you, Jeff. That is how I will handle this. Thanks for the solution, and thanks for the reminders.

  • To make it more flexible, you might consider doing it like this:

    SELECT 'databaseA', columnlist FROM databaseA.dbo.table UNION ALL

    SELECT 'databaseB', columnlist FROM databaseB.dbo.table UNION ALL

    SELECT 'databaseC', columnlist FROM databaseC.dbo.table UNION ALL

    etc...

    I think that might be what Jeff was referring to when he talked about constraints and partitioned views.

    John

  • John Mitchell-245523 (4/14/2015)


    To make it more flexible, you might consider doing it like this:

    SELECT 'databaseA', columnlist FROM databaseA.dbo.table UNION ALL

    SELECT 'databaseB', columnlist FROM databaseB.dbo.table UNION ALL

    SELECT 'databaseC', columnlist FROM databaseC.dbo.table UNION ALL

    etc...

    I think that might be what Jeff was referring to when he talked about constraints and partitioned views.

    John

    That's virtually identical to how we solved a similar problem. The only thing we ran into was when someone wanted to add new items into the collection. Since they all used the same naming structure and nothing else used the same structure, we were able to create a stored procedure to recreate the view as needed. It's worked out well over the several years we've had it in place.

  • Try this...

    DECLARE @t table(ID int not null identity(1,1), name varchar(255));

    INSERT INTO @t(name)

    SELECT name

    FROM sys.databases -WHERE database_id > 4

    SET @total = @@ROWCOUNT;

    SET @id=1;

    WHILE @id <= @total

    BEGIN

    SELECT @dbname = name FROM @t WHERE ID = @id

    SET @sql =

    '

    USE [' + @dbname + '];

    SELECT BusinessUnit, VendDBName

    FROM TmpVendDBName

    WHERE ItemStatus = ''Active''

    '

    --PRINT @sql

    EXECUTE sp_executesql @sql

    SET @id = @id + 1

    END

    You can even customize the WHERE clause and skip some databases if needed.

  • Thanks to everyone for their help. Ed, I am using your solution, and it works perfectly! Thanks.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply