Menu
I am using a SQL 2000 database.
I am working with a database in which I cannot change the types on the tables, or the stored procedures. One of the stored procedures I need to call expects a parameter of 'text'. I can get to the text field, but I am unable to figure out who to store that in a variable or any other way to pass it into the stored procedure?
If I try and create a text variable, SQL won't let me - if I convert it to varchar I only get the first character from the text field.
Any tricks to get around this much appreciated! Thank you!
725
The CAST() function converts a value (of any type) into a specified datatype.
There is no a big problem to change varchar to text because text supports more data length than varchar , but if the field has a index it must be drop and create new index with prefix col_name(length) (see CREATE INDEX syntax).
If you are planning to convert varchar to float you should know that these two data types are not compatible with each other. In the earlier versions of SQL Server you had to use CASE, ISNUMERIC & CONVERT to convert varchar to float but in SQL Server 2012, you can do it with just one function TRY_CONVERT.
Declare the variable of type varchar(8000)
declare @v varchar(8000)
SET @v = (SELECT CAST(textcol as varchar(8000)) FROM yourtable WHERE ....)
Obviously it might still be truncated but not at 1 character.
166
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
