InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 15:34:07 GMT20http://www.sqlservercentral.com/Forums/Topic916275-8-1.aspx1 - I want to have a user defined table type store the values that will be used by the stored procedure. 2 - I also want the stored procedure to insert the values from an xls spreadsheet into the userdefined table type. It can be an xls file that is in a static folder path with a specific xls file name.3 - Then use the user defined table type to feed the stored procedure with the values. Can you help me with that?This is the structure for the user defined table type:USE [QA_Collection]GO/****** Object: UserDefinedTableType [dbo].[tblt_VendorGroup] Script Date: 05/05/2010 07:43:04 ******/CREATE TYPE [dbo].[tblt_VendorGroup] AS TABLE( [CMS_VendorName] [varchar](200) NOT NULL, [NewGroupVendor] [varchar](200) NOT NULL, [NewGroupType] [varchar](50) NULL, PRIMARY KEY CLUSTERED ( [CMS_VendorName] ASC)WITH (IGNORE_DUP_KEY = OFF))GO/*-------------------------------------------------------------------------------------*/This is the data I will be inserting into the user defined table type:DECLARE @tblt_VendorGroup VarcharINSERT INTO @tblt_VendorGroup(CMS_VendorName,NewGroupVendor,NewGroupType)VALUES ('Green Dot C%','Green Dot','GiftCard'), ('Interactive Comm*','Incomm','GiftCard'), ('PRE-SOLUTIONS*','Incomm','GiftCard'), ('PRE SOLUTIONS*','Incomm','GiftCard'), ('Coinstar*','Coinstar','GiftCard'), ('Blackhawk Net*','Blackhawk Network','GiftCard'), ('Blackhawk Mark*','Blackhawk Network','GiftCard'), ('BLACKHAWK MRKTG*','Blackhawk Network','GiftCard'), SELECT * FROM @MyTable /*-------------------------------------------------------------------------------------*/This is the code for my stored procedure:SET ansi_nulls OFF GO SET quoted_identifier ON GO drop procedure dbo.Ins_cms_newvendorgoCREATE PROCEDURE dbo.Ins_cms_newvendor (@GroupVendor VARCHAR(200), @GroupType VARCHAR(50), @VendorDescription VARCHAR(200), --@VendorDescriptionTwo VARCHAR(500), --@VendorDescriptionThree VARCHAR(500), @InsertSuccess INT = -1 OUTPUT) AS /* %~$~%~$~%~$~%~$~%~$~%~$~%~$~%~$~%~$~%~$~%~$~%~$~%~$~%~$~%~$~%~$ Server : USATL02PRSQ70 Database : QA_Collection Proc Name : esp_Ins_cms_newvendor ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Created By : Alex Benitez Title : Audit Development Lead Tech Phone : ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Create Date : 05/04/2010 Project : CMS New Vendor Names/Group Project Contact : Scott Ellison Purpose : Create a Group Name for vendors with slight difference in name but are the same vendor. Input Parameters : @GroupVendor @GroupType @VendorDescription Output Parameters : @InsertSuccess = -1 = Already in the Table 1 = Success 0 = Failure to Insert Update By Update Date Description of Change ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ %~$~%~$~%~$~%~$~%~$~%~$~%~$~%~$~%~$~%~$~%~$~%~$~%~$~%~$~%~$~%~$ */ SET nocount ON SET implicit_transactions OFF SET xact_abort ON ---------------------- SP Code Begins Here ----------------------- DECLARE @NewVendorRowID INT IF EXISTS(SELECT 1 FROM dbo.tblv_Claims_2006AndGreater AS CL2006 WHERE CL2006.vendordesc LIKE @VendorDescription AND NOT EXISTS(SELECT 1 FROM dbo.CMS_NewVnd_Master AS cnv WHERE cnv.Site_Nbr = CL2006.Site_Nbr AND cnv.Client_Nbr = CL2006.Client_Nbr AND cnv.VendorCode = CL2006.VendorCode AND cnv.groupvendor = @GroupVendor AND cnv.grouptype = @GroupType ) ) BEGIN BEGIN TRAN ins_newvendor INSERT INTO cms_newvnd_master (site_nbr, auditcode, auditdesc, prgindustrydesc, prgsubindustrydesc, corpclientdesc, auditstatus, client_nbr, audityeardesc, vendorcode, vendordesc, new_vnd, new_name, groupvendor, grouptype) (SELECT site_nbr, auditcode, auditdesc, prgindustrydesc, prgsubindustrydesc, corpclientdesc, auditstatus, client_nbr, audityeardesc, vendorcode, vendordesc, new_vnd, new_name, @GroupVendor --'Blackhawk Network' , @GroupType --'GiftCard' FROM tblv_claims_2006andgreater AS CL2006 WHERE vendordesc LIKE @VendorDescription --'Blackhawk Net%' --OR vendordesc LIKE @VendorDescriptionTwo --'Blackhawk Mark%' --OR vendordesc LIKE @VendorDescriptionThree --'BLACKHAWK MRKTG%' AND NOT EXISTS(SELECT 1 FROM dbo.CMS_NewVnd_Master AS cnv WHERE cnv.Site_Nbr = CL2006.Site_Nbr AND cnv.Client_Nbr = CL2006.Client_Nbr AND cnv.VendorCode = CL2006.VendorCode) ) SET @NewVendorRowID = @@IDENTITY IF @@ERROR <> 0 BEGIN ROLLBACK TRAN ins_newvendor SET @InsertSuccess = 0 END ELSE BEGIN COMMIT TRAN ins_newvendor SET @InsertSuccess = 1 END END ELSE BEGIN SELECT @NewVendorRowID = vendorcode, @InsertSuccess = -1 FROM cms_newvnd_master WHERE groupvendor = @GroupVendor AND grouptype = @GroupType END SELECT @NewVendorRowID AS vendorcode, @InsertSuccess AS insertsuccess ----------------------- Code Ends Here ------------------------ Wed, 05 May 2010 10:41:22 GMTGrassHopper