Very Strange Data Conversion (?) Error

 Using SQL Server 2005, I hve encountered a very strange error which I cannot figure out.  The simplified stored proc below basically does a select and sorts the records based on the column name given in the @SortCol parameter.  When I enter the field names bapk, jhpk, etc everything works fine.  The records are returned sorted by the field name entered into the parameter.  The first eight variables are  field names and types

 When I enter the field name 'descr' into the @SortCol variable/parameter, I receive the error:

 Msg 295, Level 16, State 3, Procedure CMSCheck2, Line 28
Conversion failed when converting character string to smalldatetime data type.

I don't see where this is coming from at all.  The problem is in the  stored proc lines beginning with 'CASE @SortCol'

If I take out the lines and just use  OVER (Order By descr) as RowNum   — everything is fine

If I delete the line  WHEN 'descr' THEN descr  — everything is fine, it just falls through to the default.

 What is the problem?  Where is a conversion to smalldatetime coming from?   I've been working on this one for a while, and cannot figure it out.  It must be something simple.

 Thanks,

Mike Thomas
 

Below is the stored proc and a testing script

 

TESTING SCRIPT

USE biz03
GO

DECLARE
  @bapk int,
  @jhpk int,
  @transdate smalldatetime,
  @number smallint,
  @descr nvarchar(50),
  @debit decimal(9,2),
  @credit decimal(9,2),
  @cmonth tinyint,
  @StartRowIndex int,
  @MaxRows int,
  @AlphaChar varchar(1),
  @SortCol varchar(20)

SET @MaxRows = 10
SET @StartRowIndex = 0
SET @SortCol = 'descr'
SET @AlphaChar = ''

EXEC [dbo].[CMSCheck2]
  @bapk,
  @jhpk,
  @transdate,
  @number,
  @descr,
  @debit,
  @credit,
  @cmonth,
  @StartRowIndex,
  @MaxRows,
  @AlphaChar,
  @SortCol

 

STORED PROC 

 

 ALTER PROCEDURE [dbo].[CMSCheck2]
  @bapk int,
  @jhpk int,
  @transdate smalldatetime,
  @number smallint,
  @descr nvarchar(50),
  @debit decimal(9,2),
  @credit decimal(9,2),
  @cmonth tinyint,
  @StartRowIndex int,
  @MaxRows int,
  @AlphaChar varchar(1) = null,
  @SortCol varchar(20) = null

AS
BEGIN

SET NOCOUNT ON

DECLARE @lPaging bit
IF @AlphaChar is null
  SET @lPaging = 0
ELSE
  SET @lPaging = 1;

WITH BankListTemp AS
  (SELECT   bapk, jhpk, transdate, number,
    descr, debit, credit, cmonth, ROW_NUMBER()
    OVER (ORDER BY
    CASE @SortCol
       WHEN 'bapk' THEN bapk
       WHEN 'jhpk' THEN jhpk
       WHEN 'transdate' THEN transdate
       WHEN 'number' THEN number
       WHEN 'descr' THEN descr  — problem here   —
       WHEN 'debit' THEN debit
       WHEN 'credit' THEN credit
       WHEN 'cmonth' THEN cmonth
       ELSE bapk
    END) as RowNum
  FROM bank)

SELECT TOP (@MaxRows)    bapk, jhpk, transdate, number,
    descr, debit, credit, cmonth, Rownum
FROM
 ( SELECT BankListTemp.*,
    (SELECT COUNT(*) FROM BankListTemp) AS RecCount
      FROM BankListtemp) Bank

END

 

Read More

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s