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

IF ISNULL(@FIELDNAME,'''')=''''
    set @FIELDNAME = ''2''

DECLARE @SQL NVARCHAR(MAX) = N''
    DECLARE @TBL TABLE(SIRANO INT)
    MERGE TOP(1) SAYACLAR WITH(ROWLOCK) AS T
    USING( SELECT ''+CASE WHEN EXISTS(SELECT TOP(1)1 FROM SAYACLAR WITH(NOLOCK) WHERE TABLENAME = @TNAME) THEN ''1'' ELSE ''ISNULL(( SELECT MAX(''+@FIELDNAME+'') FROM [''+@TNAME+''] WITH(NOLOCK) ),0)'' END+'' AS MAXPLUSONE  ) AS S
    ON T.TABLENAME = ''''''+@TNAME+''''''
    WHEN NOT MATCHED THEN INSERT(TABLENAME,SIRANO) VALUES(''''''+@TNAME+'''''',MAXPLUSONE)
    WHEN MATCHED THEN UPDATE SET SIRANO = SIRANO+1
    OUTPUT inserted.SIRANO - 1 
    INTO @TBL( SIRANO );
    SET @NEWID = (SELECT TOP(1) SIRANO FROM @TBL)''

exec sp_executesql @SQL,N''@NEWID INT OUTPUT'', @NEWID OUTPUT
' 
END
GO


