Avoiding dynamic SQL

Question

Post reply

Avoiding dynamic SQL

derek.colley

SSCrazy Eights

Points: 8040
More actions
June 21, 2012 at 3:07 am

#145382

I have a good one for today.
I have a number of databases (inherited, not mine) that are named as follows:
Prefix_BrandOne
Prefix_BrandTwo
...
Prefix_BrandN
All databases with the same prefix have an identical set of tables, views and stored procedures.
The application interface is a large selection of stored procedures. These stored procedures need to execute on various databases depending on prefix. Obviously I can't give out actual names but, for example, if the prefix above was based on fruit, I might have a stored procedure that has to execute statements against all databases beginning with 'Apple'. The same statement(s) would execute against:
Apple_BrandOne
Apple_BrandTwo
...
Apple_BrandN
But NOT against:
Banana_BrandOne
Banana_BrandTwo
Banana_BrandN
With me so far?
Second fact: The list of databases that the code in the stored procedure must be executed against is generated from a query. Along the lines of:
SELECT name FROM dbo.eligibleDatabases WHERE active=1
And at present, this list is fed into a cursor defined inside the SP which iterates over the code that needs execution, feeding in the @dbname.
So here's the problem. SQL Server T-SQL syntax doesn't allow me to execute against a database name that's been passed in as a parameter. For example:
DECLARE @dbname VARCHAR(50)
SET @dbname = ( SELECT TOP 1 name FROM sys.databases WHERE name LIKE 'Apple%' )
INSERT INTO [@dbname].dbo.myTable VALUES ('Hello');
Msg 208, Level 16, State 1, Line 3
Invalid object name '@dbname.dbo.myTable'.
The way in which it's been overcome in the SPs I'm looking at is through dynamic SQL. So we have:
DECLARE @sql
SET @sql = ' -- MASSIVE CODE HERE -- MASSIVE CODE HERE -- MASSIVE CODE HERE -- ' + @dbname + ' -- MASSIVE CODE HERE -- MASSIVE CODE HERE -- MASSIVE CODE HERE '
EXEC @sql
This is not particularly efficient. The massive code is being iterated over and over again for each database. I'm looking for a way to make a start on reducing the amount of dynamic SQL significantly and if possible removing the cursor.
Here's my stab at removing the dynamic SQL and having the code directly compile, and the error message that follows. It uses a SQLCMD server variable 'setvar' (More information about this here: http://msdn.microsoft.com/en-us/library/aa833281(v=vs.80).aspx
SET NOCOUNT ON
DECLARE @setVar NVARCHAR(100)
DECLARE @dbname VARCHAR(100)
SELECT dbname INTO dbo.completionList FROM SANDBOX.dbo.dbLookupTable
WHILE (SELECT COUNT(*) FROM dbo.completionList) <> 0
BEGIN
SET @dbname = CAST((SELECT TOP 1 dbname FROM dbo.completionList ORDER BY dbname ) AS VARCHAR(100))
SET @setVar = ( ':setvar ') + CAST(@dbname AS NVARCHAR)
EXEC sp_executesql @setVar
/* The procedural code goes here. i.e.
INSERT INTO [@(dbname)].dbo.myTable VALUES('Elephants have a gestation period of 3 years.')
This code will be repeated per-database. However dynamic SQL is not used for the whole batch,
so a proper execution plan can be used to execute the query. */
DELETE FROM dbo.completionList WHERE dbname = @dbname
END
DROP TABLE dbo.completionList
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ': '.
(repeats once per database iteration)
As you can see this didn't work. It doesn't appear I can equate :setvar to a @variable.
The other option is sp_msforeachdb, however I can't see how I can feed it a filtered list of database names generated from a query or table.
The best way of doing things, of course, would be to redesign the data model (I'm looking to do this ASAP!)
In the meantime though, does anyone have any suggestions for ways to get around this, please?
Thank you.
---
Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:
Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

Viewing 7 posts - 1 through 6 (of 6 total)

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

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

:setvar only works in SQL command mode.

I cannot understand why would you need dynamic sql for statements there.

Why not to have the same stored procedures in every database and call them dynamic sql. For example, you will have proc p_MyDataInsert in every database. Then your dynamic sql in a cursor will be very simple:

set @sql = 'EXEC ' + @dbname + '.dbo.p_MyDataInsert'

exec sp_executesql @sql

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 2

Eugene Elutin (6/21/2012)
I cannot understand why would you need dynamic sql for statements there.
Why not to have the same stored procedures in every database and call them dynamic sql. For example, you will have proc p_MyDataInsert in every database. Then your dynamic sql in a cursor will be very simple:
set @sql = 'EXEC ' + @dbname + '.dbo.p_MyDataInsert'
exec sp_executesql @sql

1) That was my first thought as I read through the OP. Seems like a table/cursor driven dynamic execution here is appropriate given the existing constructs.

2) This could be done easily in a middle tier as well and not just in TSQL.

3) Be VERY careful using sp_msforeachdb - it has known flaws and there are cases where it will NOT hit all databases.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

TheSQLGuru (6/22/2012)
Eugene Elutin (6/21/2012)
I cannot understand why would you need dynamic sql for statements there.
Why not to have the same stored procedures in every database and call them dynamic sql. For example, you will have proc p_MyDataInsert in every database. Then your dynamic sql in a cursor will be very simple:
set @sql = 'EXEC ' + @dbname + '.dbo.p_MyDataInsert'
exec sp_executesql @sql
1) That was my first thought as I read through the OP. Seems like a table/cursor driven dynamic execution here is appropriate given the existing constructs.
2) This could be done easily in a middle tier as well and not just in TSQL.
3) Be VERY careful using sp_msforeachdb - it has known flaws and there are cases where it will NOT hit all databases.

I don't really use sp_msforeachdb but I would be interested if you could expand on point #3. What flaws? Do you have any references that explains that?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 4

Sean Lange (6/22/2012)
TheSQLGuru (6/22/2012)
Eugene Elutin (6/21/2012)
I cannot understand why would you need dynamic sql for statements there.
Why not to have the same stored procedures in every database and call them dynamic sql. For example, you will have proc p_MyDataInsert in every database. Then your dynamic sql in a cursor will be very simple:
set @sql = 'EXEC ' + @dbname + '.dbo.p_MyDataInsert'
exec sp_executesql @sql
1) That was my first thought as I read through the OP. Seems like a table/cursor driven dynamic execution here is appropriate given the existing constructs.
2) This could be done easily in a middle tier as well and not just in TSQL.
3) Be VERY careful using sp_msforeachdb - it has known flaws and there are cases where it will NOT hit all databases.
I don't really use sp_msforeachdb but I would be interested if you could expand on point #3. What flaws? Do you have any references that explains that?

Binoogle this: aaron bertrand sp_msforeachdb

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

TheSQLGuru (6/22/2012)
Sean Lange (6/22/2012)
TheSQLGuru (6/22/2012)
Eugene Elutin (6/21/2012)
I cannot understand why would you need dynamic sql for statements there.
Why not to have the same stored procedures in every database and call them dynamic sql. For example, you will have proc p_MyDataInsert in every database. Then your dynamic sql in a cursor will be very simple:
set @sql = 'EXEC ' + @dbname + '.dbo.p_MyDataInsert'
exec sp_executesql @sql
1) That was my first thought as I read through the OP. Seems like a table/cursor driven dynamic execution here is appropriate given the existing constructs.
2) This could be done easily in a middle tier as well and not just in TSQL.
3) Be VERY careful using sp_msforeachdb - it has known flaws and there are cases where it will NOT hit all databases.
I don't really use sp_msforeachdb but I would be interested if you could expand on point #3. What flaws? Do you have any references that explains that?
Binoogle this: aaron bertrand sp_msforeachdb

Thanks Kevin. I had not heard about this before. I will have to investigate a bit further.