error in image field when using CASE statement

I've this Stored procedure on a SQLserver 2000 SP3:

SELECT *,CASE immagine WHEN NULL THEN 0 ELSE 1 END AS hasImage
FROM Squadre WHERE squadra = @.squadra

this is a flag that returns if the image field is present or not..
i've a lot of this type of stored procedures.. but this one returns me an error..

--------
Microsoft SQL-DMO (ODBC SQLState: 42000)
--------
Errore 306: The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.
--------
OK
--------

An i can't save.. why?
reme,ber that in the same Db there's other Stored like this.. the same syntax and the same field or table.. can anyone help me??So, are you saying that "sometimes" it works and sometimes it doesn't?

This should work "always":

SELECT *,CASE WHEN immagine IS NULL THEN 0 ELSE 1 END AS hasImage
FROM Squadre WHERE squadra = @.squadra

But exclude the image field from the SELECT list, unless you really intend to use it.|||i must use it!!!

Error in CASE expression

I would appreciate assistance as to how I should correct the following CASE expression::

BilledCarrier = CASE LEN(cur_billed_carrier) WHEN >0 THEN cur_billed_carrier ELSE 'Not Billed' END

I want to return the cur_billed_carrier when its length is >0 but I can't find the correct syntax. My attempt returns an error at the '>' operator. Thank you.

CASE expression has two formats:

case <expr>

when <expr1> then <return_expr1>

when <expr2> then <return_expr2>

...

else <return_expr_N>

end

-- and

case

when <lt_expr1> <operator> <rt_expr1> then <return_expr1>

when <lt_expr2> <operator> <rt_expr2> then <return_expr2>

...

else <return_exprN>

end

So you need to use the 2nd syntax like:

case when LEN(cur_billed_carrier) > 0 then cur_billed_carrier else 'Not Billed' end

-- or

case sign(len(cur_billed_carrier)) when 1 then cur_billed_carrier else 'Not Billed' end

|||Very clear; thank you

Error Handling in Sql Server 2000

I am having a SP (Stored Procedure) I want to handle the error in any case for the SP. My SP is inserting a record in a table when I had changed the Column name of the Table the error is not traced but the error is directly displayed in the TSql. How to Handle any error that occured in the SP? Thanks in Advance.

CREATE PROCEDURE SP_InvoiceFromRBOPInvNoHdr @.AutoBillID int, @.NextBillDate DateTime, @.OPInvNo int OutPut
AS
declare @.InvNo int
declare @.CustID int
declare @.InvAdd1 varchar(50)
declare @.InvAdd2 varchar(50)
declare @.city varchar(50)
declare @.Postal varchar(20)
declare @.Country varchar(50)
declare @.Province varchar(50)
declare @.BillDate datetime
declare @.BillingContact varchar(50)
declare @.BillingCurrencyCode varchar(5)
declare @.StoreID int
declare @.BillingPeriod varchar(10)
declare @.BillingTime varchar(10)
declare @.comment varchar(100)
declare @.TotalBillAmount float
declare @.TotalTax1 float
declare @.TotalTax2 float

declare @.err int

if exists(select * from BillRecurringInvoiceHeader where AutoBillID=@.AutoBillID)
begin
select @.InvNo = IsNull(max(InvoiceNumber),0)+1 from BillInvoiceHeaderHistory

select @.CustID=CustID,@.InvAdd1 = Address1, @.InvAdd2 = Address2, @.city=City,@.Postal=Postal,@.Country=Country,@.Province=Province,
@.BillDate = NextBillDate,@.BillingContact=BillContactName,@.BillingCurrencyCode=BillCurrencyCode,@.StoreID=StoreID,@.BillingPeriod=BillingPeriod,@.BillingTime=PreferredBillingTime
from BillRecurringInvoiceHeader where AutoBillID=@.AutoBillID

Set @.comment = 'Invoice from BillID ' + Cast(@.AutoBillID as varchar(10))
exec SP_RBAmount @.StoreID, @.AutoBillID, 'I',@.TotalBillAmount Output, @.TotalTax1 output, @.TotalTax2 output
--BillInvoiceHeaderHistory
insert into BillInvoiceHeaderHistory (InvoiceNumber,BillingAddress1,BillingAddress2,City,
Province,Postal,Country,BillingContact,BillDate,Comments,TotalBillAmount,TotalTax1,TotalTax2,BillingCurrencyCode,
AutoBillID,TotalRcdAmount,Status,StoreID,CustID) values (@.InvNo,@.InvAdd1,@.InvAdd2,@.city,
@.Province,@.Postal,@.Country,@.BillingContact,@.BillDate,@.comment, @.TotalBillAmount, @.TotalTax1, @.TotalTax2,
@.BillingCurrencyCode,@.AutoBillID,0,Null,@.StoreID,@.CustID)

select @.err = @.@.ERROR
if @.err != 0
begin
--Error Occured
--Set @.OPInvNo = -1
return @.err
end
else
begin
--No Errors
Set @.OPInvNo = @.InvNo
end
end
GO

Error handling in sqlserver2000 is really a pain, you have to check the @.@.ERROR after any statement that may fail.

declare @.err int

select @.InvNo = IsNull(max(InvoiceNumber),0)+1 from BillInvoiceHeaderHistory

set @.err = @.@.error

if (@.err != 0) begin ... end

select @.CustID=CustID,@.InvAdd1 = Address1, @.InvAdd2 = Address2, @.city=City,@.Postal=Postal,@.Country=Country,@.Province=Province,

@.BillDate

=

NextBillDate,@.BillingContact=BillContactName,@.BillingCurrencyCode=BillCurrencyCode,@.StoreID=StoreID,@.BillingPeriod=BillingPeriod,@.BillingTime=PreferredBillingTime

from BillRecurringInvoiceHeader where AutoBillID=@.AutoBillID

set @.err = @.@.error

if (@.err != 0) begin ... end

Set @.comment = 'Invoice from BillID ' + Cast(@.AutoBillID as varchar(10))

set @.err = @.@.error

if (@.err != 0) begin ... end

exec SP_RBAmount @.StoreID, @.AutoBillID, 'I',@.TotalBillAmount Output, @.TotalTax1 output, @.TotalTax2 output

set @.err = @.@.error

if (@.err != 0) begin ... end

--BillInvoiceHeaderHistory

insert into BillInvoiceHeaderHistory (InvoiceNumber,BillingAddress1,BillingAddress2,City,

Province,Postal,Country,BillingContact,BillDate,Comments,TotalBillAmount,TotalTax1,TotalTax2,BillingCurrencyCode,

AutoBillID,TotalRcdAmount,Status,StoreID,CustID) values (@.InvNo,@.InvAdd1,@.InvAdd2,@.city,

@.Province,@.Postal,@.Country,@.BillingContact,@.BillDate,@.comment, @.TotalBillAmount, @.TotalTax1, @.TotalTax2,

@.BillingCurrencyCode,@.AutoBillID,0,Null,@.StoreID,@.CustID)

set @.err = @.@.error

if (@.err != 0) begin ... end

|||

I had changed the column name of the table and this error is what I am unable to handle. The SP stops in the insert statement only. I had changed the column BillingAddress2 to BillingAddress23 and I am getting this error :

Server: Msg 207, Level 16, State 1, Procedure SP_InvoiceFromRBOPInvNoHdr, Line 39
Invalid column name 'BillingAddress2'.

|||

Check this post..

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=944372&SiteID=1

|||

Just to add my 2 cents worth, Error Handling in SQL Server 2000 was pretty weak. Check this article: http://www.sommarskog.se/error-handling-I.html for a good bit of information about this, and this one that is about stored procedures: http://www.sommarskog.se/error-handling-II.html. Bottom line, in 2000 every error was sent to the client. In 2005, you can take care of some of this with TRY..CATCH, but not all.

I don't think even 2005 will help you one a column name change, this is something that you need to manage in your code/testing.

|||

There is no way in 2000 or 2005 to trap an errror if the column does not exist. The error trapping in 2000 and 2005 is almost non-existent.

You cannot trap a non-existent column in either version.

|||

I wouldn't put it that way: "The error trapping in 2000 and 2005 is almost non-existent"

There are only a few errors that cannot be trapped, and I am not even sure if it would be a good idea for this kind of error to be trapped. This is a compilation error, so more or less the code cannot be executed. SQL is a very flexible language (exec ("select ...") for example) but it shouldn't be that flexible. It does catch most errors that it should and is 1000 times better than in 2000.