Altering functions and CHECK constraints

Let's say I create a multi-statement function like this:

CREATE FUNCTION dbo.Test ()
RETURNS @.res TABLE (N int NOT NULL CHECK (N >= 0))
AS
BEGIN

INSERT INTO @.res
SELECT 1

RETURN
END

That works fine. Then I make a change in the function's body, replace the
CREATE FUNCTION with ALTER FUNCTION, and execute the batch. I get an error:

Server: Msg 3729, Level 16, State 3, Procedure Test, Line 9
Cannot ALTER 'dbo.Test' because it is being referenced by object
'CK__Test__N__5D2E32EB'.

Indeed, if I look at the list of dependencies for the function in QA's
object tree, I can see the check constraint referenced in the error
message.

ALTER FUNCTION works fine if I don't specify the CHECK constraint in the
definition of the @.res table.

So it seems that the only way to modify such a function is to drop and
recreate. Is that a known behavior? Is there any particular reason for it?

Thanks.

--
(remove a 9 to reply by email)Dimitri Furman (dfurman@.cloud99.net) writes:
> ALTER FUNCTION works fine if I don't specify the CHECK constraint in the
> definition of the @.res table.
> So it seems that the only way to modify such a function is to drop and
> recreate. Is that a known behavior? Is there any particular reason for it?

I will have to admit that I was not aware of this. As for why, my guess
is that this is an artefact of the metadata structure in SQL Server, and
the SQL Server developers did not write the necessary code to avoid this.

Anyway, the restriction is not there in SQL 2005, so whatever the reason
for this in SQL 2000, it is not likely to be a compelling one.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

ALTER SP if exists in all databases

Hi,
I am trying to alter an SP in all my dB's. I am trying to loop through all
the databases ,however my script always returns false when it checks if the
SP exists (even though the SP exists)...for some reason it appears to be
still in master db even though I change db inside the cursor.
use master
declare @.AlterText = "ALTER PROCEDURE ..." --SP
declare @.dbName
declare c1 cursor for
select [name] from sysdatabases
open c1
fetch c1 into @.dbname
while(@.@.fetch_status = 0)
begin
exec('use ' + @.dbname)
if Exists(select name from sysobjects where name = 'sp_mysp' )
begin
print @.dbname
--exec (@.AlterText)
end
else
begin
print 'SP does not exist in '+@.dbname
end
fetch next from c1 into @.dbname
end
close c1
deallocate c1the scope of exec is only till the execution of that command.
"Mike" wrote:

> Hi,
> I am trying to alter an SP in all my dB's. I am trying to loop through all
> the databases ,however my script always returns false when it checks if th
e
> SP exists (even though the SP exists)...for some reason it appears to be
> still in master db even though I change db inside the cursor.
> use master
> declare @.AlterText = "ALTER PROCEDURE ..." --SP
> declare @.dbName
> declare c1 cursor for
> select [name] from sysdatabases
> open c1
> fetch c1 into @.dbname
> while(@.@.fetch_status = 0)
> begin
> exec('use ' + @.dbname)
> if Exists(select name from sysobjects where name = 'sp_mysp' )
> begin
> print @.dbname
> --exec (@.AlterText)
> end
> else
> begin
> print 'SP does not exist in '+@.dbname
> end
> fetch next from c1 into @.dbname
> end
> close c1
> deallocate c1|||Mike,
You only change the db for the exec() statement.
The current database after the exec() statement
is still master, since the USE result doesn't affect
the current database of the exec statement's caller.
A good way to do this kind of thing is to generate
all the statements you need to run by a single
query, then inspect the results and copy them by
hand and re-run them as a batch. For example,
here, you would run the output of
select
replace(replace('
use $db
goo
alter proc abc (
@.a int
) as
...
goo
','$db',quotename(name)),'goo','go')
from sysdatabases
The quotename() function protects against SQL injection
attacks from maliciously-named databases intended to cause
damage when scripts like yours are run.
You could select these strings with a cursor and execute them
also.
Be warned, however, that if your procedure does have the
name sp_something, you may run into surprises, because there
are some special name resolution rules for procedures whose
names begin with sp_. That prefix should not be used for
user-defined stored procedures.
Steve Kass
Drew Univeristy
Mike wrote:

>Hi,
>I am trying to alter an SP in all my dB's. I am trying to loop through all
>the databases ,however my script always returns false when it checks if the
>SP exists (even though the SP exists)...for some reason it appears to be
>still in master db even though I change db inside the cursor.
>use master
>declare @.AlterText = "ALTER PROCEDURE ..." --SP
>declare @.dbName
>declare c1 cursor for
>select [name] from sysdatabases
>open c1
>fetch c1 into @.dbname
>while(@.@.fetch_status = 0)
>begin
>exec('use ' + @.dbname)
>if Exists(select name from sysobjects where name = 'sp_mysp' )
> begin
> print @.dbname
> --exec (@.AlterText)
> end
>else
> begin
> print 'SP does not exist in '+@.dbname
> end
>fetch next from c1 into @.dbname
>end
>close c1
>deallocate c1
>|||By the way. Functionality you try to achieve with the script can be done by
using the following.
sp_msforeachdb 'use ? if Exists(select name from sysobjects where name =
''sp_mysp'' ) begin print ''?'' end'
Hope this helps.
--
"Omnibuzz" wrote:
> the scope of exec is only till the execution of that command.
> --
>
>
> "Mike" wrote:
>

Alignment result

USE PUBS
GO
CREATE FUNCTION [dbo].[GetSpace] ()
RETURNS int AS
BEGIN
RETURN (SELECT max(len(fname)) + 1 FROM employee)
END
GO
SELECT top 5 fname + SPACE([dbo].GetSpace() - LEN(fname)) + lname AS
Expr1, [dbo].GetSpace() AS Expr2, LEN(fname) AS Expr3
FROM dbo.employee
When I execute the query over the query analyzer it gave me the correct
result like that...
Expr1
Aria Cruz
Annette Roulet
Ann Devon
Anabela Domingues
Carlos Hernadez
When I execute the query over the Enterprise Manger > View > Create New View
>
SELECT top 5 fname + SPACE([dbo].GetSpace() - LEN(fname)) + lname AS
Expr1, [dbo].GetSpace() AS Expr2, LEN(fname) AS Expr3
FROM dbo.employee
It gave the result on view pannel like that...
Expr1
Aria Cruz
Annette Roulet
Ann Devon
Anabela Domingues
Carlos Hernadez
I mean no alignment in the view pannel and when we call the same view over
the front end so it will gave the same unalign result.
Thanks
GetSpace user function returns the appropriate number of spaces but in
Enterprise Manager the problem is the font used.
Copy the result from Enterprise Manager in a word editor (like Microsoft
Word) and set the font to Courier New and Size 10. You will see that the
results will be aligned. The default font for results in Query Analyzer is
Courier New and Size 10.
Cristian Lefter, SQL Server MVP
"Joh" <joh@.mailcity.com> wrote in message
news:%235HNgnSYFHA.228@.TK2MSFTNGP12.phx.gbl...
> USE PUBS
> GO
> CREATE FUNCTION [dbo].[GetSpace] ()
> RETURNS int AS
> BEGIN
> RETURN (SELECT max(len(fname)) + 1 FROM employee)
> END
> GO
> SELECT top 5 fname + SPACE([dbo].GetSpace() - LEN(fname)) + lname AS
> Expr1, [dbo].GetSpace() AS Expr2, LEN(fname) AS Expr3
> FROM dbo.employee
> When I execute the query over the query analyzer it gave me the correct
> result like that...
> Expr1
> Aria Cruz
> Annette Roulet
> Ann Devon
> Anabela Domingues
> Carlos Hernadez
> When I execute the query over the Enterprise Manger > View > Create New
> View
> SELECT top 5 fname + SPACE([dbo].GetSpace() - LEN(fname)) + lname AS
> Expr1, [dbo].GetSpace() AS Expr2, LEN(fname) AS Expr3
> FROM dbo.employee
> It gave the result on view pannel like that...
> Expr1
> Aria Cruz
> Annette Roulet
> Ann Devon
> Anabela Domingues
> Carlos Hernadez
> I mean no alignment in the view pannel and when we call the same view over
> the front end so it will gave the same unalign result.
> Thanks
>
|||You are right. Thanks
"Cristian Lefter" <nospam_CristianLefter@.hotmail.com> wrote in message
news:ONgxYpcYFHA.2768@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> GetSpace user function returns the appropriate number of spaces but in
> Enterprise Manager the problem is the font used.
> Copy the result from Enterprise Manager in a word editor (like Microsoft
> Word) and set the font to Courier New and Size 10. You will see that the
> results will be aligned. The default font for results in Query Analyzer is
> Courier New and Size 10.
> Cristian Lefter, SQL Server MVP
> "Joh" <joh@.mailcity.com> wrote in message
> news:%235HNgnSYFHA.228@.TK2MSFTNGP12.phx.gbl...
over
>