access the Sql server: varchar

Showing posts with label varchar. Show all posts

Wednesday, March 28, 2012

How to assign values to variables in a procedure in with select s

Hi,
I have code like this
:
CREATE PROCEDURE GET_OFFENDER_DATA @.value1 VARCHAR(8),@.name VARCHAR(30)
OUTPUT,@.sex VARCHAR(1) OUTPUT
AS
SELECT NAME,SEX from table1 where .....
Now I want to assign the value of Name and sex from the select statement to
variables @.name and @.sex.
How do I do that. I just do not want to execute the select statement twice
as the selection criteria is big.
Thanks,
JSSELECT @.name = name, @.sex = sex
FROM table1
WHERE (something that guarantees exactly one row)
"JS" <JS@.discussions.microsoft.com> wrote in message
news:3796EA53-CC28-46F5-9653-68A052634C94@.microsoft.com...
> Hi,
> I have code like this
> :
> CREATE PROCEDURE GET_OFFENDER_DATA @.value1 VARCHAR(8),@.name VARCHAR(30)
> OUTPUT,@.sex VARCHAR(1) OUTPUT
> AS
> SELECT NAME,SEX from table1 where .....
> Now I want to assign the value of Name and sex from the select statement
> to
> variables @.name and @.sex.
> How do I do that. I just do not want to execute the select statement twice
> as the selection criteria is big.
> Thanks,
> JS
>sql

How to assign a column's value to another column's default

I got a table. i m using SQL 2005.

tblMy
--
col1 varchar(10) NotNull
col2 varchar(10) AllowNulls
col3 varchar(10) AllowNulls

Question: How to assign the col1's value to col3's default value. If user enters a data to col3 , its ok , but if don't SQL will automatically insert the value of col1 to col3.

Happy Coding....

I don't think a default constraint will work for this. You could use a trigger though.

Alteratively, you could mimic this behavior with a view, something like:

create view myview as select col1, col2, coalesce(col3,col1) from mytable

|||

View may be solution to another problem, but not mine. But trigger is a good idea, i thought before , but im inexprienced on triggers. I'll try to learn as quick as i can. Thanks for your post.

Happy Coding...

|||

I found the answer of my own question:

To do this, i use "Computed Column Specification" (Formula Section). Obviously it is surprising no one answered this simple question except 1 person. Interesting

Happy Coding...

sql

Monday, March 26, 2012

How to apply complex constraints

Hi all!
I want to create a constraint that uses data from other tables,
specifically i want to make sure that a varchar has exactly the length
specified in an integer-column in a table that I pointed out with a
foreign key.

I would like this to be solved something like this:

create table string_size_limits
(
row_id INTEGER PRIMARY KEY
string_size INTEGER
)

create table strings
(
string_size_limit_row_id INTEGER
REFERENCES string_size_limits(row_id)
string varcher(50)
CONSTRAINT check_string_size CHECK ?
)

Is it possible to solve this problem without SP using above model?
Is it possible to solve this problem with SP using above model?
Must the above problem be solved using triggers?

Any help appreciated"Jon" <jonsjostedt@.hotmail.com> wrote in message
news:9f379edc.0503150952.59caa802@.posting.google.c om...
> Hi all!
> I want to create a constraint that uses data from other tables,
> specifically i want to make sure that a varchar has exactly the length
> specified in an integer-column in a table that I pointed out with a
> foreign key.
> I would like this to be solved something like this:
> create table string_size_limits
> (
> row_id INTEGER PRIMARY KEY
> string_size INTEGER
> )
> create table strings
> (
> string_size_limit_row_id INTEGER
> REFERENCES string_size_limits(row_id)
> string varcher(50)
> CONSTRAINT check_string_size CHECK ?
> )
> Is it possible to solve this problem without SP using above model?
> Is it possible to solve this problem with SP using above model?
> Must the above problem be solved using triggers?
> Any help appreciated

A CHECK constraint can only access data in the table it's created on, but in
your example, what is the purpose of Row_ID as a primary key? In other
words, what is the difference between these two limits:

insert into string_size_limits select 1, 5
insert into string_size_limits select 2, 5

Is the second string size limit somehow different because its row_id is
different? If the primary key of your limits table was just the string size
itself, then the foreign key could be used in the CHECK constraint:

create table dbo.StringSizes (
StringSize int not null,
constraint PK_StringSizes primary key (StringSize)
)

create table dbo.Strings
(
StringSize int not null,
String varchar(50) not null,
constraint PK_Strings primary key (String),
constraint FK_Strings_StringSizes foreign key (StringSize)
references StringSizes (StringSize),
constraint CHK_StringLength check (len(String) = StringSize)
)

insert into dbo.StringSizes select 3
insert into dbo.StringSizes select 5

insert into dbo.Strings select 3, 'Jon'
insert into dbo.Strings select 3, 'John' -- Fails
insert into dbo.Strings select 5, 'Check'
insert into dbo.Strings select 5, 'Cheque' -- Fails

If that doesn't help, I suggest you give some more details on Row_Id, and
also working CREATE TABLE and INSERT statements. But if you can't use the
string size limit itself in the foreign key, a trigger is the most likely
alternative.

Simon|||Jon (jonsjostedt@.hotmail.com) writes:
> I want to create a constraint that uses data from other tables,
> specifically i want to make sure that a varchar has exactly the length
> specified in an integer-column in a table that I pointed out with a
> foreign key.
> I would like this to be solved something like this:
> create table string_size_limits
> (
> row_id INTEGER PRIMARY KEY
> string_size INTEGER
> )
> create table strings
> (
> string_size_limit_row_id INTEGER
> REFERENCES string_size_limits(row_id)
> string varcher(50)
> CONSTRAINT check_string_size CHECK ?
> )
> Is it possible to solve this problem without SP using above model?
> Is it possible to solve this problem with SP using above model?
> Must the above problem be solved using triggers?

The problem does not need be solved with triggers, but that's the best
solution.

The alternative is to write a UDF which access the string_size_limits
table, and then you CHECK constraint would read:

CHECK (len(string) = dbo.maxlen(row_id))

The reason you should not do this, is because the performance penalty
can be severe. I remember that I played with this once, and added a
constraint with a UDF to the copy of an existing table. I then inserted
all 24000 rows into that table. Instead of two seconds it took 30!

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||In full SQL-92, you can write such a CHECK() constraint, but not in SQL
Server yet. You would have to use a trigger and get away from
declarative code.

However, why are you putting metadata into the database in violation of
basic design principles? This is sooo wrong.|||"--CELKO--" <jcelko212@.earthlink.net> a crit dans le message de
news:1110989814.440585.47880@.o13g2000cwo.googlegro ups.com...
> In full SQL-92, you can write such a CHECK() constraint, but not in SQL
> Server yet. You would have to use a trigger and get away from
> declarative code.
> However, why are you putting metadata into the database in violation of
> basic design principles? This is sooo wrong.

Why is this so wrong? How about a link to thoes basic design principles?
Where do you think metadata should be stored?

Friday, March 23, 2012

How to alter the data type of a field

I want to change the filed type form nvarchar(5) to varchar(10)
There is no need to reserve the data in the filed.
How can I do with SQL command?Use the ALTER TABLE command:
ALTER TABLE my_table
ALTER COLUMN my_column varchar(10)
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:umBJWlAGHHA.4652@.TK2MSFTNGP04.phx.gbl...
>I want to change the filed type form nvarchar(5) to varchar(10)
> There is no need to reserve the data in the filed.
> How can I do with SQL command?
>|||Thank,
Can I determinate if the data type varchar before ater it.
Like
if type(my_table.my_column) <>varchar(10)
ALTER TABLE my_table ALTER COLUMN my_column varchar(10)
"Kalen Delaney" <replies@.public_newsgroups.com> ¼¶¼g©ó¶l¥ó·s»D:O30DznAGHHA.2128@.TK2MSFTNGP03.phx.gbl...
> Use the ALTER TABLE command:
> ALTER TABLE my_table
> ALTER COLUMN my_column varchar(10)
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
> "ad" <flying@.wfes.tcc.edu.tw> wrote in message
> news:umBJWlAGHHA.4652@.TK2MSFTNGP04.phx.gbl...
>>I want to change the filed type form nvarchar(5) to varchar(10)
>> There is no need to reserve the data in the filed.
>> How can I do with SQL command?
>|||What version?
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:OD2cMPBGHHA.5104@.TK2MSFTNGP03.phx.gbl...
> Thank,
> Can I determinate if the data type varchar before ater it.
> Like
> if type(my_table.my_column) <>varchar(10)
> ALTER TABLE my_table ALTER COLUMN my_column varchar(10)
>
>
> "Kalen Delaney" <replies@.public_newsgroups.com>
> ¼¶¼g©ó¶l¥ó·s»D:O30DznAGHHA.2128@.TK2MSFTNGP03.phx.gbl...
>> Use the ALTER TABLE command:
>> ALTER TABLE my_table
>> ALTER COLUMN my_column varchar(10)
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> http://sqlblog.com
>>
>> "ad" <flying@.wfes.tcc.edu.tw> wrote in message
>> news:umBJWlAGHHA.4652@.TK2MSFTNGP04.phx.gbl...
>>I want to change the filed type form nvarchar(5) to varchar(10)
>> There is no need to reserve the data in the filed.
>> How can I do with SQL command?
>>
>|||my version is SQLServer 2005 Express
"Kalen Delaney" <replies@.public_newsgroups.com> ¼¶¼g©ó¶l¥ó·s»D:ORvLXDCGHHA.4804@.TK2MSFTNGP03.phx.gbl...
> What version?
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
> "ad" <flying@.wfes.tcc.edu.tw> wrote in message
> news:OD2cMPBGHHA.5104@.TK2MSFTNGP03.phx.gbl...
>> Thank,
>> Can I determinate if the data type varchar before ater it.
>> Like
>> if type(my_table.my_column) <>varchar(10)
>> ALTER TABLE my_table ALTER COLUMN my_column varchar(10)
>>
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> ¼¶¼g©ó¶l¥ó·s»D:O30DznAGHHA.2128@.TK2MSFTNGP03.phx.gbl...
>> Use the ALTER TABLE command:
>> ALTER TABLE my_table
>> ALTER COLUMN my_column varchar(10)
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> http://sqlblog.com
>>
>> "ad" <flying@.wfes.tcc.edu.tw> wrote in message
>> news:umBJWlAGHHA.4652@.TK2MSFTNGP04.phx.gbl...
>>I want to change the filed type form nvarchar(5) to varchar(10)
>> There is no need to reserve the data in the filed.
>> How can I do with SQL command?
>>
>>
>|||This query returns all the datatypes and lengths for columns in a given
table. You should be able to figure out how to write your IF statement from
this information:
SELECT object_name(object_id) as [object_name], c.name as column_name,
t.name as type_name, c.max_length
FROM sys.types t join sys.columns c
ON t.user_type_id = c.system_type_id
WHERE object_name(object_id) = 'my_table'
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:eoktgyCGHHA.2456@.TK2MSFTNGP06.phx.gbl...
> my version is SQLServer 2005 Express
> "Kalen Delaney" <replies@.public_newsgroups.com>
> ¼¶¼g©ó¶l¥ó·s»D:ORvLXDCGHHA.4804@.TK2MSFTNGP03.phx.gbl...
>> What version?
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> http://sqlblog.com
>>
>> "ad" <flying@.wfes.tcc.edu.tw> wrote in message
>> news:OD2cMPBGHHA.5104@.TK2MSFTNGP03.phx.gbl...
>> Thank,
>> Can I determinate if the data type varchar before ater it.
>> Like
>> if type(my_table.my_column) <>varchar(10)
>> ALTER TABLE my_table ALTER COLUMN my_column varchar(10)
>>
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com>
>> ¼¶¼g©ó¶l¥ó·s»D:O30DznAGHHA.2128@.TK2MSFTNGP03.phx.gbl...
>> Use the ALTER TABLE command:
>> ALTER TABLE my_table
>> ALTER COLUMN my_column varchar(10)
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> http://sqlblog.com
>>
>> "ad" <flying@.wfes.tcc.edu.tw> wrote in message
>> news:umBJWlAGHHA.4652@.TK2MSFTNGP04.phx.gbl...
>>I want to change the filed type form nvarchar(5) to varchar(10)
>> There is no need to reserve the data in the filed.
>> How can I do with SQL command?
>>
>>
>>
>

How to alter an used alias type?

If I created an alias type as followed,
CREATE TYPE SSN
FROM varchar(11) NOT NULL
For some reasons, I would like to modify this alias type to varchar(20) not
null,
How to do it?
--Frank, using SQL2005devYou can to drop it and re-create it. But you cannot drop it as long as it is
being used, so...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Frank Lee" <Reply@.to.newsgroup> wrote in message news:OI8c$X$LGHA.140@.TK2MSFTNGP12.phx.gbl
..
> If I created an alias type as followed,
> CREATE TYPE SSN
> FROM varchar(11) NOT NULL
> For some reasons, I would like to modify this alias type to varchar(20) no
t
> null,
> How to do it?
> --Frank, using SQL2005dev
>|||Frank Lee (Reply@.to.newsgroup) writes:
> If I created an alias type as followed,
> CREATE TYPE SSN
> FROM varchar(11) NOT NULL
> For some reasons, I would like to modify this alias type to varchar(20)
> not null,
> How to do it?
That's a bit of work! Rename the type (sp_rename), create the new
definition. Run ALTER TABLE ALTER COLUMN on all tables that uses
the type, and recreate all procedures that uses the type as a parameter.
Finally you can drop the old definition.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Erland Sommarskog" <esquel@.sommarskog.se>
'?:Xns9769A5B38E43Yazorman@.127.0.0.1...
> Frank Lee (Reply@.to.newsgroup) writes:
> That's a bit of work! Rename the type (sp_rename), create the new
> definition. Run ALTER TABLE ALTER COLUMN on all tables that uses
> the type, and recreate all procedures that uses the type as a parameter.
> Finally you can drop the old definition.
>
Oh My god!
I would like to use alias type because I thought that if one day I want to
alter the difinition of alias type, it would be more easy as it has central
difinition. However, it's not really. It's more complex. :-(
Anyway, if it's the only way to alter the used alias type, I accept.
--Frank|||Frank Lee (Reply@.to.newsgroup) writes:
> I would like to use alias type because I thought that if one day I want
> to alter the difinition of alias type, it would be more easy as it has
> central difinition. However, it's not really. It's more complex. :-(
And it's maybe just as well. For a small database with little data in it
a single ALTER TYPE may be harmless. But for a widely used type in a 1TB
database it could send the database offline for a w

.
Changing table schema in a large database requires careful planning, and
even more so if you change a type.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Erland Sommarskog" <esquel@.sommarskog.se>
'?:Xns97695FE4BC283Yazorman@.127.0.0.1...
> And it's maybe just as well. For a small database with little data in it
> a single ALTER TYPE may be harmless. But for a widely used type in a 1TB
> database it could send the database offline for a w

.
> Changing table schema in a large database requires careful planning, and
> even more so if you change a type.
>
Ya, you are right.sql

How to alter a Varchar column to a shorter length (with data in it)

Hi,
I am trying to write a script to trim the length of a paticular column
'Unit_Name' (its orginial length is: 256). There are already data in the
table (with length over 15 chararters).
The following script gives me a erro and aborted.
Alter table Unit alter column Unit_Name varchar (15) null
go
Question:
The data can be truncated, how do I suppress the error and force the
script finish ?
Thanks.
-- Charlie
Update table Unit
set Unit_Name = left(Unit_Name, 15)
where len(Unit_Name) > 15
Then run your alter table command.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"C. Ming Lu" <cminglu@.qmxs.com> wrote in message
news:OA8na3TXEHA.2844@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I am trying to write a script to trim the length of a paticular column
> 'Unit_Name' (its orginial length is: 256). There are already data in the
> table (with length over 15 chararters).
> The following script gives me a erro and aborted.
> Alter table Unit alter column Unit_Name varchar (15) null
> go
> Question:
> The data can be truncated, how do I suppress the error and force the
> script finish ?
> Thanks.
> -- Charlie
>
|||Update table Unit
set Unit_Name = left(Unit_Name, 15)
where len(Unit_Name) > 15
Then run your alter table command.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"C. Ming Lu" <cminglu@.qmxs.com> wrote in message
news:OA8na3TXEHA.2844@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I am trying to write a script to trim the length of a paticular column
> 'Unit_Name' (its orginial length is: 256). There are already data in the
> table (with length over 15 chararters).
> The following script gives me a erro and aborted.
> Alter table Unit alter column Unit_Name varchar (15) null
> go
> Question:
> The data can be truncated, how do I suppress the error and force the
> script finish ?
> Thanks.
> -- Charlie
>

How to alter a Varchar column to a shorter length (with data in it)

How to alter a column

Hi,
What's wrong with the following code? Please help. I am using MS SQL
2000.
create table one (
a float NOT NULL,
b varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
)
alter table one
alter column a {drop} NOT NULL
alter column b {drop} NOT NULL
;
Thanks,
MikeSyntax usage is incorrect. Please use the below code to modify NOT NULL to NULL
alter table one alter column a Float NULL
alter table one alter column b Varchar(50) NULL
Thanks
Hari
"Michael" wrote:
> Hi,
> What's wrong with the following code? Please help. I am using MS SQL
> 2000.
> create table one (
> a float NOT NULL,
> b varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> )
> alter table one
> alter column a {drop} NOT NULL
> alter column b {drop} NOT NULL
> ;
> Thanks,
> Mike
>

How to alter a column

Hi,
What's wrong with the following code? Please help. I am using MS SQL
2000.
create table one (
a float NOT NULL,
b varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
)
alter table one
alter column a {drop} NOT NULL
alter column b {drop} NOT NULL
;
Thanks,
MikeSyntax usage is incorrect. Please use the below code to modify NOT NULL to N
ULL
alter table one alter column a Float NULL
alter table one alter column b Varchar(50) NULL
Thanks
Hari
"Michael" wrote:

> Hi,
> What's wrong with the following code? Please help. I am using MS SQL
> 2000.
> create table one (
> a float NOT NULL,
> b varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> )
> alter table one
> alter column a {drop} NOT NULL
> alter column b {drop} NOT NULL
> ;
> Thanks,
> Mike
>

Monday, March 19, 2012

How to add strings to NTEXT data type

Hi, All,
First of all thanks for reading my post. I’m trying to figure out how to
add strings to a NTEXT data type column.
TABLE (
NoteID VARCHAR(10) PRIMARY KEY,
NoteOppID VARCHAR(10) FOREIGN KEY,
NoteDetails VARCHAR(1000)
)
I need to take all NoteDetails in the above table and group by NoteOppID and
insert them into a single NTEXT field…
i.e)
NoteOppID NoteDetails (VARCHAR)
----
Opp123 Matt please send this customer a prototype
Opp123 Customer wants to see the G Series
Opp123 John, I send customer prototype I'll get back to
you on their response.
Opp456 Customer is not satisfied with the 12000 Series
components.
Opp456 John - Please give me your thoughts on this
-- End result should look like
OppId Details (NTEXT)
----
Opp123 Matt please send this customer a prototype
[New Line]
Customer wants to see the MIR 900 Series
[New Line]
John, I send customer prototype I'll get back to
you on their response.
Opp456 Customer is not satisfied with the 12000 Series
Components
[New Line]
John - Please give me your thoughts on this
I cannot leave it at VARCHAR because I'll go over the 8kb constraint.
I’ve tried…
SELECT [NTEXT] + VARCHAR -- Errors
SELECT SUBSTRING([NTEXT], 0, DATALEN([NTEXT]) + VARCHAR – works but
potentially [NTEXT] will be larger than the 8kb constraint.
Thanks,
-NeilAphex3000 (Aphex3000@.discussions.microsoft.com) writes:
> First of all thanks for reading my post. I'm trying to figure out how to
> add strings to a NTEXT data type column.
You will most likely need to use WRITETEXT and UPDATETEXT. Please look
these up in Books Online.
(Or you upgrade to SQL 2005 where you can use nvarchar(MAX) instead
which is a lot easier to deal with.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Wednesday, March 28, 2012

How to assign values to variables in a procedure in with select s

How to assign a column's value to another column's default

Monday, March 26, 2012

How to apply complex constraints

Friday, March 23, 2012

How to alter the data type of a field

How to alter the data type of a field

How to alter the data type of a field

How to alter an used alias type?

How to alter a Varchar column to a shorter length (with data in it)

How to alter a Varchar column to a shorter length (with data in it)

How to alter a Varchar column to a shorter length (with data in it)

How to alter a column

How to alter a column

Monday, March 19, 2012

How to add strings to NTEXT data type

access the Sql server

Blog Archive

About Me