Tomaric the Lost Tomaric the Lost - 4 months ago 15
SQL Question

T-SQL Insert into truncates data on insert

This is odd. I have two separate store procedures inserting into one table.

One works fine while the other truncates all but the 1st character on four

varchar(50)
columns. I've tested this by executing the SP in SQL.

Only difference is the working query is moving data from one table to the other. The non-working stored procedure is receiving parameters. In the non working query, there are
int
columns and they work fine. It's just the four varchar.

This works fine.

INSERT INTO PurchaseOrderItems_tbl (POID, VendorAccountID, POTicketID, POInvItemID, POLineItemQty, PartManufPartNum, PartOrderDesc, PartOrderManufacture, PartOrderModel, POType, PODescription, POStatusID, PartRequestedBy, POGLCode)
SELECT
POID, VendorAccountID, PartOrderTicketID, PartInvID,
POItemQty, PartManufPartNum, PartOrderDesc,
PartOrderManufacture, PartOrderModel, POType,
PODescription, POStatusID, PartRequestedBy, POGLCode
FROM
PurchaseOrderTmp_tbl


This is the non working stored procedure:

Procedure [dba].[xxxxx]
(
@POID int,
@VendorAccountID int,
@PartOrderTicketID int,
@PartInvID int,
@PartManufPartNum varchar,
@PartOrderDesc varchar,
@PartOrderManufacture varchar,
@PartOrderModel varchar,
@PartOrderType varchar,
@PartOrderQTY int
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
INSERT INTO PurchaseOrderItems_tbl
([POID],[VendorAccountID],[POInvItemID],[POTicketID],[PartManufPartNum],[PartOrderDesc],[PartOrderManufacture],[PartOrderModel],[POLineItemQty])

VALUES (@POID, @VendorAccountID, @PartInvID, @PartOrderTicketID, @PartManufPartNum, @PartOrderDesc, @PartOrderManufacture, @PartOrderModel, @PartOrderQTY)

END


I am completely baffled as I see nothing wrong and the qry does not error.

Thanks in advance folks

Answer

Your stored proc does not specify the length of the varchar inputs - by default this length is 1.

procedure [dba].[xxxxx]
(
    @POID int,
    @VendorAccountID int,
    @PartOrderTicketID int,
    @PartInvID int,
    @PartManufPartNum varchar(50), -- match the column length
...etc