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.
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.