Greater Than And Less Than - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Greater Than And Less Than

Author

Topic

Hinduson
Yak Posting Veteran

69 Posts

Posted - 2013-09-20 : 19:17:37

What is the Syntax for running the Greater THAN or Less THAN Querries in SQL SERVER 2005?

Best Regards.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-09-20 : 20:20:59

You mean "Not Equal To"? SELECT * FROM dbo.Table1 WHERE Age <> 15;

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-09-22 : 04:16:40

or is it


WHERE Age > somevalue 
and Age < Somevalue

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Hinduson
Yak Posting Veteran

69 Posts

Posted - 2013-09-26 : 18:46:29

No i mean am now creating that rule , or how do i say it. I need the syntax or formula for implementing the greater than or Less than rule is a table please.

Best Regards.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-09-27 : 02:36:46

You want a CHECK CONSTRAINT?

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Hinduson
Yak Posting Veteran

69 Posts

Posted - 2013-09-27 : 12:04:23

Meaby SwePeso.. You tell me and can you please show me how to write or go about the syntax so that i won't get any errors while executing ..

Thanks SwePeso

Best Regards.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-27 : 12:46:32

Use the first if you are creating the table; if the table already exists and you want to add the constraint, use the second

CREATE TABLE BTestTable
(
	id INT 
	CONSTRAINT check_ID CHECK (id >= 0 AND id <= 10)
);

CREATE TABLE CTestTable
(
	id INT
);
ALTER TABLE CTestTable ADD CONSTRAINT CCheck_ID CHECK (id >= 0 AND id <= 10)

Hinduson
Yak Posting Veteran

69 Posts

Posted - 2013-09-27 : 12:48:07

Thanks James K

Best Regards.

Hinduson
Yak Posting Veteran

69 Posts

Posted - 2013-09-28 : 11:03:13

James K .. Please am getting errors when i create the table. Below is how i wrote my query. Please check for errors .. And the question is [QUANTITYINHAND should be greater than 0. The record should not be inerted or modified manually if QUANTITYINHAND is 0.

QUANTITYINHAND INT,
CONSTRAINT CHECK_QUANTITYINHAND CHECK (QUANTITYINHAND > 0 )

Best Regards.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-09-29 : 03:05:25

the posted query part doesnt have any obvious issues. Show us full create script please. Also post the error message you got.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Hinduson
Yak Posting Veteran

69 Posts

Posted - 2013-10-01 : 03:01:36

CREATE TABLE ITEMS_ITEMDETAILS
(
ItemID int Identity(2,2) PRIMARY KEY ,
ItemName Varchar (50) not null,
ItemDescription Varchar (50) not null,
QuantityInHand int,
CONSTRAINT check_ID CHECK (QuantityInHand >= 0 )
UnitPrice Money,
CONSTRAINT check_ID CHECK (UnitPrice >= 0 )
ReorderQuantity int,
CONSTRAINT check_ID CHECK (ReorderQuantity >= 0 )
)

Msg 102, Level 15, State 1, Line 8
Incorrect syntax near 'UnitPrice'.

Best Regards.

VeeranjaneyuluAnnapureddy
Posting Yak Master

169 Posts

Posted - 2013-10-01 : 03:19:32

CREATE TABLE ITEMS_ITEMDETAILS
(
ItemID int Identity(2,2) PRIMARY KEY ,
ItemName Varchar (50) not null,
ItemDescription Varchar (50) not null,
QuantityInHand int,
CONSTRAINT check_ID1 CHECK (QuantityInHand >= 0 ),
UnitPrice Money,
CONSTRAINT check_ID2 CHECK (UnitPrice >= 0 ),
ReorderQuantity int,
CONSTRAINT check_ID3 CHECK (ReorderQuantity >= 0 )
)

veeranjaneyulu

Hinduson
Yak Posting Veteran

69 Posts

Posted - 2013-10-01 : 16:39:02

Thanks Alot Brother VeeranjaneyuluAnnapureddy.. I have learnt from you and I really appreciate it. But i have one last question. please if you are asked a question like this how will you go about it.

Phone number must be in the following format : '[0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9]'

I tried to build a rule, and sp_bindrule to bind the rule to the table. But when inserting values it give me errors. So please I will like you to help me out or anyone else around ..

Thanks alot brothers.

Best Regards.

marcusn25
Yak Posting Veteran

56 Posts

Posted - 2013-10-14 : 16:28:38

ALTER TABLE [TABLE NAME]
ADD CONSTRAINT CK_PhoneNumber
CHECK ( [PhoneNumber] LIKE '[0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9]' )

M. Ncube

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-15 : 01:15:16

if PhoneNumber must have only numeric data why not make it integer?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources