세고비아의 이글루

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[tools_CRUD_SPROC_Builder]
(
@tableName varchar(100)
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @parameterCount int
DECLARE @errMsg varchar(100)
DECLARE @parameterAt varchar(1)
DECLARE @A varchar(200)
DECLARE @C varchar(200)
DECLARE @ND varchar(200)
DECLARE @V varchar(200)

SELECT ',['+COLUMN_NAME+']' AS C
,',@'+COLUMN_NAME+' '+DATA_TYPE+' '+
CASE WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL
THEN '('+CAST(CHARACTER_MAXIMUM_LENGTH as varchar)+')'
ELSE ' '
END AS A
,'AND ['+COLUMN_NAME+']=@'+COLUMN_NAME AS ND
,',@'+COLUMN_NAME AS V
INTO #t_obj
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @tableName
ORDER BY ORDINAL_POSITION

-- 1.SELECT PROC
PRINT 'CREATE PROC usp_'+@tableName+'_Select'
PRINT '('

DECLARE cur SCROLL CURSOR FOR
SELECT * FROM #t_obj
OPEN cur

FETCH NEXT FROM cur INTO @C, @A, @ND, @V
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT ' '+@A
FETCH NEXT FROM cur INTO @C, @A, @ND, @V
END

PRINT ')'
PRINT 'AS'
PRINT 'BEGIN'
PRINT 'SELECT'

FETCH FIRST FROM cur INTO @C, @A, @ND, @V
WHILE @@FETCH_STATUS=0
BEGIN
PRINT ' '+@C
FETCH NEXT FROM cur INTO @C, @A, @ND, @V
END

PRINT ' FROM '+@tableName
PRINT ' WHERE 1=1'

FETCH FIRST FROM cur INTO @C, @A, @ND, @V
WHILE @@FETCH_STATUS=0
BEGIN
PRINT ' '+@ND
FETCH NEXT FROM cur INTO @C, @A, @ND, @V
END
PRINT 'END'
PRINT 'GO'

-- 2.INSERT PROC
PRINT 'CREATE PROC usp_'+@tableName+'_Insert'
PRINT '('

FETCH FIRST FROM cur INTO @C, @A, @ND, @V
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT ' '+@A
FETCH NEXT FROM cur INTO @C, @A, @ND, @V
END

PRINT ')'
PRINT 'AS'
PRINT 'BEGIN'
PRINT 'INSERT INTO '+@tableName
PRINT '('

FETCH FIRST FROM cur INTO @C, @A, @ND, @V
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT ' '+@C
FETCH NEXT FROM cur INTO @C, @A, @ND, @V
END

PRINT ')'
PRINT ' VALUES'
PRINT '('

FETCH FIRST FROM cur INTO @C, @A, @ND, @V
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT ' '+@V
FETCH NEXT FROM cur INTO @C, @A, @ND, @V
END

PRINT ')'
PRINT 'END'
PRINT 'GO'

-- 3. UPDATE PROC
PRINT 'CREATE PROC usp_'+@tableName+'_Update'
PRINT '('

FETCH FIRST FROM cur INTO @C, @A, @ND, @V
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT ' '+@A
FETCH NEXT FROM cur INTO @C, @A, @ND, @V
END

PRINT ')'
PRINT 'AS'
PRINT 'BEGIN'
PRINT 'UPDATE ' + @tableName
PRINT ' SET '

FETCH FIRST FROM cur INTO @C, @A, @ND, @V
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT ' ,'+SUBSTRING(@ND,5,200)
FETCH NEXT FROM cur INTO @C, @A, @ND, @V
END

PRINT ' WHERE 1=1'

FETCH FIRST FROM cur INTO @C, @A, @ND, @V
WHILE @@FETCH_STATUS=0
BEGIN
PRINT ' '+@ND
FETCH NEXT FROM cur INTO @C, @A, @ND, @V
END
PRINT 'END'
PRINT 'GO'

-- 4. DELETE PROC
PRINT 'CREATE PROC usp_'+@tableName+'_Delete'
PRINT '('

FETCH FIRST FROM cur INTO @C, @A, @ND, @V
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT ' '+@A
FETCH NEXT FROM cur INTO @C, @A, @ND, @V
END

PRINT ')'
PRINT 'AS'
PRINT 'BEGIN'
PRINT 'DELETE ' + @tableName

PRINT ' WHERE 1=1'

FETCH FIRST FROM cur INTO @C, @A, @ND, @V
WHILE @@FETCH_STATUS=0
BEGIN
PRINT ' '+@ND
FETCH NEXT FROM cur INTO @C, @A, @ND, @V
END
PRINT 'END'
PRINT 'GO'

CLOSE cur
DEALLOCATE cur
DROP TABLE #t_obj
SET NOCOUNT ON
END