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.

Improve this question
6
  • 1
    Simple, you cannot. No dynamic SQL inside functions. Commented Jul 24, 2018 at 5:45
  • use Stored Procedure if you need to use Dynamic SQL Commented Jul 24, 2018 at 6:03
  • Dynamic SQL in User-Defined Functions stackoverflow.com/questions/32868767/… Commented Jul 24, 2018 at 6:03
  • @Damien_The_Unbeliever dynamic sql not supported in function ? why you are saying No dynamic SQL inside functions ? Commented Jul 24, 2018 at 6:05
  • 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 Commented Jul 24, 2018 at 6:17
Related questions
21
Call dynamic SQL from function
0
SQL Server - evaluate a function in a dynamic query
5
Writing a dynamic SQL function
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.