May 12, 2010 at 12:46 pm
I want to use the values in a table to run with the stored procedure. There is a part in the query where I say ...Where field Like "myvalue" and I want to use the values in the table to replace "myvalue" for comparison.
What I want to do is use my “VendorGroup” table as a lookup table.
1-I will insert the values into the vendorgroup table:
INSERT INTO VendorGroup (CMS_VendorName,NewGroupVendor,NewGroupType,NewGroupDateEntered)
VALUES ('Green Dot C%','Green Dot','GiftCard',GETDATE()),
('Interactive Comm*','Incomm','GiftCard',GETDATE()),
('PRE-SOLUTIONS*','Incomm','GiftCard',GETDATE()),
('PRE SOLUTIONS*','Incomm','GiftCard',GETDATE()),
('Coinstar*','Coinstar','GiftCard',GETDATE()),
('Blackhawk Net*','Blackhawk Network','GiftCard',GETDATE()),
('Blackhawk Mark*','Blackhawk Network','GiftCard',GETDATE()),
('BLACKHAWK MRKTG*','Blackhawk Network','GiftCard',GETDATE())
2-Then I want to run the procedure below and use the vendorgroup to lookup the CMS_VendorName .
i.e. If I want to use CMS_VendorName from vendorGroup instead of @VendorDescription below. How can I do this? (See highlighted line below)
Current Code :
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
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 ------------------------
May 13, 2010 at 10:34 pm
Dynamic SQL is likely your best bet. You can use however many values are in your table to generate parts of your WHERE clause. Using properly parameterized dynamic SQL, you will even get reuse of your execution plans.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
May 15, 2010 at 9:38 am
I'm not saying any of this is great design, but you can do this sort of thing directly using LIKE and PATINDEX.
The following code snippet demonstrates the general idea:
DECLARE @Lookup
TABLE (
lookup_id INTEGER IDENTITY PRIMARY KEY,
match VARCHAR(10) NOT NULL,
value VARCHAR(10) NOT NULL
);
INSERT @Lookup
(match, value)
VALUES ('A%', 'Company A'),
('B%', 'Company B'),
('C%', 'Company C'),
('D%', 'Company D');
DECLARE @data
TABLE (
to_lookup VARCHAR(10) NOT NULL
);
INSERT @data (to_lookup)
VALUES ('Apple'), ('Banana'), ('Cherry'), ('Date');
SELECT *
FROM @data D
JOIN @Lookup L
ON D.to_lookup LIKE L.match;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 17, 2010 at 1:55 pm
Thanks! this helps a lot.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply