| Author |
Topic |
|
manisha.vk
Starting Member
17 Posts |
 Posted - 2011-09-21 : 06:30:11
|
I am working in sql server 2008 R2.
I have a scalar valued function which returns a document no.
Return value datatype is varchar.
I want to set that function as a default value or the value for one of the column of my table.
Can we assign function as a default value?
If yes how to do?
Can anybody help me out.
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-09-21 : 06:42:24
|
you can do it. it will be like
CREATE TABLE tablename
(
...other columns,
VarcharColumn AS dbo.ScalarUDF(parameters...)
)
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
manisha.vk
Starting Member
17 Posts |
Posted - 2011-09-21 : 08:56:34
|
Thanks Vishakh,
Creating a table it is working fine.
But after inserting values, function returned value is not storing
in the table.
And then i am not able to even select the rows.
It is showing Error.
like my table is
CREATE table ABC
{ DocId int,
DocNo As dbo.GetNewDocNo(),
other columns
}
and my function is
ALTER FUNCTION [dbo].[GetNEWDocNo]
(
-- Add the parameters for the function here
)
RETURNS varchar(50)
AS
BEGIN
-- Declare the return variable here
DECLARE @docno varchar(50),@rowcnt int;
-- Add the T-SQL statements to compute the return value here
SELECT @rowcnt=COUNT(*) FROM DocMaster; -- For First Entry in table
IF @rowcnt=0
BEGIN
SET @docno=1
END
IF @rowcnt>0
BEGIN
SELECT @docno=MAX(DocNo) FROM DocMaster -- For all next entries in the table
SET @docno=@docno+1
END
-- Return the result of the function
RETURN @docno
END
and the error i am getting is
**** Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32). ****
Function returns varchar because further I want to add some Prefix to the DocNo. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-09-21 : 10:05:34
|
| You should be using an identity column for this. |
|
|
|
manisha.vk
Starting Member
17 Posts |
Posted - 2011-09-21 : 10:13:25
|
Russell
iam using identity for DocId not for DocNo |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-09-21 : 10:20:27
|
were you using a normal insert or trying to do something recursively?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-09-21 : 13:24:30
|
IF @rowcnt=0
BEGIN
SET @docno=1
END
IF @rowcnt>0
BEGIN
SELECT @docno=MAX(DocNo) FROM DocMaster -- For all next entries in the table
SET @docno=@docno+1
That code does the same thing that an identity column does. Would be better to store the next available docno in a table -- either with an identity column, or (within an explicit transaction), increment and return the value. |
|
|
|
manisha.vk
Starting Member
17 Posts |
Posted - 2011-09-22 : 00:40:23
|
Vishakh,
I am using the normal insert only.
it is inserting the row for first time.
for first condition
@rowcnt=0;
but afterwords I could not select the rows from table nor i could add more rows to the table. For these operations it is giving the error which i mentioned in previous post.
I can not set it as identity because next step is to put prefix for DocNo. that is DocNo will be like "Prefix00001".
Then it is not an int, we set identity for number column only. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-09-22 : 00:52:29
|
just for setting prefix you dont need to do this. as Russell suggested you can make an identity column say ID then create a computed column based on that like
CREATE TABLE tablename
(...,
ID int IDENTITY(1,1),
DocNo AS 'Prefix' + RIGHT('00000' + CAST(ID AS varchar(10)),5)
)
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
manisha.vk
Starting Member
17 Posts |
Posted - 2011-09-22 : 01:42:14
|
Thanks aaaaaaaaaaa lotttttttttttt
Vishakh and Russell.
It is working fine and smoothly.
Thanks again. |
|
|
|
manisha.vk
Starting Member
17 Posts |
Posted - 2011-09-22 : 07:56:52
|
Vishakh
I have gone through your post on composable DML
and I tried to do it but it is not working....
here is what I did
INSERT INTO Child
(Parent_ID,Value2,Value3)
SELECT
FROM ID,
'ABC',
'PQR'
(
INSERT INTO Parent (Value1)
OUTPUT INSERTED.ID
VALUES('LMN')
)t
Errors
are
Incorrect syntax near the keyword 'FROM'.
Msg 102, Level 15, State 1, Line 21
Incorrect syntax near ')'.
How to correct it. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-09-22 : 13:21:20
|
are you on sql 2008 or above. Composable DML works only on SQL 2008 and above
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
manisha.vk
Starting Member
17 Posts |
Posted - 2011-09-24 : 00:30:46
|
Ya I am on sql server 2008 R2.
It is working when I changed code as
INSERT INTO Child(Parent_ID, Value2, Value3)
SELECT Parent_ID, Value2, Value3
FROM (
INSERT INTO Parent (Value1)
OUTPUT inserted.ID,'Second','Third'
values ('First')
) AS T(Parent_ID,Value2,Value3) |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-09-24 : 03:28:24
|
quote:
Originally posted by manisha.vk
Ya I am on sql server 2008 R2.
It is working when I changed code as
INSERT INTO Child(Parent_ID, Value2, Value3)
SELECT Parent_ID, Value2, Value3
FROM (
INSERT INTO Parent (Value1)
OUTPUT inserted.ID,'Second','Third'
values ('First')
) AS T(Parent_ID,Value2,Value3)
oh you had some constant values. Thats why it didnt work as it need an alias
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
|
User Defined Functions as Default Value for column