Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_script_insertforcftresolution

  No additional text.


Syntax
create procedure sys.sp_script_insertforcftresolution
(
	@objid int				-- object id
	,@artid int				-- article id
	,@identity_insert bit		-- enable identity insert
	,@prefix nvarchar(10)=N'@c' -- prefix
	,@suffix nvarchar(10)=NULL  -- suffix
)
as
begin
	declare @cmd nvarchar(4000)
			,@qualname nvarchar(517)
			,@column_string nvarchar(4000)
			,@var_string nvarchar(4000)
			,@colname      sysname
			,@ccoltype     sysname
			,@this_col     int
			,@rc           int
			,@num_col	  int
	declare @worktab  table( c1 int identity NOT NULL, procedure_text nvarchar(4000) collate database_default null)
	declare @worktab2 table( c1 int identity NOT NULL, procedure_text nvarchar(4000) collate database_default null)

	
	-- initialize
	
	exec sp_MSget_qualified_name @objid, @qualname OUTPUT
	
	-- prepare the assignments and column list for
	-- insert statement
	
	select @num_col = 0
	DECLARE hCColid CURSOR LOCAL FAST_FORWARD FOR
	select column_id from sys.columns where object_id = @objid order by column_id asc

	OPEN hCColid
	FETCH hCColid INTO @this_col
	WHILE (@@fetch_status != -1)
	begin
		exec @rc = sp_MSget_colinfo @objid, @this_col, @artid, 0, @colname output, @ccoltype output
		if @rc = 0  and EXISTS (select name from sys.columns where object_id=@objid and column_id=@this_col and is_computed<>1)
		begin
			if rtrim(@ccoltype) not like N'timestamp'
			begin
				select @num_col = @num_col + 1
					,@column_string = quotename(@colname)
					,@var_string = @prefix + cast(@this_col as nvarchar(4))
				if (@suffix is not null)
					select @var_string = @var_string + @suffix
				
				if (@num_col > 1)
				begin
					select @column_string = N', ' + @column_string
						,@var_string = N', ' +@var_string
				end
				insert into @worktab(procedure_text) values( @column_string )				
				insert into @worktab2(procedure_text) values( @var_string )				
			end
		end
		FETCH hCColid INTO @this_col
	end
	CLOSE hCColid
	DEALLOCATE hCColid

	if (@num_col > 0)
	begin
		
		-- script the explicit identity insert setting
		
		if (@identity_insert = 1)
		begin
			select @cmd = N'
			set identity_insert ' + @qualname + N' on '
			insert into #proctext(procedure_text) values( @cmd )
		end
		
		-- script the insert
		
		select @cmd = N'
			insert into ' + @qualname + N'( '
		insert into #proctext(procedure_text) values( @cmd )
		insert into #proctext(procedure_text)
			select procedure_text from @worktab order by c1 asc
		select @cmd = N' )
			values ( '
		insert into #proctext(procedure_text) values( @cmd )
		insert into #proctext(procedure_text)
			select procedure_text from @worktab2 order by c1 asc
		select @cmd = N' )'
		insert into #proctext(procedure_text) values( @cmd )
		
		-- save the indentity insert error status
		
		if (@identity_insert = 1)
		begin
			select @cmd = N'
			select @iderror = @@error '
			insert into #proctext(procedure_text) values( @cmd )
		end
		
		-- script the explicit identity insert resetting
		
		if (@identity_insert = 1)
		begin
			select @cmd = N'
			set identity_insert ' + @qualname + N' off '
			insert into #proctext(procedure_text) values( @cmd )
		end
	end
	
	-- all done
	
	return 0
end

 
Last revision 2008RTM
See also

  sp_MSdrop_repltran (Procedure)
sp_MSscript_insert_subwins (Procedure)
sp_MSscript_update_subwins (Procedure)
       



News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash