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 SourceTypes
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
end
Below 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
Subscribe