use sp as default column value

7 Sep 2006 11:05 PM

gerry

i am trying to do what i would think is a very simple thing, define the
default value for a column as the return value from a stored procedure

within enterprise manager, when I enter the sp in the column Default Value ,
em forces this to be a quoted string.
ie entering dbo.MySP for the default value em converts this to 'dbo.MySP'
for whatever reason, em doesn't recognize the sp

if i do this using a udf, entering dbo.MyUDF]() for the default value works.

CREATE PROCEDURE [dbo].[MySP]
AS
return 'hooha'
GO

CREATE FUNCTION [dbo].[MyUDF]()
RETURNS varchar
AS
return 'hooha'
GO

Gerry

8 Sep 2006 12:38 AM

Andrew J. Kelly

It is not possible to use a sp for a default value.

--
Andrew J. Kelly SQL MVP

Show quote

"gerry" <germ@nospam.nospam> wrote in message
news:uszHsIt0GHA.2636@TK2MSFTNGP06.phx.gbl...
>i am trying to do what i would think is a very simple thing, define the
> default value for a column as the return value from a stored procedure
>
> within enterprise manager, when I enter the sp in the column Default Value
> ,
> em forces this to be a quoted string.
> ie entering dbo.MySP for the default value em converts this to 'dbo.MySP'
> for whatever reason, em doesn't recognize the sp
>
> if i do this using a udf, entering dbo.MyUDF]() for the default value
> works.
>
> CREATE PROCEDURE [dbo].[MySP]
> AS
> return 'hooha'
> GO
>
> CREATE FUNCTION [dbo].[MyUDF]()
> RETURNS varchar
> AS
> return 'hooha'
> GO
>
> Gerry
>
>

8 Sep 2006 12:47 AM

Dan Guzman

You can't use a stored procedure as a default value. The SQL Server Books
Online states:

<Excerpt
href="mk:@MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\tsqlref.chm::/ts_create2_8g9x.htm">

A DEFAULT definition can contain constant values, functions, SQL-92 niladic
functions, or NULL.

</Excerpt>

A couple of other issues too. A stored procedure RETURN statement can only
return an integer. Furthermore, the Best Practice is to use the return
value to indicate success or failure rather than return data. Data can be
returned from stored procedures using output parameters or result sets.

--
Hope this helps.

Dan Guzman
SQL Server MVP

Show quote

"gerry" <germ@nospam.nospam> wrote in message
news:uszHsIt0GHA.2636@TK2MSFTNGP06.phx.gbl...
>i am trying to do what i would think is a very simple thing, define the
> default value for a column as the return value from a stored procedure
>
> within enterprise manager, when I enter the sp in the column Default Value
> ,
> em forces this to be a quoted string.
> ie entering dbo.MySP for the default value em converts this to 'dbo.MySP'
> for whatever reason, em doesn't recognize the sp
>
> if i do this using a udf, entering dbo.MyUDF]() for the default value
> works.
>
> CREATE PROCEDURE [dbo].[MySP]
> AS
> return 'hooha'
> GO
>
> CREATE FUNCTION [dbo].[MyUDF]()
> RETURNS varchar
> AS
> return 'hooha'
> GO
>
> Gerry
>
>

8 Sep 2006 3:16 AM

Charles Wang[MSFT]

Just add a point. a stored procedure can't be used a right value such as
'select @i = getIII', while function is available in syntax.

Sincerely,
Charles Wang
Microsoft Online Community Support