January 16, 2012 at 2:14 am
CREATE TABLE [dbo].[abc](
[FId] [int] NOT NULL,
[PId] [varchar](5) NOT NULL,
[StartDate] [datetime] NOT NULL,
CONSTRAINT [PK__FId__123] PRIMARY KEY CLUSTERED
(
[FId] ASC,
[PId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
insert into
abc(FId,PId,StartDate)
values
(1,1,GETDATE())
,(2,1,Getdate())
,(3,1,Getdate())
,(1,2,GETDATE())
,(2,2,Getdate())
,(3,2,Getdate())
I am writing a validation for update function. I have parameter @Fid,@Pid and
@Pvalue which is a value selected by the user from the drop downlist.
in connection with the data in table abc I want the following output
if the user selects the same value from the drop downlist suppose
@fid = 1 @pid = 1 @pvalue = 1 then the output should be 'not exists'
however if
@fid =1 @pid =1 and @pvalue = 2 then the output should be 'exists'
also if
@fid =1 @pid =1 and @pvalue = 5 then the output should be 'not exists'
thanks
January 16, 2012 at 2:26 am
i figured it out myself
if exists(select pid from abc where fid = @fid and pid <> @pid)
begin
if exists (select pid from abc where pid = @pid and fid = @fid and pid <> @pvalue)
select 'exist'
else
select 'not exist'
end
else
begin
select 'not exist'
end
January 16, 2012 at 2:50 am
You might find this a little better (only hits the table once, instead of twice as your code does)
DECLARE @exist TINYINT, @exist2 TINYINT
SELECT
@exist = CASE WHEN fid = @fid AND pid <> @pid
THEN 1
ELSE 0 END,
@exist2 = CASE WHEN pid = @pid AND fid = @fid AND pid <> @pvalue
THEN 1
ELSE 0 END
FROM abc
IF @exist = 1
BEGIN
IF @exist2 = 1
BEGIN
SELECT 'exist', 'exist'
END
ELSE
BEGIN
SELECT 'exist', 'not exist'
END
END
ELSE
BEGIN
IF @exist2 = 1
BEGIN
SELECT 'not exist', 'exist'
END
ELSE
BEGIN
SELECT 'not exist', 'not exist'
END
END
January 16, 2012 at 3:07 am
cool thanks 🙂
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply