Home     |     .Net Programming    |     cSharp Home    |     Sql Server Home    |     Javascript / Client Side Development     |     Ajax Programming

Ruby on Rails Development     |     Perl Programming     |     C Programming Language     |     C++ Programming     |     IT Jobs

Python Programming Language     |     Laptop Suggestions?    |     TCL Scripting     |     Fortran Programming     |     Scheme Programming Language


 
 
Cervo Technologies
The Right Source to Outsource

MS Dynamics CRM 3.0

Sql Server Programming

Computed column in a table with formula referencing a UDF


I want to change a UDF that is referenced by a "Computed Column" in a table,
but get an error that prevents me from doing that.
To bypass the problem I do the following time-consuming steps:

1.: delete the formula from the column definition
2.  update the UDF
3.  retype the calling to the UDF in the formula property of the column.

Is there a shorter and direct  way to update the UDF without the above
process?
--
Thank You
Gadi

On May 28, 6:22 pm, MAGICIAN1967

<MAGICIAN1@discussions.microsoft.com> wrote:
> I want to change a UDF that is referenced by a "Computed Column" in a table,
> but get an error that prevents me from doing that.
> To bypass the problem I do the following time-consuming steps:

> 1.: delete the formula from the column definition
> 2.  update the UDF
> 3.  retype the calling to the UDF in the formula property of the column.

> Is there a shorter and direct  way to update the UDF without the above
> process?
> --
> Thank You
> Gadi

Since your UDF is schemabound ,  I don't think you can do in any other
way

-----------------------------------------------Reply-----------------------------------------------
Hello, Gadi

Considering the following function and a computed column that uses it:

USE tempdb
GO
CREATE FUNCTION F(@x int)
RETURNS int WITH SCHEMABINDING
AS BEGIN
        RETURN @x+1
END
GO
CREATE TABLE T (
        a int PRIMARY KEY,
        b as dbo.f(a)
)

You can automate the process of dropping/recreating the dependent
columns using the following script:

BEGIN TRAN

SELECT o.name as TableName, c.name as ColumnName, x.text as Definition
INTO #ComputedColumns FROM sysobjects o
INNER JOIN syscolumns c ON c.id=o.id
INNER JOIN syscomments x ON x.id=c.id AND x.number=c.colid
WHERE o.xtype='U'

DECLARE ComputedColumns CURSOR LOCAL FOR
SELECT * FROM #ComputedColumns

DECLARE @TableName sysname, @ColumnName sysname, @Definition
nvarchar(3000), @SQL nvarchar(4000)

OPEN ComputedColumns
WHILE 1=1 BEGIN
        FETCH NEXT FROM ComputedColumns INTO @TableName, @ColumnName,
@Definition
        IF @@FETCH_STATUS<>0 BREAK

        SET @SQL='ALTER TABLE '+QUOTENAME(@TableName)
                +' DROP COLUMN '+QUOTENAME(@ColumnName)
        EXEC (@SQL)
        IF @@ERROR<>0 BEGIN ROLLBACK RETURN END
END
CLOSE ComputedColumns

EXEC('
ALTER FUNCTION F(@x int)
RETURNS int WITH SCHEMABINDING
AS BEGIN
        RETURN @x+2
END
')

OPEN ComputedColumns
WHILE 1=1 BEGIN
        FETCH NEXT FROM ComputedColumns INTO @TableName, @ColumnName,
@Definition
        IF @@FETCH_STATUS<>0 BREAK

        SET @SQL='ALTER TABLE '+QUOTENAME(@TableName)
                +' ADD '+QUOTENAME(@ColumnName)+' AS '+@Definition
        EXEC (@SQL)
        IF @@ERROR<>0 BEGIN ROLLBACK RETURN END
END
CLOSE ComputedColumns
DEALLOCATE ComputedColumns

DROP TABLE #ComputedColumns
COMMIT

However, the above script may fail if there are other objects defined
with schema binding that reference the affected computed columns.

For example, if we have the following view:

CREATE VIEW dbo.V
WITH SCHEMABINDING
AS
SELECT b FROM dbo.T

then we also need to drop the view before dropping the column, and
recreate it after re-adding the computed column.

Razvan

Add to del.icio.us | Digg this | Stumble it | Powered by Megasolutions Inc