May 5, 2010 at 10:41 am
1 - 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 Varchar
INSERT 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_newvendor
go
CREATE 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 ------------------------
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply