Problem with Trigger - 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)

Problem with Trigger

Author

Topic

anup
Starting Member

2 Posts

Posted - 2002-11-21 : 01:58:16

Error :Server: Msg 206, Level 16, State 2, Procedure SPARCustomerShippingAddress_UPDATE, Line 35
Operand type clash: smallint is incompatible with text

Can anyone help me with this?

CREATE TRIGGER SPARCustomerShippingAddress_UPDATE ON [dbo].[SPARCustomerShippingAddress]
FOR UPDATE
AS
DECLARE
@CustomerCode varchar (15),
@ShippingID smallint,
@AddressLine1 varchar (29),
@AddressLine2 varchar (29),
@AddressLine3 varchar (29),
@AddressLine4 varchar (29),
@City varchar (40),
@CountryCode varchar (4),
@PostCode varchar (25),
@Phone varchar (30),
@Fax varchar (30),
@Mobile varchar (40),
@Email varchar (255),
@DTS_SR_NO numeric (5,0)
SELECT
@CustomerCode = CustomerCode,
@ShippingID = ShippingID,
@AddressLine1 = AddressLine1,
@AddressLine2 = AddressLine2,
@AddressLine3 = AddressLine3,
@AddressLine4 = AddressLine4,
@City = City,
@CountryCode = CountryCode,
@PostCode = PostCode,
@Phone = Phone,
@Fax = Fax,
@Mobile = Mobile,
@Email = Email,
@DTS_SR_NO = DTS_SR_NO
FROM INSERTED
Insert into SPUpdateIn(ShopCode,Priority,Sql,Updated,Tallied,Remarks,Destination,SagePriorityNo,RecordNo) Values( ' + @ShopCode + ' ,0,'UPDATE SPARCustomerShippingAddress SET
CustomerCode = ''' + @CustomerCode + ''',
ShippingID = ''' + cast(isnull(@ShippingID,' ') as varchar (5) ) + ''',
AddressLine1 = ''' + isnull(@AddressLine1,' ') + ''',
AddressLine2 = ''' + isnull(@AddressLine2,' ') + ''',
AddressLine3 = ''' + isnull(@AddressLine3,' ') + ''',
AddressLine4 = ''' + isnull(@AddressLine4,' ') + ''',
City = ''' + isnull(@City,' ') + ''',
CountryCode = ''' + isnull(@CountryCode,' ') + ''',
PostCode = ''' + isnull(@PostCode,' ') + ''',
Phone = ''' + isnull(@Phone,' ') + ''',
Fax = ''' + isnull(@Fax,' ') + ''',
Mobile = ''' + isnull(@Mobile,' ') + ''',
Email = ''' + isnull(@Email,' ') + ''',
DTS_SR_NO = ''' + cast(isnull(@DTS_SR_NO,' ') as varchar (5) ) + ''',
WHERE CustomerCode = ''' + @CustomerCode + ''' AND ShippingID = ''' + @ShippingID + ''' ','N','N','','D',0,'R')

rihardh
Constraint Violating Yak Guru

307 Posts

Posted - 2002-11-21 : 02:50:33

I'd say you're trying to insert smallint data into a text column.

robad
Starting Member

1 Post

Posted - 2002-11-21 : 04:17:42

I think the problem is with the line

DTS_SR_NO = ''' + cast(isnull(@DTS_SR_NO,' ') as varchar (5) )

when @DTS_SR_NO has a NULL value.

I pulled this out and tried it with QueryAnalyzer like so:

DECLARE @DTS_SR_NO numeric (5,0)
SET @DTS_SR_NO = NULL
SELECT cast(isnull(@DTS_SR_NO,' ') as varchar (5) )

This produces the error:

Server: Msg 8114, Level 16, State 5, Line 3
Error converting data type varchar to numeric.

This works a lot better:

DECLARE @DTS_SR_NO numeric (5,0)
SET @DTS_SR_NO = NULL
SELECT ISNULL(CONVERT(varchar(5), @DTS_SR_NO), ' ')

So you need to do the casting FIRST and then apply the ISNULL, at least if you want to have a string as the ISNULL option.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources