To create store procedure

[ramuygl]

want to create store procedure that. want to send the table name as argument and retrive the data of that argument. and want to store data in temperary table using the insert query.

HERE I AM ATTACHING A SAMPLE PROCEDURE IT WAS NOT WORKING PERFECTLY. HELP ME


--exec SP_Generate_Insert_Statements tbl_Attachment,1
--select * from tbl_Attachmenttesting
--exec SP_Generate_Insert_Statements tbl_Attachmenttesting,1

ALTER PROCEDURE [dbo].[SP_Generate_Insert_Statements_Testing]
@strTableName varchar(128), -- used to specify the table to generate data for
@RequiredGo bit = 0 -- used to allow GO statements to separate the insert statements
AS

--Variable declarations
DECLARE @InsertStmt varchar(max)
DECLARE @Fields varchar(8000)
DECLARE @SelList varchar(max)
DECLARE @ColName varchar(128)
DECLARE @IsChar tinyint
DECLARE @tableData varchar(8000)
DECLARE @strImageSQL varchar(200)
DECLARE @strTextSQL varchar(200)
DECLARE @ImageData varbinary(8000)
DECLARE @TextData varchar(max)

DECLARE @bitIdentity BIT

SET NOCOUNT OFF

SELECT @bitIdentity = OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'TableHasIdentity')
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_Name =@strTableName

PRINT '---- ** Start of Inserts ** ----'
PRINT ''

IF @bitIdentity = 1
BEGIN
PRINT 'SET IDENTITY_INSERT [' + @strTableName +'] ON '
END

--initialize variables
SELECT @InsertStmt = 'INSERT INTO [' + @strTableName + '] (',
@Fields = '',
@SelList = 'SELECT '


--create a cursor that loops through the fields in the table
--and retrieves the column names and determines the delimiter type that the field needs
DECLARE CR_Table CURSOR FAST_FORWARD FOR

SELECT COLUMN_NAME,
'IsChar' = CASE
WHEN DATA_TYPE in ('int', 'money', 'decimal', 'tinyint', 'smallint' ,'numeric', 'bit', 'bigint', 'smallmoney', 'float','timestamp') THEN 0
WHEN DATA_TYPE in ('char', 'varchar', 'nvarchar','uniqueidentifier', 'nchar') THEN 1
WHEN DATA_TYPE in ('datetime', 'smalldatetime') THEN 2
WHEN DATA_TYPE in ('text', 'ntext') THEN 3
WHEN DATA_TYPE in ('sql_variant') THEN 4
WHEN DATA_TYPE in ('image') THEN 5
ELSE 6
END
FROM INFORMATION_SCHEMA.COLUMNS c WITH (NOLOCK)
INNER JOIN syscolumns sc WITH (NOLOCK)
ON c.COLUMN_NAME = sc.name
INNER JOIN sysobjects so WITH (NOLOCK)
ON sc.id = so.id
AND so.name = c.TABLE_NAME
WHERE table_name = @strTableName
AND DATA_TYPE <> 'timestamp'
AND sc.IsComputed = 0
ORDER BY ORDINAL_POSITION
FOR READ ONLY
OPEN CR_Table

FETCH NEXT FROM CR_Table INTO @ColName, @IsChar

WHILE (@@fetch_status <> -1)
BEGIN

IF @@fetch_status <> -1
BEGIN
IF @SelList = 'SELECT'
BEGIN
SELECT @Fields = @Fields + '[' + @ColName + ']' + ', '
SELECT @SelList = CASE @IsChar
WHEN 1 THEN @SelList + ' ISNULL('''''''' + REPLACE(['+ @ColName + '],'''''''', '''''''''''' ) + '''''''',''NULL'') ' + ' COLLATE database_default + '
WHEN 2 THEN @SelList + 'ISNULL('''''''' + CONVERT(VARCHAR(20),['+ @ColName + '])+ '''''''',''NULL'') ' + ' COLLATE database_default + '
WHEN 3 THEN @SelList + ' ISNULL('''''''' + REPLACE(CONVERT(VARCHAR(MAX),['+ @ColName + ']),'''''''', '''''''''''')+ '''''''' ,''NULL'') '+ ' COLLATE database_default + '
--WHEN 3 THEN @SelList + ''' CONVERT(VARCHAR(MAX),['+ @ColName + ']) '''
WHEN 4 THEN @SelList + ' ISNULL('''''''' + REPLACE(CONVERT(VARCHAR(8000),['+ @ColName + ']),'''''''', '''''''''''')+ '''''''' ,''NULL'') '+ ' COLLATE database_default + '
WHEN 5 THEN @SelList + '''MyImageData'''''
ELSE @SelList + ' ISNULL(CONVERT(VARCHAR(2000),['+ @ColName + '],0),''NULL'')' + ' COLLATE database_default + ' END
END
ELSE
BEGIN
SELECT @Fields = @Fields + '[' + @ColName + ']' + ', '
SELECT @SelList = CASE @IsChar
WHEN 1 THEN @SelList + ''',''' + ' + ' + ' ISNULL('''''''' + REPLACE(['+ @ColName + '],'''''''', '''''''''''' ) + '''''''',''NULL'') ' + ' COLLATE database_default + '
WHEN 2 THEN @SelList + ''',''' + ' + ' + 'ISNULL('''''''' + CONVERT(VARCHAR(20),['+ @ColName + '])+ '''''''',''NULL'') ' + ' COLLATE database_default + '
WHEN 3 THEN @SelList + ' ISNULL('''''''' + REPLACE(CONVERT(VARCHAR(MAX),['+ @ColName + ']),'''''''', '''''''''''')+ '''''''' ,''NULL'') '+ ' COLLATE database_default + '
--WHEN 3 THEN @SelList + ''' CONVERT(VARCHAR(MAX),['+ @ColName + ']) '''
WHEN 4 THEN @SelList + ''',''' + ' + ' + ' ISNULL('''''''' + REPLACE(CONVERT(VARCHAR(8000),['+ @ColName + ']),'''''''', '''''''''''')+ '''''''' ,''NULL'') '+ ' COLLATE database_default + '
WHEN 5 THEN @SelList + ''',''' + '''MyImageData'''''
ELSE @SelList + ''',''' + ' + ' + ' ISNULL(CONVERT(VARCHAR(2000),['+@ColName + '],0),''NULL'')' + ' COLLATE database_default + ' END
END

IF @IsChar = 5
SET @strImageSQL = 'SELECT ' + @ColName + ' FROM ' + @strTableName
END

FETCH NEXT FROM CR_Table INTO @ColName, @IsChar
END

CLOSE CR_Table
DEALLOCATE CR_Table

SELECT @Fields = SUBSTRING(@Fields, 1,(len(@Fields)-1))
SELECT @SelList = SUBSTRING(@SelList, 1,(len(@SelList)-1))
SELECT @SelList = @SelList + ' FROM ' + @strTableName
SELECT @InsertStmt = @InsertStmt + @Fields + ')'


SET NOCOUNT ON

--now we need to create and load the temp table that will hold the data
--that we are going to generate into an insert statement

CREATE TABLE #TheData (TableData varchar(MAX))
INSERT INTO #TheData (TableData) EXEC (@SelList)

IF @strImageSQL <> ''
BEGIN
CREATE TABLE #ImageData (TableData image)
INSERT INTO #ImageData (TableData) EXEC (@strImageSQL)
END

--Cursor through the data to generate the INSERT statement / VALUES
DECLARE CR_Data CURSOR FAST_FORWARD FOR SELECT TableData FROM #TheData FOR
READ ONLY

OPEN CR_Data
FETCH NEXT FROM CR_Data INTO @tableData

IF @strImageSQL <> ''
BEGIN
DECLARE CR_ImageData CURSOR FAST_FORWARD FOR SELECT TableData FROM #ImageData FOR
READ ONLY
OPEN CR_ImageData
FETCH NEXT FROM CR_ImageData INTO @ImageData
END


WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN

IF @strImageSQL <> ''
BEGIN
PRINT @InsertStmt
PRINT 'VALUES '
PRINT '('
PRINT SUBSTRING(@tableData,1,CHARINDEX('MyImageData',@ta bleData)-2)
PRINT @ImageData
PRINT SUBSTRING(@tableData,CHARINDEX('MyImageData',@tabl eData) + 12,LEN(@tableData))
PRINT ')' + CHAR(13)
END
ELSE
PRINT @InsertStmt + ' VALUES (' + @tableData + ')' + CHAR(13)


IF @RequiredGo = 1
PRINT 'GO'

END
FETCH NEXT FROM CR_Data INTO @tableData

IF @ImageData <> ''
FETCH NEXT FROM CR_ImageData INTO @ImageData

-- IF @TextData <> ''
-- FETCH NEXT FROM CR_TextData INTO @TextData

END
CLOSE CR_Data
DEALLOCATE CR_Data

IF @ImageData <> ''
BEGIN
CLOSE CR_ImageData
DEALLOCATE CR_ImageData
END


IF @bitIdentity = 1
BEGIN
PRINT 'SET IDENTITY_INSERT [' + @strTableName + '] OFF '
END

PRINT '---- ** End of Inserts ** ----'
RETURN (0)