Inserting from function or using stored procedure like function in T-SQL

Question

I'm trying to write a program that does a bulk insert, and within this bulk insert I am converting all of the data that doesn't parse as its correct data type as null. I still need the rest of the row inserted in there.

This wasn't a problem, but I also need to record where these errors happen. But I'm having a bit of trouble doing this because you cannot insert in a function.

Here is the function I have tried:

CREATE FUNCTION [dbo].[ReplaceNonNumericInt]
    (@strText NVARCHAR(100),
     @strField nvarchar(100),
     @LoanOrShare nvarchar(1))
RETURNS INT
AS
BEGIN
    DECLARE @Return as int

    IF TRY_CAST(@strText AS INT) > 0 
    BEGIN
        SET @Return = CAST(@strText as int)
    END 
    ELSE BEGIN
        IF @LoanOrShare = 'L' 
        BEGIN
            INSERT INTO tblLoan_ImportErrors(Error, Field, Row)
            VALUES('Type Conversion Failure', @strField, @@IDENTITY + 1)

            SET @Return = NULL
        END

        SET @Return = NULL
    END

    RETURN @Return
END

However this does not work because you cannot do insert statements in functions. The solution I have found to this was "use a stored procedure", however I don't know how to get the stored procedure to return like a function. I can't use an output parameter to my knowledge since this is being called from within an insert statement.

Here is the code I'm using to insert the records:

CREATE PROCEDURE [dbo].[sp_insLoans]
    @FileLocation nvarchar(500)
AS
CREATE TABLE #tmpLOAN (
[RecordCode]         NVARCHAR (100)    NULL,
[AccountNum]         NVARCHAR (100)   NULL,
[MembersName]        NVARCHAR (100)   NULL,
[MailingAddress]     NVARCHAR (100)   NULL,
[City]               NVARCHAR (100)   NULL,
[State]              NVARCHAR (100)    NULL,
[ZipCode]            NVARCHAR (100)    NULL,
[OtherStreet]        NVARCHAR (100)   NULL,
[LoanTypeCode]       NVARCHAR (100)    NULL,
[PaymentAmt]         NVARCHAR(100) NULL,
[PurposeCode]        NVARCHAR (100)    NULL,
[LoanTerm]           NVARCHAR (100)    NULL,
[PaymentFreqCode]    NVARCHAR (100)    NULL,
[DateOfLoan]         NVARCHAR(100)       NULL,
[OriginalLoanAmt]    NVARCHAR(100) NULL,
[InterestRate]       NVARCHAR(100)  NULL,
[InterestRateCode]   NVARCHAR (100)    NULL,
[CurrentLoanBal]     NVARCHAR(100) NULL,
[DateOfLastActivity] NVARCHAR(100)      NULL,
[LastActivityCode]   NVARCHAR (100)    NULL,
[NextPaymentDueDate] NVARCHAR(100)       NULL,
[AccruedInt]         NVARCHAR(100) NULL,
[CreditLimit]        NVARCHAR(100) NULL,
[SSN]                NVARCHAR (100)   NULL,
[DaysDelinq]         nvarchar(100)            NULL,
[Delinq30_59]        nvarchar(100)        NULL,
[Delinq60_89]        nvarchar(100)        NULL,
[Delinq90_119]       nvarchar(100)        NULL,
[Delinq120_plus]     nvarchar(100)        NULL,
[InsiderCode]        NVARCHAR (100)    NULL,
[LoanOfficer_CCInit] NVARCHAR (100)   NULL,
[CreditScore]        nvarchar(100)       NULL,
[ChargeOffAmt]       NVARCHAR(100)    NULL,
[LoanRiskGrade]      NVARCHAR (100)   NULL,
[RemainingPayments]  nvarchar(100)       NULL,
[LoanCollateralCode] NVARCHAR (100)    NULL,
[LastFileMaintDate]  NVARCHAR(100)       NULL,
[LastFIleMaintUser]  NVARCHAR (100)    NULL,
[BranchId]           NVARCHAR (100)   NULL
)

DECLARE @sql NVARCHAR(4000) = 'BULK INSERT #tmpLOAN FROM ''' + @FileLocation + ''' WITH ( FIELDTERMINATOR ='''+ CHAR(9) +''', ROWTERMINATOR ='''+CHAR(10)+''' )';
exec(@sql)
INSERT INTO tblLOAN(RecordCode,AccountNum,MembersName,MailingAddress,City,[State],ZipCode,OtherStreet,LoanTypeCode,PaymentAmt,PurposeCode,LoanTerm,PaymentFreqCode,DateOfLoan,OriginalLoanAmt,InterestRate,InterestRateCode,CurrentLoanBal,DateOfLastActivity,LastActivityCode,NextPaymentDueDate,AccruedInt,CreditLimit,SSN,DaysDelinq,dbo.Delinq30_59,Delinq60_89,Delinq90_119,Delinq120_plus,InsiderCode,LoanOfficer_CCInit,CreditScore,ChargeOffAmt,LoanRiskGrade,RemainingPayments,LoanCollateralCode,LastFileMaintDate,LastFileMaintUser,BranchId)
SELECT LTRIM(RTRIM(RecordCode)),
        LTRIM(RTRIM(AccountNum)),
        LTRIM(RTRIM(MembersName)),
        LTRIM(RTRIM(MailingAddress)),
        LTRIM(RTRIM(City)),
        LTRIM(RTRIM([State])),
        LTRIM(RTRIM(ZipCode)),
        LTRIM(RTRIM(OtherStreet)),
        LTRIM(RTRIM(LoanTypeCode)),
        dbo.ReplaceNonNumericDecimal((RTRIM(PaymentAmt))),
        LTRIM(RTRIM(PurposeCode)),
        LTRIM(RTRIM(LoanTerm)),
        LTRIM(RTRIM(PaymentFreqCode)),
        dbo.DateOrNull((LTRIM(RTRIM(DateOfLoan)))),
        dbo.ReplaceNonNumericDecimal(LTRIM(RTRIM(OriginalLoanAmt))),
        dbo.ReplaceNonNumericDecimal(LTRIM(RTRIM(InterestRate))),
        LTRIM(RTRIM(InterestRateCode)),
        dbo.ReplaceNonNumericDecimal(LTRIM(RTRIM(CurrentLoanBal))),
        dbo.DateOrNull(DateOfLastActivity),
        LTRIM(RTRIM(LastActivityCode)),
        dbo.DateOrNull(LTRIM(RTRIM(NextPaymentDueDate))),
        dbo.ReplaceNonNumericDecimal(LTRIM(RTRIM(AccruedInt))),
        dbo.ReplaceNonNumericDecimal(LTRIM(RTRIM(CreditLimit))),
        LTRIM(RTRIM(SSN)),
        dbo.ReplaceNonNumericInt(LTRIM(RTRIM(DaysDelinq)),'DaysDelinq','L'),
        dbo.ReplaceNonNumericInt(LTRIM(RTRIM(Delinq30_59)),'Delinq30_59','L'),
        dbo.ReplaceNonNumericInt(LTRIM(RTRIM(Delinq60_89)),'Delinq60_89','L'),
        dbo.ReplaceNonNumericInt(LTRIM(RTRIM(Delinq90_119)),'Delinq90_119','L'),
        dbo.ReplaceNonNumericInt(LTRIM(RTRIM(Delinq120_plus)),'Delinq120_plus','L'),
        LTRIM(RTRIM(InsiderCode)),
        LTRIM(RTRIM(LoanOfficer_CCInit)),
        dbo.ReplaceNonNumericInt(LTRIM(RTRIM(CreditScore)),'CreditScore','L'),
        dbo.ReplaceNonNumericDecimal(LTRIM(RTRIM(ChargeOffAmt))),
        LTRIM(RTRIM(LoanRiskGrade)),
        dbo.ReplaceNonNumericInt(LTRIM(RTRIM(RemainingPayments)),'RemainingPayments','L'),
        LTRIM(RTRIM(LoanCollateralCode)),
        dbo.DateOrNull((RTRIM(LastFileMaintDate))),
        LTRIM(RTRIM(LastFileMaintUser)),
        LTRIM(RTRIM(BranchId))
FROM #tmpLoan
DROP TABLE #tmpLoan

Is there any way to do what I'm trying to do?

What you want to look into is/are output parameters technet.microsoft.com/en-us/library/ms187004(v=sql.105).aspx — SCFi
– SCFi, Commented May 3, 2017 at 17:57
Side note: you should not use the sp_ prefix for your stored procedures. Microsoft has reserved that prefix for its own use (see Naming Stored Procedures), and you do run the risk of a name clash sometime in the future. It's also bad for your stored procedure performance. It's best to just simply avoid sp_ and use something else as a prefix - or no prefix at all! — marc_s
– marc_s, Commented May 3, 2017 at 17:57
@SCFi how would I use output parameters in this context? I am currently calling the function as a value in an insert statement and need the value returned right there — rgorr
– rgorr, Commented May 3, 2017 at 18:11
@rgorr I was only referencing how to return a value. In your case you cannot do that and the insert statement from the function would need to be moved into the procedure or a procedure of it's own with I would suggest a table parameter. Procedures cannot be inlined in SQL statements — SCFi
– SCFi, Commented May 3, 2017 at 18:23
If the errors are uncommon you could run a first pass to flag the unacceptable data, e.g. save the id of the row in a temporary table. Then a second pass can process all of the rows except the known troublemakers. Aside: @@Identity can return interesting results in the presence of triggers. Scope_Identity() or an Output clause are rather more reliable. Using @@Identity + 1 has an unpleasant smell. — HABO
– HABO, Commented May 3, 2017 at 19:45

RBarryYoung · Accepted Answer · 2017-05-10 16:36:53Z

1

In SQL, functions have to act like true functions (i.e., no side-effects). There is no other way to get per-row invocation in a column expression, so you are going to have to take a different approach.

There are a number of different ways to get what you appear to be trying to get, with SSIS being the canonical tool for this, but the shortest path from where you already are is probably to do this in two steps:

Scan your temp table for any exceptions in the column conversions, remove those rows from the temp table and log them to your error log table.
Then, execute your command above copying and converting all rows and columns remaining without trying to check/validate them (you already did that in the first step).

It's also worth mentioning that in T-SQL, user-defined scalar functions have notoriously poor performance, so if you are importing a lot of rows, then you may want to consider using table-valued functions instead.

edited May 10, 2017 at 16:36

answered May 3, 2017 at 18:11

RBarryYoung

57k15 gold badges99 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

rgorr Over a year ago

I ended up doing a modification of this. I'm not deleting the rows with errors since I still need the rest of their data, and sort of re-validating again when inserting into the real table so it can convert the wrong data types to nulls. It seems terribly inefficient but the performance is fine on my end with ~40,000 rows and it's a very closed-in app using a localDB, not something that needs scalability.

Collectives™ on Stack Overflow

Inserting from function or using stored procedure like function in T-SQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related