August 17, 2010 at 10:31 am
CRATE procedure [dbo].[Auto_Create_Procedure]
@table_name varchar(200)
AS
DECLARE @column_name varchar(30)
DECLARE @column_length int
--DECLARE @table_name varchar(30)
DECLARE @column_string varchar(4000)
DECLARE @value_string varchar(4000)
DECLARE @Row_Count int
DECLARE @data_type varchar(30)
set @Row_Count = 0
set @column_string = ''
set @value_string = ''
--set @table_name ='tblBook'
DECLARE column_cursor CURSOR FOR
select b.name column_name, b.length, c.name data_type
from sysobjects a, syscolumns b, systypes c
where a.id = b.id and b.xtype = c.xtype and a.name = @table_name and c.name <> 'sysname'
order by colorder
-- Print PROCEDURE command
PRINT 'CREATE PROCEDURE Prc_Insert_' + @table_name + '('
OPEN column_cursor
FETCH NEXT FROM column_cursor
INTO @column_name, @column_length, @data_type
WHILE @@FETCH_STATUS = 0
BEGIN
set @Row_Count = @Row_Count + 1
if(@ROW_COUNT <> @@CURSOR_ROWS)
-- IN TUNG THAM SO RA MAN HINH
PRINT ' @' + @column_name + ' ' + dbo.fnc_get_datatype(@data_type, @column_length) + ', '
else
PRINT ' @' + @column_name + ' ' + dbo.fnc_get_datatype(@data_type, @column_length)
--
set @column_string = @column_string + @column_name + ', '
--
set @value_string = @value_string + '@' + @column_name + ', '
FETCH NEXT FROM column_cursor
INTO @column_name, @column_length, @data_type
END
CLOSE column_cursor;
DEALLOCATE column_cursor;
PRINT ')'
PRINT 'AS'
PRINT 'BEGIN'
-- Print Insert command
print ' INSERT INTO ' + @table_name
print ' (' + SUBSTRING(@column_string, 1, LEN(@column_string) - 1 ) + ') '
print ' VALUES'
print ' (' + SUBSTRING(@value_string, 1, LEN(@value_string) - 1 ) + ')'
PRINT 'END'
PRINT 'GO'
Visit this site to reference SQL command
August 25, 2010 at 7:10 am
Not sure what you question is or if you are just providing a script. The script doesn't work. You need to change 'CRATE' to 'CREATE'. Also, dbo.fnc_get_datatype does not exist
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 15, 2012 at 1:30 am
Been around for a long time and well used by a lot of people.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply