PCACAC: Forum Posts

Test

Kerry Greenstein

Just testing for ya. Gotta find something to do on my last day before vacation.

the second reply

Robert Knight

Robby Knight wrote:

completely untrue test data

[SqlException (0x80131904): Divide by zero error encountered.]
   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +859322
   System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +736198
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
   System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1959
   System.Data.SqlClient.SqlDataReader.HasMoreRows() +150
   System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout) +214
   System.Data.SqlClient.SqlDataReader.NextResult() +162
   System.Data.ProviderBase.DataReaderContainer.NextResult() +16
   System.Data.Common.DataAdapter.FillNextResult(DataReaderContain

This reply includes the quote from the previous reply

the second reply

Robert Knight

completely untrue test data

[SqlException (0x80131904): Divide by zero error encountered.]
   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +859322
   System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +736198
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
   System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1959
   System.Data.SqlClient.SqlDataReader.HasMoreRows() +150
   System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout) +214
   System.Data.SqlClient.SqlDataReader.NextResult() +162
   System.Data.ProviderBase.DataReaderContainer.NextResult() +16
   System.Data.Common.DataAdapter.FillNextResult(DataReaderContain

the second reply

Robert Knight

Latest post: rbeezley wrote: Thanks Thoze... That is exactly what I needed. One additional question. It is not

First Reply

Robert Knight

Thankfully there are a few options for dynamic SQL in a stored procedure. One option would to be to use a CASE statement in the ORDER BY clause, as discussed in Dynamic ORDER BY Statements in Stored Procedures and The Power of SQL CASE Statements. Unfortunately this hampers the performance when sorting non-string columns because of conversion requirements and so forth.

A better performing option is to build and execute a dynamic SQL statement, a technique which is also discussed in Dynamic ORDER BY Statements in Stored Procedures. This approach, with indexes on the columns that may be sorted, offers performance time akin to the custom paging without sorting.