sp_GetColumns

SQL Server procedure to write code based on table columns. Useful for creating update procedures, recordset loaders, and form updates.

CREATE proc [dbo].[sp_GetColumns] (@Table VARCHAR(50), @treatment varchar(50) = 'list', @prefix varchar(50) = '', @suffix varchar(50) = ',') AS
SET NOCOUNT ON
/*
declare @Table VARCHAR(50), @prefix varchar(50), @suffix varchar(50), @treatment varchar(50)
select	@Table = 'mood', @prefix = null, @suffix = ',', @treatment = 'updateproc'
*/

declare @returntypes table (rtype varchar(50), descrip varchar(1000))
declare @return table (col varchar(1000), colpos int, ordinal int)

insert into @returntypes (rtype, descrip) values ('list', 'Lists all the field names')
insert into @returntypes (rtype, descrip) values ('declare', 'Creates a stored procedure arguments')
insert into @returntypes (rtype, descrip) values ('insert', 'Creates an insert-style statement ')
insert into @returntypes (rtype, descrip) values ('update', 'Creates update-style statement')
insert into @returntypes (rtype, descrip) values ('rcdset', 'returns vb: declares variable and assign it to value from rcdset')
insert into @returntypes (rtype, descrip) values ('form', 'returns vb: declares variable and sets it to request value')
insert into @returntypes (rtype, descrip) values ('exec', 'returns vb: builds sql string with named variables')
insert into @returntypes (rtype, descrip) values ('updateproc', 'creates a standard update proc')
insert into @returntypes (rtype, descrip) values ('help', 'returns this information')

declare @bigreturn varchar(max)
if @prefix is null
begin
	select @prefix = ''
end

if @suffix is null
begin
	select @suffix = ''
end

if @treatment not in (select rtype from @returntypes)
begin
	select @treatment = 'help'
end

if @treatment = 'help'
begin
	select	* from @returntypes
	--return
end

if @treatment = 'list'
begin
	select	@prefix + [column_name] + @suffix
	from	information_schema.columns 
	where	table_name = @Table and table_schema = 'dbo'
	order by ordinal_position
end

if @treatment = 'insert'
begin
	insert into @return
	select	@prefix + [column_name] + @suffix, ordinal_position, 1
	from	information_schema.columns 
	where	table_name = @Table and table_schema = 'dbo'
	union
	select	@prefix + '@' + [column_name] + @suffix, ordinal_position, 2
	from	information_schema.columns 
	where	table_name = @Table and table_schema = 'dbo'

select col from @return order by ordinal, colpos
	
end

if @treatment = 'update'
begin
	select	@prefix + [column_name] + ' = @' + [column_name] + @suffix
	from	information_schema.columns 
	where	table_name = @Table and table_schema = 'dbo'
	order by ordinal_position
end

if @treatment = 'declare'
begin
	select	@prefix + 
			'@' + [column_name] + ' ' + 
			data_type + 
			case when character_maximum_length is not null then '(' + convert(varchar, character_maximum_length) + ')' else '' end +
			case is_nullable when 'YES' then ' = NULL' else '' end  + 
			@suffix
	from	information_schema.columns 
	where	table_name = @Table and table_schema = 'dbo'
	order by ordinal_position
end

if @treatment = 'rcdset'
begin
	--select	@prefix + 'dim ' + [column_name] + ' : ' + [column_name] + ' = rcdset("' + [column_name] +  '") & ""' + @suffix 
	select	@prefix + [column_name] + ' = rcdset("' + [column_name] +  '") & ""' + @suffix
	from	information_schema.columns 
	where	table_name = @Table and table_schema = 'dbo'
	order by ordinal_position
end

if @treatment = 'form'
begin
	select	@prefix + 'dim ' + [column_name] + ' : ' + [column_name] + ' = request("' + [column_name] +  '") & ""' + @suffix 
	--select	@prefix + [column_name] + ' = request("' + [column_name] +  '") & ""' + @suffix 
	from	information_schema.columns 
	where	table_name = @Table and table_schema = 'dbo'
	order by ordinal_position
end

if @treatment = 'exec'
begin
	select	@prefix + 
			'" @' + 
			[column_name] + 
			' = " & sqlsafe' + case when numeric_precision is not null then 'number' else '' end + 
			'(' + [column_name] + ')' +
			@suffix
	from	information_schema.columns 
	where	table_name = @Table and table_schema = 'dbo'
	order by ordinal_position
end
	
if @treatment = 'updateproc'
begin
	select @bigreturn = 'create proc sp_update' + @Table + ' ( '
	
	select @bigreturn = @bigreturn + @prefix + 
			'     @' + [column_name] + ' ' + 
			data_type + 
			case when character_maximum_length = -1 then '(max)' when character_maximum_length is not null then '(' + convert(varchar, character_maximum_length) + ')' else '' end +
			case is_nullable when 'YES' then ' = NULL' else '' end + 
			@suffix
			+ ' '
	from	information_schema.columns 
	where	table_name = @Table and table_schema = 'dbo'
	order by ordinal_position
	select @bigreturn = substring(@bigreturn,0,LEN(@bigreturn)-5)
	select @bigreturn = @bigreturn + ') AS DECLARE @return varchar(max) if @id = 0 begin '
	select @bigreturn = @bigreturn + '     INSERT INTO ' + @Table + '( '
	
	insert into @return
	select	@prefix + [column_name] + @suffix, ordinal_position, 1
	from	information_schema.columns 
	where	table_name = @Table and table_schema = 'dbo' and [column_name] <> 'id'
	union
	select	@prefix + '@' + [column_name] + @suffix, ordinal_position, 2
	from	information_schema.columns 
	where	table_name = @Table and table_schema = 'dbo' and [column_name] <> 'id'
	select @bigreturn = @bigreturn + '          ' + r.col + ' ' from @return r where r.ordinal = 1 order by colpos
	select @bigreturn = substring(@bigreturn,0,LEN(@bigreturn) -5)
	select @bigreturn = @bigreturn + ')      select '
	select @bigreturn = @bigreturn + '          ' + col + ' ' from @return r where r.ordinal = 2 order by colpos
	select @bigreturn = substring(@bigreturn,0,LEN(@bigreturn) -5) + ' '
	select @bigreturn = @bigreturn + '      select @return = @@identity'
	select @bigreturn = @bigreturn + ' end else begin '
	select @bigreturn = @bigreturn + '     UPDATE ' + @Table + '      SET'
	select @bigreturn = @bigreturn + '          ' + @prefix + [column_name] + ' = @' + [column_name] + @suffix + ' '
	from	information_schema.columns 
	where	table_name = @Table and table_schema = 'dbo' and [column_name] <> 'id'
	order by ordinal_position
	select @bigreturn = substring(@bigreturn,0,LEN(@bigreturn) -5) + ' '
	select @bigreturn = @bigreturn + '     WHERE id = @id '
	select @bigreturn = @bigreturn + '     select @return = @id '
	select @bigreturn = @bigreturn + ' end '
	select @bigreturn = @bigreturn + ' select @return ''id'' '
	select @bigreturn
end