Multiple If else in Sql Server Function return error

Question

I am trying to get a unique id number and i have wrote the following SQL Server function to do it. I will give the function the ID of the current admin and it will be on the first digit on the left. Now i want it to return a very unique number only. I have used multiple if so that there shouldn't be a match. I want that when there is no orderid in the table orderdetailstb. And if there is it should +1.

USE [ResturantManagementSystem]
GO
/****** Object:  UserDefinedFunction [dbo].[we]    Script Date: 11/11/2016 11:48:59 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create FUNCTION [dbo].[UniqueOrderId] (@currentAdminid int)
RETURNS int
AS BEGIN
declare @UniqueOrderId int
if (select orderid from OrderDetailsTB) is null
return (select concat(left(@currentAdminid,1),CONVERT(int,getdate(),112),right('1',1)) from Admin)
else
if
(select max(CONVERT(int,getdate(),112)) from OrderDetailsTB)>CONVERT(int,getdate(),112)
return (select concat(left(@currentAdminid,1),CONVERT(int,getdate(),112),right('1',1)) from Admin)
else
return (select concat(left(@currentAdminid,1),CONVERT(int,getdate(),112),right(select max(orderid)+1 from OrderDetailsTB),3) from Admin)
return @UniqueOrderId
END

the problem is its giving error at

return (select concat(left(@currentAdminid,1),CONVERT(int,getdate(),112),right(select max(orderid)+1 from OrderDetailsTB),3) from Admin)

the error is

Msg 156, Level 15, State 1, Procedure UniqueOrderId, Line 12 Incorrect syntax near the keyword 'select'. Msg 102, Level 15, State 1, Procedure UniqueOrderId, Line 12 Incorrect syntax near ')'.

How can i do it and if it is appropriate for me to use a function or should i move it towards a stored procedure.

gotqn · Accepted Answer · 2016-11-11 07:26:46Z

1

Could you try this:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create FUNCTION [dbo].[UniqueOrderId] (@currentAdminid int)
RETURNS int
AS BEGIN
declare @UniqueOrderId int
if (select orderid from OrderDetailsTB) is null
return (select concat(left(@currentAdminid,1),CONVERT(int,getdate(),112),right('1',1)) from Admin)
else
if
(select max(CONVERT(int,getdate(),112)) from OrderDetailsTB)>CONVERT(int,getdate(),112)
return (select concat(left(@currentAdminid,1),CONVERT(int,getdate(),112),right('1',1)) from Admin)
else
return (select concat(left(@currentAdminid,1),CONVERT(int,getdate(),112)
       ,right(maxOrderId,3)) 
        from Admin
        cross apply (select max(orderid)+1 from OrderDetailsTB) ds(maxOrderId)

        )
return @UniqueOrderId
END

The idea is to use outer or cross apply in order to transform inline select statement:

return (select concat(left(@currentAdminid,1),CONVERT(int,getdate(),112),right(select max(orderid)+1 from OrderDetailsTB),3) from Admin)

to:

return (select concat(left(@currentAdminid,1),CONVERT(int,getdate(),112)
       ,right(maxOrderId,3)) 
        from Admin
        cross apply (select max(orderid)+1 from OrderDetailsTB) ds(maxOrderId)

        )

answered Nov 11, 2016 at 7:26

gotqn

44k47 gold badges167 silver badges257 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Zohar Peled Over a year ago

Side note: (select max(CONVERT(int,getdate(),112)) from OrderDetailsTB)>CONVERT(int,getdate(),112) doesn't make sense.

Zohar Peled Over a year ago

Sorry, that was meant as a comment to the question.

Nelson Cajetin Diego Alfonso Over a year ago

Well i want that everyday a new code needs to be generated.

Nelson Cajetin Diego Alfonso Over a year ago

in the end you have put ds? what is that for?

gotqn Over a year ago

This is just alias that we are giving to the cross apply data - you can put anything you want

|

Collectives™ on Stack Overflow

Multiple If else in Sql Server Function return error

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest