Sending data to a stored procedure is easy enough with the tool.
But what if we also want the stored procedure to validate the data too?
Lets say we want to validate a Days Credit value, and only allow values of 10, 20, 30 or 60 days.
We want to relay a message back to the Excel-to-database application when the amount is invalid.
We just use a select statement to return the message to the application, here is an example procedure.
Code:
CREATE PROCEDURE up_CreateCustomer
(
@company_name VARCHAR(100) ,
@address1 VARCHAR(300) ,
@address2 VARCHAR(300) ,
@town VARCHAR(300) ,
@postcodezip VARCHAR(20) ,
@CreditLimit MONEY ,
@DaysCredit TINYINT
)
AS
BEGIN
-- Validate days Credit
IF @DaysCredit NOT IN (10,20,30,60)
BEGIN
--- Simply use a select statement
--- to return messages to the Excel to database application
SELECT 'Days credit: value must be 10, 20, 30 or 60 days'
RETURN -1
END
ELSE
BEGIN
INSERT INTO dbo.Customers
( company_name ,
address1 ,
address2 ,
town ,
[postcode-zip] ,
[Credit Limit] ,
[Days-Credit]
)
VALUES ( @company_name,
@address1,
@address2,
@town,
@postcodezip,
@CreditLimit,
@DaysCredit
)
RETURN 0
END
END
GO
The example has a return value of -1 on failure so the Excel to database default SQL code will work fine, here is the default code generated by the application :
Default SQL:
Code:
DECLARE @ReturnCode int;
EXEC @ReturnCode = up_CreateCustomer
@company_name='{XL-merge:company_name}',
@address1='{XL-merge:address1}',
@address2='{XL-merge:address2}',
@town='{XL-merge:town}',
@postcodezip='{XL-merge:postcodezip}',
@CreditLimit={XL-merge:CreditLimit},
@DaysCredit={XL-merge:DaysCredit};
IF @ReturnCode <> 0 RAISERROR('Procedure failed',16,1)
When you click the validate button, the message is displayed in the first column:
The same principle can be used when inserting directly into tables, we will create a post on that topic in the new Tips & Tricks covering how this can be done.
Edited by user Sunday, August 05, 2012 4:05:43 PM(UTC)
| Reason: Not specified