Part of my pattern during development is to:
1. Create a new table
2. Create the stored procs to get data and save data
This second step is tedious and lends itself to automation. So I created the stored proc createSPFromTable that takes the name of a table and creates a simple save store proc. So the example:
createSPFromTable SourceTypesHere is the source code for createSPFromTable:
create proc saveSourceTypes(@id int, @Name varchar(50),
@DisplayOrder float, @Status char(1))
as
begin
set nocount on
if @id > 0
update SourceTypes
set Name = @Name,
DisplayOrder = @DisplayOrder,
Status = @Status
where id = @id
else
begin
insert into SourceTypes
(Name, DisplayOrder, Status)
values (@Name, @DisplayOrder, @Status)
set @id = scope_identity()
end
select @id
return @id
endBelow is the stored proc that generates the basic save proc. Not that you have to pass in the primary key column if it is not named “id”.
create proc [dbo].[createSPFromTable](@TableName varchar(250),
@idColumn varchar(25)=null)
as
set nocount on
declare @crlf char(2)
set @crlf = char(13)+char(10)
declare @parameterList varchar(8000)
set @parameterList='create proc save'+@TableName+'('
-- This converts sql type to c# type.
SELECT @parameterList= @parameterList + case when lower(c.COLUMN_NAME) = isnull(@idColumn,'id') then '' else '@'+c.COLUMN_NAME+' ' + c.DATA_TYPE+
CASE c.DATA_TYPE
WHEN 'binary' THEN '('+ltrim(cast(c.CHARACTER_MAXIMUM_LENGTH as varchar(4)))+')'
WHEN 'char' THEN '('+ltrim(cast(c.CHARACTER_MAXIMUM_LENGTH as varchar(4)))+')'
WHEN 'decimal' THEN '('+ltrim(cast(c.CHARACTER_MAXIMUM_LENGTH as varchar(4)))+')'
WHEN 'nchar' THEN '('+ltrim(cast(c.CHARACTER_MAXIMUM_LENGTH as varchar(4)))+')'
WHEN 'numeric' THEN '('+ltrim(cast(c.CHARACTER_MAXIMUM_LENGTH as varchar(4)))+')'
WHEN 'nvarchar' THEN '('+ltrim(cast(c.CHARACTER_MAXIMUM_LENGTH as varchar(4)))+')'
WHEN 'varbinary' THEN '('+ltrim(cast(c.CHARACTER_MAXIMUM_LENGTH as varchar(4)))+')'
WHEN 'varchar' THEN '('+ltrim(cast(c.CHARACTER_MAXIMUM_LENGTH as varchar(4)))+')'
ELSE '' END+', ' end,
@idColumn = case when @idColumn is null and lower(c.COLUMN_NAME) = 'id' then c.COLUMN_NAME else @idColumn end
from INFORMATION_SCHEMA.COLUMNS c
where TABLE_NAME = @TableName
order by c.ORDINAL_POSITION
set @parameterList=substring(@parameterList,1,len(@parameterList)-1)
set @parameterList=@parameterList+')'+@crlf+'as'+@crlf+'begin'+@crlf+'set nocount on'+@crlf+@crlf+@crlf
set @parameterList=@parameterList+'if @'+@idColumn +' > 0 ' + @crlf
set @parameterList=@parameterList+' update '+@TableName+ @crlf +' set'
SELECT @parameterList=@parameterList+' '+ c.COLUMN_NAME+' = @'+c.COLUMN_NAME+', '+ @crlf
from INFORMATION_SCHEMA.COLUMNS c
where TABLE_NAME = @TableName
and c.COLUMN_NAME <> @idColumn
order by c.ORDINAL_POSITION
set @parameterList=substring(@parameterList,1,len(@parameterList)-4)+ @crlf+ ' where '+@idColumn +' = @'+@idColumn+ @crlf
set @parameterList=@parameterList+'else '+ @crlf +'begin'+@crlf+ ' insert into '+@TableName+@crlf +' ('
SELECT @parameterList=@parameterList+ c.COLUMN_NAME+', '
from INFORMATION_SCHEMA.COLUMNS c
where TABLE_NAME = @TableName
and c.COLUMN_NAME <> @idColumn
order by c.ORDINAL_POSITION
set @parameterList=substring(@parameterList,1,len(@parameterList)-1)
set @parameterList=@parameterList+')'+@crlf
set @parameterList=@parameterList+' values ('
SELECT @parameterList=@parameterList+'@'+ c.COLUMN_NAME+', '
from INFORMATION_SCHEMA.COLUMNS c
where TABLE_NAME = @TableName
and c.COLUMN_NAME <> @idColumn
order by c.ORDINAL_POSITION
set @parameterList=substring(@parameterList,1,len(@parameterList)-1)
set @parameterList=@parameterList+')'+@crlf+@crlf
set @parameterList=@parameterList+' set @'+@idColumn+' = scope_identity()'+@crlf
set @parameterList=@parameterList+'end'+@crlf
set @parameterList=@parameterList+'select @'+@idColumn+@crlf
set @parameterList=@parameterList+'return @'+@idColumn+@crlf
set @parameterList=@parameterList+'end'+@crlf
print @parameterList