print '/* ignore this
'
GO

SET NOCOUNT ON

DECLARE @TableName nvarchar(128)
DECLARE @TablePrefix nvarchar(20)
DECLARE @ProcedurePrefix nvarchar(20)
DECLARE @IncludeTabulation bit
DECLARE @CreateSaveAndAddTogether bit

/* !!!!!!!!!!!!! parameters !!!!!!!!!!!!!*/
SET @ProcedurePrefix = 'usp_'
SET @TablePrefix = ''
SET @TableName = 'Customers'
SET @IncludeTabulation = 1
SET @CreateSaveAndAddTogether = 0
/*!!!!!!!!!!   parameters !!!!!!!!!!!!!*/

DECLARE @ShortTableName nvarchar(20)
SET @ShortTableName = RIGHT(@TableName, DATALENGTH(@TableName) / 2 - DATALENGTH(@TablePrefix) / 2)
DECLARE @GetProcedureName nvarchar(128), @SaveProcedureName nvarchar(128),
@AddProcedureName nvarchar(128), @UpdateProcedureName nvarchar(128), @DeleteProcedureName nvarchar(128)
SET @GetProcedureName = @ProcedurePrefix + 'Get' + @ShortTableName
SET @SaveProcedureName = @ProcedurePrefix + 'Save' + @ShortTableName
SET @AddProcedureName = @ProcedurePrefix + 'Insert' + @ShortTableName
SET @UpdateProcedureName = @ProcedurePrefix + 'Update' + @ShortTableName
SET @DeleteProcedureName = @ProcedurePrefix + 'Delete' + @ShortTableName

DECLARE @columns table (colname nvarchar(128), coldefault nvarchar(4000),
 isnullable bit, datatype nvarchar(128), collen int)
DECLARE @pkdatatype nvarchar(128), @pkcollen smallint

INSERT @columns (colname, coldefault, isnullable, datatype, collen)
SELECT COLUMN_NAME, 
 RIGHT(LEFT(COLUMN_DEFAULT, DATALENGTH(COLUMN_DEFAULT) /2 - 1), DATALENGTH(COLUMN_DEFAULT) / 2  - 2),
 (CASE WHEN IS_NULLABLE = 'Yes' THEN 1 ELSE 0 END), DATA_TYPE,
 CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName

DECLARE @colname nvarchar(128), @coldefault nvarchar(4000),
 @isnullable bit, @datatype nvarchar(128), @collen int

-- primary key
IF OBJECT_ID('tempdb..#tmpTpk') IS NOT NULL DROP TABLE #tmpTpk

CREATE TABLE #tmpTpk (TABLE_QUALIFIER sysname, TABLE_OWNER sysname, TABLE_NAME sysname, COLUMN_NAME sysname, KEY_SEQ smallint, PK_NAME sysname)
INSERT #tmpTpk (TABLE_QUALIFIER, TABLE_OWNER, TABLE_NAME, COLUMN_NAME, KEY_SEQ, PK_NAME)
EXEC sp_pkeys @table_name = @TableName

DECLARE @pk nvarchar(128)
SELECT TOP 1 @pk = COLUMN_NAME FROM #tmpTpk

print '
*/
GO'

DECLARE @sql varchar(8000)

-- generating get sproc
SET @sql = 'IF EXISTS (SELECT * FROM sysobjects WHERE type = ''P'' AND name = ''' + @GetProcedureName + ''')
BEGIN
    DROP  PROCEDURE  ' + @GetProcedureName + '
END
GO'
SET @sql = @sql + '
/*
** procedure getting records from table ' + @TableName + ' 
*/
CREATE PROCEDURE ' + @GetProcedureName + '
'

IF @pk IS NOT NULL
BEGIN
	SELECT @colname = colname, @coldefault = coldefault,
	@isnullable = isnullable, @pkdatatype = datatype,
	@pkcollen = collen
	FROM @columns
	WHERE @pk = colname

	SET @sql = @sql + '@' + @colname + ' ' + @pkdatatype
 
	IF @pkcollen IS NOT NULL
		SET @sql = @sql + ' (' + @pkcollen + ')'
	SET @sql = @sql + ' = ' + ISNULL (@coldefault, 'NULL') 
END

IF @IncludeTabulation = 1
BEGIN
	SET @sql = @sql + ',
@OrderByField varchar(50) = NULL,
@OrderByAsc bit = 1,
@FirstRecordToDisplay int = NULL,
@NumberOfRecordsToDisplay int = NULL'
END


SET @sql = @sql + '
AS

SET NOCOUNT ON'

DECLARE cur CURSOR FOR
SELECT colname, coldefault, isnullable, datatype, CASE WHEN datatype IN ('text', 'ntext') THEN NULL ELSE collen END FROM @columns
DECLARE @notfirst bit


IF @IncludeTabulation = 1
BEGIN

	 SET @sql = @sql +
'
DECLARE @TotalCount int
IF OBJECT_ID(''tempdb..#tmpGet' + @TableName + ''') IS NOT NULL DROP TABLE #tmpGet' + @TableName + '
CREATE TABLE #tmpGet' + @TableName + '
(
tmpGetID int IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
'
	OPEN cur
	SET @notfirst = 0
	WHILE 1 = 1
	BEGIN
		FETCH NEXT FROM cur INTO @colname, @coldefault, @isnullable, @datatype, @collen
		IF @@FETCH_STATUS <> 0 BREAK
		IF @notfirst = 1
		SET @sql = @sql + ',
'
		SET @sql = @sql + @colname + ' ' + @datatype
		IF @collen IS NOT NULL
			SET @sql = @sql + '(' + CAST(@collen AS varchar(15)) + ')'
		SET @notfirst = 1
	END
	CLOSE cur
	SET @sql = @sql + '
)
INSERT #tmpGet' + @TableName + '('
	OPEN cur
	SET @notfirst = 0
	WHILE 1 = 1
	BEGIN
		FETCH NEXT FROM cur INTO @colname, @coldefault, @isnullable, @datatype, @collen
		IF @@FETCH_STATUS <> 0 BREAK
		IF @notfirst = 1
		SET @sql = @sql + ', '
		SET @sql = @sql + @colname
		SET @notfirst = 1
	END
	CLOSE cur
	SET @sql = @sql + ')'
END

SET @sql = @sql + '
SELECT '

OPEN cur
SET @notfirst = 0
WHILE 1 = 1
BEGIN
	FETCH NEXT FROM cur INTO @colname, @coldefault, @isnullable, @datatype, @collen
	IF @@FETCH_STATUS <> 0 BREAK
	IF @notfirst = 1
		SET @sql = @sql + ', '
	SET @sql = @sql + @colname
	SET @notfirst = 1
END
CLOSE cur


SET @sql = @sql + '
FROM ' + QUOTENAME(@TableName) + '
WHERE (@' + @pk + ' IS NULL OR @' + @pk + ' = ' + @pk + ')'

PRINT @sql
SET @sql = ''

IF @IncludeTabulation = 1
BEGIN
	SET @sql = @sql + '
 ORDER BY '
	OPEN cur
	SET @notfirst = 0
	WHILE 1 = 1
	BEGIN
		FETCH NEXT FROM cur INTO @colname, @coldefault, @isnullable, @datatype, @collen
		IF @@FETCH_STATUS <> 0 BREAK
		IF @datatype IN ('text', 'ntext') CONTINUE
		IF @notfirst = 1
			SET @sql = @sql + ','
		SET @sql = @sql + '
 CASE WHEN @OrderByField = ''' + @colname + ''' THEN ' + @colname + ' ELSE NULL END'
		SET @notfirst = 1
	END
	CLOSE cur

	SET @sql = @sql + '
SET @TotalCount = @@ROWCOUNT
DECLARE @k smallint
DECLARE @NewFirstRecord int
IF @OrderByAsc = 0
BEGIN
	SET @k = -1
	SET @NewFirstRecord = @TotalCount - ISNULL(@FirstRecordToDisplay, 1) + 1
END
ELSE
BEGIN
	SET @k = 1
	SET @NewFirstRecord = ISNULL(@FirstRecordToDisplay, 1)
END

SELECT
'
	OPEN cur
	SET @notfirst = 0
	WHILE 1 = 1
	BEGIN
		FETCH NEXT FROM cur INTO @colname, @coldefault, @isnullable, @datatype, @collen
		IF @@FETCH_STATUS <> 0 BREAK
		IF @notfirst = 1
			SET @sql = @sql + ', '
		SET @sql = @sql + @colname
		SET @notfirst = 1
	END
	CLOSE cur
	SET @sql = @sql + '
FROM #tmpGet' + @TableName + '
WHERE (@FirstRecordToDisplay IS NULL OR (tmpGetID - @NewFirstRecord) * @k >= 0 )
AND (@NumberOfRecordsToDisplay IS NULL OR (@NewFirstRecord + @k * @NumberOfRecordsToDisplay - tmpGetID) * @k > 0)
ORDER BY @k * tmpGetID
'
END

SET @sql = @sql + 'GO
GRANT EXEC ON ' + @GetProcedureName + ' TO PUBLIC
GO'
PRINT @sql
SET @sql = 'IF EXISTS (SELECT * FROM sysobjects WHERE type = ''P'' AND name = ''' + CASE WHEN @CreateSaveAndAddTogether = 1 THEN @SaveProcedureName ELSE @AddProcedureName END + ''')
BEGIN
    DROP  PROCEDURE  ' + CASE WHEN @CreateSaveAndAddTogether = 1 THEN @SaveProcedureName ELSE @AddProcedureName END + '
END
'
SET @sql = @sql + 'GO
/*
** procedure adding a new record into a table ' + @TableName + '
*/
'
SET @sql = @sql + 'CREATE PROCEDURE ' + CASE WHEN @CreateSaveAndAddTogether = 1 THEN @SaveProcedureName ELSE @AddProcedureName END  + '
'

OPEN cur
SET @notfirst = 0
WHILE 1 = 1
BEGIN
	FETCH NEXT FROM cur INTO @colname, @coldefault, @isnullable, @datatype, @collen
	IF @@FETCH_STATUS <> 0 BREAK
	IF @notfirst = 1
		SET @sql = @sql + ',
'
	SET @sql = @sql + '@' + @colname + ' ' + @datatype
	IF @collen IS NOT NULL
		SET @sql = @sql + '(' + CAST(@collen AS varchar(10)) + ')'
	IF @colname = @pk
		SET @sql = @sql + ' = NULL OUTPUT'
	ELSE IF @isnullable = 1
		SET @sql = @sql + ' = NULL'
	SET @notfirst = 1
END
CLOSE cur
SET @sql = @sql + '
AS

SET NOCOUNT ON
'
IF @CreateSaveAndAddTogether = 1
    SET @sql = @sql + '
IF @' + @pk + ' IS NULL
BEGIN'
SET @sql = @sql + '
	INSERT ' + QUOTENAME(@TableName) + '('
SET @notfirst = 0

OPEN cur
WHILE 1 = 1
BEGIN
	FETCH NEXT FROM cur INTO @colname, @coldefault, @isnullable, @datatype, @collen
	IF @@FETCH_STATUS <> 0 BREAK
	IF @colname = @pk CONTINUE
	IF @notfirst = 1
		SET @sql = @sql + ', '
	SET @sql = @sql + @colname 
	SET @notfirst = 1
END
CLOSE cur

SET @sql = @sql + ')
	VALUES ('
SET @notfirst = 0

OPEN cur
WHILE 1 = 1
BEGIN
	FETCH NEXT FROM cur INTO @colname, @coldefault, @isnullable, @datatype, @collen
	IF @@FETCH_STATUS <> 0 BREAK
	IF @colname = @pk CONTINUE
	IF @notfirst = 1
		SET @sql = @sql + ', '
	SET @sql = @sql + '@' + @colname 
	SET @notfirst = 1
END
CLOSE cur

SET @sql = @sql + ')
	SET @' + @pk + ' = SCOPE_IDENTITY()
'
IF @CreateSaveAndAddTogether = 1
BEGIN
    SET @sql = @sql + 'END
ELSE 
	UPDATE ' + QUOTENAME(@TableName) + '
	SET
		'
    SET @notfirst = 0
    OPEN cur
    WHILE 1 = 1
    BEGIN
		FETCH NEXT FROM cur INTO @colname, @coldefault, @isnullable, @datatype, @collen
		IF @@FETCH_STATUS <> 0 BREAK
		IF @colname = @pk CONTINUE
		IF @notfirst = 1
			SET @sql = @sql + ',
		'
		SET @sql = @sql + @colname + ' = @' + @colname 
		SET @notfirst = 1
    END
    CLOSE cur
    SET @sql = @sql + '
		WHERE @' + @pk + ' = ' + @pk
END
SET @sql = @sql + '
'
SET @sql = @sql + 'GO
GRANT EXEC ON ' + CASE WHEN @CreateSaveAndAddTogether = 1 THEN @SaveProcedureName ELSE @AddProcedureName END + ' TO PUBLIC
'
SET @sql = @sql + 'GO'

print @sql

IF @CreateSaveAndAddTogether = 0
BEGIN
-- updating sproc
	SET @sql = 'IF EXISTS (SELECT * FROM sysobjects WHERE type = ''P'' AND name = ''' + @UpdateProcedureName + ''')
BEGIN
    DROP  PROCEDURE  ' + @UpdateProcedureName + '
END
'
SET @sql = @sql + 'GO
/*
procedure updating a record in a table ' + @TableName + ' */
'
SET @sql = @sql + 'CREATE PROCEDURE ' + @UpdateProcedureName + '
'
SET @notfirst = 0

OPEN cur
WHILE 1 = 1
BEGIN
	FETCH NEXT FROM cur INTO @colname, @coldefault, @isnullable, @datatype, @collen
	IF @@FETCH_STATUS <> 0 BREAK
	IF @notfirst = 1
		SET @sql = @sql + ',
'
	SET @sql = @sql + '@' + @colname + ' ' + @datatype
	IF @collen IS NOT NULL
		SET @sql = @sql + '(' + CAST(@collen AS varchar(10)) + ')'
	ELSE IF @isnullable = 1
		SET @sql = @sql + ' = NULL'
	SET @notfirst = 1
END
CLOSE cur

SET @sql = @sql + '
AS

SET NOCOUNT ON

UPDATE ' + QUOTENAME(@TableName) + '
SET
'
SET @notfirst = 0

OPEN cur
WHILE 1 = 1
BEGIN
	FETCH NEXT FROM cur INTO @colname, @coldefault, @isnullable, @datatype, @collen
	IF @@FETCH_STATUS <> 0 BREAK
	IF @colname = @pk CONTINUE
	IF @notfirst = 1
		SET @sql = @sql + ',
'
	SET @sql = @sql + @colname + ' = @' + @colname 
	SET @notfirst = 1
END
CLOSE cur
SET @sql = @sql + '
WHERE @' + @pk + ' = ' + @pk + '
'
SET @sql = @sql + 'GO
GRANT EXEC ON ' + @UpdateProcedureName + ' TO PUBLIC
'
SET @sql = @sql + 'GO'
PRINT @sql
END

-- deleting a record
SET @sql = 'IF EXISTS (SELECT * FROM sysobjects WHERE type = ''P'' AND name = ''' + @DeleteProcedureName + ''')
BEGIN
    DROP  PROCEDURE  ' + @DeleteProcedureName + '
END
'
SET @sql = @sql + 'GO
/*
** procedure deleting a record from a table ' + @TableName + '
*/
CREATE PROCEDURE ' + @DeleteProcedureName + '
@' + @pk + ' ' + @pkdatatype
IF @pkcollen IS NOT NULL
	SET @sql = @sql + '(' + @pkcollen + ')'
SET @sql = @sql + '
AS

SET NOCOUNT ON

DELETE ' + QUOTENAME(@TableName) + ' WHERE @' + @pk + ' = ' + @pk + '
'
SET @sql = @sql + 'GO

GRANT EXEC ON ' + @DeleteProcedureName + ' TO PUBLIC

GO'

print @sql

DEALLOCATE cur