Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to Build Dynamic Stored Procedures


How to Build Dynamic Stored Procedures

Author
Message
pkmccarthy
pkmccarthy
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 25
all, you can concat in sp_executesql so you can go beyond the 4k limit...



Scott Coleman
Scott Coleman
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2942 Visits: 1416

I have learned to detest code that builds dynamic SQL by repeated concatenation, especially with char functions for line breaks and tabs. A complex statement is difficult to read when it is pieced together with a bunch of CHAR, CAST, and CONVERT fuctions.

SQL is perfectly happy to interpret multiline strings with all line breaks and tabs included. I create a template of the complete SQL statement with tags for all variable parts, then use REPLACE functions to handle the modifications. This is especially useful when one variation requires changes in the field list, tables, and where clause. Another advantage of REPLACE is it can do implicit conversions to string of integers and avoid '...' + CAST(x AS VARCHAR) + '...'.

-- Basic template
SET @sql = 'SELECT fld1, fld2<fields>
FROM tbl1
<joins>
WHERE tbl1.xyz=0 <filters>'

-- One logical test controls additional fields, joins, and filter clauses
-- All replaceable tokens are duplicated in the replacement strings to allow further actions
IF modification1 = 1
SET @sql = REPLACE(REPLACE(REPLACE(@sql,
'<fields>', ', fld3, fld4<fields>'),
'<joins>', 'INNER JOIN tbl2 ON tbl1.fld1 = tbl2.fld1
<joins>'),
'<filters>', '
AND tbl2.pqr IS NOT NULL <filters>')

-- Further modifications using the same logic

-- Strip out any remaining tokens
SET @sql = REPLACE(REPLACE(REPLACE(@sql,
'<fields>', ''),
'<joins>', ''),
'<filters>', '')





Matt Klein-228552
Matt Klein-228552
SSC-Enthusiastic
SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)

Group: General Forum Members
Points: 134 Visits: 554

In a tangential topic, I've built a process using dynamic sql which reads through all the databases on my servers to determine which indexes need reorganizing. Works great on all our servers, except for the ones with outside apps, where there the table owners are not dbo. I'm attempting to adapt the process to append the database owner name to the tablename -- building a variable ahead of time -- however I get a compile error whenever I have a variable in the "from" clause.

Is there a proper way to handle this that I'm missing?


Scott Coleman
Scott Coleman
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2942 Visits: 1416

Presumably you're getting the index info from sysindexes? Join to sysobjects to get the owner. It might look something like this:

select 'DBCC DBREINDEX (''' + quotename(user_name(so.uid)) + '.'
+ quotename(so.name) + ''',''' + rtrim(si.name) +
'''[, fillfactor]'
from sysindexes
si
inner join sysobjects so on so.id = si.
id
where xtype =
'U'
and indexproperty(si.id, si.name, 'IsStatistics')=
0
and indexproperty(si.id, si.name, 'IsHypothetical')=0





BuilderBits
BuilderBits
Mr or Mrs. 500
Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)

Group: General Forum Members
Points: 501 Visits: 251

I really enjoyed the article.

There is huge benefit for dynamic stored procedures in when vb.net and sql2000 work togeather;.

thanks!

erik



Dam again!
Stuart Anderson-198975
Stuart Anderson-198975
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 141

Thanks for the article. I am currently re-writing a chunk of my code because, in this case, it's going to give me a huge performance gain. The format I have settled on is:

DECLARE @query nvarchar(4000), -- // nvarchar using sp_executesql

@LN char(1),

@TB char(1),

@LT char(2)

set @LN = char(10) -- // Line Feed

set @TB = char(9) -- // Tab

set @LT = char(10) + char(9) -- // Line feed + Tab

set @query = ''

set @query = @query

+ 'select column1,' + @LT

+ 'column2' + @LN

+ 'from table1 (nolock)'

It is a little neater than having char() in lots of places.


Scott Coleman
Scott Coleman
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2942 Visits: 1416

One more time, SQL Server is happy to let you use multi-line strings with embedded tabs. The end-of-line is just white space to the SQL syntax analyzer, you don't have to close the string on each line and start the next with "+".

Instead of using all that concatenation, just use:

set @query =
'select column1,
column2
from table1 (nolock)'

I can't enter a tab in this text box so there are four spaces before 'column2' instead of a tab, but you can use the tab character in Query Analyzer.





BuilderBits
BuilderBits
Mr or Mrs. 500
Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)

Group: General Forum Members
Points: 501 Visits: 251

will someone please help and o'll wondering soul out by giving me a human version of (NOLOCK)??

THANKS!

ERIK



Dam again!
BuilderBits
BuilderBits
Mr or Mrs. 500
Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)

Group: General Forum Members
Points: 501 Visits: 251

will someone please help and o'll wondering soul out by giving me a human version of (NOLOCK)??

THANKS!

ERIK



Dam again!
Scott Coleman
Scott Coleman
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2942 Visits: 1416

NOLOCK aka READ UNCOMITTED aka "dirty read"

Allows reading a table in spite of any uncommitted transactions that may be in progress on other connections.

Pro: It saves time by not creating locks, and by not waiting for or blocking other transactions. It only requires a schema lock on the table, rather than creating locks for all the rows/pages/extents that are read. It ignores exclusive locks owned by other connections instead of waiting for those transactions to complete.

Cons: You can read data from partially completed transactions, then those transactions may be rolled back and the data disappears. For instance, you have a query WITH(NOLOCK) to sum the sales for each salesman to calculate commission payments. Just before you run this, someone begins a transaction showing they sold the Brooklyn Bridge for $2 billion. After your comission query runs and calculates a huge bonus for this guy, the transaction is rolled back and there is no trace of the data to explain what happened to the accountants.

It is very useful for reporting queries on production databases, if you know that either the data you're reading is static or you don't mind having counts and totals being a little off. A history table that sees INSERTs but no UPDATEs for example, or maybe a query summarizing last week's data when only today's data might be volatile. An inventory query that is checking whether there are less than 5000 #8 wood screws on hand to decide when to reorder is not going to notice the difference between 3875 and 3750.

It obviously is not recommended for accounting, or any application where exact numbers are required. An inventory query that is a little fuzzy about whether there should be 2 or 3 Ferraris on the showroom floor is a bad idea.





Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search