Check constraints on Tables within UDFs - cannot drop constraint l

This is probably obscure usage of the SQL Server feature-set, but any help
appreciated.
I attempted to include a CHECK constraint in the table-definition for the
RETURN table value of a UDF. Like this:
create function dbo.MyFunction ()
returns @.r table
( MyColumn int not null,
check (MyColumn in (1,2,3))
)
as
... ... ...
(Greatly simplified of course.)
I succeeded in having it create the constraint, as long as I (a) did not
name it, and (b) did it as a table constraint rather than inline with the
column definition. [These are also odd behaviors to me.]
However, when I later attempt to ALTER FUNCTION to apply a new version, I
get an error that it cannot alter the function because it is being reference
d
by another object, then gives the obviously system-generated name of the
CHECK constraint it created, apparently, under the hood.
It seems the only way to get rid of it now is to DROP the function (which I
do not like for other reasons, preferring "ALTER" until SQL Server gets an
Oracle-esque "create or replace" syntax going).
But outside of that, there seems to be no way to get rid of it. I can't
alter-function-drop-constraint, like one could with a table. And I can't jus
t
drop the constraint by itself.
Thoughts? Suggestions? Future feature request maybe?
It would be nice if table-valued functions were more closely aligned with
tables in functionality.
Eric M. Wilson
www.datazulu.comHi
Your finding seem to be correct! It does seem to be an obscure requirement
and I can not think of a reason why you would want to do this. The most
obvious way to get around it is to work with a table variable within the
function that has the constraint and remove it from the function.
If you have any requests for additional/changed functionality you can email
them too SQLWish@.microsoft.com
John
"Eric Wilson" wrote:

> This is probably obscure usage of the SQL Server feature-set, but any help
> appreciated.
> I attempted to include a CHECK constraint in the table-definition for the
> RETURN table value of a UDF. Like this:
> create function dbo.MyFunction ()
> returns @.r table
> ( MyColumn int not null,
> check (MyColumn in (1,2,3))
> )
> as
> ... ... ...
> (Greatly simplified of course.)
> I succeeded in having it create the constraint, as long as I (a) did not
> name it, and (b) did it as a table constraint rather than inline with the
> column definition. [These are also odd behaviors to me.]
> However, when I later attempt to ALTER FUNCTION to apply a new version, I
> get an error that it cannot alter the function because it is being referen
ced
> by another object, then gives the obviously system-generated name of the
> CHECK constraint it created, apparently, under the hood.
> It seems the only way to get rid of it now is to DROP the function (which
I
> do not like for other reasons, preferring "ALTER" until SQL Server gets an
> Oracle-esque "create or replace" syntax going).
> But outside of that, there seems to be no way to get rid of it. I can't
> alter-function-drop-constraint, like one could with a table. And I can't j
ust
> drop the constraint by itself.
> Thoughts? Suggestions? Future feature request maybe?
> It would be nice if table-valued functions were more closely aligned with
> tables in functionality.
> --
> Eric M. Wilson
> www.datazulu.com

charts

I've created a stacked bar chart using reporting services. I'm
building the chart from 20 records that include amounts, years, ect.
The problem is whenever there are two amounts that are the same the
chart only shows one of the amounts. I tried to find out if there was
a property that was hiding duplicates with no luck.
thanksYou need to create a group for the chart that will identify the unique
records.
Andy Potter

Charindex to not include the delimeter question

Hi, I have this:
select left (mycategory, CHARINDEX( ':', mycategory) - 1) as Cat
from allCats
If I have a column that has no ':' colon in it I get an error about
invalid string. As long as there actually is a delimeter then all is
well. But there isn't always a delimeter in the column value. How can
I make it not give me the error when the column does not have the
delimeter?
Thank you for any help.One option might be to do the following:
SELECT Cat = CASE WHEN myCategory LIKE '%:%' THEN left (mycategory,
CHARINDEX( ':', mycategory) - 1)
ELSE myCategory END
FROM allCats
HTH
Stu|||> How can
> I make it not give me the error when the column does not have the
> delimeter?
Try:
SELECT
CASE CHARINDEX( ':', mycategory)
WHEN 0 THEN mycategory
ELSE LEFT(mycategory, CHARINDEX( ':', mycategory) - 1) END AS Cat
FROM allCats
Hope this helps.
Dan Guzman
SQL Server MVP
<needin4mation@.gmail.com> wrote in message
news:1146795826.199177.38170@.v46g2000cwv.googlegroups.com...
> Hi, I have this:
> select left (mycategory, CHARINDEX( ':', mycategory) - 1) as Cat
> from allCats
> If I have a column that has no ':' colon in it I get an error about
> invalid string. As long as there actually is a delimeter then all is
> well. But there isn't always a delimeter in the column value. How can
> I make it not give me the error when the column does not have the
> delimeter?
> Thank you for any help.
>|||Another alternative is
select
left(mycategory, charindex(':', mycategory+':') - 1) as Cat
from allCats
Steve Kass
Drew University
needin4mation@.gmail.com wrote:

>Hi, I have this:
>select left (mycategory, CHARINDEX( ':', mycategory) - 1) as Cat
>from allCats
>If I have a column that has no ':' colon in it I get an error about
>invalid string. As long as there actually is a delimeter then all is
>well. But there isn't always a delimeter in the column value. How can
>I make it not give me the error when the column does not have the
>delimeter?
>Thank you for any help.
>
>|||That's very nice; subtle, though.
Stu|||Anyone have any ideas on how to traverse the entire string instead of
just first occurrence?
Thanks for all the answers.
Stu wrote:
> That's very nice; subtle, though.
> Stu|||Using a numbers table as in http://www.aspfaq.com/show.asp?id=2516
you can do this
select substring(mycategory,
Number,
charindex(':',
mycategory + ':',
Number) - Number) as Cat
from allCats
inner join Numbers on Number between 1 and len(mycategory) + 1
and substring(':' + mycategory, Number, 1) = ':'|||Aside from the table-of-numbers method, you'll need to resort to procedural
looping. You can encapsulate the code in a user-defined function to
facilitate reuse. In SQL 2005, you also have CLR languages available which
can do string parsing and manipulation more efficiently than Transact-SQL.
Hope this helps.
Dan Guzman
SQL Server MVP
<needin4mation@.gmail.com> wrote in message
news:1146868473.352163.33280@.j73g2000cwa.googlegroups.com...
> Anyone have any ideas on how to traverse the entire string instead of
> just first occurrence?
> Thanks for all the answers.
> Stu wrote:
>