How to trap an error from insert ?

When inserting rows from a staging table to a production one, I need to
convert a column of type varchar to type int. Often there're rows that
have junk data in this column and that makes convert() fail. Is it
possible to know that such junk rows exist without getting an error
message ? In other words, is there a way to prevent the insert query
from throwing an error msg, but I still know that it fails ?
thanks,
TamYou can use the ISNUMERIC() to determine whether an
expression is a valid numeric type.
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Tam Vu" <vuht2000@.yahoo.com> wrote in message
news:1124509954.485547.268620@.o13g2000cwo.googlegroups.com...
> When inserting rows from a staging table to a production one, I need to
> convert a column of type varchar to type int. Often there're rows that
> have junk data in this column and that makes convert() fail. Is it
> possible to know that such junk rows exist without getting an error
> message ? In other words, is there a way to prevent the insert query
> from throwing an error msg, but I still know that it fails ?
> thanks,
> Tam
>|||I've tried isnumeric() - indeed this was the first thing I did. Yet
strings that have character like 'd', 'e', '.' also pass isnumeric()
but are not convertible to int, and these characters happen quite
commonly in the junk rows in my DB. Any other suggestions ?|||You can check for all digits with:
CASE WHEN
REPLACE(
REPLACE(
.
REPLACE (num '0',''),
1, ''),
.
9, '') = '' THEN CAST(num AS INTEGER) ELSE NULL END|||On 20 Aug 2005 18:30:21 -0700, --CELKO-- wrote:

>You can check for all digits with:
>CASE WHEN
>REPLACE(
> REPLACE(
> ..
> REPLACE (num '0',''),
> 1, ''),
> ..
> 9, '') = '' THEN CAST(num AS INTEGER) ELSE NULL END
Hi Joe,
Never rely on implicit conversion if you don't have to. Use quotes
around 1, 2, ..., 9 as well to prevent conversions.
And of course, this is lots more complicated then encessary:
CASE WHEN num NOT LIKE '%^[0-9]%' THEN CASE(num AS INTEGER) END
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||these are interesting solutions. btw, is there any "try-catch"
structure in sql server as I'm concerned there're cases that exceptions
are not known before hand ?
thanks,
Tam|||>> CASE WHEN num NOT LIKE '%^[0-9]%' THEN CASE(num AS INTEGER) END
Should be
> CASE WHEN num NOT LIKE '%^[0-9]%' THEN CAST(num AS INTEGER) END
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:kgrgg11r74k425gau3958b7dgjjtcsu9vu@.
4ax.com...
> On 20 Aug 2005 18:30:21 -0700, --CELKO-- wrote:
>
> Hi Joe,
> Never rely on implicit conversion if you don't have to. Use quotes
> around 1, 2, ..., 9 as well to prevent conversions.
> And of course, this is lots more complicated then encessary:
> CASE WHEN num NOT LIKE '%^[0-9]%' THEN CASE(num AS INTEGER) END
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||Yep. But in SQL Server 2005
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Tam Vu" <vuht2000@.yahoo.com> wrote in message
news:1124668962.565306.81090@.g43g2000cwa.googlegroups.com...
> these are interesting solutions. btw, is there any "try-catch"
> structure in sql server as I'm concerned there're cases that exceptions
> are not known before hand ?
> thanks,
> Tam
>|||On Mon, 22 Aug 2005 11:06:30 +0530, Roji. P. Thomas wrote:
>Should be
Hi Roji,
You are correct that I made a mistake. But your correction is wrong too
(since it's an exact same copy - you obviously forgot to correct the
mistake before posting).
For others reading this discussion: the correct statement is
(Note how the caret has sneaked one position to the right)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||> You are correct that I made a mistake. But your correction is wrong too
> (since it's an exact same copy - you obviously forgot to correct the
> mistake before posting).
:)
In a hurry to correct you asap, I refuse to look at the finer details :)
Infact I have'nt noticed the misplaced caret, but only the mispelled CAST.
Lets keep correcting each other :p
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:m1mkg19d2vmgq7vvus5ncer3di83gdvhut@.
4ax.com...
> On Mon, 22 Aug 2005 11:06:30 +0530, Roji. P. Thomas wrote:
>
> Hi Roji,
>
> You are correct that I made a mistake. But your correction is wrong too
> (since it's an exact same copy - you obviously forgot to correct the
> mistake before posting).
> For others reading this discussion: the correct statement is
>
> (Note how the caret has sneaked one position to the right)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

How to tell if I can convert a varchar to int

Hi,
I am attempting to write a procedure whereby I can get the maximum numeric
value in a varchar column (which may contain a mix of words and number
strings).
I'm trying to use:
SELECT MAX ( case isnumeric(value) when 1 then cast (value as int) else 0
end ) FROM table
However, given that entries in the value column can be up to 255 characters,
I obviously have a problem when the number is too long to be cast as an int
(or indeed a bigint or whatever).
In the case of this happening, I'd like to ignore these values, so what I
need is a means of telling whether a value can be cast as an int before
attempting to do it. Using isnumeric(..) isn't enough because it can't
confirm that I can actually convert it.
Any help would be very welcome,
Thanks in advance,
Chris.Maybe try datalength(column) which gives you the number of characters...
Wayne Snyder MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
(Please respond only to the newsgroups.)
I support the Professional Association for SQL Server
(www.sqlpass.org)
"Chris Lacey" <chris.lacey@.bigfoot.com> wrote in message
news:LnD3c.3274$re1.2931@.newsfe1-win...
> Hi,
> I am attempting to write a procedure whereby I can get the maximum numeric
> value in a varchar column (which may contain a mix of words and number
> strings).
> I'm trying to use:
> SELECT MAX ( case isnumeric(value) when 1 then cast (value as int) else 0
> end ) FROM table
> However, given that entries in the value column can be up to 255
characters,
> I obviously have a problem when the number is too long to be cast as an
int
> (or indeed a bigint or whatever).
> In the case of this happening, I'd like to ignore these values, so what I
> need is a means of telling whether a value can be cast as an int before
> attempting to do it. Using isnumeric(..) isn't enough because it can't
> confirm that I can actually convert it.
> Any help would be very welcome,
> Thanks in advance,
> Chris.
>|||To add to Wayne's response, consider using LIKE instead of ISNUMERIC. The
ISNUMERIC function will return 1 for some obscure numeric expressions.
SELECT
MAX(CAST(Value as decimal(38)))
FROM MyTable
WHERE
Value NOT LIKE '%[^0-9]%' AND
DATALENGTH(Value) < 39 AND
Value <> ''
Also, the need to do this sort of thing may be symptomatic of a database
design issue. It's usually not a good idea to store different types of data
in the same column in a relational database.
Hope this helps.
Dan Guzman
SQL Server MVP
"Chris Lacey" <chris.lacey@.bigfoot.com> wrote in message
news:LnD3c.3274$re1.2931@.newsfe1-win...
> Hi,
> I am attempting to write a procedure whereby I can get the maximum numeric
> value in a varchar column (which may contain a mix of words and number
> strings).
> I'm trying to use:
> SELECT MAX ( case isnumeric(value) when 1 then cast (value as int) else 0
> end ) FROM table
> However, given that entries in the value column can be up to 255
characters,
> I obviously have a problem when the number is too long to be cast as an
int
> (or indeed a bigint or whatever).
> In the case of this happening, I'd like to ignore these values, so what I
> need is a means of telling whether a value can be cast as an int before
> attempting to do it. Using isnumeric(..) isn't enough because it can't
> confirm that I can actually convert it.
> Any help would be very welcome,
> Thanks in advance,
> Chris.
>

how to sum up this thing?

hi guys.
in my select statement, i have this
CASE WHEN CONVERT(int,(SELECT SUM(TC.amount) FROM tb_payment AS TC WHERE TC.transactionid = TA.TransactionID AND TC.deletedby IS NULL)) IS NULL THEN '0' ELSE CONVERT(varchar,(SELECT SUM(TC.amount) FROM tb_payment AS TC WHERE TC.transactionid = TA.TransactionID AND TC.deletedby IS NULL)) END AS AmountPaid

the problem that i faced is, i need to sum up another time according to the year. the above statement will shoe the amount paid. i need to sum up the amount for each year. i've tried to use sum function, but it gives me error. Please help me to solve this problem. Thanks for all advise. I would appreciate it very much.

The simplified query....

(SELECT Convert(varchar,ISNULL(SUM(TC.amount),0)) FROM tb_payment AS TC
WHERE TC.transactionid = TA.TransactionID AND TC.deletedby IS NULL) as AmountPaid

Here you can use join rather subquery, if you post the full query i may help you to tune..

|||
SELECT TOP 5 tr.year, CASE WHEN CONVERT(int,(SELECT SUM(TC.amount) FROM tb_payment AS TC WHERE TC.transactionid = TA.TransactionID AND TC.deletedby IS NULL)) IS NULL THEN '0' ELSE CONVERT(varchar,(SELECT SUM(TC.amount) FROM tb_payment AS TC WHERE TC.transactionid = TA.TransactionID AND TC.deletedby IS NULL)) END AS AmountPaid, tr.edemedpoints, tr.adjustedpoints, tr.expiredpoints

FROM tb_pointtransactions AS TA
JOIN tb_transactionsummaries tr on tr.transactionid=TA.transactionid
JOIN tb_memberships m ON TA.membershipid = m.membershipid
JOIN tb_users u ON u.UserID = m.UserID
LEFT JOIN tb_salesstatus s ON s.id = TA.salesstatus
INNER JOIN tb_saletransactions AS TB ON TA.TransactionID = TB.TransactionID AND TA.deletedby IS Null
WHERE membershipid = '1' ORDER BY year

i'm really appreciate your help, ManiD. I'm still a newbie in programming field. With your help, my learning path will be wonderful. Thank you very much.

how to store output of exec stmt in another variable?

Hi

I am trying to store the output/result of exec statement in another variable like @.b.

Code Snippet

declare @.a varchar(10)

declare @.b int

set @.a='2 * 3';

EXEC ('select ' + @.a)

in the above sample I need to store 6 in @.b, how?

Please advice

Thanks

You could use sp_executesql like:

declare @.res int /*?*/;

declare @.sql nvarchar(max);

set @.sql = N'SELECT @.res = ' + @.a; -- Protect against SQL injection by using QUOTENAME as appropriate etc...

exec sp_executesql @.sql, N'@.res int OUTPUT', @.res = @.res OUTPUT;

But why do you want to do this? What are you trying to achieve? In SQL Server 2005, you could store the formula in a table and evaluate using CLR logic. This will actually perform better for complex calculations and will not suffer from SQL Injection issues. Otherwise, you should try to avoid dynamic SQL as far as possible. It is easy to code but hard to get it right in terms of security, performance and manageability. The risks are too much if you miss anything.

|||

Thanks a lot Chandar, for your nice reply

I got my answer from your query

The reason why I want this one is

I calculated the product of some values in a Table's column using COALESCE & EXEC Statements. But I am unable to store that result in a variable.

But now I got it using exec sp_executesql etc…..

Once again Thanks

|||You don't need to use dynamic SQL to get the product of values. You can do that with simple expressions using SUM and LOG. Search in this forum for my posts on this topic - I have some links that point to sample code that you can use.