IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SPREADID2]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SPREADID2]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SPREADID2]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N' CREATE PROCEDURE [dbo].[SPREADID2]  
@TNAME nvarchar(255),@FIELDNAME NVARCHAR(50) = '''' , @NEWID int OUTPUT
 AS
    declare @LOCKRESOURCE NVARCHAR(200) = ''SPREADID2_'' + @TNAME
 
    exec sp_getapplock 
                        @Resource=@LOCKRESOURCE,
                        @LockOwner=''Session'',
                        @LockMode=''Exclusive'',
                        @LockTimeout=2147483647  
 
    UPDATE SAYACLAR set @NEWID = SIRANO, SIRANO =SIRANO+1 where TABLENAME=@TNAME
 
     IF (@NEWID IS NULL)
     BEGIN
      IF (ISNULL(@FIELDNAME,'''') = '''') OR (OBJECT_ID(@TNAME) IS NULL)
      BEGIN
         SET @NEWID = 1
         INSERT INTO SAYACLAR (TABLENAME , SIRANO) VALUES (@TNAME , 2)
      END
      ELSE
      BEGIN
        DECLARE @SQLString NVARCHAR(500)
        DECLARE @ParmDefinition NVARCHAR(500)
        DECLARE @OUTVALUE INT
        SET @SQLString = N''SELECT @OUTVALUE_OUT = ISNULL(MAX(''+@FIELDNAME+''),0) FROM '' + @TNAME
        SET @ParmDefinition = N''@OUTVALUE_OUT INT OUTPUT''
 
        EXECUTE sp_executesql
        @SQLString,
        @ParmDefinition,
        @OUTVALUE_OUT=@OUTVALUE OUTPUT
        SET @NEWID = @OUTVALUE + 1
 
        SET  @SQLString = N''DELETE FROM SAYACLAR WHERE [TABLENAME] = '''''' + @TNAME + '''''' '' +
                           ''INSERT INTO SAYACLAR ([TABLENAME],SIRANO) VALUES(''''''+@TNAME+'''''',''+CAST(@NEWID+1 AS NVARCHAR(50))+'')'';
 
        EXEC (@SQLString)
      END
     END
 
      exec sp_releaseapplock 
                        @Resource=@LOCKRESOURCE,
                        @LockOwner=''Session''
' 
END
GO


