I have a stored procedure that is able to ultimately do an create a temp table and insert into another table. I'm trying to figure out how I could execute the stored procedure that would get it's input parameters from a spreadsheet rather than execute it line by line. Can anyone suggest anything?
CREATE PROCEDURE osp_xmlSvc_UpdateItemAttribute
@.PartNumber varchar(50) = '',
@.Rev varchar(50) = '',
@.AttributeName varchar(256) = '',
@.AttributeValue varchar(1024) = '',
@.EditSource varchar(255) = '',
@.UserName varchar(50) = 'admin',
@.ReturnState int = 0 Output
WITH ENCRYPTION
AS
DECLARE @.ItemID int
DECLARE @.RevID int
DECLARE @.AssignAllRevs int
DECLARE @.ParamIndexID int
DECLARE @.CurrentValue varchar(1024)
DECLARE @.UserID int
SET @.ReturnState = -1
SET @.ItemID = -1
SET @.RevID = -1
SET @.ParamIndexID = -1
SET @.CurrentValue = ''
SET @.AssignAllRevs = 0
SELECT @.UserID = ID FROM UserProfile WHERE UserName = @.UserName
SELECT @.ItemID = ID FROM Entry WHERE PartNumber = @.PartNumber
if(NOT(@.ItemID = -1) AND @.ItemID IS NOT NULL) begin
SELECT @.RevID = ID FROM Rev WHERE ItemID = @.ItemID AND Rev=@.Rev
if(@.RevID = -1 OR @.RevID IS NULL) begin
SET @.AssignAllRevs = 1
SELECT @.RevID = ID FROM Rev WHERE ItemID = @.ItemID AND Expired=0
end
SELECT @.ParamIndexID = ID FROM ParamIndex WHERE [Name]=@.AttributeName
if(NOT(@.ParamIndexID = -1) AND @.ParamIndexID IS NOT NULL) begin
SET @.ReturnState = 0
CREATE TABLE tmp_xml_AV (AttVal varchar(1024))
DECLARE @.tmpSQL nvarchar(2024)
SET @.tmpSQL = 'INSERT INTO tmp_xml_AV (AttVal) SELECT [' + @.AttributeName + '] FROM ParamValue WHERE Expired=0 AND ItemID=' + CAST(@.ItemID AS VARCHAR)+ ' AND RevID=' + CAST(@.RevID AS VARCHAR)
EXECUTE sp_executesql @.tmpSQL
SELECT @.CurrentValue = IsNull(AttVal, '') FROM tmp_xml_AV
DROP TABLE tmp_xml_AV
if(NOT(@.CurrentValue = @.AttributeValue) OR @.CurrentValue IS NULL) begin
SET @.ReturnState = 1
SET @.tmpSQL = 'UPDATE ParamValue SET [' + @.AttributeName + ']=''' + @.AttributeValue + ''' WHERE ItemID=' + CAST(@.ItemID AS VARCHAR)
if(@.AssignAllRevs = 0) begin
SET @.tmpSQL = @.tmpSQL + ' AND RevID=' + CAST(@.RevID AS VARCHAR)
end
EXECUTE sp_executesql @.tmpSQL
-- Record history
DECLARE @.tmpInt int
SELECT @.tmpInt = Max(ID)+1 FROM EntryChangeAction
INSERT INTO EntryChangeAction (ID,EntryAffected,RevID,ActionType,Details,Tool,UserID)
VALUES (@.tmpInt,@.ItemID,@.RevID,6,@.AttributeName + ': ' + @.CurrentValue + ' to: ' + @.AttributeValue,@.EditSource,@.UserID)
end
end
end
GRANT EXECUTE ON [dbo].[osp_xmlSvc_UpdateItemAttribute] TO [public]
GO
Thanks.
Amy
In the stored procedure, you could open and read the xls file and put the values into variables. (An xml file would be simpler -Excel could save the file as xml.)
Check in Books Online about [OpenXML].
