-- =============================================
-- Create scalar function (FN)
-- =============================================
IF EXISTS (SELECT *
FROM sysobjects
WHERE name = N'f_4P_columnType')
DROP FUNCTION f_4P_columnType
GO
CREATE FUNCTION f_4P_columnType
(@intColumnType as int,@intLength int, @intPrecision int , @intscale int)
-- select dbo.f_4P_columnType(56,0,0,0)
RETURNS varchar(20)
AS
BEGIN
declare @out varchar(20)
select @out =
case when @intColumnType = 56 then 'int'
when @intColumnType = 48 then 'tinyint'
when @intColumnType = 36 then 'uniqueidentifier'
when @intColumnType = 52 then 'smallint'
when @intColumnType = 60 then 'money'
when @intColumnType = 45 then 'bit'
when @intColumnType = 62 then 'float'
when @intColumnType = 35 then 'text'
when @intColumnType = 61 then 'datetime'
when @intColumnType = 189 then 'timestamp'
when @intColumnType = 239 then 'nvar(' + convert(varchar(4),@intLength) + ')'
when @intColumnType = 231 then 'nvarchar(' + convert(varchar(4),@intLength) + ')'
when @intColumnType = 173 then 'binary(' + convert(varchar(4),@intLength) + ')'
when @intColumnType in (175,47) then 'char(' + convert(varchar(4),@intLength) + ')'
when @intColumnType = 167 then 'varchar(' + convert(varchar(4),@intLength) + ')'
when @intColumnType in (108,63) then 'numeric(' + convert(varchar(4),@intPrecision) + ',' + convert(varchar(4),@intscale) + ')'
when @intColumnType in (106) then 'numeric(' + convert(varchar(4),@intPrecision) + ',' + convert(varchar(4),@intscale) + ')'
else 'dunno - ' + convert(varchar(4),@intColumnType)
end
return @out
END
GO
-- =============================================
-- v 1.5
-- =============================================
IF EXISTS (SELECT *
FROM sysobjects
WHERE name = N'f_4P_columnTypeVB')
DROP FUNCTION f_4P_columnTypeVB
GO
CREATE FUNCTION f_4P_columnTypeVB
(@intColumnType as int)
-- select dbo.f_4P_columnTypeVB(56)
RETURNS varchar(20)
AS
BEGIN
declare @out varchar(20)
select @out =
case when @intColumnType in(48,52, 56) then 'system.int64'
when @intColumnType = 36 then 'GUID'
when @intColumnType in(108,60,63,106) then 'double'
when @intColumnType = 45 then 'boolean'
when @intColumnType in (35,47,167, 175,231) then 'string'
when @intColumnType = 61 then 'date'
when @intColumnType = 104 then 'int16' --bit
--when @intColumnType = 173 then 'binary(' + convert(varchar(4),@intLength) + ')'
else 'dunno - ' + convert(varchar(4),@intColumnType)
end
return @out
END
GO
-- =============================================
--
-- =============================================
-- 1/1/1900 created
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'sp_select'
AND type = 'P')
DROP PROCEDURE sp_select
GO
CREATE PROCEDURE sp_select
-- sp_select 'groups'
@vchrTableName varchar(50)
AS
set nocount on
declare @vchrProcName varchar(100)
declare @vchrFieldName varchar(100)
declare @vchrFieldType varchar(100)
declare @vchrFieldTypeVB varchar(100)
declare @vchrParamName varchar(100)
declare @intColstat int
declare @vchrParamList varchar(1000)
declare @vchrSelectList varchar(1000)
select
@vchrParamList = '',
@vchrSelectList = '',
-- @vchrParamListVB = '',
-- @vchrParamListHelper = '',
-- @output = '',
@vchrProcName = '_4P_' + upper(@vchrTableName) + '_SEL',
@vchrTableName = upper(@vchrTableName)
print 'IF EXISTS (SELECT name '
print ' FROM sysobjects '
print ' WHERE name = N' + char(39) + @vchrTableName + char(39)
print ' AND type = ' + char(39) + 'P' + char(39) + ')'
print ' DROP PROCEDURE ' + @vchrProcName
print 'GO'
PRINT ''
print 'CREATE PROCEDURE ' + @vchrProcName
print ''
declare curFields CURSOR for
select syscolumns.name,
dbo.f_4P_columnType (xusertype,length, xprec , xscale),
dbo.f_4P_columnTypeVB (xusertype),
char(64) + syscolumns.name,
syscolumns.colstat
from sysobjects
left join syscolumns on syscolumns.id = sysobjects.id
where sysobjects.name = @vchrTableName
-- sp_select 'groups'
DECLARE @vchrWhere varchar(1000)
set @vchrWhere = ''
OPEN curFields
FETCH NEXT FROM curFields INTO @vchrfieldName, @vchrFieldType, @vchrFieldTypeVB,@vchrparamName, @intColstat
WHILE @@FETCH_STATUS = 0
BEGIN
--build a where clause
if @intColStat = 1 begin
set @vchrWhere = @vchrWhere + ' ' + @vchrFieldName + ' = ' + @vchrparamName+ char(13)
--build a param list (builds @name varchar(20) )
set @vchrParamList = @vchrParamList + char(13) + @vchrparamName + ' ' + @vchrFieldType + ','
end
--build the 'set' list
set @vchrSelectList = @vchrSelectList + char(13) + space(8) + @vchrFieldName + ','
FETCH NEXT FROM curFields INTO @vchrfieldName, @vchrFieldType,@vchrFieldTypeVB, @vchrparamName, @intColstat
END
CLOSE curFields
DEALLOCATE curFields
--remove the last char from the list
set @vchrParamList = left(@vchrParamList, len(@vchrParamList)-1)
--remove the first and last char from the list
set @vchrSelectList = stuff(@vchrSelectList,1,1,'')
set @vchrSelectList = left(@vchrSelectList, len(@vchrSelectList)-1)
print @vchrParamList
print ''
PRINT 'AS'
print ''
Print 'SELECT'
print @vchrSelectList
print ' FROM ' + @vchrTableName + ' WITH (NOLOCK)'
print ' WHERE'
print @vchrWhere
PRINT 'GO'
PRINT 'GRANT EXEC ON ' + @vchrProcName + ' TO PUBLIC'
-- sp_select 'groups'
go
grant exec on sp_select to public
go
-- =============================================
--
-- =============================================
-- 1/1/1900 created
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'sp_insert'
AND type = 'P')
DROP PROCEDURE sp_insert
GO
CREATE PROCEDURE sp_insert
-- sp_insert 'sop10101'
@vchrTableName varchar(50)
AS
declare @fieldName varchar(500)
declare @paramName varchar(255)
declare @fieldType varchar(50)
declare @fieldTypeVB varchar(50)
declare @intTableNameLen int
declare @vchrProcName varchar(50)
declare @vchrAlias varchar(2)
declare @vchrFieldList as varchar(8000)
declare @vchrFieldList2 as varchar(8000)
Declare @vchrParamList as varchar(8000)
Declare @vchrIntoList as varchar(8000)
declare @paramListHelper varchar(8000)
declare @paramListVB varchar(8000)
select @vchrTableName = upper(@vchrTableName)
select
@vchrProcName = 'FP_' + @vchrTableName + '_INS',
@vchrAlias = case when left(@vchrTableName,1) = '_' then substring(@vchrTableName,2,1) else left(@vchrTableName,1) end + '.',
@intTableNameLen = len(@vchrTableName),
@vchrFieldList = '',
@vchrFieldList2 = '',
@vchrParamList = '',
@vchrIntoList = '',
@paramListVB = '',
@paramListVB = '',
@paramListHelper = ''
select
@vchrProcName = replace(@vchrProcName,'__','_')
print '-- ============================================='
print '-- '
print '-- ============================================='
print '-- 1/1/1900 created'
print 'IF EXISTS (SELECT name'
print ' FROM sysobjects '
print ' WHERE name = N' + '''' + @vchrProcName + ''''
print ' AND type = ''P'') '
print ' DROP PROCEDURE ' + @vchrProcName
print 'GO'
print 'CREATE PROCEDURE ' + @vchrProcName
print ''
declare curFields CURSOR for
select syscolumns.name,
dbo.f_4P_columnType(xusertype,length,xprec,xscale) as fieldType,
dbo.f_4P_columnTypeVB (xusertype),
char(64) + syscolumns.name
from sysobjects
left join syscolumns on syscolumns.id = sysobjects.id
where sysobjects.name = @vchrTableName
and colstat <> 1
OPEN curFields
FETCH NEXT FROM curFields INTO @fieldName, @fieldType, @fieldTypeVB, @paramName
WHILE @@FETCH_STATUS = 0
BEGIN
if @fieldType <> 'text' begin
set @vchrFieldList = @vchrFieldList + @fieldName + ' ' + ', '
set @vchrFieldList2 = @vchrFieldList2 + @vchrAlias + @fieldName + ', '
set @vchrParamList = @vchrParamList + char(64) + @fieldName + ' ' + @fieldtype + ',' + char(13) + char(10)
set @vchrIntoList = @vchrIntoList + char(64) + @fieldName + ' , '
--build a VB param list
set @paramListVB = @paramListVB + ', ' + 'ByVal ' + @fieldName + ' As ' + @fieldTypeVB
--build a sqlhelper param list
set @paramListHelper = @paramListHelper + ', _ ' + char(13) + space(16) + 'New SqlParameter("' + @paramName + '", ' + @fieldName + ')'
end
FETCH NEXT FROM curFields INTO @fieldName, @fieldType,@fieldTypeVB, @paramName
END
CLOSE curFields
DEALLOCATE curFields
set @vchrFieldList = left(@vchrFieldList, len(@vchrFieldList) - 1)
set @vchrFieldList2 = left(@vchrFieldList2, len(@vchrFieldList2) - 1)
set @vchrParamList = left (@vchrParamList,len(@vchrParamList)-3)
set @vchrIntoList = left (@vchrIntoList,len(@vchrIntoList)-1)
set @paramListVB = stuff(@paramListVB,1,1,'')
-- sp_insert 'XPOTrans_HIST'
print @vchrParamList
print ''
print 'AS'
PRINT ''
print 'INSERT INTO ' + @vchrTableName + ' (' + @vchrFieldList + ')'
print ' SELECT ' + space(@intTableNameLen) + @vchrFieldList2
print ' FROM '
PRINT ''
PRINT ''
print 'INSERT INTO ' + @vchrTableName + ' (' + @vchrFieldList + ')'
print space(len(@vchrProcName)) + 'values(' + @vchrIntoList + ')'
PRINT ''
PRINT 'GO'
print ''
print 'grant exec on ' + @vchrProcName + ' to public'
print ''
print 'Public Sub ' + @vchrTableName + '_INS(' + @paramListVB + ') '
print ' SqlHelper.ExecuteNonQuery(m_cs, CommandType.StoredProcedure, "' + @vchrProcName + '"' + @paramListHelper + ' )'
print 'End Sub'
go
-- =============================================
--
-- =============================================
-- 1/1/1900 created
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'sp_update'
AND type = 'P')
DROP PROCEDURE sp_update
GO
CREATE PROCEDURE sp_update
-- sp_update 'sop10101'
@tablename varchar(50)
AS
set nocount on
declare @output varchar(8000)
declare @fieldTypeVB varchar(50)
declare @fieldName varchar(500)
declare @fieldType varchar(50)
declare @paramName varchar(255)
declare @paramList varchar(8000)
declare @paramListVB varchar(8000)
declare @paramListHelper varchar(8000)
declare @vchrProcName varchar(255),
@vchrSetList varchar(8000),
@intColStat int
set @output = ''
select
@paramList = '',
@vchrSetList = '',
@paramListVB = '',
@paramListHelper = '',
@output = '',
@vchrProcName = 'FP_' + upper(@tablename) + '_UPD',
@tableName = upper(@tableName)
print 'IF EXISTS (SELECT name '
print ' FROM sysobjects '
print ' WHERE name = N' + char(39) + @tablename + char(39)
print ' AND type = ' + char(39) + 'P' + char(39) + ')'
print ' DROP PROCEDURE ' + @vchrProcName
print 'GO'
print ''
print 'CREATE PROCEDURE ' + @vchrProcName
declare curFields CURSOR for
select syscolumns.name,
dbo.f_4P_columnType (xusertype,length, xprec , xscale),
dbo.f_4P_columnTypeVB (xusertype),
char(64) + syscolumns.name,
syscolumns.colstat
from sysobjects
left join syscolumns on syscolumns.id = sysobjects.id
where sysobjects.name = @tablename
-- and colstat <> 1
DECLARE @vchrWhere varchar(1000)
set @vchrWhere = ''
OPEN curFields
FETCH NEXT FROM curFields INTO @fieldName, @fieldType, @fieldTypeVB,@paramName, @intColstat
WHILE @@FETCH_STATUS = 0
BEGIN
--build a where clause
if @intColStat = 1 begin
set @vchrWhere = @vchrWhere + ' ' + @fieldName + ' = ' + @paramName+ char(13)
end
--build a param list
set @paramList = @paramList + char(13) + @paramName + ' ' + @fieldType + ','
--build the 'set' list
set @vchrSetList = @vchrSetList + char(13) + space(8) + @fieldName + ' = ' + @paramName + ','
--build a VB param list
set @paramListVB = @paramListVB + ', ' + 'ByVal ' + @fieldName + ' As ' + @fieldTypeVB
--build a sqlhelper param list
set @paramListHelper = @paramListHelper + ', _ ' + char(13) + space(16) + 'New SqlParameter("' + @paramName + '", ' + @fieldName + ')'
FETCH NEXT FROM curFields INTO @fieldName, @fieldType,@fieldTypeVB, @paramName, @intColstat
END
CLOSE curFields
DEALLOCATE curFields
set @paramListVB = stuff(@paramListVB,1,2,'')
--remove the first and last char from the list
set @vchrSetList = stuff(@vchrSetList,1,1,'')
set @vchrSetList = left(@vchrSetList, len(@vchrSetList)-1)
--remove the last char from the list
set @paramList = left(@paramList, len(@paramList)-1)
print @paramlist
print ''
print 'AS'
print ''
print 'UPDATE ' + @tablename + ' SET'
print @vchrSetList
--print ' FROM ' + @tablename
PRINT ' WHERE '
print @vchrWhere
-- sp_update 'cn20100'
print ''
print 'GO'
print ''
print 'grant all on ' + @vchrProcName + ' to public'
print ''
print ''
print ''
print ''
print 'Public Sub ' + @tableName + '_UPD(' + @paramListVB + ') '
print ' SqlHelper.ExecuteNonQuery(m_cs, CommandType.StoredProcedure, "' + @vchrProcName + '"' + @paramListHelper + ' )'