iVend Database Database : InsertGenerator Stored Procedure

Creation Date

4/13/2015 12:00 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@tableName

In

VarChar

100

@RETURN_VALUE

Return Value

Integer

4

CREATE PROCEDURE [dbo].[InsertGenerator]

(@tableName varchar(100)) as

--Declare a cursor to retrieve column specific information for the specified table

DECLARE cursCol CURSOR FAST_FORWARD FOR

SELECT column_name,data_type FROM information_schema.columns WHERE table_name = @tableName

OPEN cursCol

DECLARE @string nvarchar(3000) --for storing the first half of INSERT statement

DECLARE @stringData nvarchar(3000) --for storing the data (VALUES) related statement

DECLARE @dataType nvarchar(1000) --data types returned for respective columns

SET @string='INSERT '+@tableName+'('

SET @stringData=''

DECLARE @colName nvarchar(50)

FETCH NEXT FROM cursCol INTO @colName,@dataType

IF @@fetch_status<>0

begin

print 'Table '+@tableName+' not found, processing skipped.'

close curscol

deallocate curscol

return

END

WHILE @@FETCH_STATUS=0

BEGIN

IF @dataType in ('varchar','char','nchar','nvarchar')

BEGIN

--SET @stringData=@stringData+'''''''''+isnull('+@colName+','''')+'''''',''+'

SET @stringData=@stringData+''''+'''+isnull('''''+'''''+'+@colName+'+'''''+''''',''NULL'')+'',''+'

END

ELSE

if @dataType in ('text','ntext') --if the datatype is text or something else

BEGIN

SET @stringData=@stringData+'''''''''+isnull(cast('+@colName+' as varchar(2000)),'''')+'''''',''+'

END

ELSE

IF @dataType = 'money' --because money doesn't get converted from varchar implicitly

BEGIN

SET @stringData=@stringData+'''convert(money,''''''+isnull(cast('+@colName+' as varchar(200)),''0.0000'')+''''''),''+'

END

ELSE

IF @dataType='datetime'

BEGIN

--SET @stringData=@stringData+'''convert(datetime,''''''+isnull(cast('+@colName+' as varchar(200)),''0'')+''''''),''+'

--SELECT 'INSERT Authorizations(StatusDate) VALUES('+'convert(datetime,'+isnull(''''+convert(varchar(200),StatusDate,121)+'''','NULL')+',121),)' FROM Authorizations

--SET @stringData=@stringData+'''convert(money,''''''+isnull(cast('+@colName+' as varchar(200)),''0.0000'')+''''''),''+'

SET @stringData=@stringData+'''convert(datetime,'+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+',121)+'''''+''''',''NULL'')+'',121),''+'

--                             'convert(datetime,'+isnull(''''+convert(varchar(200),StatusDate,121)+'''','NULL')+',121),)' FROM Authorizations

END

ELSE

IF @dataType='image'

BEGIN

SET @stringData=@stringData+'''''''''+isnull(cast(convert(varbinary,'+@colName+') as varchar(6)),''0'')+'''''',''+'

END

ELSE --presuming the data type is int,bit,numeric,decimal

BEGIN

--SET @stringData=@stringData+'''''''''+isnull(cast('+@colName+' as varchar(200)),''0'')+'''''',''+'

--SET @stringData=@stringData+'''convert(datetime,'+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+',121)+'''''+''''',''NULL'')+'',121),''+'

SET @stringData=@stringData+''''+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+')+'''''+''''',''NULL'')+'',''+'

END

SET @string=@string+@colName+','

FETCH NEXT FROM cursCol INTO @colName,@dataType

END

DECLARE @Query nvarchar(4000)

SET @query ='SELECT '''+substring(@string,0,len(@string)) + ') VALUES(''+ ' + substring(@stringData,0,len(@stringData)-2)+'''+'')'' FROM '+@tableName

exec sp_executesql @query

select @query

CLOSE cursCol

DEALLOCATE cursCol