Auto Number Function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Auto Number Function

Author

Topic

ero-sennin26
Starting Member

7 Posts

Posted - 2007-07-15 : 02:55:41

Hey guys i'm just new here, i get some trouble when i make
this function, i got this error message:
Msg 174, Level 15, State 1, Procedure AUTONUM, Line 37
The SUM function requires 1 argument(s).

Create Function AUTONUM(@TABLE varchar(255),@PKEY Varchar(255),@PREFIX Varchar(255))

Returns Varchar(255)

AS

Begin

Declare @VTable Varchar(255)
Declare @VPkey varchar(255)
Declare @VPrefix varchar(255)
Declare @SQL Varchar(2000)
Declare @VAutonum Varchar(1000)

Set @VTable=@Table
Set @VPkey=@Pkey
Set @VPrefix=@Prefix

SET @VPKEY='ID'

SET @VTABLE='dbo.Last_PO'

Set @SQL ='Select ' & @VPKEY & ' from ' & Upper(@VTABLE)

Exec(@SQL)

If SUM(dbo.Mid(@VPKEY), Len(@VPREFIX),50) >= 100 Then

Set @VAUTONUM = @VPREFIX & '0' & SUM(dbo.Mid(@VPKEY, SUM(Len(@PREFIX)),50) + 1)

Return (@VAutonum)

END

i'm just novice in SQL Server, please help me i'll apprciate everything

Regards

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-15 : 04:27:44

1. You can't user exec() inside a function. Write this as a store procedure
2. Use substring instead of mid()

KH
[spoiler]Time is always against us[/spoiler]