Change local variable inside query

/*Given*/

CREATE TABLE [_T1sub] (
[PK] [int] IDENTITY (1, 1) NOT NULL ,
[FK] [int] NULL ,
[St] [char] (2) NULL ,
[Wt] [int] NULL ,
CONSTRAINT [PK__T1sub] PRIMARY KEY CLUSTERED
(
[PK]
) ON [PRIMARY]
) ON [PRIMARY]
GO

INSERT INTO _T1sub (FK,St,Wt) VALUES (1,'id',10)
INSERT INTO _T1sub (FK,St,Wt) VALUES (2,'nv',20)
INSERT INTO _T1sub (FK,St,Wt) VALUES (3,'wa',30)

/*
Is something like the following possible.
The point is to change the value of the variable
inside the query and use it in the calculated field.

This doesn't compile of course, but is there
a way to accomplish the same thing?
*/

DECLARE @.ndx int

SET @.ndx = 1

SELECT

(a.FK+ (CASE WHEN @.ndx > 0
THEN (SELECT @.ndx = b.Wt
FROM _T1sub b
WHERE b.Wt = a.Wt)
ELSE 0 END)
) as FKplusWT

FROM _T1sub a

/*Output would look like this:*/

FKplusWT
----
11
22
33

/*
I know, I can get this output just by adding
FK+WT. This is not about that.
This is about setting vars inside a query
*/

thanks, Otto PorterOn Sat, 02 Oct 2004 12:20:48 -0600, Otto Porter wrote:

>I know, I can get this output just by adding
>FK+WT. This is not about that.
>This is about setting vars inside a query

Hi Otto,

It's not possible to change the value of a variable during the execution
of a SELECT statement. At least not the way you are trying to do it.

You can of course do
SELECT @.var = ..., @.var = ...
FROM table
WHERE ...
but I assume that this is not what you want. You can't mix this format of
the SELECT statement with a SELECT that outputs a result set.

The way I read your example, it would be very easy to have queries where
the result would be dependent on the order in which rows are processed by
SQL Server. Since SQL Server is entirely free in it's choice of processing
order, the results would be unexpected and might even vary from execution
to execution.

Check out the following link to find some good examples of the possible
effects of unexpected processing order on assignments with the SELECT
statement:
http://groups.google.com/groups?hl=...FTNGP12.phx.gbl

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)sql

Change in Job Execution - SQL Server 2005?

I just converted a SQL Server 2000 database to 2005. I have a job whose only
step does the following:
Declare @.Command char(240)
Set @.Command = '\SDBS\Scheduler\Scheduler.Exe ' +
Convert(char(50),Convert(uniqueidentifie
r,[JOBID])) + ' ' + (Select
Replace([name],' ','~')
From msdb..sysjobs where job_id=[JOBID])
exec master..xp_cmdshell @.Command, NO_OUTPUT
Notice the [JOBID] portion. Under SQL Server 2000, the job id of the job was
passed to the job step. Under 2005, I get an error when I run the job saying
'JOBID' is undefined. Does anyone know what change I need to make?
Amos.The tokens have changed slightly. Have a look at this subject in 2005 BOL:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/105bbb66-0ade-4b46-b8e4-f849
e5fc4d43.htm
Andrew J. Kelly SQL MVP
"Amos Soma" <amos_j_soma@.yahoo.com> wrote in message
news:uYN9s7s5FHA.3544@.TK2MSFTNGP09.phx.gbl...
>I just converted a SQL Server 2000 database to 2005. I have a job whose
>only step does the following:
> Declare @.Command char(240)
> Set @.Command = '\SDBS\Scheduler\Scheduler.Exe ' +
> Convert(char(50),Convert(uniqueidentifie
r,[JOBID])) + ' ' + (Select
> Replace([name],' ','~')
> From msdb..sysjobs where job_id=[JOBID])
> exec master..xp_cmdshell @.Command, NO_OUTPUT
> Notice the [JOBID] portion. Under SQL Server 2000, the job id of the job
> was passed to the job step. Under 2005, I get an error when I run the job
> saying 'JOBID' is undefined. Does anyone know what change I need to make?
> Amos.
>

Change in Job Execution - SQL Server 2005?

I just converted a SQL Server 2000 database to 2005. I have a job whose only
step does the following:
Declare @.Command char(240)
Set @.Command = '\SDBS\Scheduler\Scheduler.Exe ' +
Convert(char(50),Convert(uniqueidentifier,[JOBID])) + ' ' + (Select
Replace([name],' ','~')
From msdb..sysjobs where job_id=[JOBID])
exec master..xp_cmdshell @.Command, NO_OUTPUT
Notice the [JOBID] portion. Under SQL Server 2000, the job id of the job was
passed to the job step. Under 2005, I get an error when I run the job saying
'JOBID' is undefined. Does anyone know what change I need to make?
Amos.Please don't post independently in separate newsgroups. You can add
multiple newsgroups to the header and then all the answers appear as one.
See my reply in the other newsgroup.
Andrew J. Kelly SQL MVP
"Amos Soma" <amos_j_soma@.yahoo.com> wrote in message
news:eIE1j7s5FHA.2576@.TK2MSFTNGP09.phx.gbl...
>I just converted a SQL Server 2000 database to 2005. I have a job whose
>only step does the following:
> Declare @.Command char(240)
> Set @.Command = '\SDBS\Scheduler\Scheduler.Exe ' +
> Convert(char(50),Convert(uniqueidentifier,[JOBID])) + ' ' + (Select
> Replace([name],' ','~')
> From msdb..sysjobs where job_id=[JOBID])
> exec master..xp_cmdshell @.Command, NO_OUTPUT
> Notice the [JOBID] portion. Under SQL Server 2000, the job id of the job
> was passed to the job step. Under 2005, I get an error when I run the job
> saying 'JOBID' is undefined. Does anyone know what change I need to make?
> Amos.
>|||Thank you.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e8AeOpt5FHA.3296@.TK2MSFTNGP09.phx.gbl...
> Please don't post independently in separate newsgroups. You can add
> multiple newsgroups to the header and then all the answers appear as one.
> See my reply in the other newsgroup.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Amos Soma" <amos_j_soma@.yahoo.com> wrote in message
> news:eIE1j7s5FHA.2576@.TK2MSFTNGP09.phx.gbl...
>>I just converted a SQL Server 2000 database to 2005. I have a job whose
>>only step does the following:
>> Declare @.Command char(240)
>> Set @.Command = '\SDBS\Scheduler\Scheduler.Exe ' +
>> Convert(char(50),Convert(uniqueidentifier,[JOBID])) + ' ' + (Select
>> Replace([name],' ','~')
>> From msdb..sysjobs where job_id=[JOBID])
>> exec master..xp_cmdshell @.Command, NO_OUTPUT
>> Notice the [JOBID] portion. Under SQL Server 2000, the job id of the job
>> was passed to the job step. Under 2005, I get an error when I run the job
>> saying 'JOBID' is undefined. Does anyone know what change I need to make?
>> Amos.
>sql

Change in Job Execution - SQL Server 2005?

I just converted a SQL Server 2000 database to 2005. I have a job whose only
step does the following:
Declare @.Command char(240)
Set @.Command = '\SDBS\Scheduler\Scheduler.Exe ' +
Convert(char(50),Convert(uniqueidentifier,[JOBID])) + ' ' + (Select
Replace([name],' ','~')
From msdb..sysjobs where job_id=[JOBID])
exec master..xp_cmdshell @.Command, NO_OUTPUT
Notice the [JOBID] portion. Under SQL Server 2000, the job id of the job was
passed to the job step. Under 2005, I get an error when I run the job saying
'JOBID' is undefined. Does anyone know what change I need to make?
Amos.
Please don't post independently in separate newsgroups. You can add
multiple newsgroups to the header and then all the answers appear as one.
See my reply in the other newsgroup.
Andrew J. Kelly SQL MVP
"Amos Soma" <amos_j_soma@.yahoo.com> wrote in message
news:eIE1j7s5FHA.2576@.TK2MSFTNGP09.phx.gbl...
>I just converted a SQL Server 2000 database to 2005. I have a job whose
>only step does the following:
> Declare @.Command char(240)
> Set @.Command = '\SDBS\Scheduler\Scheduler.Exe ' +
> Convert(char(50),Convert(uniqueidentifier,[JOBID])) + ' ' + (Select
> Replace([name],' ','~')
> From msdb..sysjobs where job_id=[JOBID])
> exec master..xp_cmdshell @.Command, NO_OUTPUT
> Notice the [JOBID] portion. Under SQL Server 2000, the job id of the job
> was passed to the job step. Under 2005, I get an error when I run the job
> saying 'JOBID' is undefined. Does anyone know what change I need to make?
> Amos.
>
|||Thank you.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e8AeOpt5FHA.3296@.TK2MSFTNGP09.phx.gbl...
> Please don't post independently in separate newsgroups. You can add
> multiple newsgroups to the header and then all the answers appear as one.
> See my reply in the other newsgroup.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Amos Soma" <amos_j_soma@.yahoo.com> wrote in message
> news:eIE1j7s5FHA.2576@.TK2MSFTNGP09.phx.gbl...
>

Change in Job Execution - SQL Server 2005?

I just converted a SQL Server 2000 database to 2005. I have a job whose only
step does the following:
Declare @.Command char(240)
Set @.Command = '\SDBS\Scheduler\Scheduler.Exe ' +
Convert(char(50),Convert(uniqueidentifie
r,[JOBID])) + ' ' + (Select
Replace([name],' ','~')
From msdb..sysjobs where job_id=[JOBID])
exec master..xp_cmdshell @.Command, NO_OUTPUT
Notice the [JOBID] portion. Under SQL Server 2000, the job id of the job
was
passed to the job step. Under 2005, I get an error when I run the job saying
'JOBID' is undefined. Does anyone know what change I need to make?
Amos.Please don't post independently in separate newsgroups. You can add
multiple newsgroups to the header and then all the answers appear as one.
See my reply in the other newsgroup.
Andrew J. Kelly SQL MVP
"Amos Soma" <amos_j_soma@.yahoo.com> wrote in message
news:eIE1j7s5FHA.2576@.TK2MSFTNGP09.phx.gbl...
>I just converted a SQL Server 2000 database to 2005. I have a job whose
>only step does the following:
> Declare @.Command char(240)
> Set @.Command = '\SDBS\Scheduler\Scheduler.Exe ' +
> Convert(char(50),Convert(uniqueidentifie
r,[JOBID])) + ' ' + (Select
> Replace([name],' ','~')
> From msdb..sysjobs where job_id=[JOBID])
> exec master..xp_cmdshell @.Command, NO_OUTPUT
> Notice the [JOBID] portion. Under SQL Server 2000, the job id of the j
ob
> was passed to the job step. Under 2005, I get an error when I run the job
> saying 'JOBID' is undefined. Does anyone know what change I need to make?
> Amos.
>|||Thank you.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e8AeOpt5FHA.3296@.TK2MSFTNGP09.phx.gbl...
> Please don't post independently in separate newsgroups. You can add
> multiple newsgroups to the header and then all the answers appear as one.
> See my reply in the other newsgroup.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Amos Soma" <amos_j_soma@.yahoo.com> wrote in message
> news:eIE1j7s5FHA.2576@.TK2MSFTNGP09.phx.gbl...
>

Change from char to varchar and RTRIM

Hi,
I have a big database with most of the columns having the datatype char
(char(10), (char(50) etc.)
I would like to change all these columns to varchar (varchar(10),
varchar(50), ...), and have all the spaces on the right trimmed of. So
instead of 'ABC ' I want to have 'ABC'.
Is there a 'nice' way to do this? Changing all the char-columns to varchar
doesn't seem too difficult to me, but would there be a way that automaticly
cuts of this trailing spaces? Or is there some script that exists that does
this for me?
The biggest problem in my opinion is that some of these columns are used as
foreign keys. So I don't know if it is possible that they will be trimmed in
one table, but not yet in the foreign-key-table?
Any help our hints would be really appreciated!
Thanks a lot in advance,
PieterHello, Pieter
To change the data types of the columns, you need to drop the foreign
keys, change all the columns involved (using "ALTER TABLE tbl ALTER
COLUMN col varchar(n)") and then recreate the foreign keys. That's
because a foreign key requires the referencing columns to have the same
data type as the referenced columns (additionally, a column involved in
any kind of constraint cannot be altered).
The trimming can be done before or after recreating the foreign keys,
because when SQL Server compares 'a' with 'a ', they will be equal. For
example, the following would work just fine:
USE tempdb
CREATE TABLE t1 (x varchar(10) primary key)
CREATE TABLE t2 (y varchar(10) references t1)
INSERT INTO t1 VALUES('a')
INSERT INTO t2 VALUES('a ')
SELECT x+'!', y+'!' FROM t1 INNER JOIN t2 ON x=y
DROP TABLE t2,t1
Razvan

Change from char to varchar and RTRIM

Hi,
I have a big database with most of the columns having the datatype char
(char(10), (char(50) etc.)
I would like to change all these columns to varchar (varchar(10),
varchar(50), ...), and have all the spaces on the right trimmed of. So
instead of 'ABC ' I want to have 'ABC'.
Is there a 'nice' way to do this? Changing all the char-columns to varchar
doesn't seem too difficult to me, but would there be a way that automaticly
cuts of this trailing spaces? Or is there some script that exists that does
this for me?
The biggest problem in my opinion is that some of these columns are used as
foreign keys. So I don't know if it is possible that they will be trimmed in
one table, but not yet in the foreign-key-table?
Any help our hints would be really appreciated!
Thanks a lot in advance,
PieterHello, Pieter
To change the data types of the columns, you need to drop the foreign
keys, change all the columns involved (using "ALTER TABLE tbl ALTER
COLUMN col varchar(n)") and then recreate the foreign keys. That's
because a foreign key requires the referencing columns to have the same
data type as the referenced columns (additionally, a column involved in
any kind of constraint cannot be altered).
The trimming can be done before or after recreating the foreign keys,
because when SQL Server compares 'a' with 'a ', they will be equal. For
example, the following would work just fine:
USE tempdb
CREATE TABLE t1 (x varchar(10) primary key)
CREATE TABLE t2 (y varchar(10) references t1)
INSERT INTO t1 VALUES('a')
INSERT INTO t2 VALUES('a ')
SELECT x+'!', y+'!' FROM t1 INNER JOIN t2 ON x=y
DROP TABLE t2,t1
Razvan

Change Format of Dates

I don't know why my company did this, but dates are being stored in a char field within our database. Meanwhile, I've been setting up new pages using datetime.

That's just a little back story. My question is, is it possible to change the format of all dates in the table from yyyy/MM/dd to MM/dd/yyyy in the char field? I'm just trying to think of an easier way to change a thousand or so records instead of doing it manually.

Thanks.

Try this:

string temp ="2006/12/16";
string[] field = temp.Split(("/").ToCharArray());
string reversedate = field[2] +"/" + field[1] +"/" + field[0];
DateTime newdate = DateTime.Parse(reversedate);

|||

Or better still, run this SQL

Update table set newdatefield = parsename(replace(oldchardate, '/', '.'), 2) + '/' + parsename(replace(oldchardate, '/', '.'), 1) + '/' + parsename(replace(oldchardate, '/', '.'), 3)

|||

try this logic

print

convert(varchar(20),convert(datetime,'2002/02/23',111),101)

so you can just do update on your table date field content by

update YourTable
set datefield=convert(varchar(20),convert(datetime,datefield,111),101)

Thanks

|||

I ended up using this logic in a SQL statement:

datefield=right(datefield, 5)&'/'&left(datefield, 4)

sql

Change Datafield Length for all Table

in myDatabase, over 50 tables.
Every table got the field 'userid' , Now I need to change the field length
from char(10) to char(15)
How Can I do that ? Any simple and fast way to do ?
Thanks for your kind help
> in myDatabase, over 50 tables.
> Every table got the field 'userid' , Now I need to change the field length
> from char(10) to char(15)
> How Can I do that ? Any simple and fast way to do ?
You could create a cursor, loop though your tables and dynamically
concatenate the ALTER TABLE string and execute it with the EXECUTE
statement. Unfortunately I don't see any other shortcut.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
|||Also you can use undocumented SP sp_Msforeachtable 'your_command [?]', it
executes supplied command on each table in your DB, (it is similar to the
cursor on each table).
"Dejan Sarka" wrote:

> You could create a cursor, loop though your tables and dynamically
> concatenate the ALTER TABLE string and execute it with the EXECUTE
> statement. Unfortunately I don't see any other shortcut.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
>
>

Change Datafield Length for all Table

in myDatabase, over 50 tables.
Every table got the field 'userid' , Now I need to change the field length
from char(10) to char(15)
How Can I do that ? Any simple and fast way to do '
Thanks for your kind help> in myDatabase, over 50 tables.
> Every table got the field 'userid' , Now I need to change the field length
> from char(10) to char(15)
> How Can I do that ? Any simple and fast way to do '
You could create a cursor, loop though your tables and dynamically
concatenate the ALTER TABLE string and execute it with the EXECUTE
statement. Unfortunately I don't see any other shortcut.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Also you can use undocumented SP sp_Msforeachtable 'your_command [?]', it
executes supplied command on each table in your DB, (it is similar to the
cursor on each table).
"Dejan Sarka" wrote:
> > in myDatabase, over 50 tables.
> > Every table got the field 'userid' , Now I need to change the field length
> > from char(10) to char(15)
> > How Can I do that ? Any simple and fast way to do '
> You could create a cursor, loop though your tables and dynamically
> concatenate the ALTER TABLE string and execute it with the EXECUTE
> statement. Unfortunately I don't see any other shortcut.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
>
>

Change Datafield Length for all Table

in myDatabase, over 50 tables.
Every table got the field 'userid' , Now I need to change the field length
from char(10) to char(15)
How Can I do that ? Any simple and fast way to do '
Thanks for your kind help> in myDatabase, over 50 tables.
> Every table got the field 'userid' , Now I need to change the field length
> from char(10) to char(15)
> How Can I do that ? Any simple and fast way to do '
You could create a cursor, loop though your tables and dynamically
concatenate the ALTER TABLE string and execute it with the EXECUTE
statement. Unfortunately I don't see any other shortcut.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Also you can use undocumented SP sp_Msforeachtable 'your_command [?]', i
t
executes supplied command on each table in your DB, (it is similar to the
cursor on each table).
"Dejan Sarka" wrote:

> You could create a cursor, loop though your tables and dynamically
> concatenate the ALTER TABLE string and execute it with the EXECUTE
> statement. Unfortunately I don't see any other shortcut.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
>
>

Change Column lenght on a replicated database

I need to increase the a column in a replicated merge database. The
column is 100 char in lenght and I need to change it to 200. I have
read the "Schema Changes on Publication Databases". But it seems to
be a little confusing.
Thanks.
Alter table is possible in SQL Server 2005 but unfortunately this is not
possible in SQL Server 2000. You could drop and readd the subscription, or
you could solve it in a roundabout way: add a new column with the new
datatype (sp_repladdcolumn), do an update on the table to populate the
column, then drop the column (sp_repldropcolumn). Do this again to create
the column having the same original name.
HTH,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message news:<#Q7NK#KrEHA.2588@.TK2MSFTNGP12.phx.gbl>...
> Alter table is possible in SQL Server 2005 but unfortunately this is not
> possible in SQL Server 2000. You could drop and readd the subscription, or
> you could solve it in a roundabout way: add a new column with the new
> datatype (sp_repladdcolumn), do an update on the table to populate the
> column, then drop the column (sp_repldropcolumn). Do this again to create
> the column having the same original name.
> HTH,
> Paul Ibison
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
Thank you that's what I thought. Unfortunally, I have SQL 2000 and it
seems there are a lot of steps for something so simple. Maybe I should
just stop replication make the changes, delete the replica and do
replication again.
Same amount of work I guess
|||This has caused us a lot of heartache also... Such a simple thing! Once this
stuff is in production it can be a nightmare & unnecessary risk to change
columns
"xkravenx" <scubamike@.gmail.com> wrote in message
news:596eda8b.0410080740.76ee0786@.posting.google.c om...
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:<#Q7NK#KrEHA.2588@.TK2MSFTNGP12.phx.gbl>...[vbcol=seagreen]
or[vbcol=seagreen]
create
>
> Thank you that's what I thought. Unfortunally, I have SQL 2000 and it
> seems there are a lot of steps for something so simple. Maybe I should
> just stop replication make the changes, delete the replica and do
> replication again.
> Same amount of work I guess

change all char columns to varchar

Hi ,
Our company decided to convert all char columns to varchar in our next
software release.
We have lot (40+) of client databases which is more than 75GB .
Below the query I wrote for this ,but it's taking long long hours to
complete the upgrade.
Is there any better way to write this '
Thanks
--*******************************************************************
-- Script to convert all char columns to varchar
--*******************************************************************
declare @.tbname varchar(255)
declare @.column_name varchar(255)
declare @.is_nullable varchar(255)
declare @.character_maximum_length varchar(255)
declare @.Query varchar(8000)
declare c1 cursor for
select
collist.table_name,collist.column_name,collist.is_nullable,collist.character
_maximum_length from information_schema.columns
collist,information_schema.tables tablist
where collist.table_name=tablist.table_name and tablist.table_type='BASE
TABLE' and data_type='char'
open c1
fetch c1 into @.tbname,@.column_name,@.is_nullable,@.character_maximum_length
while @.@.fetch_status=0
begin
if (@.is_nullable='No')
set @.is_nullable='NOT NULL'
else
set @.is_nullable='NULL'
set @.Query='alter table '+@.tbname +' alter column '+@.column_name+'
VARCHAR('+@.character_maximum_length+') '+@.is_nullable
exec (@.Query)
fetch c1 into @.tbname,@.column_name,@.is_nullable,@.character_maximum_length
end
deallocate c1
GO
PRINT '** Script 07 -- 07.char to varchar.sql completed **'
goI think you can change the Cursor to table variable ... Add Set NOCOUNT ON
to remove the un-necessary verbose of "n rows affected" ...
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
"Abraham" <binu_ca@.yahoo.com> wrote in message
news:uQ78i$QmDHA.2068@.TK2MSFTNGP09.phx.gbl...
> Hi ,
> Our company decided to convert all char columns to varchar in our next
> software release.
> We have lot (40+) of client databases which is more than 75GB .
> Below the query I wrote for this ,but it's taking long long hours to
> complete the upgrade.
> Is there any better way to write this '
> Thanks
>
> --*******************************************************************
> -- Script to convert all char columns to varchar
> --*******************************************************************
> declare @.tbname varchar(255)
> declare @.column_name varchar(255)
> declare @.is_nullable varchar(255)
> declare @.character_maximum_length varchar(255)
> declare @.Query varchar(8000)
> declare c1 cursor for
> select
>
collist.table_name,collist.column_name,collist.is_nullable,collist.character
> _maximum_length from information_schema.columns
> collist,information_schema.tables tablist
> where collist.table_name=tablist.table_name and tablist.table_type='BASE
> TABLE' and data_type='char'
> open c1
> fetch c1 into @.tbname,@.column_name,@.is_nullable,@.character_maximum_length
> while @.@.fetch_status=0
> begin
> if (@.is_nullable='No')
> set @.is_nullable='NOT NULL'
> else
> set @.is_nullable='NULL'
> set @.Query='alter table '+@.tbname +' alter column '+@.column_name+'
> VARCHAR('+@.character_maximum_length+') '+@.is_nullable
> exec (@.Query)
> fetch c1 into @.tbname,@.column_name,@.is_nullable,@.character_maximum_length
> end
> deallocate c1
> GO
> PRINT '** Script 07 -- 07.char to varchar.sql completed **'
> go
>
>|||Each ALTER TABLE statement will require updating every row in the table.
This will take quite some time if you have many char columns in a single
table because you'll be updating each row many times. Furthermore, this
will not trim trailing spaces from the char column and leave a lot of
other wasted space in the table.
You will be better off creating a set of tables with the new structure
and loading via DTS. You can trim spaces during the process. This
method will provide the additional flexibility of allowing you to keep
existing char datatypes where appropriate.
Another method is to create new tables using SELECT INTO. This can be a
bit tricky and is problematic with identity columns. Example below.
CREATE TABLE MyTable
(
Col1 int NOT NULL,
Col2 char(10) NOT NULL,
Col3 char(10) NULL
)
INSERT INTO MyTable VALUES (1, 'a', 'b')
GO
SELECT
Col1,
-- need to specify ISNULL to create a NOT NULL
ISNULL(CAST(RTRIM(Col2) AS varchar(10)), '') AS Col2,
CAST(RTRIM(Col2) AS varchar(10)) AS Col3
INTO MyTable_New
FROM MyTable
DROP TABLE MyTable
EXEC sp_rename 'MyTable_New', 'MyTable'
GO
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Abraham" <binu_ca@.yahoo.com> wrote in message
news:uQ78i$QmDHA.2068@.TK2MSFTNGP09.phx.gbl...
> Hi ,
> Our company decided to convert all char columns to varchar in our next
> software release.
> We have lot (40+) of client databases which is more than 75GB .
> Below the query I wrote for this ,but it's taking long long hours to
> complete the upgrade.
> Is there any better way to write this '
> Thanks
>
> --*******************************************************************
> -- Script to convert all char columns to varchar
> --*******************************************************************
> declare @.tbname varchar(255)
> declare @.column_name varchar(255)
> declare @.is_nullable varchar(255)
> declare @.character_maximum_length varchar(255)
> declare @.Query varchar(8000)
> declare c1 cursor for
> select
>
collist.table_name,collist.column_name,collist.is_nullable,collist.chara
cter
> _maximum_length from information_schema.columns
> collist,information_schema.tables tablist
> where collist.table_name=tablist.table_name and
tablist.table_type='BASE
> TABLE' and data_type='char'
> open c1
> fetch c1 into
@.tbname,@.column_name,@.is_nullable,@.character_maximum_length
> while @.@.fetch_status=0
> begin
> if (@.is_nullable='No')
> set @.is_nullable='NOT NULL'
> else
> set @.is_nullable='NULL'
> set @.Query='alter table '+@.tbname +' alter column '+@.column_name+'
> VARCHAR('+@.character_maximum_length+') '+@.is_nullable
> exec (@.Query)
> fetch c1 into
@.tbname,@.column_name,@.is_nullable,@.character_maximum_length
> end
> deallocate c1
> GO
> PRINT '** Script 07 -- 07.char to varchar.sql completed **'
> go
>
>

Casting char to int but not causing error

Hi. In the where clause of my query I will be converting a char column to an
int to match it to another int column. But the char could contain characters
and not convert to an int. In that case I don't want the query to fail but
simply not do the match. Is that possible?
select * from A, B where cast ( A.CharCol as int ) = B.IntCol
Thanks.
McGy
[url]http://mcgy.blogspot.com[/url]Hi
CREATE TABLE #Test
(
col VARCHAR(10)
)
INSERT INTO #Test VALUES ('441')
INSERT INTO #Test VALUES ('55a')
SELECT CAST(col AS INT) FROM #Test
--Server: Msg 245, Level 16, State 1, Line 1
--Syntax error converting the varchar value '55a' to a column of data type
int.
SELECT CAST(col AS INT) FROM #Test WHERE ISNUMERIC(col)=1
You can visit at Aaron's web site www.aspfaq.com to find a script as
alternative to ISNUMERIC() function
"McGy" <anon@.anon.com> wrote in message
news:eiVV8j1wFHA.2620@.TK2MSFTNGP09.phx.gbl...
> Hi. In the where clause of my query I will be converting a char column to
> an
> int to match it to another int column. But the char could contain
> characters
> and not convert to an int. In that case I don't want the query to fail but
> simply not do the match. Is that possible?
> select * from A, B where cast ( A.CharCol as int ) = B.IntCol
> Thanks.
> --
> McGy
> [url]http://mcgy.blogspot.com[/url]
>
>|||Thanks Uri. Unfortunately your example does not work for me. I am not
selecting the char column as an int but rather using it in the where clause
as an int.
Thankfully I have just figured it out myself using the AND clause as
follows - try with A set to 'a' then A set to '1':
declare @.A as char (1)
set @.A = 'a'
select
1
where
( isnumeric ( @.A ) = 1 )
and
( cast ( @.A as int ) = 1 )
McGy
[url]http://mcgy.blogspot.com[/url]
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:u$4xKx1wFHA.3720@.TK2MSFTNGP14.phx.gbl...
> Hi
> CREATE TABLE #Test
> (
> col VARCHAR(10)
> )
> INSERT INTO #Test VALUES ('441')
> INSERT INTO #Test VALUES ('55a')
> SELECT CAST(col AS INT) FROM #Test
> --Server: Msg 245, Level 16, State 1, Line 1
> --Syntax error converting the varchar value '55a' to a column of data
type
> int.
> SELECT CAST(col AS INT) FROM #Test WHERE ISNUMERIC(col)=1
>
> You can visit at Aaron's web site www.aspfaq.com to find a script as
> alternative to ISNUMERIC() function
>
> "McGy" <anon@.anon.com> wrote in message
> news:eiVV8j1wFHA.2620@.TK2MSFTNGP09.phx.gbl...
to
but
>|||Hi
Actually ,I only tried to give you an idea for solving the problem.
"McGy" <anon@.anon.com> wrote in message
news:eDZ8cA2wFHA.1032@.TK2MSFTNGP12.phx.gbl...
> Thanks Uri. Unfortunately your example does not work for me. I am not
> selecting the char column as an int but rather using it in the where
> clause
> as an int.
> Thankfully I have just figured it out myself using the AND clause as
> follows - try with A set to 'a' then A set to '1':
> declare @.A as char (1)
> set @.A = 'a'
> select
> 1
> where
> ( isnumeric ( @.A ) = 1 )
> and
> ( cast ( @.A as int ) = 1 )
>
> --
> McGy
> [url]http://mcgy.blogspot.com[/url]
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:u$4xKx1wFHA.3720@.TK2MSFTNGP14.phx.gbl...
> type
> to
> but
>|||Hi Uri,
isNumeric() doesn't work in all cases. For example:
select isnumeric('34e5') -- return 1
select cast('34e5' as int) -- return error
Back to your problem, try this:
select * from A, B
where case when A.CharCol not like '%^[0-9]%' then null else cast (
A.CharCol as int ) end = B.IntCol|||Thanks for that clarification!
McGy
[url]http://mcgy.blogspot.com[/url]
"Tam Vu" <vuht2000@.yahoo.com> wrote in message
news:1127833853.921282.306230@.f14g2000cwb.googlegroups.com...
> Hi Uri,
> isNumeric() doesn't work in all cases. For example:
> select isnumeric('34e5') -- return 1
> select cast('34e5' as int) -- return error
> Back to your problem, try this:
> select * from A, B
> where case when A.CharCol not like '%^[0-9]%' then null else cast (
> A.CharCol as int ) end = B.IntCol
>|||Tam
I you read my post carefully , you would see what I wrote at the ned of the
post
"Tam Vu" <vuht2000@.yahoo.com> wrote in message
news:1127833853.921282.306230@.f14g2000cwb.googlegroups.com...
> Hi Uri,
> isNumeric() doesn't work in all cases. For example:
> select isnumeric('34e5') -- return 1
> select cast('34e5' as int) -- return error
> Back to your problem, try this:
> select * from A, B
> where case when A.CharCol not like '%^[0-9]%' then null else cast (
> A.CharCol as int ) end = B.IntCol
>|||Uri,
indeed I didn't read your post carefully. My post was meant to the
original poster ( = McGy), but I thoguth it was you ;)
cheers,

cast a date to string

Hi,
how to cast a date field to a string:
select case(TrxDate, char(20)) & ' Add me' AS newfield from tb'
Thanks.Hopefully this example gets you started:
select convert(char(20),getdate()) + ' add me' as newcolumn
select convert(char(20),getdate(),101) + ' add me' as newcolumn
select convert(char(20),getdate(),113) + ' add me' as newcolumn
select convert(char(20),getdate(),111) + ' add me' as newcolumn
Keith
"js" <js@.someone@.hotmail.com> wrote in message
news:uyPTlxHBFHA.1084@.tk2msftngp13.phx.gbl...
> Hi,
> how to cast a date field to a string:
> select case(TrxDate, char(20)) & ' Add me' AS newfield from tb'
> Thanks.
>|||Use function CAST or CONVERT.
Example:
use northwind
go
select orderid, convert(char(8), orderdate, 112) + ' whatever'
from orders
go
AMB
"js" wrote:

> Hi,
> how to cast a date field to a string:
> select case(TrxDate, char(20)) & ' Add me' AS newfield from tb'
> Thanks.
>
>|||Thanks Keith,
But has one requirement: need to format the date using a outside predefined
format(gDateFormat = "dd-mmm-yyyy", can be changed by the user)
How to do that? Do it in a query or in application level? Pls advice.
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:%23xyoY0HBFHA.1388@.TK2MSFTNGP09.phx.gbl...
> Hopefully this example gets you started:
> select convert(char(20),getdate()) + ' add me' as newcolumn
> select convert(char(20),getdate(),101) + ' add me' as newcolumn
> select convert(char(20),getdate(),113) + ' add me' as newcolumn
> select convert(char(20),getdate(),111) + ' add me' as newcolumn
>|||Read up on CAST and CONVERT within (SQL Server) Books Online (within the SQL
Server program group). If all the user definable predefined formats map to
styles that can be specified by the user perhaps you could use T-SQL.
The easier approach might be to simply return the datetime from SQL Server
and then convert it as requested within your application's business logic.
Keith
"js" <js@.someone@.hotmail.com> wrote in message
news:OpPLvKIBFHA.824@.TK2MSFTNGP11.phx.gbl...
> Thanks Keith,
> But has one requirement: need to format the date using a outside
predefined
> format(gDateFormat = "dd-mmm-yyyy", can be changed by the user)
> How to do that? Do it in a query or in application level? Pls advice.
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
> news:%23xyoY0HBFHA.1388@.TK2MSFTNGP09.phx.gbl...
>|||Thanks Keith,
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:ubF5ZhIBFHA.3708@.TK2MSFTNGP14.phx.gbl...
> Read up on CAST and CONVERT within (SQL Server) Books Online (within the
> SQL
> Server program group). If all the user definable predefined formats map
> to
> styles that can be specified by the user perhaps you could use T-SQL.
>
What's T-SQL, how to do that? Pls advice.