Fundamental Functions MS SQL Server 7 or 2000 users VALUE MOST

Hi all,

What are the fundamentals (fundamental functions) that most small to
medium sized organizations that use MS SQL Server 7 or 2000 value
most?
What's your insight?

OK, here's my biased definition of small to medium sized organization,
annual revenue from 20m to 300m.

Thanks.

DLdaaa@.rock.com (DaaaDaaa) wrote in message news:<2193afb.0406011507.37ee4f6d@.posting.google.com>...
> Hi all,
> What are the fundamentals (fundamental functions) that most small to
> medium sized organizations that use MS SQL Server 7 or 2000 value
> most?
> What's your insight?
> OK, here's my biased definition of small to medium sized organization,
> annual revenue from 20m to 300m.
> Thanks.
> DL

http://www.microsoft.com/sql/evalua...ies/default.asp

Apart from that, I would guess that MSSQL is like most software -
people are using it to do more or less anything and everything you can
do with it.

Simon|||> http://www.microsoft.com/sql/evalua...ies/default.asp
> Apart from that, I would guess that MSSQL is like most software -
> people are using it to do more or less anything and everything you can
> do with it.
> Simon

Thanks, Simon.

Don

Functions?

WIthin SQL Server 2005, there are functions. This feature is new to me and I haven't found anyone that has written their own fucntions? I'm wondering if functions are written the same as stored procedures, and can a function be called from a stored procedure or even from within a query.

Yes SQL functions are pretty much like your VB or any other functions. They take one/more parameters return one value. You could also return a table from a function. You can call the function from a Query or even inside a stored procedure. You can read up Books On Line for more info. There are some built in functions too.|||

Functions are not new to SQL Server 2005, they were introduced in SQL Server 2000. Try the link below for answers about most known UDF. The site is run by a UDF expert. Hope this helps.

http://www.novicksoftware.com/UDFofWeek/UDF_FAQ.htm

Functions with global variables

Hello,

I am porting a stored procedure from Oracle. It uses a variable that
remembers its previous values from each invocation. (It uses a PRAGMA
REFERENCES clause for those who are familiar with Oracle.) In other
words, the variable in a particular stored procedure acts as a global
variable. So the each invocation of the stored procedure can see its
last value, instead of its initial default value.

Is there something similar in SQLServer?There are no global variables in SQL and local variables in a stored
procedure go out of scope when the SP returns. Maybe you can put the values
you want to persist into a table?

I can think of two likely reasons for wanting to do what you have described:
an auto-incrementing ID or a user-defined aggregate function. A
auto-incrementing ID is easy: use an IDENTITY column. User-defined aggregate
functions aren't possible in SQL2000 but there are solutions for some of the
non-standard aggregates that are commonly requested (Median, Product and
String Concatenation for example).

--
David Portas
SQL Server MVP
--

Functions vs StoredProcedures

From a sp I should call many other sp or functions. There're difference
between SP and Functions about performance? Are functions more performant
then sp.
ThanksProcedures and Functions are not functionaly equivalent. For example,
functions can only perform inserts/updates/deletes on table variables that
it declares. Also, only a function can return a scalar value.
http://msdn.microsoft.com/library/d...edprocedure.asp
http://msdn.microsoft.com/library/d...br />
50mr.asp
"checcouno" <checcouno@.discussions.microsoft.com> wrote in message
news:D8456144-D913-4F39-AF15-F786C3155C18@.microsoft.com...
> From a sp I should call many other sp or functions. There're difference
> between SP and Functions about performance? Are functions more performant
> then sp.
> Thanks|||>> functions can only perform inserts/updates/deletes on table variables
Just to clarify. Functions can be table valued as well. And you can update
permanent tables in a database using a table valued function as well:
CREATE TABLE tbl ( col INT NOT NULL PRIMARY KEY );
GO
CREATE FUNCTION ufn ( @.p INT ) RETURNS TABLE AS
RETURN ( SELECT col FROM tbl WHERE col = @.p )
GO
INSERT ufn(1) SELECT 1 ; SELECT * FROM tbl ;
UPDATE ufn(1) SET col = 2 WHERE col = 1 ; SELECT * FROM tbl ;
DELETE ufn(2) WHERE col = 2 ; SELECT * FROM tbl ;
The restriction is that you cannot perform them on permanent tables from
within the function.
Anith|||FWIW: 'performant' isn't a word.
"checcouno" <checcouno@.discussions.microsoft.com> wrote in message
news:D8456144-D913-4F39-AF15-F786C3155C18@.microsoft.com...
> From a sp I should call many other sp or functions. There're difference
> between SP and Functions about performance? Are functions more performant
> then sp.
> Thanks|||On Wed, 28 Sep 2005 11:26:57 -0500, Anith Sen wrote:

>Just to clarify. Functions can be table valued as well. And you can update
>permanent tables in a database using a table valued function as well:
(snip)
Hi Anith,
After running your code, I can't deny that you _CAN_ do this. But this
is not mentioned anywhere in Books Online (in fact, BOL says that
INSERT, UPDATE, and DELETE all operate on a table, a view, or a
OPENQUERY or OPENROWSET rowset-function). I don't think that anyone
should ever rely on this behaviour!
(If I may speculate - I *think* that the reason for this behaviour is
that inline table-valued functions and views have so much in common that
they re-use lots of the same code, and someone at MS forgot to exclude
UDFs in the re-used code for INSERT, UPDATE and DELETE).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Actually it is a word, but perhaps used out of context:
http://dictionary.reference.com/search?q=performant
"Mark" <a@.b.net> wrote in message
news:ueozaHGxFHA.612@.TK2MSFTNGP10.phx.gbl...
> FWIW: 'performant' isn't a word.
>
> "checcouno" <checcouno@.discussions.microsoft.com> wrote in message
> news:D8456144-D913-4F39-AF15-F786C3155C18@.microsoft.com...
>|||I don't think that english is his mother tongue.
In French "performant" is an adjective that discribes something that has
performs well.
For the life of me I cannot think of an English word that can be used in the
same way.
My French-English translator doesn't even make a suggestion.
"JT" <someone@.microsoft.com> wrote in message
news:OQKvMtRxFHA.3756@.tk2msftngp13.phx.gbl...
> Actually it is a word, but perhaps used out of context:
> http://dictionary.reference.com/search?q=performant
> "Mark" <a@.b.net> wrote in message
> news:ueozaHGxFHA.612@.TK2MSFTNGP10.phx.gbl...
>|||>> After running your code, I can't deny that you _CAN_ do this. But this is
I tend to agree with the lack of sufficient documentation which can cause
some confusion. For instance, add a DISTINCT to the SELECT clause in the UDF
& you'll see the same limitations of updateable views.
Anith

functions performance question

I'm sure everybody agrees on the idea that SQL server 2000 functions are not well taken care of as far as performance optimization. I'm not sure if it is because they don't have excution plans? or if they have it but it's not optimized or whatever reason. The fact is they are slow!

My question for experts in that field is if this issue has been addressed in SQL 2005? or we still going to avoid the functions as much as possible?

Thanks,

Robert.

Hi Robert,

Could you please be more specific? In general it should be the other way around, see http://msdn2.microsoft.com/en-us/library/ms191007.aspx

Reasons for slowdown may be numerous and require further investigation.

Thank you,

Boris.

functions performance question

I'm sure everybody agrees on the idea that SQL server 2000 functions are not well taken care of as far as performance optimization. I'm not sure if it is because they don't have excution plans? or if they have it but it's not optimized or whatever reason. The fact is they are slow!

My question for experts in that field is if this issue has been addressed in SQL 2005? or we still going to avoid the functions as much as possible?

Thanks,

Robert.

Hi Robert,

Could you please be more specific? In general it should be the other way around, see http://msdn2.microsoft.com/en-us/library/ms191007.aspx

Reasons for slowdown may be numerous and require further investigation.

Thank you,

Boris.

Functions not documented?

if you look at Chris Hays's Blog
http://blogs.msdn.com/chrishays/archive/2004/07/23/193292.aspx

we find functions which were not documented in Reporting Services.
eg.
=Ceiling(RowNumber(Nothing)/3)

now where do i go to find more goodies like that? microsoft has given us alot of good features with reporting services. if only we know how to use them...
All of the aggregate functions are documented in the included docs (http://msdn2.microsoft.com/en-us/library/ms159269(en-US,SQL.90).aspx). Other functions are part of the .NET Framework. The new Expression Dialog has a list of commonly used functions (see also http://msdn2.microsoft.com/en-us/library/ms157328(en-US,SQL.90).aspx).

functions in view

I have a view on SQL server 2000.
Using enterprise mngr to admin it.
In my view I would like to add a column that calculates something with the
other column.
An example could be:
Column Table
a x
b x
a1 y
mycustomcolumn = (a+b)*a1 =>> how can I insert this.
It's a bit like in Ms Access, where you have the expression builder.
THX
nicholas wrote:
> I have a view on SQL server 2000.
> Using enterprise mngr to admin it.
> In my view I would like to add a column that calculates something
> with the other column.
> An example could be:
> Column Table
> a x
> b x
> a1 y
> mycustomcolumn = (a+b)*a1 =>> how can I insert this.
> It's a bit like in Ms Access, where you have the expression builder.
> THX
create table test123 (col1 int, col2 int)
create view vTest
as
Select col1, col2, col3 = col1 * col2
From test123
Don't rely on SQL EM for object maintenance. Learn to use actual DDL
scripting. SQL EM doesn't do everyting and maintaining from scripts give
you a good handle on object design.
David Gugick
Imceda Software
www.imceda.com
|||perfect !
THX a lot
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:%23Zy9QfsEFHA.3924@.TK2MSFTNGP09.phx.gbl...
> nicholas wrote:
> create table test123 (col1 int, col2 int)
> create view vTest
> as
> Select col1, col2, col3 = col1 * col2
> From test123
>
> Don't rely on SQL EM for object maintenance. Learn to use actual DDL
> scripting. SQL EM doesn't do everyting and maintaining from scripts give
> you a good handle on object design.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>

functions in view

I have a view on SQL server 2000.
Using enterprise mngr to admin it.
In my view I would like to add a column that calculates something with the
other column.
An example could be:
Column Table
a x
b x
a1 y
mycustomcolumn = (a+b)*a1 =>> how can I insert this.
It's a bit like in Ms Access, where you have the expression builder.
THXnicholas wrote:
> I have a view on SQL server 2000.
> Using enterprise mngr to admin it.
> In my view I would like to add a column that calculates something
> with the other column.
> An example could be:
> Column Table
> a x
> b x
> a1 y
> mycustomcolumn = (a+b)*a1 =>> how can I insert this.
> It's a bit like in Ms Access, where you have the expression builder.
> THX
create table test123 (col1 int, col2 int)
create view vTest
as
Select col1, col2, col3 = col1 * col2
From test123
Don't rely on SQL EM for object maintenance. Learn to use actual DDL
scripting. SQL EM doesn't do everyting and maintaining from scripts give
you a good handle on object design.
David Gugick
Imceda Software
www.imceda.com|||perfect !
THX a lot
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:%23Zy9QfsEFHA.3924@.TK2MSFTNGP09.phx.gbl...
> nicholas wrote:
> > I have a view on SQL server 2000.
> > Using enterprise mngr to admin it.
> >
> > In my view I would like to add a column that calculates something
> > with the other column.
> > An example could be:
> >
> > Column Table
> > a x
> > b x
> > a1 y
> > mycustomcolumn = (a+b)*a1 =>> how can I insert this.
> >
> > It's a bit like in Ms Access, where you have the expression builder.
> >
> > THX
> create table test123 (col1 int, col2 int)
> create view vTest
> as
> Select col1, col2, col3 = col1 * col2
> From test123
>
> Don't rely on SQL EM for object maintenance. Learn to use actual DDL
> scripting. SQL EM doesn't do everyting and maintaining from scripts give
> you a good handle on object design.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>

functions in view

I have a view on SQL server 2000.
Using enterprise mngr to admin it.
In my view I would like to add a column that calculates something with the
other column.
An example could be:
Column Table
a x
b x
a1 y
mycustomcolumn = (a+b)*a1 =>> how can I insert this.
It's a bit like in Ms Access, where you have the expression builder.
THXnicholas wrote:
> I have a view on SQL server 2000.
> Using enterprise mngr to admin it.
> In my view I would like to add a column that calculates something
> with the other column.
> An example could be:
> Column Table
> a x
> b x
> a1 y
> mycustomcolumn = (a+b)*a1 =>> how can I insert this.
> It's a bit like in Ms Access, where you have the expression builder.
> THX
create table test123 (col1 int, col2 int)
create view vTest
as
Select col1, col2, col3 = col1 * col2
From test123
Don't rely on SQL EM for object maintenance. Learn to use actual DDL
scripting. SQL EM doesn't do everyting and maintaining from scripts give
you a good handle on object design.
David Gugick
Imceda Software
www.imceda.com|||perfect !
THX a lot
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:%23Zy9QfsEFHA.3924@.TK2MSFTNGP09.phx.gbl...
> nicholas wrote:
> create table test123 (col1 int, col2 int)
> create view vTest
> as
> Select col1, col2, col3 = col1 * col2
> From test123
>
> Don't rely on SQL EM for object maintenance. Learn to use actual DDL
> scripting. SQL EM doesn't do everyting and maintaining from scripts give
> you a good handle on object design.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>

Functions in SQL Server7

Is it possible to create Functions in SqlServer 7?
I have a huge query > 500,000 rows that I want to select a subset of using a
function
Select IdentityInd, ColA, ColB
From TableA Where
UDFContains(IdentityInd, ColB ) = 1
**************************************
--Function
And UDFContains will looklike
UDFContains(@.IdentityInd, @.ColB )
Returns Bit
Begin
IF EXISTS(Select IdentityInd From TableA Where
IdentityInd = @.IdentityInd AND CONTAINS(ColA,
@.ColB)) BEGIN
Return 1
End
ELSE BEGIN
Retuen 0
End
End
****************************************
******Just in SQL Server 2000 for now.
AMB
"Sanjay Pais" wrote:

> Is it possible to create Functions in SqlServer 7?
> I have a huge query > 500,000 rows that I want to select a subset of using
a
> function
> Select IdentityInd, ColA, ColB
> From TableA Where
> UDFContains(IdentityInd, ColB ) = 1
> **************************************
> --Function
> And UDFContains will looklike
> UDFContains(@.IdentityInd, @.ColB )
> Returns Bit
> Begin
> IF EXISTS(Select IdentityInd From TableA Where
> IdentityInd = @.IdentityInd AND CONTAINS(ColA,
> @.ColB)) BEGIN
> Return 1
> End
> ELSE BEGIN
> Retuen 0
> End
> End
> ****************************************
******
>
>|||No, but you can do this in the where clause:
Select IdentityInd, ColA, ColB
From TableA
Where EXISTS( Select inExists.IdentityInd
From TableA as inExists
Where IdentityInd = tableA.IdentityInd
AND CONTAINS(inExists.ColA, tableA.ColB))
Can't you? It should be preferrable performancewise anyhow, I would expect.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Sanjay Pais" <spaisatnospammarketlinksolutions.com> wrote in message
news:eeK6c$UBFHA.3924@.TK2MSFTNGP10.phx.gbl...
> Is it possible to create Functions in SqlServer 7?
> I have a huge query > 500,000 rows that I want to select a subset of using
> a function
> Select IdentityInd, ColA, ColB
> From TableA Where
> UDFContains(IdentityInd, ColB ) = 1
> **************************************
> --Function
> And UDFContains will looklike
> UDFContains(@.IdentityInd, @.ColB )
> Returns Bit
> Begin
> IF EXISTS(Select IdentityInd From TableA Where
> IdentityInd = @.IdentityInd AND CONTAINS(ColA,
> @.ColB)) BEGIN
> Return 1
> End
> ELSE BEGIN
> Retuen 0
> End
> End
> ****************************************
******
>|||You can't use two columns in a contains clause which caused my dilema in the
first place :)
Sanjay
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:ul5SFhWBFHA.4004@.tk2msftngp13.phx.gbl...
> No, but you can do this in the where clause:
> Select IdentityInd, ColA, ColB
> From TableA
> Where EXISTS( Select inExists.IdentityInd
> From TableA as inExists
> Where IdentityInd = tableA.IdentityInd
> AND CONTAINS(inExists.ColA, tableA.ColB))
> Can't you? It should be preferrable performancewise anyhow, I would
> expect.
> --
> ----
--
> Louis Davidson - drsql@.hotmail.com
> SQL Server MVP
> Compass Technology Management - www.compass.net
> Pro SQL Server 2000 Database Design -
> http://www.apress.com/book/bookDisplay.html?bID=266
> Note: Please reply to the newsgroups only unless you are interested in
> consulting services. All other replies may be ignored :)
> "Sanjay Pais" <spaisatnospammarketlinksolutions.com> wrote in message
> news:eeK6c$UBFHA.3924@.TK2MSFTNGP10.phx.gbl...
>|||Ah, sorry :)
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Sanjay Pais" <spaisatnospammarketlinksolutions.com> wrote in message
news:uI%23xQvWBFHA.3700@.tk2msftngp13.phx.gbl...
> You can't use two columns in a contains clause which caused my dilema in
> the first place :)
> Sanjay
> "Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
> news:ul5SFhWBFHA.4004@.tk2msftngp13.phx.gbl...
>

Functions in Parameter Definitions

I have a pair of reports that are the same except that one takes date parameters and another runs for the calculated lst full month. Currently they run off of separate stored procedures, but I would like to combine them. I don't know if my reporting tool will handle it or not, but I want to test it, so I need to combine the 2 sp's into one. I thought the first thing to try would be to set the default values to the start and end date of the previous month, like so:

CREATE PROCEDURE [dbo].[usp_blahblah]

(

@.StartDate SmallDateTime = DateAdd(mm,-1,DateAdd(mm,DateDiff(mm,0,GetDate()),0)), -- 1st of Last Month

@.EndDate SmallDateTime = DateAdd(ms,-3,DateAdd(mm, DateDiff(mm,0,GetDate()),0)) -- End of Last Month

)

..but it won't parse ("Incorrect syntax near '('."). So I'm thinking that you can't use a function in the definition of a param, although I can't find any documentation.

I'm sure there are other approaches, but I thought this would be the most straight forward... Does anybody have a really elegant idea?

From the CREATE PROCEDURE documentation - "Is a default value for the parameter. If a default is defined, the procedure can be executed without specifying a value for that parameter. The default must be a constant or it can be NULL."

Just make the default value NULL, then test to see if the parameters are NULL and set them in the body of the proc. Like this

CREATE PROCEDURE [dbo].[usp_blahblah]
(
@.StartDate SmallDateTime = NULL,
@.EndDate SmallDateTime = NULL)
IF @.StartDate IS NULL AND @.EndDate IS NULL
BEGIN
SET @.StartDate = DateAdd(mm,-1,DateAdd(mm,DateDiff(mm,0,GetDate()),0)), -- 1st of Last Month
SET @.EndDate = DateAdd(ms,-3,DateAdd(mm, DateDiff(mm,0,GetDate()),0)) -- End of Last Month
END

|||That's slick. I'll give it a try and hope Crystal can handle it!|||

Be careful using the above approach though if you are using the variables further in a query. The query optimizer can do parameter sniffing to get optimal plan based on the parameter values but if you modify it within the SP then the sniffing cannot happen. So if you need to modify the parameter values then move the actual query into another SP and pass the modified parameters to that instead. See the link below for more details on how plan caching works.

http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx

Functions in Functions

Hi,

I have to calculate data in function with "EXEC". During runtime I get the Error:

"Only functions and extended stored procedures can be executed from within a function."

I would use a Stored Procedure, but the function is to be called from a view. I don't understand, why that should not be possible. Is there any way to shut that message down or to work around?

btw: Storing all the data in a table, would mean a lot of work, I rather not like to do. ;-)

Thx for any help

Blubb10

Wih in the function,

- You can't use dynamic SQL

- You can't call any stored proc

These are the limitation of the Function.

Post your soruce code.

|||Here is a thread describe the issue similar to yours.

F.Y.I.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=457236&SiteID=1

Thanks,

Zuomin
|||Without knowing OP's logic we can't simply declare it is by design.. Let him post the logic used inside the function.. We will wait.. |||

DECLARE @.decBOM_Count INT

DECLARE @.strBSC_Formula NVARCHAR(200)

-- For demo, is a parameter of the function

SELECT @.strBSC_Formula = 'BR-(MW-25)-8'

--the next steps are, replacing all the variables in the formula by actual number-values

.

.

.

-- Here @.strBSC_Formula contains something like '1000-(70-25)-8'

SELECT @.strBSC_Formula = 'SET @.decBOM_Count =' + @.strBSC_Formula

EXEC sp_executesql @.strBSC_Formula, N'@.decBOM_Count DECIMAL(10, 4) OUTPUT', @.decBOM_Count OUTPUT

-- In @.decBOM_Count I expect a number as result of the formula.

|||

Ok..

Here you can't achive it from the function directly.

If you use SQL Server 2000,

You have to use extended stored procedure

- a com program which will evaluate the given fromula and return back the value

- need to register that com dll on the db server

If you use SQL Server 2005,

You have to use the CLR function

- a simple C#/VB.NET code which will evulate the expression.

Let me know the version of SQL Server.. I will try to help you on this.

|||

It is SQL Server 2005 Standard and the program is written in Access 2003 / VBA.

functions in dll?

Hi All,
I have same functions writed in C++ on a DLL.
My questions is: Have same way of calling these functions of an store
procedure or trigger?
ThanksYou could write an extended stored procedure, see "Creating Extended
Stored Procedures" in Books Online. I wouldn't recommend using that in
a trigger however. TSQL or client/middle tier code may be the best for
your functions. Have you considered implementing them in TSQL?
David Portas
SQL Server MVP
--

functions in check constraint

Hi there,
Is it possible to modify a function used in a check constraint, without
having to drop the constraint first?
E.G.
Create function dbo.CheckSampleItemIssueStatus (@.sampleItemIssueId int,
@.StatusId int) Returns bit As
Begin
declare @.RetVal bit
if(@.StatusId = dbo.GetSampleItemIssueStatus(@.sampleItemIssueId))
Set @.RetVal = 1
else
Set @.RetVal = 0
Return @.RetVal
End
go
Alter table dbo.SampleItemIssue Add Constraint
CK_SampleItemIssue_StatusTypeId Check(
dbo.CheckSampleItemIssueStatus(SampleItemIssueId, StatusTypeId) = 1
)
go
Alter function dbo.CheckSampleItemIssueStatus(...
returns an error along the lines of cannot alter function because it is
referenced by constraint..
Thanks.
Fred.You have to drop the constraint first, before you can change the function.
What does the function GetSampleItemIssueStatus do? Because I think you can
solve this with foreign keys or otherwise without having to use functions.
--
Jacco Schalkwijk
SQL Server MVP
"Fred" <Fred@.discussions.microsoft.com> wrote in message
news:5EB25407-CCB1-4D31-A6A8-0AA62DB6D19D@.microsoft.com...
> Hi there,
> Is it possible to modify a function used in a check constraint, without
> having to drop the constraint first?
> E.G.
> Create function dbo.CheckSampleItemIssueStatus (@.sampleItemIssueId int,
> @.StatusId int) Returns bit As
> Begin
> declare @.RetVal bit
> if(@.StatusId = dbo.GetSampleItemIssueStatus(@.sampleItemIssueId))
> Set @.RetVal = 1
> else
> Set @.RetVal = 0
> Return @.RetVal
> End
> go
> Alter table dbo.SampleItemIssue Add Constraint
> CK_SampleItemIssue_StatusTypeId Check(
> dbo.CheckSampleItemIssueStatus(SampleItemIssueId, StatusTypeId) = 1
> )
> go
> Alter function dbo.CheckSampleItemIssueStatus(...
> returns an error along the lines of cannot alter function because it is
> referenced by constraint..
>
> Thanks.
> Fred.
>|||Thanks for the reply,
You confirmed my thoughts, I guess what I'm after is something like
Alter table disable/enable trigger, but for constraints.
That function is just an example and i can't do it via foreign keys,
because the rules governing the value of the statusId are based in part on
records from other tables.
In an other case I also need to check that a number matches the luhn
algorithm.
(http://www.brainyencyclopedia.com/encyclopedia/l/lu/luhn_algorithm.html)
Cheers.
"Jacco Schalkwijk" wrote:
> You have to drop the constraint first, before you can change the function.
> What does the function GetSampleItemIssueStatus do? Because I think you can
> solve this with foreign keys or otherwise without having to use functions.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Fred" <Fred@.discussions.microsoft.com> wrote in message
> news:5EB25407-CCB1-4D31-A6A8-0AA62DB6D19D@.microsoft.com...
> > Hi there,
> >
> > Is it possible to modify a function used in a check constraint, without
> > having to drop the constraint first?
> >
> > E.G.
> > Create function dbo.CheckSampleItemIssueStatus (@.sampleItemIssueId int,
> > @.StatusId int) Returns bit As
> > Begin
> > declare @.RetVal bit
> > if(@.StatusId = dbo.GetSampleItemIssueStatus(@.sampleItemIssueId))
> > Set @.RetVal = 1
> > else
> > Set @.RetVal = 0
> > Return @.RetVal
> > End
> > go
> >
> > Alter table dbo.SampleItemIssue Add Constraint
> > CK_SampleItemIssue_StatusTypeId Check(
> > dbo.CheckSampleItemIssueStatus(SampleItemIssueId, StatusTypeId) = 1
> > )
> > go
> >
> > Alter function dbo.CheckSampleItemIssueStatus(...
> >
> > returns an error along the lines of cannot alter function because it is
> > referenced by constraint..
> >
> >
> > Thanks.
> >
> > Fred.
> >
>
>

functions in check constraint

Hi there,
Is it possible to modify a function used in a check constraint, without
having to drop the constraint first?
E.G.
Create function dbo.CheckSampleItemIssueStatus (@.sampleItemIssueId int,
@.StatusId int) Returns bit As
Begin
declare @.RetVal bit
if(@.StatusId = dbo.GetSampleItemIssueStatus(@.sampleItemIssueId))
Set @.RetVal = 1
else
Set @.RetVal = 0
Return @.RetVal
End
go
Alter table dbo.SampleItemIssue Add Constraint
CK_SampleItemIssue_StatusTypeId Check(
dbo.CheckSampleItemIssueStatus(SampleItemIssueId, StatusTypeId) = 1
)
go
Alter function dbo.CheckSampleItemIssueStatus(...
returns an error along the lines of cannot alter function because it is
referenced by constraint..
Thanks.
Fred.
You have to drop the constraint first, before you can change the function.
What does the function GetSampleItemIssueStatus do? Because I think you can
solve this with foreign keys or otherwise without having to use functions.
Jacco Schalkwijk
SQL Server MVP
"Fred" <Fred@.discussions.microsoft.com> wrote in message
news:5EB25407-CCB1-4D31-A6A8-0AA62DB6D19D@.microsoft.com...
> Hi there,
> Is it possible to modify a function used in a check constraint, without
> having to drop the constraint first?
> E.G.
> Create function dbo.CheckSampleItemIssueStatus (@.sampleItemIssueId int,
> @.StatusId int) Returns bit As
> Begin
> declare @.RetVal bit
> if(@.StatusId = dbo.GetSampleItemIssueStatus(@.sampleItemIssueId))
> Set @.RetVal = 1
> else
> Set @.RetVal = 0
> Return @.RetVal
> End
> go
> Alter table dbo.SampleItemIssue Add Constraint
> CK_SampleItemIssue_StatusTypeId Check(
> dbo.CheckSampleItemIssueStatus(SampleItemIssueId, StatusTypeId) = 1
> )
> go
> Alter function dbo.CheckSampleItemIssueStatus(...
> returns an error along the lines of cannot alter function because it is
> referenced by constraint..
>
> Thanks.
> Fred.
>
|||Thanks for the reply,
You confirmed my thoughts, I guess what I'm after is something like
Alter table disable/enable trigger, but for constraints.
That function is just an example and i can't do it via foreign keys,
because the rules governing the value of the statusId are based in part on
records from other tables.
In an other case I also need to check that a number matches the luhn
algorithm.
(http://www.brainyencyclopedia.com/en...algorithm.html)
Cheers.
"Jacco Schalkwijk" wrote:

> You have to drop the constraint first, before you can change the function.
> What does the function GetSampleItemIssueStatus do? Because I think you can
> solve this with foreign keys or otherwise without having to use functions.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Fred" <Fred@.discussions.microsoft.com> wrote in message
> news:5EB25407-CCB1-4D31-A6A8-0AA62DB6D19D@.microsoft.com...
>
>

Functions don't show dependencies?

I use a user defined function in several stored procedures. However, only
tables show up when I look at the function's dependencies. How do I also
have the stored procedures that are using this function show up as
dependencies?
If that cannot be done, how else do I keep track of the stored procedures
which are using a certain function?
Thanks,
BrettThis works for me. Try recreating the sps.
use northwind
go
create function dbo.ufn_f1 ()
returns int
as
begin
return (1)
end
go
create procedure dbo.usp_p1
as
select dbo.ufn_f1()
go
exec sp_depends ufn_f1
go
drop procedure dbo.usp_p1
go
drop function dbo.ufn_f1
go
AMB
"Brett" wrote:

> I use a user defined function in several stored procedures. However, only
> tables show up when I look at the function's dependencies. How do I also
> have the stored procedures that are using this function show up as
> dependencies?
> If that cannot be done, how else do I keep track of the stored procedures
> which are using a certain function?
> Thanks,
> Brett
>
>|||Also,
How do I find a stored procedure containing <text>?
http://www.aspfaq.com/show.asp?id=2037
AMB
"Alejandro Mesa" wrote:
> This works for me. Try recreating the sps.
> use northwind
> go
> create function dbo.ufn_f1 ()
> returns int
> as
> begin
> return (1)
> end
> go
> create procedure dbo.usp_p1
> as
> select dbo.ufn_f1()
> go
> exec sp_depends ufn_f1
> go
> drop procedure dbo.usp_p1
> go
> drop function dbo.ufn_f1
> go
>
> AMB
>
> "Brett" wrote:
>

functions DIFFERENCE() and SOUNDEX()

Hi!
Is there any other function that can compares two strings ? I'm using the
functions DIFFERENCE() and SOUNDEX(), but they don't consider vowels, "y" and
"h", and I need something that compares everything!
thanks
--
Message posted via http://www.sqlmonster.comOn Fri, 09 Sep 2005 15:37:43 GMT, Amaury Coria via SQLMonster.com wrote:
>Hi!
>Is there any other function that can compares two strings ? I'm using the
>functions DIFFERENCE() and SOUNDEX(), but they don't consider vowels, "y" and
>"h", and I need something that compares everything!
>thanks
Hi Amaury,
What exactly do you mean with "compares everything"? If you are looking
for completely equal strings, just use the '=' operator. The SOUNDEX and
DIFFERENCE functions are deliberately leaving out certain parts of the
string, since they are intended to find common misspelling of words or
names. And in case you and/or your users are not English, beware that
they are designed for English.
If you want to find ""almost equal" strings but are not satisfied with
the algorithm used in SOUNDEX and DIFFERENCE, you'll have to create your
own functions for it. I have no experience with this kind of string
handling, but I believe that several algorithms for this kind of task
are out there on the internet. Google is your friend!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||There's an interesting article about alternative (better) soundex-like
schemes at http://www.avotaynu.com/soundex.html.
Paul Shapiro
"Amaury Coria via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:541CE9D86DFA7@.SQLMonster.com...
> Is there any other function that can compares two strings ? I'm using the
> functions DIFFERENCE() and SOUNDEX(), but they don't consider vowels, "y"
> and
> "h", and I need something that compares everything!

functions DIFFERENCE() and SOUNDEX()

Hi!
Is there any other function that can compares two strings ? I'm using the
functions DIFFERENCE() and SOUNDEX(), but they don't consider vowels, "y" an
d
"h", and I need something that compares everything!
thanks
Message posted via http://www.droptable.comOn Fri, 09 Sep 2005 15:37:43 GMT, Amaury Coria via droptable.com wrote:

>Hi!
>Is there any other function that can compares two strings ? I'm using the
>functions DIFFERENCE() and SOUNDEX(), but they don't consider vowels, "y" a
nd
>"h", and I need something that compares everything!
>thanks
Hi Amaury,
What exactly do you mean with "compares everything"? If you are looking
for completely equal strings, just use the '=' operator. The SOUNDEX and
DIFFERENCE functions are deliberately leaving out certain parts of the
string, since they are intended to find common misspelling of words or
names. And in case you and/or your users are not English, beware that
they are designed for English.
If you want to find ""almost equal" strings but are not satisfied with
the algorithm used in SOUNDEX and DIFFERENCE, you'll have to create your
own functions for it. I have no experience with this kind of string
handling, but I believe that several algorithms for this kind of task
are out there on the internet. Google is your friend!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||There's an interesting article about alternative (better) soundex-like
schemes at http://www.avotaynu.com/soundex.html.
Paul Shapiro
"Amaury Coria via droptable.com" <forum@.droptable.com> wrote in message
news:541CE9D86DFA7@.droptable.com...
> Is there any other function that can compares two strings ? I'm using the
> functions DIFFERENCE() and SOUNDEX(), but they don't consider vowels, "y"
> and
> "h", and I need something that compares everything!

functions DIFFERENCE() and SOUNDEX()

Hi!
Is there any other function that can compares two strings ? I'm using the
functions DIFFERENCE() and SOUNDEX(), but they don't consider vowels, "y" and
"h", and I need something that compares everything!
thanks
Message posted via http://www.droptable.com
On Fri, 09 Sep 2005 15:37:43 GMT, Amaury Coria via droptable.com wrote:

>Hi!
>Is there any other function that can compares two strings ? I'm using the
>functions DIFFERENCE() and SOUNDEX(), but they don't consider vowels, "y" and
>"h", and I need something that compares everything!
>thanks
Hi Amaury,
What exactly do you mean with "compares everything"? If you are looking
for completely equal strings, just use the '=' operator. The SOUNDEX and
DIFFERENCE functions are deliberately leaving out certain parts of the
string, since they are intended to find common misspelling of words or
names. And in case you and/or your users are not English, beware that
they are designed for English.
If you want to find ""almost equal" strings but are not satisfied with
the algorithm used in SOUNDEX and DIFFERENCE, you'll have to create your
own functions for it. I have no experience with this kind of string
handling, but I believe that several algorithms for this kind of task
are out there on the internet. Google is your friend!
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||There's an interesting article about alternative (better) soundex-like
schemes at http://www.avotaynu.com/soundex.html.
Paul Shapiro
"Amaury Coria via droptable.com" <forum@.droptable.com> wrote in message
news:541CE9D86DFA7@.droptable.com...
> Is there any other function that can compares two strings ? I'm using the
> functions DIFFERENCE() and SOUNDEX(), but they don't consider vowels, "y"
> and
> "h", and I need something that compares everything!