SQL Server: ltrim on User-Defined Table Types

Question

I have a SQL Server 2012 User-Defined Table Types that I am using to get data from a PHP array and pass that data to a Procedure. Sometimes I get white spaces from the web app and I would like to use ltrim/rtrim to clean it. Can I do this at the User-Defined Table Type level? IE: where I declare that it should expect a parm1 varchar(10) can I somehow trim is there? I haven't had any luck. Thank you

Example, my Table Type looks like this:

CREATE TYPE LocationTableType AS TABLE 
( LocationName VARCHAR(50)
, CostRate INT );
GO

I want to change it so that when LocationName comes in, it will ltrim()/rtrim() to clear out any extra spaces.

can you post some examples instead of describing in terms of words — TheGameiswar
Use an INSERT/UPDATE trigger that would LTRIM/RTRIM the location. An alternative would be to use a computed column. Store the location name in a field LocationNameDirty and have a computed column LocationName AS RTRIM(LTRIM(LocationNameDirty)). — TT.

Bogdan Sahlean Bogdan Sahlean · Accepted Answer · 2016-06-04T14:52:12

I assume that reported issue is linked to READONLY property of table valued params, property that is mandatory. This means that rows from table valued params can not be updated/deleted and also we can't insert other rows.

CREATE PROCEDURE dbo.DoSomething
@list dbo.LocationTableType READONLY -- <-- table table param have to be READONLY
AS 
BEGIN
    SELECT * FROM @list
END

On short term solution could be

1)to declare another variable within stored procedure

2)insert trimmed data into this variable and then

3)to change next references to all variable - param to new variable.

ALTER PROCEDURE dbo.DoSomething
@list dbo.LocationTableType READONLY
AS 
BEGIN
    DECLARE @listNoSpc dbo.LocationTableType    -- New variable
    INSERT  @listNoSpc (LocationName, CostRate) -- Inserting trimmed data
    SELECT  LTRIM(l.LocationName), l.CostRate
    FROM    @list l

    SELECT * FROM @listNoSpc                    -- Update references from @list to @listNoSpc
END
GO

Permanent solution should be to update webapp (PHP) to remove those spaces before sending data to SQL Server.

SQL Server: ltrim on User-Defined Table Types

1 Answers