Check if record exists - 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

General SQL Server Forums

New to SQL Server Programming

Check if record exists

Author

Topic

bplvid
Starting Member

45 Posts

Posted - 2012-04-22 : 21:38:01

I use my below stored procedure to check if record exists in 3 multiple tables (Items,products,occupation) based on one common table employee .On execution it only returns 'not exists' even if the record exists.Whatz going wrong in my stored procedure.

ALTER PROCEDURE [dbo].[CheckRecordExists]
(
@Name nvarchar(256),
@deptname nvarchar(256)
)
AS
BEGIN
declare @Proceed bit;

SELECT @Proceed = CASE WHEN EXISTS (
select 1 from employee,items where Items.deptname = @deptname and employee.name = @Name) THEN 0
WHEN EXISTS (select 1 from employee,products where products.deptname = @deptname and employee.name = @Name) THEN 0
WHEN EXISTS (select 1 from employee,occupation where occupation.deptname = @deptname and employee.name = @Name) THEN 0
ELSE 1

END

IF @Proceed = 1
BEGIN

print 'Not exists'
end
else

print 'exists'

END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-22 : 21:46:35

you've not included any join conditions in all selects so they're effectively doing a cross join and will always return records.
i think the logic should be


ALTER PROCEDURE [dbo].[CheckRecordExists]
(
@Name nvarchar(256),
@deptname nvarchar(256)
)
AS
BEGIN
declare @Proceed bit;

SELECT @Proceed = CASE WHEN EXISTS (
select 1 from employee,items where employee.somecolumn = items.relatedcolumn Items.deptname = @deptname and employee.name = @Name) THEN 0
WHEN EXISTS (select 1 from employee,products where employee.somecolumn = products.relatedcolumn products.deptname = @deptname and employee.name = @Name) THEN 0
WHEN EXISTS (select 1 from employee,occupation where employee.somecolumn = occupation.relatedcolumn occupation.deptname = @deptname and employee.name = @Name) THEN 0 
ELSE 1

END

IF @Proceed = 1
BEGIN

print 'Not exists'
end
else

print 'exists'

END

i dont know actual column names so have given indicate names. please replace them with actual column names by which tables are related and check

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bplvid
Starting Member

45 Posts

Posted - 2012-04-22 : 22:00:25

quote:
Originally posted by visakh16

you've not included any join conditions in all selects so they're effectively doing a cross join and will always return records.
i think the logic should be

ALTER PROCEDURE [dbo].[CheckRecordExists]
(
@Name nvarchar(256),
@deptname nvarchar(256)
)
AS
BEGIN
declare @Proceed bit;

SELECT @Proceed = CASE WHEN EXISTS (
select 1 from employee,items where employee.somecolumn = items.relatedcolumn Items.deptname = @deptname and employee.name = @Name) THEN 0
WHEN EXISTS (select 1 from employee,products where employee.somecolumn = products.relatedcolumn products.deptname = @deptname and employee.name = @Name) THEN 0
WHEN EXISTS (select 1 from employee,occupation where employee.somecolumn = occupation.relatedcolumn occupation.deptname = @deptname and employee.name = @Name) THEN 0
ELSE 1

END

IF @Proceed = 1
BEGIN

print 'Not exists'
end
else

print 'exists'

END

i dont know actual column names so have given indicate names. please replace them with actual column names by which tables are related and check

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

your suggestion is gud if I've identical columns in both my employee and other tables.but my employee table don't have identical columns.Just the primary key which connects to other tables.

My employee table has different column names whereas my other 3 tables items,products,occupation has identical column names,all these tables have employee table id as foreign key.My employee table has the employeename and based on that employee name and departmentname in my other 3 identical tables i need to check if record exists.
Hope this info helps to understand better.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-22 : 22:03:56

not mandatory that column names should be identical
only mandatory condition is they should be related by fk relationship

so you should be using (as per your explanation) something like


ALTER PROCEDURE [dbo].[CheckRecordExists]
(
@Name nvarchar(256),
@deptname nvarchar(256)
)
AS
BEGIN
declare @Proceed bit;

SELECT @Proceed = CASE WHEN EXISTS (
select 1 from employee,items where employee.employeeid = items.employeeid Items.deptname = @deptname and employee.name = @Name) THEN 0
WHEN EXISTS (select 1 from employee,products where employee.employeeid = products.employeeid products.deptname = @deptname and employee.name = @Name) THEN 0
WHEN EXISTS (select 1 from employee,occupation where employee.employeeid= occupation.employeeid occupation.deptname = @deptname and employee.name = @Name) THEN 0 
ELSE 1

END

IF @Proceed = 1
BEGIN

print 'Not exists'
end
else

print 'exists'

END

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bplvid
Starting Member

45 Posts

Posted - 2012-04-22 : 22:16:15

I did the changes no luck,still the same.Is there a better way to handle this stored procedure?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-22 : 22:23:57

are you expecting it to return exists only if it exists in all three tables? then code should be


ALTER PROCEDURE [dbo].[CheckRecordExists]
(
@Name nvarchar(256),
@deptname nvarchar(256)
)
AS
BEGIN
declare @Proceed bit;

SELECT @Proceed = CASE WHEN EXISTS (
select 1 from employee,items where employee.employeeid = items.employeeid Items.deptname = @deptname and employee.name = @Name) AND
EXISTS (select 1 from employee,products where employee.employeeid = products.employeeid products.deptname = @deptname and employee.name = @Name) AND
EXISTS (select 1 from employee,occupation where employee.employeeid= occupation.employeeid occupation.deptname = @deptname and employee.name = @Name) THEN 0 
ELSE 1

END

IF @Proceed = 1
BEGIN

print 'Not exists'
end
else

print 'exists'

END

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bplvid
Starting Member

45 Posts

Posted - 2012-04-22 : 22:31:58

quote:

Originally posted by visakh16

are you expecting it to return exists only if it exists in all three tables? then code should be


ALTER PROCEDURE [dbo].[CheckRecordExists]
(
@Name nvarchar(256),
@deptname nvarchar(256)
)
AS
BEGIN
declare @Proceed bit;

SELECT @Proceed = CASE WHEN EXISTS (
select 1 from employee,items where employee.employeeid = items.employeeid Items.deptname = @deptname and employee.name = @Name) AND
EXISTS (select 1 from employee,products where employee.employeeid = products.employeeid products.deptname = @deptname and employee.name = @Name) AND
EXISTS (select 1 from employee,occupation where employee.employeeid= occupation.employeeid occupation.deptname = @deptname and employee.name = @Name) THEN 0 
ELSE 1

END

IF @Proceed = 1
BEGIN

print 'Not exists'
end
else

print 'exists'

END

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

I understand the change from your sp and I did it as per your suggestion but no change,it's still the same.Am I missing anything?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-22 : 22:34:21

i'm not sure what exactly you're looking at as you didnt answer my question yet!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bplvid
Starting Member

45 Posts

Posted - 2012-04-22 : 22:38:33

quote:
Originally posted by visakh16

i'm not sure what exactly you're looking at as you didnt answer my question yet!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Sorry,If i haven't answered your question.I need to return exists if it's in any of these 3 tables so that I can get the return value in my frontend and check for the @proceed output value and display the message if exists saying "record exists".

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-22 : 22:40:36

nope my question was

are you expecting it to return exists only if it exists in all three tables?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bplvid
Starting Member

45 Posts

Posted - 2012-04-22 : 22:42:59

quote:
Originally posted by visakh16

nope my question was

are you expecting it to return exists only if it exists in all three tables?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Return exists if the record exists in any of these 3 tables.Even if one tables has these values it should return exists.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-22 : 22:45:53

[code]
ALTER PROCEDURE [dbo].[CheckRecordExists]
(
@Name nvarchar(256),
@deptname nvarchar(256)
)
AS
BEGIN
declare @Proceed bit;

SELECT @Proceed = CASE WHEN EXISTS (
select 1 from employee,items where employee.employeeid = items.employeeid Items.deptname = @deptname and employee.name = @Name) OR
EXISTS (select 1 from employee,products where employee.employeeid = products.employeeid products.deptname = @deptname and employee.name = @Name) OR
EXISTS (select 1 from employee,occupation where employee.employeeid= occupation.employeeid occupation.deptname = @deptname and employee.name = @Name) THEN 0
ELSE 1

END

IF @Proceed = 1
BEGIN

print 'Not exists'
end
else

print 'exists'

END
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bplvid
Starting Member

45 Posts

Posted - 2012-04-22 : 22:55:48

quote:

Originally posted by visakh16


ALTER PROCEDURE [dbo].[CheckRecordExists]
(
@Name nvarchar(256),
@deptname nvarchar(256)
)
AS
BEGIN
declare @Proceed bit;

SELECT @Proceed = CASE WHEN EXISTS (
select 1 from employee,items where employee.employeeid = items.employeeid Items.deptname = @deptname and employee.name = @Name) OR
EXISTS (select 1 from employee,products where employee.employeeid = products.employeeid products.deptname = @deptname and employee.name = @Name) OR
EXISTS (select 1 from employee,occupation where employee.employeeid= occupation.employeeid occupation.deptname = @deptname and employee.name = @Name) THEN 0 
ELSE 1

END

IF @Proceed = 1
BEGIN

print 'Not exists'
end
else

print 'exists'

END

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Sorry,no change.may be i'll have to use join?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-22 : 22:58:18

ok...now what you can do is post some sample data and explain what exactly you're expecting as output out of them

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bplvid
Starting Member

45 Posts

Posted - 2012-04-22 : 23:04:26

quote:
Originally posted by visakh16

ok...now what you can do is post some sample data and explain what exactly you're expecting as output out of them

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Employee table

empid - pk
Name
Dateofjoin
Dateofbirth

Items table

empid -fk
itemsid - pk
deptname

Products table

empid -fk
productsid - pk
deptname

occupation table

empid - fk
occupationid = pk
deptname

Now i need to check if the name of employee and the department name that i'm trying to insert exists in any of these tables.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-22 : 23:18:07

for that why do you need to do all the above? is nt just a matter of checking against Items table for department and employee table for the employee?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bplvid
Starting Member

45 Posts

Posted - 2012-04-22 : 23:27:56

quote:
Originally posted by visakh16

for that why do you need to do all the above? is nt just a matter of checking against Items table for department and employee table for the employee?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

I need to check from 3 tables which holds identical column deptname and name from employee table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources