if exist or not exist query need.

  • 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

  • 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

  • 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


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • cool thanks 🙂

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply