Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234»»

How to Build Dynamic Stored Procedures Expand / Collapse
Author
Message
Posted Saturday, December 17, 2005 5:38 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 13, 2013 9:38 PM
Points: 9, Visits: 21
all, you can concat in sp_executesql so you can go beyond the 4k limit...


Post #245042
Posted Monday, December 19, 2005 10:00 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, November 4, 2014 1:50 PM
Points: 2,845, Visits: 1,160

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>', '')




Post #245237
Posted Monday, December 19, 2005 12:51 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 1:48 PM
Points: 57, Visits: 521

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?

Post #245309
Posted Monday, December 19, 2005 3:38 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, November 4, 2014 1:50 PM
Points: 2,845, Visits: 1,160

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




Post #245380
Posted Tuesday, December 20, 2005 12:29 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, March 5, 2014 6:38 PM
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!
Post #245660
Posted Wednesday, December 21, 2005 7:05 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 4, 2014 2:41 AM
Points: 4, Visits: 140

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.

Post #245831
Posted Wednesday, December 21, 2005 4:23 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, November 4, 2014 1:50 PM
Points: 2,845, Visits: 1,160

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.




Post #246025
Posted Wednesday, December 21, 2005 4:30 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, March 5, 2014 6:38 PM
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!
Post #246028
Posted Wednesday, December 21, 2005 4:30 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, March 5, 2014 6:38 PM
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!
Post #246029
Posted Thursday, December 22, 2005 9:29 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, November 4, 2014 1:50 PM
Points: 2,845, Visits: 1,160

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.




Post #246251
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse