stored procedure using user defined table type for parameters

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » T-SQL » stored procedure using user defined table...

stored procedure using user defined table type for parameters

Rate Topic

Display Mode

Topic Options

Author

Message

GrassHopper

Posted Wednesday, May 05, 2010 10:41 AM

SSC Journeyman

Group: General Forum Members
Last Login: Monday, February 18, 2013 8:19 AM
Points: 87, Visits: 211

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 ------------------------

Post #916275

« Prev Topic | Next Topic »

Permissions