Problems with Conversion ???? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Problems with Conversion ????

Author

Topic

chrispy
Posting Yak Master

107 Posts

Posted - 2002-01-17 : 23:25:40

Hello all,

I have a bug that I cannot simply figure out.
I have the following stored procedure :

PROCEDURE dbo.jclogfind
@jcfunction nvarchar(10), @daysview datetime, @jcorder nvarchar(20)
AS
SELECT *
FROM JClog
WHERE JCFunction= @jcfunction AND SessionDate>=@daysview
ORDER BY CASE @jcorder WHEN 'SessionDate' THEN SessionDate
WHEN 'AcctNM' THEN AcctNM
WHEN 'UserName' THEN UName
WHEN 'SToken' THEN SToken
END DESC,
SessionDate DESC

When I execute the code using the following all works as expected:
EXEC [Jobmark].[dbo].[jclogfind] Mkt, {ts '2002-01-16 23:11:09'}, UName

When I do the same but with the AcctName in the last field as such :
EXEC [Jobmark].[dbo].[jclogfind] Mkt, {ts '2002-01-16 23:11:09'}, AcctNM

I get the error :
Arithmetic overflow error converting expression to data type datetime.

Any thoughts as to where I am going wrong. Using the AcctNM in the last field is the only time I gett this error.

Here is the table :
CREATE TABLE [JCLog] (
[SessionDate] [datetime] NOT NULL ,
[AcctNM] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Uname] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SToken] [int] NULL ,
[JCFunction] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

Thanks for any help,
Chris

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2002-01-17 : 23:28:44

Chris,

You have to cast all your order by columns to the same type as used by the first expression ie In your case a DateTime...

I recommend moving a varchar field as the first choose and then cast everything to it...

DavidM

Tomorrow is the same day as Today was the day before.

chrispy
Posting Yak Master

107 Posts

Posted - 2002-01-18 : 00:40:48

David,
Thanks that got it. For the life of me I do not understand why this is but it does work. I wish it could be easier than this. Even easier would be to use a variable for a column name and not the CASE statement.
Anyone care to help me understand this?

BTW here is the code as suggested.

ALTER PROCEDURE dbo.jclogfind
@jcfunction nvarchar(10), @daysview datetime, @jcorder nvarchar(20)
AS
SELECT *
FROM JClog
WHERE JCFunction= @jcfunction AND SessionDate>=@daysview
ORDER BY CASE @jcorder WHEN 'AcctNM' THEN AcctNM
WHEN 'UserName' THEN UName
WHEN 'SToken' THEN CAST(SToken as NVARCHAR)
WHEN 'SessionDate' THEN CAST(SessionDate as NVARCHAR)
END DESC,
SessionDate DESC

Thanks again David,
Chris

Nazim
A custom title

1408 Posts

Posted - 2002-01-18 : 09:00:32

Will this not do it
Create PROCEDURE dbo.jclogfind
@jcfunction nvarchar(10), @daysview datetime, @jcorder nvarchar(20)
AS
declare @mSelect varchar(500)

Select @mSelect="SELECT * FROM JClog WHERE JCFunction= @jcfunction AND SessionDate>=" + @daysview + "ORDER BY " + @jOrder + " Desc,
SessionDate DESC "

exec (@mSelect)
Go

Coming to your doubt as David suggested, the datatypes in a order by should be same if you are using Case to sort, otherwise it will generate a error.

HTH

----------------------------------
"True love stories don't have endings."

chrispy
Posting Yak Master

107 Posts

Posted - 2002-01-21 : 12:39:55

Nazim,
Thanks. That helps me along even better. Not only with this statement but all future ones as well.

Thanks again ALL
Chris

Nazim
A custom title

1408 Posts

Posted - 2002-01-22 : 03:34:03

Am glad it helped.

i would suggest you to go thru the articles written by Merkin on Dynamic Sql for Sqlteam.

http://www.sqlteam.com/item.asp?ItemID=4599
http://www.sqlteam.com/item.asp?ItemID=4619

--------------------------------------------------------------
Dont Tell God how big your Problem is , Tell the Problem how Big your God is

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources