DataType Conversion using WHERE IN ( )

I am getting a "Syntax error converting the varchar value '10,90' to a column of data type int." error when I run the following procedure:

@.myList varchar(200)

SELECT column1
FROM table1
WHERE table1.ID IN (@.myList)

When @.myList is a single value, I get no errors. However, when @.myList is a comma separated list like in the message above, I error out. I am using SQL Server 2000.

How else can I build this list of IDs? Thank you in advance for your comments.

--ColonelYou cannot do what you are trying to do. YOu need to use dynamic SQL, or send in a string and use a function to create a table variable and do the operation based upon a select on that table variable.|||I found that my varchar parameter was being sent in with single quotes around it. I removed these, and now my WHERE clause looks like this:

WHERE table1.ID IN (REPLACE(@.myList,'''',NULL))

and it works just fine.

I did not add those quotes to the list of values. I believe that SQL Server adds them to delimit the text. Thank you for your comments.

Datatype conversion strangeness

Hi all,

After more headbanging and cursing than an entire Metallica audience, I have finally deduced how to store and calculate the values of a stack storing RPN calculations using a recursive stored procedure. But there is a new conundrum. Originally, to test the above, I was using fairly simple values -- 10, 20, +, 3, /, etc. But the real data is likely to include values with at least a couple of decimal places -- these have been configured using the money data type. Nevertheless, in my actual stack table the value needs to be specified as a varchar. However, as soon as I start sending in 0.68 as a varchar to my stored procedure, it gives an error.

Can anyone offer any light on this?

(The error message number is 245, data type conversion error. But this is inexplicable as I have commented out any conversion code in order to get to the root of the problem. As far as I am aware this value "0.68" is always being passed around as a varchar.)

Thanks.

But you might be doing some operation that involves say concatenation of numeric value with varchar or vice versa. This will result in implicit conversion of the value to the data type with highest precedence and depending on the value you can get run-time errors. It is hard to tell what is wrong without looking at some repro. You should however avoid doing these type of operations since the potential for misinterpretation or run-time error is large. If you are dealing with numeric values then specify the data type of the variables accordingly.|||Umachandar,

Many thanks for your reply. The reason I'm doing it this way is that I'm modelling a stack. Also some values are actually the tablename.objectname id of values in a read-only section of the database, so the <value> part of this stack has to be flexible -- hence varchar. Surely the potential for runtime error is not that great, as I only want to be able to handle values such as the following:-

table1.id1 (this gets parsed by the stored procedure into values such as follow...)
0.68
0.43
10
20
+
*

|||Again, it is hard to tell what is wrong without looking at some piece of code that reproes the error. Alternatively, you could run profiler trace with statement level events to see which statement is failing (you can even get this from the error message header which will point to the line that generated the error).|||OK let me try to explain as fully as possible.

I have a table NEO_FORMULA_STACK (f_id(int), position(int), val(varchar(100)), valname(varchar(100)), units (nvarchar(12).

I have a stored proc, which accepts f_id (as above) as a parameter. Based on this f_id it then loads the 'stack' of values into table TEMP_STACK (pos(int),val(varchar(100).

What I mean is that there are multiple formulas in the original table each with their own 'stack' of values (arranged in Reverse Polish Notation format, i.e. 10,20,+,3,/ [a way of writing ((10+20)/3).

Now, in the course of the load from NEO_FORMULA_STACK TO TEMP_STACK conversions such as tablename.objectname being evaluated to e.g. 0.098 might very well occur. But my impression is that if NEO_FORMULA_STACK has a val column of VARCHAR(100) datatype, and so does TEMP_STACK, then there should be absolutely no problem going from 'tablename.objectname' to '0.000918'. These are BOTH completely valid strings. Bear in mind that NO calculation has taken place at this point, I have simply loaded NEO_FORMULA_STACK for a particular f_id into TEMP_STACK. Attempting to pop off a value like '0.0098' into a temporary variable of varchar(100) is throwing error: 245, converting from varchar to int at line 77 of the stored proc:-

I can't give you the exact line, because one sp is calling another so the line it cites as being in the wrong is a comment.

Please let me know if you need more detail, or if you have any clues based on this information. Many thanks!
|||"But you might be doing some operation that involves say concatenation of numeric value with varchar or vice versa. This will result in implicit conversion of the value to the data type with highest precedence and depending on the value you can get run-time errors."

I assure you I am doing no concatenation or anything like that. I removed all such code in order to try and attack the problem. I am loading one varchar into another varchar, then popping that varchar off into a temporary variable of type varchar -- and it is that pop which is throwing the error, saying illegal conversion, when as far as I can see they are ALL varchar(100).
|||Your explanation doesn't really help to clarify the exact operations being done in the SP. As I said, this error happens due to implicit conversions of values of different types or initialization of variables/parameters using value of different data type. Add additional debug statements to see the results before the input so you can identify the issue. Or create a simpler repro that will help you identify the problem due to implicit conversion.

Datatype Conversion Problem.

Hi ALL!

I have a table named 'Table1' which contains a column 'Name'.
The data type of column [Name] is varchar(50).

When i try to change its datatype to binary by trying following code

ALTER TABLE Table1 Alter Column [Name] Binary(5000)

It gives following error.

" Creation of table 'bp_MAIN' failed because the row size would be 10021, including internal overhead. This exceeds the maximum allowable table row size, 8060. "

So, how can i change the datatype of this column ?

Regards,
Shabber Abbas.U cannot convert varchar column to binary column explicitly.
One solution is ,create a new table (lets say t1) with binary datatype.
Then convert and insert record into t1 table from ur original table.
Drop original table and rename new table to original table.
set same permission as orginal table.

--eg:
insert into t1(othercolumnnames,name) select othercolumnnames,convert(binary(5000),name) as name from Table1sql

Datatype Conversion Problem.

Hi ALL!

I have a table named 'Table1' which contains a column 'Name'.
The data type of column [Name] is varchar(50).

When i try to change its datatype to binary by trying following code

ALTER TABLE Table1 Alter Column [Name] Binary(5000)

It gives following error.

" Creation of table 'bp_MAIN' failed because the row size would be 10021, including internal overhead. This exceeds the maximum allowable table row size, 8060. "

So, how can i change the datatype of this column ?

Regards,
Shabber Abbas.Not meaning to be thick here, but why are you changing a varchar to a binary? Did you want instead to change it to nvarchar?

Regards,

hmscott|||In this case, you are implying that you want to convert varchar data to binary. I don't think that can be done automatically. The error might be misleading.

If that's the only field, it shouldn't give that error, but a table consisiting of only a binary field seems like it's not very useful. Is a blob out of the question? it only takes up 16bytes of the page. Yould definetely need to export/import then.

You should be able to add a binary column, or export the data, recreate the table with a binary field, and then import the data, with suitable massaging.|||You could use varbinary, but if the amount of data in the row exceeds 8060, you will get errors, instead of warnings.

Datatype Conversion during insert

I am performing an insert inside a stored procedure. In the values list , I am doing some data converision. I am getting an compile error like:

Server: Msg 170, Level 15, State 1, Procedure premiumstage_to_fact, Line 303
Line 303: Incorrect syntax near '='.
The code is:

INSERT INTO table-name( c1,c2,c3,c4)

VALUES
(@.v1,
@.v2,
@.variable = CASE WHEN ISDATE([@.variable]) <> 1
THEN 'NULL'
END
END AS @.variable,
@.v3)

Where am I going wrong? Where do I do the conversion? The comma after END AS @.variable, Is that syntax right?

Please advise.

ThanksUse SELECT instead of VALUES.

INSERT INTO table-name( c1,c2,c3,c4)

SELECT @.v1,
@.v2,
@.variable = CASE WHEN ISDATE([@.variable]) <> 1
THEN 'NULL'
END
END AS @.variable,
@.v3|||snail, i don't think your select will work. your syntax attempts to perform a variable assignment which is not allowed in this context. just remove "@.variable =" from your select. i would also remove quotes from THEN 'NULL' because i think the true null is intended.|||Originally posted by ms_sql_dba
snail, i don't think your select will work. your syntax attempts to perform a variable assignment which is not allowed in this context. just remove "@.variable =" from your select. i would also remove quotes from THEN 'NULL' because i think the true null is intended.

to ms_sql_dba:

You are right - it works for 2000. I am not sure about 7. May somebody test it and reply.

create table test13(id int,code varchar(10))
go
insert test13 values(1,case when 1=1 then 1 else 0 end)
insert test13 values(1,'4'+'5')

Datatype COnversion chart

Do you have a SQL Datataype conversion chart, ie a chart that represents if for eg an INT datatype can be converted to Datetime... etc...?

I dont need the one on SQL Books online. But need some other chart that lists out the datatype covnersions in SQL

If you are using T-SQL to do the 'conversion', the chart in Books Online is the definitive source.

And since it is the most complete source, I guess I don't understand what you are hoping to find.

|||

What does this mean? "lists out the datatype covnersions in SQL"

There are a couple of things here. Are you talking about implicit conversions? Or what types you can convert using cast and convert? Both are included in the chart in BOL in the CAST and CONVERT section.

Can you give us a sample of what you are looking for?

|||Btw, if you don't find the information in the Books Online topic useful or not satisfactory then please use the "Send Feedback" link in the topic. This will help you to provide feedback directly to the topic owners and will help improve the quality of the documentation.|||

I tried to convert an INT to a datetime, it gave an error "Arithmetic overflow error converting expression to data type datetime."

So I first converted it to varchar, and then to datetime, then it worked fine. eg

select convert(datetime,convert(varchar,xxx)),xxx,* from tblq

However, when I looked up, the BOL, it shows That Conversion from INT TO DATETIME, AND VARCHAR TO DATETIME Are both IMPLICT... Then how come the convert from Int to datetime errored out....?

I guess I am not able to Interpret the BOL Doc, for Implied and Explict conversions.. If it says impied conversion for both Varchar to datetime, and Int to datetime-- then why does 1 work and not the other.

|||

Sounds like a typo in the conversion -number too big, or perhaps, not a number ...

The following works as expected:

select convert( datetime, 2958463 )

9999-12-31 00:00:00.000

The following fails with the error you received -only 1 digit larger...

select convert( datetime, 2958464 )

Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.

|||hi,
what's the integer you're trying to convert as datetime? if it's greater than 2,958,463, it will produce that error because i think sqlserver supports datetime from 01/01/17? [convert(datetime, -53690)] to 12/31/9999 [convert(datetime, 2958463)].

- clintz|||

The Integer I am trying to convert is a large one, ie

select convert(datetime,20060404)
It is has a date value.

But I still need to know what is the diff between the Implicit and explicit conversions. BOL was not clear enough.

|||

Is 20060404 an integer, or a date? (It looks like a date to me...)

There is a really, really, big difference.

(The interger value 38809 would convert to 2006/04/04.)

As I, and others indicated in other responses to this post, the largest integer value that will convert to a datetime is 2958463. Your number, 20060404, greatly exceeds that value and WILL always cause an overflow error.

|||

If your data looks like 20060404 and is stored as interger values, and you wish to convert them to datetime values, try something like this:

First convert (or cast) to char(8), and then cast that to a datetime.

Code Snippet

SELECT cast( cast( 20060404 AS char(8)) AS datetime )

2006-04-04 00:00:00.000