IF EXISTS (
SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE SPECIFIC_SCHEMA = N'dbo'
AND SPECIFIC_NAME = N'dd_CreateTableFromStoredProcedureParameters'
)
DROP PROCEDURE dbo.dd_CreateTableFromStoredProcedureParameters
GO
CREATE PROCEDURE dbo.dd_CreateTableFromStoredProcedureParameters
-- dd_CreateTableFromStoredProcedureParameters 'taRMCashReceiptInsert'
@StoredProcedureName varchar(100)
AS
set nocount on
DECLARE @text varchar(100)
print 'CREATE TABLE DD' + @StoredProcedureName
print '('
PRINT 'RowID int NOT NULL IDENTITY (1, 1),'
DECLARE curName CURSOR LOCAL FAST_FORWARD FOR
select rtrim(replace(PARAMETER_NAME,'@I_v','')) + ' ' + DATA_TYPE +
case DATA_TYPE
when 'char' then ' (' + convert(varchar(20),CHARACTER_MAXIMUM_LENGTH) + ') '
when 'varchar' then ' (' + convert(varchar(20),CHARACTER_MAXIMUM_LENGTH) + ') '
when 'datetime' then ' '
when 'date' then ' '
when 'numeric' then ' (' + convert(varchar(20),NUMERIC_PRECISION) + ',' + convert(varchar(20),NUMERIC_SCALE) + ') '
when 'smallint' then ' '
when 'int' then ' '
when 'bigint' then ' '
when 'bit' then ' '
else 'zzz ' end +
'NOT NULL' +
case when ROW_NUMBER() over (partition by specific_name order by ordinal_position) = MAX(ORDINAL_POSITION) over (partition by specific_name) then '' else ',' end
from INFORMATION_SCHEMA.PARAMETERS
where SPECIFIC_NAME = 'taRMCashReceiptInsert'
and not PARAMETER_NAME in ('@O_iErrorState','@oErrString')
OPEN curName
WHILE 1=1
BEGIN
FETCH NEXT FROM curName INTO @text
if @@fetch_status <> 0 begin
break
end
print @text
END
CLOSE curName
DEALLOCATE curName
print ') ON [PRIMARY]'
print 'GO'
Print 'ALTER TABLE DD' + @StoredProcedureName + ' ADD CONSTRAINT'
Print ' PK_DDErrorLog PRIMARY KEY CLUSTERED '
Print ' ('
Print ' RowID'
Print ' ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]'
Print 'GO'
grant exec on dd_CreateTableFromStoredProcedureParameters to public