August 13, 2003 at 7:21 pm
I am trying to return a string from a stored proc that is a concatenation of 2 integers and a hyphen ('-'). Specifically, I want the last two digits of the current year, a hyphen, and the value of @@Identity. I am having a very difficult time. The error I am getting is "Error converting data type varchar to int", and my proc looks like this:
CREATE PROC dbo.sp_ets_insert_PersonalTrade
@_EmpID varchar(11),
@_CallerName varchar(50),
@_TranType varchar(4),
@_Company varchar(50),
@_Ticker varchar(12),
@_Qty int,
@_MNPI integer,
@_TradeSec integer,
@_30Days integer,
@_LosingTrans integer,
@_TradeStat varchar(8),
@_HitWatch integer,
@_Out_TradeCID varchar(11) OUTPUT AS
declare @_TID integer
declare @_Year char(2)
INSERT INTO dbo.ets_PersonalTrade VALUES ('',@_EmpID,getdate(), @_CallerName,@_TranType,@_Company,@_Ticker,@_Qty, @_MNPI,@_TradeSec,@_30Days,@_LosingTrans,
@_TradeStat,@_HitWatch,'','','',0,getdate(),user)
SET @_TID = @@Identity
SET @_Year = cast(right(datepart(yyyy, getdate()),2) as varchar(2))
SET @_Out_TradeCID = RIGHT(cast(datepart(yyyy, getdate()) as varchar),2) +
cast('-' as char(1)) +
cast(@_TID as varchar)
And I expect the output parm to look similar to the following: 03-298
Any help is appreciated.
August 13, 2003 at 8:08 pm
Although your string manipulation may be more complex than it needs to be, it looks ok to me.
Are you sure you're not getting the conversion error on the INSERT. Whats the table definition?
Cheers,
- Mark
Cheers,
- Mark
August 13, 2003 at 9:44 pm
Agree with mccork.
Try a Select 'Before Insert' and Select 'After Insert' after the actual Insert.
Alternatively step through proc.
August 14, 2003 at 6:56 am
quote:
Although your string manipulation may be more complex than it needs to be, it looks ok to me.Are you sure you're not getting the conversion error on the INSERT. Whats the table definition?
Cheers,
- Mark
The conversion error is actually happenning when I attempt to set the value of @_Out_TradeCID. The three concatenated pieces that make up the Parm are all CAST as char or varchar, and the Parm itself is varchar, so I am not clear on why the conversion error is referncing 'varchar to int', when if anything, I'm going from int to varchar.
Alternative suppgestions are welcome, so long as the Parm resembles: '03-xxxxxx', where 'xxxxxx' is the value of @_TID.
August 14, 2003 at 7:06 am
quote:
The conversion error is actually happenning when I attempt to set the value of @_Out_TradeCID
I ran your code under QA, using a cutdown version of your table and generating @@IDENTITY values of 1,2,3,4.... It ran fine each time.
Cheers,
- Mark
Cheers,
- Mark
August 14, 2003 at 8:25 am
quote:
quote:
The conversion error is actually happenning when I attempt to set the value of @_Out_TradeCIDI ran your code under QA, using a cutdown version of your table and generating @@IDENTITY values of 1,2,3,4.... It ran fine each time.
Mark, thanks. This proc is called from a prior proc, which was what was causing this error. In the prior proc, I had changed the OUTPUT parm from 'int' to varchar(11), but apparently the compile was not successful. Upon editing and compiling again, my situation is resolved. Thanks again.
--Scott
Edited by - sbdesalvo on 08/14/2003 11:34:42 AM
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply