| Author |
 Topic  |
|
|
bplvid
Starting Member
45 Posts |
 Posted - 04/22/2012 : 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
|
Edited by - bplvid on 04/22/2012 21:40:00
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47157 Posts |
Posted - 04/22/2012 : 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 - 04/22/2012 : 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.
|
Edited by - bplvid on 04/22/2012 22:01:19 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47157 Posts |
Posted - 04/22/2012 : 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 - 04/22/2012 : 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
India
47157 Posts |
Posted - 04/22/2012 : 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 - 04/22/2012 : 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
India
47157 Posts |
Posted - 04/22/2012 : 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 - 04/22/2012 : 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".
|
Edited by - bplvid on 04/22/2012 22:40:15 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47157 Posts |
Posted - 04/22/2012 : 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 - 04/22/2012 : 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
India
47157 Posts |
Posted - 04/22/2012 : 22:45:53
|
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/
|
|
|
|
bplvid
Starting Member
45 Posts |
Posted - 04/22/2012 : 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
India
47157 Posts |
Posted - 04/22/2012 : 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 - 04/22/2012 : 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
India
47157 Posts |
Posted - 04/22/2012 : 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 - 04/22/2012 : 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.
|
|
|
| |
Topic |
|
Check if record exists
