Sql if else to grab the next available id from a table to insert

1

Hi Peeps,

I have a table called job, where i want to insert a new row with a next id available from tenant table.
USE [Config_SUPP]
GO

DECLARE
@JobId INTEGER,
@TaskId INTEGER,
@TenantId INTEGER,
@WorkTriggerId INTEGER

SET @TenantId = SCOPE_IDENTITY()

BEGIN
SET @TaskId = (SELECT TOP 1 TaskID FROM WorkTrigger WHERE WorkTriggerName='Scheduled Fees')

IF EXISTS (SELECT 1 FROM Job WHERE tenantid=@TenantId)
BEGIN
PRINT 'Adding the Scheduled Fees Processing'
SET @TenantId = @TenantId + 1
EXEC support.JobInsert @Taskid,'Scheduled Fees Processing', 'SvrScheduledFees',NULL,NULL,@TenantId
END
BEGIN
PRINT 'The Scheduled Fees Processing job already exists'
END
END
GO

the tenant table primary key is not incremental i.e. it starts with 1,31,32,33 ....

i want the script above to insert into job table while getting a next id from the tenant table.

any help please

thanks

2

Hi,
Can you please list out the table def from tenant table?

From the above script, since you do not have any insert statement, I don't think you can get SCOPE_IDENTITY() to work.

Given you said that the tenant id is not incremental, but what you trying to do is going to be incremental as you just adding 1 to the next id, you can either use identity field or use sequence. Both should be able to get what you wanted.

3

USE [NGP_SUPP_Config]
GO

/****** Object: Table [dbo].[Tenant] Script Date: 24/02/2016 3:18:43 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Tenant](
[TenantID] [bigint] IDENTITY(1,1) NOT NULL,
[TenantName] nvarchar NULL,
CONSTRAINT [PK_Tenant] PRIMARY KEY CLUSTERED
(
[TenantID] 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

4

Do you need an ORDER BY on that, or is there only one match row / will "any" row from that table do?

Would

SELECT MAX(TenantID) + 1 FROM dbo.Tenant

be any good? (It would need some locking / blocking to make sure that two people didn't get the same Next ID at the same time ...)