I'm not sure that a UDF would work. I don't have acces to SQL Server right now to try it. Basically, try creating a DEFAULT constraint that references the udf
CREATE TABLE mytable (
mypk int DEFAULT (dbo.getID('mytable'))
)
-Mike
>Thank you for your reply Michael.
>
>I am thinking through PKs for a school administration system targeted at a school system. Since school data may be moved among different databases it is important that PKs be unique across the system.
>
>Besides PKs that consist of two fields (one of which could be an identity field), a way to achieve this is by single field PKs that contain two parts: a high part, which may be the school code or a code indicating the site (database) where it was originally created, and a sequential part. This led to the question I asked.
>
>How would a UDF work in SQL Server? What other alternatives do you suggest?
>
>I appreciate your intput.
>
>Alex
>
>
>>You cannot set the default to be the return value of a stored procedure. In fact, the convention is that the return value of the stored procedure should reflect the status of the stored procedure's execution.
>>
>>A User Defined Function might work, although I've never tried it.
>>
>>Is there any reason that you're not using IDENTITY values for your PKs?
>>
>>-Mike
>>
>>>Hi,
>>>
>>>In SQL Server, is it possible to define the default of a field to be the return value of a stored procedure? For example how can I say that the default value of field CustomerPK is SP_MyNewId(
).
>>>
>>>TIA,
>>>
>>>Alex