Stored Procedure to concatenate a varchar and an incremental integer in fields with repeated data - SQLServer [duplicated]

Question

Stored Procedure to concatenate a varchar and an incremental integer in fields with repeated data - SQLServer [duplicated]

Navigation

#1 by (0 votes)

0

I need to edit the name of a field (in case it already exists) of my table in my database in SQLServer 2014.
• If the @pNameTarea parameter is NULL or empty, an automatic Name is created, this YYMMDD being created and if there is a Task with that name, it will be concatenated with "_" and an incrmental number of 1 started in 1 , and the first non-occurrence will be searched, for example 110920, then 110920_1 and then 110920_2. In case the @pNameTarea parameter is specified, it will be searched for that there is no Task with that name inside the @pResourceID. If it exists, return -4. Otherwise, continue.
What I have so far is this but it does not work:

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 @Cant = (select count(*) from Appointments where subject = @pNombreTarea)
    if @Cant > 0
    begin
        set @pNombreTarea = @pNombreTarea + '_' + CAST(@Cant as varchar(max))
    end 
    else
        return @pNombreTarea  
END

The table where I store the data is as follows:

UniqueID PK, int
Type int,
NombreTarea nvarchar(max),
StartDate datetime,
EndDate datetime,
AllDay bit,
Location nvarchar(50),
Description nvarchar(max),
Status int,
Label int,
ResourceID int,
ResourcesIDs nvarchar(max),
ReminderInfo nvarchar(max)

Clarification:

The SP I call from the data layer of my application and the name of the task I get from a form to create tasks in a calendar.

sql-server sql-server-2014 stored-procedure

asked by Pablo Matias 14.08.2017 в 14:48

source

1 answer

ComboBox Problems Pass data from one dbgrid to another and close query form

score 0 · Answer 1

Try this, I hope it's what you need:

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

    -- Primero verificamos si el valor viene vacio
    if @pNombreTarea='' or @pNombreTarea is NULL
    begin
        set @pNombreTarea = Format(GetDate(), 'yyMMdd')

        set @Cant = (select count(*) from Appointments where subject like @pNombreTarea+'%')
        if @Cant > 0
        begin
            set @pNombreTarea = @pNombreTarea + '_' + CAST(@Cant as varchar(max))
        end 
        return @pNombreTarea  
    end
    else
    begin
        -- Si el string de @pNombreTarea ya esta dentro del string @pResourceID
        if CHARINDEX( @pNombreTarea, @pResourceID ) > 0
            return -4
        else
            return @pNombreTarea
    end 
END