IF OBJECT_ID(N'dbo.Attempt_Generic_Insert') IS NULL
BEGIN
PRINT 'Create procedure : dbo.Attempt_Generic_Insert'
EXECUTE('CREATE PROCEDURE dbo.Attempt_Generic_Insert AS RETURN 0')
END
GO
ALTER PROCEDURE [dbo].[Attempt_Generic_Insert]
(
@TableName varchar(max),
@ColumnSet Column_Setting READONLY
)
AS
BEGIN
DECLARE @LeftHalf varchar(max) = 'INSERT INTO [' + @TableName + '] ('
DECLARE @RightHalf varchar(max) = ']) VALUES ('
DECLARE @FullInsert varchar(max)
DECLARE @LeadWithComma bit = 0
DECLARE @ColumnSetName varchar(255)
DECLARE @ColumnSetSetting varchar(255)
DECLARE @ColumnSetEnclosedInQuotes bit
DECLARE curs CURSOR FOR SELECT * From @ColumnSet
OPEN curs
FETCH NEXT FROM curs into @ColumnSetName, @ColumnSetSetting,
@ColumnSetEnclosedInQuotes
WHILE @@FETCH_STATUS = 0
BEGIN
SET @LeftHalf = @LeftHalf + CASE @LeadWithComma WHEN 1 THEN '], [' ELSE
'[' END + @ColumnSetName
SET @RightHalf = @RightHalf + CASE @LeadWithComma WHEN 1 THEN ', ' ELSE
'' END
SET @RightHalf = @RightHalf + CASE @ColumnSetEnclosedInQuotes WHEN 1
THEN '''' ELSE '' END
SET @RightHalf = @RightHalf + @ColumnSetSetting
SET @RightHalf = @RightHalf + CASE @ColumnSetEnclosedInQuotes WHEN 1
THEN '''' ELSE '' END
SET @LeadWithComma = 1
FETCH NEXT FROM curs into @ColumnSetName, @ColumnSetSetting,
@ColumnSetEnclosedInQuotes
END
CLOSE curs
DEALLOCATE curs
SET @FullInsert = @LeftHalf + @RightHalf + ')'
EXECUTE(@FullInsert)
END
Alright, a few things to note... First, this will clearly blow up if a required column is left out, so the guts of this thing should really be wrapped in some try/catch logic. An error message should be returned if an error exists. Secondly, the line reading...
EXECUTE(@FullInsert)
...often times can be represented as...
exec sp_executesql @FullInsert
...I just couldn't get it to work in this case. Also we need a type named Column_Setting and that can look like this:
CREATE TYPE [dbo].[Column_Setting] AS TABLE(
Name varchar(255) NOT NULL,
Setting varchar(255) NOT NULL,
EncloseInQuotes bit NOT NULL,
PRIMARY KEY CLUSTERED
(
Name ASC
)WITH (IGNORE_DUP_KEY = OFF)
)
Assuming this table...
CREATE TABLE dbo.Cat
(
CatId int IDENTITY NOT NULL,
Name varchar(255) NOT NULL,
LivesRemaining int NOT NULL,
Birthdate datetime NULL,
IsFluffy bit NOT NULL
)
...we could insert to it like so:
DECLARE @TableName varchar(max) = 'Cat'
DECLARE @ColumnSet Column_Setting
INSERT INTO @ColumnSet VALUES ('Name','Shadow',1)
INSERT INTO @ColumnSet VALUES ('LivesRemaining',9,0)
INSERT INTO @ColumnSet VALUES ('Birthdate','8/24/1974',1)
INSERT INTO @ColumnSet VALUES ('IsFluffy',1,0)
EXEC Attempt_Generic_Insert @TableName, @ColumnSet
