Check if record exists

Hello,

I created the following SQL script to check if a record exists:

IF (EXISTS (SELECT LevelName FROM dbo.by27_Levels WHERE LOWER(@.LevelName) = LOWER(LevelName)))
Return (1)
ELSE
Return (0)

And I also found in a web page another solution:
IF EXISTS(SELECT 1 FROM TABLENAME WHERE LevelName=@.LevelName)
SELECT 1
ELSE
SELECT 0

- Which approach should I use?
- Why "SELECT 1 FROM"?
- And when should I use SELECT or RETURN?

All I need is to know if the record exists ... nothing else.

I will use this procedure on an ASP.NET 2.0 / C# web site.
I am not sure if this important but anyway ...

Thank You,
Miguel

select 1 from table returns a value which is basically the same as selecting a column name when evaluating from the exists function. The difference is that 1 is a constant so the column name does not need to be looked up, and since you do not need the value of the column then select 1 can be used.

using return or select depends on what you are using to call the sql statement. If you use return then you need to look into the calls returns parameters. Using a select , you need to use a scalar or dataset return call. Most people use the select call because those calls are easier to handle but not necessarily more efficient

|||

In EXISTS you can use any of them but the result will be the same (it always look for first occurrence of value selected) I do not know about time of execution but I would prefer something like this

RETURN (CASE
when EXISTS (SELECT LevelName
FROM dbo.by27_Levels
WHERE LOWER(@.LevelName) = LOWER(LevelName))) then 1
else
0
end)

If it will be executed on SQL server and you server is Case insensitive you do not have to use LOWER and it will speed up a little.

Thanks

|||

ozkary:

If you use return then you need to look into the calls returns parameters.

What do you mean to look the calls returns parameters?

Can you point to some info about it?

Thanks,.

Miguel

|||

By default SQL Server is not case sensitive so the LOWER() is not needed.

If the LevelName is a unique key for the table I would avaoid using T-SQL and use a single generic SQL query:

SELECT COUNT(*) FROM dbo.by27_Levels WHERE @.LevelName = LevelName

RETURN ends the execution of the batch T-SQL and SELECT does not. Note any select results not stored in local variables will be output.

|||

Some stored procedures could have return parameters they are defined with OUTPUT

create procedure TEST

@.tcParam1 as varchar(100) = NULL,

@.tcOutputParam as varchar(100) = NULL OUTPUT

AS

BEGIN

...

SET @.tcOutputParam= 'result'

END

and if you call it

declare @.oparam as varchar(100)

exec test 'Test valuee', @.oparam OUTPUT

you can get output value from procedure

RETURN always is returned by stored procedure and you can get it like:but it only integer, output parameter can be almost any type

EXEC @.result = test 'Test valuee', @.oparam OUTPUT

Thanks

|||

yes, one usually uses parameters to call a stored procedure. Those parameters can have the following directions: INPUT, OUTPUT, RETURN.

To hadle a return value, one needs to add a return parameter to the call:

SqlCommand cmd = new SqlCommand("myProc", myConnection)

cmd.CommandType = CommandType.StoredProcedure

cmd.Parameters..Add("ReturnValue", SqlDbType.Int).Direction = ParameterDirection.ReturnValue

add block to make the call

if using a reader make sure to close it before trying to read the return parameter

read the return parameter value

string value = cmd.Parameters.item["ReturnValue"].Value.Tostring();

for more info search on ParameterDirection.ReturnValue

I hope this helps.

Check for username

Hello,
I have the following Table:
CREATE TABLE [dbo].[tb_User] (
[usr_id] [int] IDENTITY (1, 1) NOT NULL ,
[usg_groupID] [int] NOT NULL ,
[usr_createdBy] [int] NOT NULL ,
[usr_isActive] [int] NOT NULL ,
[usr_dateCreated] [datetime] NOT NULL ,
[usr_userName] [varchar] (25) NULL ,
[usr_password] [varchar] (50) NULL ,
[usr_firstName] [varchar] (50) NULL ,
[usr_lastName] [varchar] (50) NULL ,
[usr_eMail] [varchar] (50) NULL ,
[usr_phoneNumber] [varchar] (50) NULL ,
[usr_notes] [varchar] (512) NULL
)
I want to set up a stored procedure that will take two parameters
(@.UserName varchar(50) and @.Password varchar(50)) that I want to use to
check for the following
1. Check to see that the username exisits and that the column isActive = 1
2. If step 1 is true, then check that the @.Password Parameter is the
same for the user
3. If step 2 above is true return 1
4. If step 1 about is false (either the username does not exist, of if
it does it's not active (isActive = 0) return 0
I am going to use the Return value in my C# application to warn the user
that either the username does not exist, or it's not active or the
password is wrong.
Anything anyone can provide me with will be greatly appreciated!It would be simple as this:
e.g.
create proc usp
@.user varchar(25),
@.pass varchar(50)
as
set nocount on
if exists(select 1 from tb_User where usr_userName=@.user and usr_isActive=1
and usr_password=@.pass)
return 0
else
return 1
go
-oj
"Ed_P." <ed_p@.no-email.com> wrote in message
news:eX3GmN0CFHA.2180@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I have the following Table:
> CREATE TABLE [dbo].[tb_User] (
> [usr_id] [int] IDENTITY (1, 1) NOT NULL ,
> [usg_groupID] [int] NOT NULL ,
> [usr_createdBy] [int] NOT NULL ,
> [usr_isActive] [int] NOT NULL ,
> [usr_dateCreated] [datetime] NOT NULL ,
> [usr_userName] [varchar] (25) NULL ,
> [usr_password] [varchar] (50) NULL ,
> [usr_firstName] [varchar] (50) NULL ,
> [usr_lastName] [varchar] (50) NULL ,
> [usr_eMail] [varchar] (50) NULL ,
> [usr_phoneNumber] [varchar] (50) NULL ,
> [usr_notes] [varchar] (512) NULL
> )
> I want to set up a stored procedure that will take two parameters
> (@.UserName varchar(50) and @.Password varchar(50)) that I want to use to
> check for the following
> 1. Check to see that the username exisits and that the column isActive = 1
> 2. If step 1 is true, then check that the @.Password Parameter is the same
> for the user
> 3. If step 2 above is true return 1
> 4. If step 1 about is false (either the username does not exist, of if it
> does it's not active (isActive = 0) return 0
> I am going to use the Return value in my C# application to warn the user
> that either the username does not exist, or it's not active or the
> password is wrong.
> Anything anyone can provide me with will be greatly appreciated!

Check Constraint Violation

I am getting a check constraint error on the following query.
INSERT TABLEA
( COL1, COL2, COL3)
SELECT dbo.Function1(),-20000, (dbo.Function1() + (-20000) )
There is a check constraint on COL3 ( COL3 >= 0 ) and dbo.Function() is
returning 20000. All columns are of datatype INT as is the return value of
the function.
If i replace the function call with a literal 0, it works, but having the
function in there violates the constraint on COL3, despite the value still
being 0.
Does anyone have any idea why this is happening? I have tried making COL3 a
computed column, but can't have a constraint on a computed column, tried
making the select statement into a derived table and selecting from that int
o
my insert statement.
This is being done on SQL Server 2000 Enterprise.
Thank you
Clint ColefaxHi
What is the error that you received.
and what was displayed when you tried:
SELECT dbo.Function1(),-20000, (dbo.Function1() + (-20000) )
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Clint Colefax" wrote:

> I am getting a check constraint error on the following query.
> INSERT TABLEA
> ( COL1, COL2, COL3)
> SELECT dbo.Function1(),-20000, (dbo.Function1() + (-20000) )
> There is a check constraint on COL3 ( COL3 >= 0 ) and dbo.Function() is
> returning 20000. All columns are of datatype INT as is the return value of
> the function.
> If i replace the function call with a literal 0, it works, but having the
> function in there violates the constraint on COL3, despite the value still
> being 0.
> Does anyone have any idea why this is happening? I have tried making COL3
a
> computed column, but can't have a constraint on a computed column, tried
> making the select statement into a derived table and selecting from that i
nto
> my insert statement.
> This is being done on SQL Server 2000 Enterprise.
> Thank you
> Clint Colefax
>
>|||The error received was a violation of check constraint.
INSERT statement conflicted with COLUMN CHECK constraint...
Execute that select statement returns as expected
20000, -20000, 0
Thank you
Clint Colefax|||Hi
Can you try as
INSERT INTO TABLEA
SELECT dbo.Function1(),-20000, dbo.Function1() + (-20000)
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Clint Colefax" wrote:

> The error received was a violation of check constraint.
> INSERT statement conflicted with COLUMN CHECK constraint...
> Execute that select statement returns as expected
> 20000, -20000, 0
> Thank you
> Clint Colefax
>|||What data types are the columns and the function?
If they are not of an exact type, but e.g. "real", the third expression may
evaluate to a value slightly below 0, although it is displayed as 0. CAST to
integer to avoid this problem.
I hope this helps!
Martin
"Clint Colefax" <ClintColefax@.discussions.microsoft.com> wrote in message
news:42D1D89D-9EA0-4F00-8F91-D8D55732C387@.microsoft.com...
>I am getting a check constraint error on the following query.
> INSERT TABLEA
> ( COL1, COL2, COL3)
> SELECT dbo.Function1(),-20000, (dbo.Function1() + (-20000) )
> There is a check constraint on COL3 ( COL3 >= 0 ) and dbo.Function() is
> returning 20000. All columns are of datatype INT as is the return value of
> the function.
> If i replace the function call with a literal 0, it works, but having the
> function in there violates the constraint on COL3, despite the value still
> being 0.
> Does anyone have any idea why this is happening? I have tried making COL3
> a
> computed column, but can't have a constraint on a computed column, tried
> making the select statement into a derived table and selecting from that
> into
> my insert statement.
> This is being done on SQL Server 2000 Enterprise.
> Thank you
> Clint Colefax
>
>|||On Mon, 30 May 2005 19:19:41 -0700, Clint Colefax wrote:

>I am getting a check constraint error on the following query.
>INSERT TABLEA
>( COL1, COL2, COL3)
>SELECT dbo.Function1(),-20000, (dbo.Function1() + (-20000) )
>There is a check constraint on COL3 ( COL3 >= 0 ) and dbo.Function() is
>returning 20000. All columns are of datatype INT as is the return value of
>the function.
Hi Clint,
I could not reproduce this behaviour (see repro script below). Could you
post a repro script for me to run and reproduce the error?

>I have tried making COL3 a
>computed column, but can't have a constraint on a computed column,
If Col3 is always equal to Col1 - Col2, you should make it a computed
column. You can replace the check constraint wiuth the following
equivalent:
CHECK (Col1 >= Col2)
Here's the script I used to try to reproduce your problem, and the
output I got from it:
create table TableA
(Col1 int not null,
Col2 int not null,
Col3 int not null,
PRIMARY KEY (Col1),
CHECK (Col3 >= 0)
)
go
create function dbo.Function1()
returns int
as
begin
return 20000
end
go
INSERT TableA
( Col1, Col2, Col3)
SELECT dbo.Function1(),-20000, (dbo.Function1() + (-20000) )
go
select * from TableA
go
drop function dbo.Function1
go
drop table TableA
go
Col1 Col2 Col3
-- -- --
20000 -20000 0
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||I gave the earlier example as I didn't want to post all of the infrastructur
e
around this problem. With the following cut down version, I am still able to
replicate the problem.
CREATE TABLE test (
MOVEMENT_NO int IDENTITY (1, 1) NOT NULL ,
FACTORY_STATIONERY_NO int NOT NULL ,
INITIAL_VALUE int NOT NULL ,
MOVEMENT int NOT NULL ,
FINAL_VALUE int NOT NULL ,
CREATION_DATE smalldatetime not null
CONSTRAINT testPK PRIMARY KEY NONCLUSTERED ( MOVEMENT_NO ) ,
CHECK (FINAL_VALUE >= 0),
CHECK (INITIAL_VALUE >= 0)
)
INSERT TEST
VALUES( 46, 0, 50000, 50000, '2004-12-11 11:21:00' )
INSERT TEST
VALUES( 46, 50000, -30000, 20000, '2004-12-13 15:34:00' )
CREATE FUNCTION fntest( @.factory_stationery_no INT )
RETURNS INT AS
BEGIN
RETURN ISNULL( ( SELECT TOP 1 FINAL_VALUE
FROM dbo.test
WHERE FACTORY_STATIONERY_NO = @.factory_stationery_no
ORDER BY creation_date desc, MOVEMENT_NO DESC ), 0 )
END
INSERT TEST
SELECT 46 AS FACTORY_STATIONERY_NO,
DBO.FNTEST(46),
-20000 AS MOVEMENT,
DBO.FNTEST(46) + (-20000),
GETDATE()
DROP FUNCTION FNTEST
DROP TABLE TEST
Thank you
Clint Colefax|||Sorry, I didn't think out my example very well, the following is code that
should reproduce the error.
CREATE TABLE test (
MOVEMENT_NO int IDENTITY (1, 1) NOT NULL ,
FACTORY_STATIONERY_NO int NOT NULL ,
INITIAL_VALUE int NOT NULL ,
MOVEMENT int NOT NULL ,
FINAL_VALUE int NOT NULL ,
CREATION_DATE smalldatetime not null
CONSTRAINT testPK PRIMARY KEY NONCLUSTERED ( MOVEMENT_NO ) ,
CHECK (FINAL_VALUE >= 0),
CHECK (INITIAL_VALUE >= 0)
)
INSERT TEST
VALUES( 46, 0, 50000, 50000, '2004-12-11 11:21:00' )
INSERT TEST
VALUES( 46, 50000, -30000, 20000, '2004-12-13 15:34:00' )
CREATE FUNCTION fntest( @.factory_stationery_no INT )
RETURNS INT AS
BEGIN
RETURN ISNULL( ( SELECT TOP 1 FINAL_VALUE
FROM dbo.test
WHERE FACTORY_STATIONERY_NO = @.factory_stationery_no
ORDER BY creation_date desc, MOVEMENT_NO DESC ), 0 )
END
INSERT TEST
SELECT 46 AS FACTORY_STATIONERY_NO,
DBO.FNTEST(46),
-20000 AS MOVEMENT,
DBO.FNTEST(46) + (-20000),
GETDATE()
DROP FUNCTION FNTEST
DROP TABLE TEST|||Thank you but all datatype are of INT, all match, even using a CAST or
CONVERT statement does not get around the problem (had previouslty attempted
).
Thank you for your input
Clint Colefax|||It's a bug, and a surprising one:
CREATE TABLE TEST (
a int not null,
b smalldatetime not null,
constraint finalv CHECK (a >= 0)
)
go
INSERT TEST(a) SELECT 1
Gives this error:
Server: Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'b', table 'tempdb.dbo.TEST';
column does not allow nulls. INSERT fails.
The statement has been terminated.
Can you post the result of
SELECT @.@.version
so we can see what version you're running?
I verified this on 8.00.2039, and will report it to Microsoft.
SK
Clint Colefax wrote:

>there error is a check constraint violation for the FINAL_VALUE constraint.
>Server: Msg 547, Level 16, State 1, Line 1
>INSERT statement conflicted with COLUMN CHECK constraint
>'CK__test__FINAL_VALU__3AA27A0F'. The conflict occurred in database
>'LIPSDev', table 'test', column 'FINAL_VALUE'.
>The statement has been terminated.
>Thank you
>

Check Constraint fails!

Hi Everybody,
Can anybody help me on the following query...
I have a table structure as follows
CREATE TABLE [dbo].[event_logs] (
[WSE_Idx] [int] NULL ,
[WSE_Type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WSE_Date_Generated] [datetime] NULL ,
[WSE_lDate_Generated] [datetime] NULL ,
[WSE_Date_Written] [datetime] NULL ,
[WSE_lDate_Written] [datetime] NULL ,
[WSE_tzname] [varchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WSE_Source] [varchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WSE_Category] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WSE_Event] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WSE_User] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WSE_User_Type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WSE_Computer] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WSE_Message] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WSE_Agent] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WSE_Log_Type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
go
It contains data. I tried to create the following 'check constraint' to the above table
alter table event_logs
add constraint ck_event_logs
check((WSE_Category = 'application' and wse_log_type in ('Audit Success','error')) OR
(WSE_Category = 'system' and wse_log_type in ('Warning')) OR
(WSE_Category = 'security' and wse_log_type in ('Audit Failure')))
It is giving the following error...
Server: Msg 547, Level 16, State 1, Line 1
ALTER TABLE statement conflicted with TABLE CHECK constraint 'ck_event_logs'.
The conflict occurred in database 'slm', table 'event_logs'.
Even I modified the above alter table script as follows, still it is giving the same error.
alter table event_logs
add constraint ck_event_logs
check(WSE_Category like '%applica%')
I created the similar table structure with different table name and applied the check constraint,
it works. No error. Ofcourse table doesn't have data (Empty table).
I have created RULE on this 'event_logs' table (with data). It works fine. No Error.
Can anybody tell me why this 'Check Constraint' is giving problem?.
tks in advance,
vasumData in a table are not valid for 'check constraint' that you specified.
You mast correct data in your table or in ALTER TABLE statement put WITH
NOCHECK option.
Look ALTER TABLE in BOL.
"vasum" <anonymous@.discussions.microsoft.com> wrote in message
news:748BD3CB-E545-4DEA-B05B-103EEF45BFAE@.microsoft.com...
> Hi Everybody,
> Can anybody help me on the following query...
> I have a table structure as follows
> CREATE TABLE [dbo].[event_logs] (
> [WSE_Idx] [int] NULL ,
> [WSE_Type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [WSE_Date_Generated] [datetime] NULL ,
> [WSE_lDate_Generated] [datetime] NULL ,
> [WSE_Date_Written] [datetime] NULL ,
> [WSE_lDate_Written] [datetime] NULL ,
> [WSE_tzname] [varchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [WSE_Source] [varchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [WSE_Category] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [WSE_Event] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [WSE_User] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [WSE_User_Type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [WSE_Computer] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [WSE_Message] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [WSE_Agent] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [WSE_Log_Type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> )
> go
> It contains data. I tried to create the following 'check constraint' to
the above table
> alter table event_logs
> add constraint ck_event_logs
> check((WSE_Category = 'application' and wse_log_type in ('Audit
Success','error')) OR
> (WSE_Category = 'system' and wse_log_type in ('Warning')) OR
> (WSE_Category = 'security' and wse_log_type in ('Audit Failure')))
> It is giving the following error...
> Server: Msg 547, Level 16, State 1, Line 1
> ALTER TABLE statement conflicted with TABLE CHECK constraint
'ck_event_logs'.
> The conflict occurred in database 'slm', table 'event_logs'.
> Even I modified the above alter table script as follows, still it is
giving the same error.
> alter table event_logs
> add constraint ck_event_logs
> check(WSE_Category like '%applica%')
> I created the similar table structure with different table name and
applied the check constraint,
> it works. No error. Ofcourse table doesn't have data (Empty table).
> I have created RULE on this 'event_logs' table (with data). It works fine.
No Error.
> Can anybody tell me why this 'Check Constraint' is giving problem?.
> tks in advance,
> vasum
>|||thanks for the timely help. I works. I used 'with nocheck' option. Able to create new check constraint and this new check constraint is validating the any new rows coming into the table