CODEWITHSUNDEEP
sqlsql-servert-sql
user5021612
user5021612

Reputation:

How to compare wheter a value exists in a table

I have a list of our SQL jobs and I want to create a unit test, that would evaluate whether a given parameter (job name) exists within the jobs.

I've tried:

-- Test function
     DECLARE @FinalResult NVARCHAR(30)
     DECLARE @ResultTable TABLE (name VARCHAR(30))

     INSERT INTO @ResultTable
     SELECT [name] 
     FROM msdb.dbo.sysjobs
     WHERE [name] = @Parameter_Name
     -- Evaluation
     SET @FinalResult = SELECT TOP 1 FROM @ResultTable
     SELECT @TestResult = IIF(FinalResult IS NULL,0,1)

Thanks in advance.

Upvotes: 0

Views: 49

Answers (3)

Tom
Tom

Reputation: 15061

Maybe something like:

    DECLARE @Parameter_Name Varchar(MAX) = '' -- Job name
    DECLARE @ResultTable TABLE (name VARCHAR(30),JobExists bit )

    IF EXISTS( SELECT [name]  FROM msdb.dbo.sysjobs WHERE [name] = @Parameter_Name) 
    BEGIN
        INSERT INTO @ResultTable (name, JobExists )
        Values (@Parameter_Name,1)
    END
ELSE
    BEGIN
        INSERT INTO @ResultTable (name, JobExists )
        Values (@Parameter_Name,0)
    END

Upvotes: 0

M.Ali
M.Ali

Reputation: 69524

A much simple way would be something like 

Declare @JobExists INT
    ,   @Parameter_Name SYSNAME = 'Backup Job';

IF EXISTS ( SELECT 1 FROM msdb.dbo.sysjobs WHERE [name] = @Parameter_Name)
    BEGIN
      SET @JobExists =1;
    END
ELSE 
    BEGIN
      SET @JobExists =0;
    END

Upvotes: 0

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

You can use EXISTS():

IF EXISTS(SELECT 1 FROM SomeWhere WHERE SomeCondition) --The '1' is of no importance... Just checking for any result...
BEGIN
    Do what ever you need
END

You can add an ELSE block to react to the opposite too.

Hint: You can negate this easily using IF NOT EXISTS()...

Upvotes: 3

Related Questions