Split the Stored procedure variable to next line if input exceeds certain length in SQL server-CodePudding

I have a sp tried to code something like below

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[ABC]

 
 @Food_Pickup           VARCHAR(10),
 @Food_Dropoff          VARCHAR(10),    

 AS
BEGIN
    SET NOCOUNT ON;

 INSERT INTO [dbo].[DEF] (QUOTE, COMPANY_CODE, BAID, EID) 
 VALUES
 
(@Food_Pickup,'B','A','3#') ,
    (IF len(@Food_Dropoff) > 3 SUBSTRING(@Food_Dropoff,1, 3),'C', 'k', (Row INT IDENTITY(1, 1))  CONCAT('...',SUBSTRING(@Food_Dropoff,4, len(@Food_Dropoff))),'C', 'k', )

ELSE 
 (@Food_Dropoff,'C', 'k' ,)
    
END
GO

So, My requirement is, if i exec sp and input the Food_dropoff as 'Atlanta' , if the input exceeds a certain length in this case 3, then it should go next line

Exec [dbo].[ABC] 'Alabama' 'Atlanta'

Then the output should be like this

    Quote     COMPANY_CODE  BAID   EID 
    
 1   Alabama      B            A     3#
 2  Atl           C            k
 3  ...anta       C            k

CodePudding user response：

I don't know your use case, but this seems like a bad way of storing data. It seems like it would be safer to store "Atlanta". You could always parse it when selecting or have a downstream program format it the way you want.

Having said that, I think you want:

CREATE PROCEDURE [dbo].[ABC]
 @Food_Pickup           VARCHAR(10),
 @Food_Dropoff          VARCHAR(10)  
 AS
BEGIN
    SET NOCOUNT ON;
 INSERT INTO [dbo].[DEF] (QUOTE, COMPANY_CODE, BAID, EID) 
 VALUES
(@Food_Pickup,'B','A','3#') ,
(left(@Food_Dropoff, 3)  ,'C', 'k', null);

if (len(@Food_Dropoff) > 3)
  INSERT INTO [dbo].[DEF] (QUOTE, COMPANY_CODE, BAID, EID) 
  VALUES
   ('...'   right(@Food_Dropoff, len(@Food_Dropoff)-3),'C', 'k', null)
END

The idea here is to always insert up-to-the first 3 characters of @Food_Dropoff. Then, if it is actually longer, do a second insert with the remainder.

You can see it working here.