0
Create function NextCustomerNumber
(
    @TABLE_NAME varchar(20)
)
returns varchar(10)
as
begin

    declare @lastval varchar(10)
    set @lastval = right('000000000' + convert(varchar(10),(select IsNull(max(Serialno),0)+1 from TestSerialNo)),10)
    return @lastval
end

please tell me how could i return serial no to its calling environment make the sql dynamic inside function not store procedure.

KendoStarter
  • 139
  • 2
  • 13
  • 1
    Simple, you cannot. No dynamic SQL inside functions. – Damien_The_Unbeliever Jul 24 '18 at 05:45
  • use `Stored Procedure` if you need to use `Dynamic SQL` – Squirrel Jul 24 '18 at 06:03
  • Dynamic SQL in User-Defined Functions https://stackoverflow.com/questions/32868767/tricks-on-how-to-execute-string-inside-a-function-in-sql-server – Stanislav Kundii Jul 24 '18 at 06:03
  • @Damien_The_Unbeliever dynamic sql not supported in function ? why you are saying `No dynamic SQL inside functions` ? – KendoStarter Jul 24 '18 at 06:05
  • **Limitations and Restrictions** _User-defined functions cannot be used to perform actions that modify the database state._ – Stanislav Kundii Jul 24 '18 at 06:07
  • 2
    This is not dynamic SQL. however, it seems like you are attempting to create your own auto-increment mechanism. This **will** fail in a multi-user environment. You should use a regular `identity` column and either create the string representation when selecting the data or create a computed column that does that for you (which can also be a persisted computed column). For more information, read [this SO post](https://stackoverflow.com/questions/48486572/in-sql-server-how-do-i-generate-an-auto-increment-primary-key-id-that-consists-o) – Zohar Peled Jul 24 '18 at 06:17

0 Answers0