Verify the name of a varchar field and concatenate an int in case it already exists

Navigation
2

I am reprogramming a calendar to save tasks to perform and I need a stored procedure that allows me to verify if the name of the task exists in the 'subject' column of my Appointments table, if it exists it must modify the name adding it at the end "_" followed by a number starting at 1.
It would be something like this:

  

subject
   170811
    170811_1
  170811_2
      ...
     170811_n
  170812
  170812_1
  170812_2
  ...

I have the following code:

  

My Appointments table:
   UniqueID PK, int
   Type int,
  Startdate datetime,
  EndDate datetime,
   AllDay bit,
  Location nvarchar (50),
  Description nvarchar (max),
  Status int,
  Label int,
  ResourceID int,
  ResourcesIDs nvarchar (max),
  ReminderInfo nvarchar (max)

and the stored procedure: 

Create Proc sp_VerifNombreTarea
ALTER PROCEDURE [dbo].[sp_VerificarNombreTarea] 
    -- Add the parameters for the stored procedure here
    @pNombreTarea varchar(max)
AS
BEGIN
declare @var int = 0
declare @pUniqueID int
declare @pType int
declare @pStartDate smalldatetime
declare @pEndDate smalldatetime
declare @pAllDay bit
declare @pLocation nvarchar(max)
declare @pStatus int
declare @pLabel int
declare @pResourceID int
declare @pResourceIDs nvarchar(max)
declare @pReminderInfo nvarchar(max)
declare @pRecurrenceInfo nvarchar(max)
declare @pTimeZoneId nvarchar(max)
declare @pCustomField1 nvarchar(max)
declare @Cant int
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    set @Cant = (select count(subject) from Appointments)
    if @Cant > 0
    begin
    set @pNombreTarea = @pNombreTarea + '_' + CAST(@Cant as varchar)
    end

    insert into Appointments(Type ,StartDate, EndDate, AllDay, Location, Status, Label, ResourceID, ResourceIDs, 
                            ReminderInfo, RecurrenceInfo, TimeZoneId, CustomField1, subject)
            values(@pType, @pStartDate, @pEndDate, @pAllDay, @pLocation, @pStatus, @pLabel, @pResourceID, 
                    @pResourceIDs, @pReminderInfo, @pRecurrenceInfo, @pTimeZoneId, @pCustomField1, @pNombreTarea)

END

The problem is that when I execute it I get the following result:

This is where you should show me the name of the task as I described it at the beginning.

Update:

Now I have this code with the help of @Lamak: 

ALTER PROCEDURE [dbo].[sp_VerificarNombreTarea] 
    -- Add the parameters for the stored procedure here
    @pNombreTarea varchar(max)
AS
BEGIN
declare @var int = 0
declare @pUniqueID int
declare @pType int
declare @pStartDate smalldatetime
declare @pEndDate smalldatetime
declare @pAllDay bit
declare @pLocation nvarchar(max)
declare @pStatus int
declare @pLabel int
declare @pResourceID int
declare @pResourceIDs nvarchar(max)
declare @pReminderInfo nvarchar(max)
declare @pRecurrenceInfo nvarchar(max)
declare @pTimeZoneId nvarchar(max)
declare @pCustomField1 nvarchar(max)
declare @Cant int

SET NOCOUNT ON;
select @cant = case
                  when subject LIKE '%_%' then right(subject,len(subject)-CHARINDEX('_',subject)) + 1
                  else 1
               end
    from Appointments ;

    set @cant = isnull(@cant,0);

    if @Cant > 0
        begin
            update Appointments
            set subject = @pNombreTarea + '_' + CAST(@Cant as varchar(max))
            where case 
             when subject LIKE '%_%' THEN left(subject, ISNULL(NULLIF(CHARINDEX('_', subject)-1, -1), LEN(subject))) 
             else subject 
          end = @pNombreTarea 

        end
END

But I get the following result having created 3 tasks:

In this case, add 1 to the last number of the name and concatenate it to the previous name and while creating new tasks, repeat the name again, while in the new task created, enter the name that the previous task should have. The desired result is: 170811_1, 170811_2.

    
asked by Pablo Matias 11.08.2017 в 15:03
source

1 answer

1

A couple of comments. First of all, I do not know how with this code you can be inserting repeated values for the subject column, it does not seem to be possible.

Secondly, the line where you check if it is unique is bad, since you check if the whole table has more than one row or not, which clearly is not what you want. It should be: 

set @Cant = (select count(*) 
             from Appointments 
             where case 
                      when subject LIKE '%_%' then left(subject,CHARINDEX('_',subject)-1))
                      else subject
                   end = @pNombreTarea)

Finally, whenever you use varchar or nvarchar you must explicitly assign it a length: 

set @pNombreTarea = @pNombreTarea + '_' + CAST(@Cant as varchar(5))

ACUTALIZATION:

Ok, what you want to do is a lot more complex than I initially understood. You must change the code of your sp for the following (and take into account that this will work if the table already meets the conditions of name of subject that you described): 

select @cant = case
                  when subject LIKE '%_%' then right(subject,len(subject)-CHARINDEX('_',subject)) + 1
                  else 1
               end
from Appointments ;

set @cant = isnull(@cant,0);

if @Cant > 0
begin
    update Appointments
    set subject = @pNombreTarea + '_' + CAST(@Cant as varchar(5))
    where case 
             when subject LIKE '%_%' THEN left(subject,CHARINDEX('_',subject)-1))
             else subject
          end = @pNombreTarea 

end
else 
begin
    insert into Appointments(Type ,StartDate, EndDate, AllDay, Location, Status, Label, ResourceID, ResourceIDs, 
                            ReminderInfo, RecurrenceInfo, TimeZoneId, CustomField1, subject)
            values(@pType, @pStartDate, @pEndDate, @pAllDay, @pLocation, @pStatus, @pLabel, @pResourceID, 
                    @pResourceIDs, @pReminderInfo, @pRecurrenceInfo, @pTimeZoneId, @pCustomField1, @pNombreTarea)
end
    
answered by 11.08.2017 в 15:36
Upload file with bootstrap input file in laravel 5 hide key of google maps in web page