Default Constraints (call: User-Defined Function or proc)

Hi
Have any way to call User-Defined Function or proc of a Default Constraint'
INT CONSTRAINT test_df DEFAULT dbo.myFunc()
ThaksAccording to the SQL BOL, a user-defined function can be used in a DEFAULT
clause. A stored procedure - not that I am aware of.
HTH
Jerry
"ReTF" <re.tf@.newsgroup.nospam> wrote in message
news:%23hlZz7LwFHA.624@.TK2MSFTNGP11.phx.gbl...
> Hi
> Have any way to call User-Defined Function or proc of a Default
> Constraint'
> INT CONSTRAINT test_df DEFAULT dbo.myFunc()
> Thaks
>|||Hi
Here is an example of a udf:
CREATE FUNCTION SetDate ()
RETURNS Datetime
AS
BEGIN
DECLARE @.Startdate Datetime
SET @.Startdate = '20050101'
RETURN @.Startdate
END
CREATE TABLE MyTest ( id int not null identity(1,1) Primary key,
name varchar(10) NOT NULL,
StartDate datetime not null default dbo.SetDate(),
EndDate datetime not null default getdate()+1
)
INSERT INTO MyTest ( name ) VALUES ( 'John')
SELECT * FROM MyTest
/*
id name StartDate
EndDate
-- -- ----
-
--1 John 2005-01-01 00:00:00.000
2005-09-25 15:38:48.220
(1 row(s) affected)
*/
John
"ReTF" <re.tf@.newsgroup.nospam> wrote in message
news:%23hlZz7LwFHA.624@.TK2MSFTNGP11.phx.gbl...
> Hi
> Have any way to call User-Defined Function or proc of a Default
> Constraint'
> INT CONSTRAINT test_df DEFAULT dbo.myFunc()
> Thaks
>

Declaring USER_NAME() as SQL Variable

Hi,

I have a User-defined function "Concatenate_NoteTexts" which I use in a
query (SQL Server 2000). On my local development machine it is called like
this:

SELECT
dbo.Concatenate_NoteTexts(Introducers.IntroducerID ) as NoteTexts
FROM tblIntroducers

I want to run the same code on a shared remote server where I am user "JON"
instead of "dbo". I don't want to hard-code the User Name into the SQL, but
when I tried to put the user name into a variable as here:

DECLARE @.USER_NAME VarChar(30)
SET @.USER_NAME = USER_NAME()

SELECT
@.USER_NAME.Concatenate_NoteTexts(Introducers.Intro ducerID) as NoteTexts
FROM tblIntroducers

I get the following error:

Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near '.'

Any advice?

TIA,

JON

PS First posted earlier today to AspMessageBoard - no answers yet.
http://www.aspmessageboard.com/foru...=626289&F=21&P=
1"Jon Maz" <jonmaz@.NOSPAM.surfeu.de> wrote in message
news:bj4s3n$kh5$1@.online.de...
> Hi,
> I have a User-defined function "Concatenate_NoteTexts" which I use in a
> query (SQL Server 2000). On my local development machine it is called
like
> this:
> SELECT
> dbo.Concatenate_NoteTexts(Introducers.IntroducerID ) as NoteTexts
> FROM tblIntroducers
> I want to run the same code on a shared remote server where I am user
"JON"
> instead of "dbo". I don't want to hard-code the User Name into the SQL,
but
> when I tried to put the user name into a variable as here:
> DECLARE @.USER_NAME VarChar(30)
> SET @.USER_NAME = USER_NAME()
> SELECT
> @.USER_NAME.Concatenate_NoteTexts(Introducers.Intro ducerID) as NoteTexts
> FROM tblIntroducers
> I get the following error:
> Server: Msg 170, Level 15, State 1, Line 4
> Line 4: Incorrect syntax near '.'
> Any advice?

Beg for your own database.
Development as a non-dbo is really a hastle.

You can have your own database without being a SystemAdministrator. Just
have a SystemAdministrator to run this code:

create database jon_dev
go
use jon_dev
go
sp_addalias 'jon', 'dbo'

David|||Hi David,

Thanks, nice idea, I'll have to see if the webhosts will do that.

But there must also be a way to code what I want *without* being a SysAd!

JON|||What are you trying to do with the variable? Concatenate? Or return it in
the select statement as a column? If the latter, change the period to a
comma.

DECLARE @.USER_NAME VarChar(30)
SET @.USER_NAME = USER_NAME()

SELECT
@.USER_NAME,Concatenate_NoteTexts(Introducers.Intro ducerID) as NoteTexts
FROM tblIntroducers

"Jon Maz" <jonmaz@.NOSPAM.surfeu.de> wrote in message
news:bj4s3n$kh5$1@.online.de...
> Hi,
> I have a User-defined function "Concatenate_NoteTexts" which I use in a
> query (SQL Server 2000). On my local development machine it is called
like
> this:
> SELECT
> dbo.Concatenate_NoteTexts(Introducers.IntroducerID ) as NoteTexts
> FROM tblIntroducers
> I want to run the same code on a shared remote server where I am user
"JON"
> instead of "dbo". I don't want to hard-code the User Name into the SQL,
but
> when I tried to put the user name into a variable as here:
> DECLARE @.USER_NAME VarChar(30)
> SET @.USER_NAME = USER_NAME()
> SELECT
> @.USER_NAME.Concatenate_NoteTexts(Introducers.Intro ducerID) as NoteTexts
> FROM tblIntroducers
> I get the following error:
> Server: Msg 170, Level 15, State 1, Line 4
> Line 4: Incorrect syntax near '.'
> Any advice?
> TIA,
> JON
>
> PS First posted earlier today to AspMessageBoard - no answers yet.
http://www.aspmessageboard.com/foru...=626289&F=21&P=
> 1
>
>
>|||Sorry... misread your message - you need access to the function.

"Morgan" <mfears@.spamcop.net> wrote in message
news:OCNLY9icDHA.1280@.tk2msftngp13.phx.gbl...
> What are you trying to do with the variable? Concatenate? Or return it in
> the select statement as a column? If the latter, change the period to a
> comma.
> DECLARE @.USER_NAME VarChar(30)
> SET @.USER_NAME = USER_NAME()
> SELECT
> @.USER_NAME,Concatenate_NoteTexts(Introducers.Intro ducerID) as NoteTexts
> FROM tblIntroducers
> "Jon Maz" <jonmaz@.NOSPAM.surfeu.de> wrote in message
> news:bj4s3n$kh5$1@.online.de...
> > Hi,
> > I have a User-defined function "Concatenate_NoteTexts" which I use in a
> > query (SQL Server 2000). On my local development machine it is called
> like
> > this:
> > SELECT
> > dbo.Concatenate_NoteTexts(Introducers.IntroducerID ) as NoteTexts
> > FROM tblIntroducers
> > I want to run the same code on a shared remote server where I am user
> "JON"
> > instead of "dbo". I don't want to hard-code the User Name into the SQL,
> but
> > when I tried to put the user name into a variable as here:
> > DECLARE @.USER_NAME VarChar(30)
> > SET @.USER_NAME = USER_NAME()
> > SELECT
> > @.USER_NAME.Concatenate_NoteTexts(Introducers.Intro ducerID) as
NoteTexts
> > FROM tblIntroducers
> > I get the following error:
> > Server: Msg 170, Level 15, State 1, Line 4
> > Line 4: Incorrect syntax near '.'
> > Any advice?
> > TIA,
> > JON
> > PS First posted earlier today to AspMessageBoard - no answers yet.
http://www.aspmessageboard.com/foru...=626289&F=21&P=
> > 1|||Jon Maz (jonmaz@.NOSPAM.surfeu.de) writes:
> I have a User-defined function "Concatenate_NoteTexts" which I use in a
> query (SQL Server 2000). On my local development machine it is called
> like this:
> SELECT
> dbo.Concatenate_NoteTexts(Introducers.IntroducerID ) as NoteTexts
> FROM tblIntroducers
> I want to run the same code on a shared remote server where I am user
> "JON" instead of "dbo". I don't want to hard-code the User Name into
> the SQL, but when I tried to put the user name into a variable as here:
> DECLARE @.USER_NAME VarChar(30)
> SET @.USER_NAME = USER_NAME()
> SELECT
> @.USER_NAME.Concatenate_NoteTexts(Introducers.Intro ducerID) as NoteTexts
> FROM tblIntroducers

The question is slightly more interesting than it may look like.

Say that you instead had had a stored procedure, call it notetext_sp.
This would not have constituted any problem, because you could have
called it as:

EXEC notetext_sp

When you are logged in as JON on the remote server, SQL Server would
have found the notetext_sp owned by you. This works for any other
SQL Server object as well. Except scalar user-defined functions, because
you must refer to them with a two-part name. The reason for this is
syntactical, so that the parser can distinguish between UDF and built-in
functions.

However, there is an exception to the exception. This works:

ALTER FUNCTION nisse_fun (@.a int) returns varchar(90) as
BEGIN
RETURN (SELECT replicate('nisse', @.a))
END
go
declare @.g varchar(90)
exec @.g = nisse_fun 8
select @.g

That is you can invoke a scalar UDF with EXEC as well, and in this case
you don't need the two-part name. Whether this actually helps you, I
don't know.

However, as noted by David Browne, getting your database makes life a
lot easier.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

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