HELP!!!!! Dynamic SQL needs converted to set-based solution
-
Here is my problem:
I currently have a cursor-based solution to read from 3 different databases and merge selected data into one master. I am using dynamic SQL, but I would like to get away from that, if possible. Here is a sample of my dyanmic SQL:
SET @SQL = 'INSERT INTO MergeMaster.dbo.GrandDesign (designField, moneyField, timeField) SELECT fldA, fldB, fldC FROM ' + @dbase + 'dbo.Table1 WHERE fldD = ''x'' '
The cursor sets the @dbase value. I can post some of the code, but I have to be careful since a lot of it is proprietary.
I want to get rid of the cursor, remove the dynamic SQL, and make it easier to maintain. The SQL statement is always the same, except for the database name.
If someone can suggest an alternative method, it would be greatly appreciated. This is but one of 15 procs that I need to revise; they all have dynamic SQL in them similar to this.
Thanks in advance!!!!!
-
It's hard to say without knowing more about your problem, but one solution might be to use a view which UNION ALLs together the result sets from your tables... i.e.
CREATE VIEW vw_Table1
AS
SELECT fldA, fldB, fldC FROM db1.dbo.Table1 WHERE fldD = 'x'
UNION ALL
SELECT fldA, fldB, fldC FROM db2.dbo.Table1 WHERE fldD = 'x'
UNION ALL
SELECT fldA, fldB, fldC FROM db3.dbo.Table1 WHERE fldD = 'x'
Then just do your insert from the view.
-
Thank you for the idea. I did think of this, but the one problem I have is that the databases I read from are created by another application, and I would need to retrieve data from all of the databases. If I do a view, that means I would have to add databases as I remember, whereas I would prefer that the solution be more automated and read from the new databases as they are created.
I have a process which runs every hour to retrieve the new databases, so I have a way to get the names. Maybe I'll automate updating the view with the new UNION statements and leave it at that. Then, the set-based solution becomes much easier.
-
Sometimes, cursors and dynamic sql make sense for a particular job. This may be one of those cases.
-
Agreed. Dynamic sql was made for cases like this one.
AS long as you are not using user inputs in the proc, stay with loop / dynamic sql. The solution is already perfectly fitted for your needs.
The only modification I can offer is to maybe add a list of servers/databases so that the cursor will know what databases to execute on. That way all you have to do is keep the databse list up to date in the master db and you're done.
-
join your database list with
SELECT
dbname FROM tblDBList a
INNER
JOIN sys.databases b
ON
b.NAME = a.dbname
AND
b.state=0 -- (make sure the DB exists and is also online)
-
I think you are faced with two options:
A) Modify the application that creates the database to generate new stored procedures/views after the databases are online. Eliminates the cursor and dynamic sql string. Moves you to generated/dynamic but compiled sql.
B) Use the list of databases selected to create the dynamic sql string to execute each time. Eliminates the cursor but not the dynamic sql string.
[font="Arial"]Clifton G. Collins III[/font]
-
Thanks to everyone for suggesting a course of action. I'll try some ideas based off the suggestions and see what happens.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply