Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_bindrule

  No additional text.


Syntax
create procedure sys.sp_bindrule	-- 1996/08/14 15:02
	@rulename nvarchar(776),		-- name of the rule
	@objname nvarchar(776),			-- table or usertype name
	@futureonly varchar(15) = NULL	-- column name
as
	declare @ruleid int					-- id of the rule to bind
	declare @futurevalue varchar(15)	-- the value of @futureonly that causes
										--  the binding to be limited
	declare
		@vc1			nvarchar(517)
		,@tab_id		int
		,@col_id		int
		,@colname		sysname
		,@oldrule		int				-- current rule for type
		,@xtype			smallint
		,@xtypelen		int
		,@xusertype		int
		,@schid			int

	declare
		@UnqualRule		sysname
		,@QualRule1		sysname
		,@QualRule2		sysname
		,@QualRule3		sysname

		,@UnqualObj		sysname
		,@QualObj1		sysname
		,@QualObj2		sysname
		,@QualObj3		sysname

	set cursor_close_on_commit	off
	set nocount on

	select @futurevalue = 'futureonly'	-- initialize @futurevalue

	-- When a default or rule is bound to a user-defined datatype, it is also
	--  bound, by default, to any columns of the user datatype that are currently
	--  using the existing default or rule as their default or rule.  This default
	--  action may be overridden by setting @futureonly = @futurevalue when the
	--  procedure is invoked.  In this case existing columns with the user
	--  datatype won't have their existing default or rule changed.

	-- get name parts 
	select @UnqualRule = parsename(@rulename, 1),
			@QualRule1 = parsename(@rulename, 2),
			@QualRule2 = parsename(@rulename, 3),
			@QualRule3 = parsename(@rulename, 4)

	select @UnqualObj = parsename(@objname, 1),
			@QualObj1 = parsename(@objname, 2),
			@QualObj2 = parsename(@objname, 3),
			@QualObj3 = parsename(@objname, 4)

	if (@UnqualRule is null OR @QualRule3 is not null)
	begin
		raiserror(15253,-1,-1,@rulename)
		return (1)
	end

	if (@UnqualObj is null OR @QualObj3 is not null)
	begin
		raiserror(15253,-1,-1,@objname)
		return (1)
	end

	------------------  Verify database.
	if ((@QualObj2 is not null and @QualObj1 is null)
		or (@QualRule2 is not null and @QualRule2 <> db_name()))
	begin
		raiserror(15077,-1,-1)
		return (1)
	end

	--  Check that the @futureonly argument, if supplied, is correct.
	if (@futureonly is not null)
	begin
		select @futureonly = lower(@futureonly)
		begin
			if (@futureonly <> @futurevalue)
			begin
				raiserror(15106,-1,-1)
				return (1)
			end
		end
	end

	BEGIN TRANSACTION

	--  Check to see that the rule exists and get its id.
	select @ruleid = object_id from sys.objects
			where object_id = object_id(@rulename,'local')
				and type='R ' --rule object 7

	-- Share Lock rule so that it cannot be dropped
	if not (@ruleid is null)
	begin
		EXEC %%Object(MultiName = @rulename).LockMatchID(ID = @ruleid, Exclusive = 0, BindInternal = 0)
		if @@error <> 0
			select @ruleid = null
	end

	if @ruleid is null
	begin
		raiserror(15017,-1,-1,@rulename)
		goto error_abort_exit
	end

	-- Try to resolve column first. We need to extract
	--  and verify the table and column names and make sure the user owns
	--  the table that is getting the rule bound.
	if @QualObj1 is not null
	begin
		if (@QualObj2 is not null)
			select @vc1 = QuoteName(@QualObj2) + '.' + QuoteName(@QualObj1)
		else
			select @vc1 = QuoteName(@QualObj1)

		-- Check that table and column exist
		select @tab_id = o.object_id
			from sys.tables o join sys.columns c
				on o.object_id = c.object_id
			where o.object_id = object_id(@vc1,'local')
				and c.name = @UnqualObj
 		if @tab_id is not null
		begin
			declare @is_sparse int
			declare @is_column_set int

			EXEC %%Object(MultiName = @vc1).LockMatchID(ID = @tab_id, Exclusive = 1, BindInternal = 0)

			-- Check again that table and column exist
			if @@error = 0
				select @xtype = system_type_id, @xtypelen = max_length,
					@is_sparse = is_sparse,
					@is_column_set = is_column_set
					from sys.columns
					where object_id = @tab_id
						and name = @UnqualObj
			if @xtype is null
			begin
				raiserror(15148,-1,-1, @objname)
				goto error_abort_exit
			end
			

			-- If the column type is image, text, or timestamp, disallow the bind.
			--  Rules can't be bound to image, text, or timestamp columns.
			--  The types are checked in case
			--  there is a user-defined datatype that is an image or text.
			--  User-defined datatypes mapping to timestamp are not allowed
			--  by sp_addtype.
			-- If the column is computed, disallow the bind.
			if ( type_name(@xtype) in ('text', 'ntext', 'image', 'timestamp', 'xml')
				or (type_name(@xtype) in ('varchar', 'nvarchar', 'varbinary') and
					 @xtypelen = -1)
				or (ColumnProperty(object_id(@vc1,'local'), @UnqualObj, 'IsComputed') = 1)
				or @is_sparse = 1
				or @is_column_set = 1
			    or @xtype = 240 ) -- CLR UDT
			begin
				raiserror(15107,-1,-1)
				goto error_abort_exit
			end

			EXEC %%ColumnEx(ObjectID = @tab_id, Name = @UnqualObj).SetRule(ID = @ruleid)

			-- EMDEventType(x_eet_Bind_Rule), EMDUniversalClass(x_eunc_Table), src major id, src minor id, src name
			-- EMDUniversalClass(x_eunc_Table), target major id, 1 means target name is column, target name,
			-- # of parameters, 5 parameters
			EXEC %%System().FireTrigger(ID = 219, ID = 1, ID = @ruleid, ID = 0, Value = NULL,
				ID = 1, ID = @tab_id, ID = 1, Value = @UnqualObj, ID = 3,
				Value = @rulename, Value = @objname, Value = @futureonly, Value = NULL, Value = NULL, Value = NULL, Value = NULL)

			raiserror(15514,-1,-1)
		end
	end

	-- We're binding to a user type.  In this case, the @objname
	--  is really the name of the user datatype.
	--  When we bind to a user type, any existing columns get changed
	--  to the new binding unless their current binding is not equal
	--  to the current binding for the usertype or if they set the
	--  @futureonly parameter to @futurevalue.
	if @tab_id is null
	begin

		-- Get the current rule for the datatype.
		if @QualObj2 is null
			select @oldrule = rule_object_id, @xtype = system_type_id, @xusertype = user_type_id,
					@schid = schema_id
				from sys.types
				where user_type_id = type_id(@objname)
				and is_table_type=0
		
		-- Ex-lock and check permission
		if not (@xusertype is null)
		begin
			EXEC %%ScalarType(MultiName = @objname).LockMatchID(ID = @xusertype, Exclusive = 1)
			if (@@error <> 0)
				select @xusertype = null
		end

		if @xusertype is null
		begin
			raiserror(15148,-1,-1, @objname)
			goto error_abort_exit
		end

		-- If the column type is image, text, or timestamp, disallow the bind.
		--  Rules can't be bound to image, text columns or columns of CLR UDT types
		if ( type_name(@xtype) in ('text', 'ntext', 'image', 'timestamp', 'xml')
			 or  (type_name(@xtype) in ('varchar', 'nvarchar', 'varbinary') and
				 @xtypelen = -1)
			 or @xtype = 240 ) -- CLR UDT
		begin
			raiserror(15107,-1,-1)
			goto error_abort_exit
		end

		if exists (select * from sys.columns c where user_type_id = @xusertype and c.is_sparse = 1)
		begin
			raiserror(33079,-1,-1, @objname)
			goto error_abort_exit
		end

		EXEC %%ScalarType(ID = @xusertype).SetRule(ID = @ruleid)
		raiserror(15515,-1,-1)

		-- Now see if there are any columns with the usertype that need the new binding.
		if isnull(@futureonly, ' ') <> @futurevalue
		begin

			declare	@cur_tab_id		int
					,@bad_tab_id	int

			select	@cur_tab_id = 0		-- detect table id change for lock schema
					,@bad_tab_id = 0	-- skip bad tables (dropped, etc)

			declare ms_crs_bindrule_1 cursor local static for
			  select distinct
					 c.object_id, c.column_id
				from	 sys.columns c join sys.tables o
					on c.object_id = o.object_id
				where c.user_type_id = @xusertype
					and	((c.rule_object_id = 0)
						 or (c.rule_object_id = @oldrule))
				order by c.object_id
					  for read only

			open ms_crs_bindrule_1
			fetch next from ms_crs_bindrule_1 into
				@tab_id, @col_id	-- take care of rename

			while @@fetch_status = 0
			begin

				if @cur_tab_id <> @tab_id	-- not same table
				begin
					select @cur_tab_id = @tab_id
					select @vc1 = quotename(schema_name(OBJECTPROPERTY(@tab_id,'SchemaId'))) + '.'
								+ quotename(object_name(@tab_id))

					EXEC %%Object(MultiName = @vc1).LockMatchID(ID = @tab_id, Exclusive = 1, BindInternal = 0)
					if @@error <> 0			-- bad table, eg. removed
						select @bad_tab_id = @tab_id
				end

				--  Update syscolumns with new binding.
				if @bad_tab_id <> @tab_id	-- table schema locked
				begin
					-- Column cannot be dropped due to type shared lock
					select @colname = COL_NAME(@tab_id, @col_id)
					EXEC %%ColumnEx(ObjectID = @tab_id, Name = @colname).SetRule(ID = @ruleid)
				end

				fetch next from ms_crs_bindrule_1 into
					@tab_id, @col_id

			end

			deallocate ms_crs_bindrule_1
			raiserror(15516,-1,-1)

		end

		-- EMDEventType(x_eet_Bind_Rule), EMDUniversalClass(x_eunc_Table), src major id, src minor id, src name
		-- EMDUniversalClass(x_eunc_Type), target major id, target minor id, target name,
		-- # of parameters, 5 parameters
		EXEC %%System().FireTrigger(ID = 219, ID = 1, ID = @ruleid, ID = 0, Value = NULL,
			ID = 6, ID = @xusertype, ID = 0, Value = NULL, ID = 3,
			Value = @rulename, Value = @objname, Value = @futureonly, Value = NULL, Value = NULL, Value = NULL, Value = NULL)

	end

	-- SUCCESS 
	COMMIT TRANSACTION
	return (0)

error_abort_exit:
	COMMIT TRANSACTION
	return (1) -- sp_bindrule

 
Last revision 2008RTM
See also

  sp_helpconstraint (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