STORED PROCEDURE Calculations and performance improvements
I currently have the following stored procedure:
CREATE PROCEDURE web.insertNewCampaign
(
@tmp_Id BIGINT,
@tmp_Title VARCHAR(100),
@tmp_Content VARCHAR(8000),
@tmp_Pledge DECIMAL(7,2),
--@tmp_Recipients BIGINT,
@tmp_Date DATETIME,
@tmp_Private BIT,
@tmp_Template BIGINT,
@tmp_AddyBook BIGINT
)
AS
declare @recipients BIGINT
declare @tmp_IDENTITY BIGINT
declare @fave BIGINT
declare @allocation VARCHAR(50)
--insert campaign data
BEGIN TRAN
SELECT @recipients = addMaster_NoRecipients FROM tbl_AddressBookMaster
WHERE addMaster_UserId = @tmp_Id AND addMaster_Key = @tmp_AddyBook;
INSERT INTO TBL_CAMPAIGNS ([campaign_MemberId], [campaign_Title], [campaign_Content], [campaign_Pledge], [campaign_Date], [campaign_Private], [campaign_Template], [campaign_AddressBook], [campaign_Recipients])
VALUES (@tmp_Id, @tmp_Title, @tmp_Content, @tmp_Pledge, @tmp_Date, @tmp_Private, @tmp_Template, @tmp_AddyBook, @recipients)
SELECT @tmp_IDENTITY = SCOPE_IDENTITY() --this returns the newly added IDENTITY ID
COMMIT
......
I have 2 questions:
1) How to split @tmp_Pledge to @recipients to give @allocation like: (@alloc = @ tmp_Pledge / @ recipients)
2) Is it possible to combine these statements into more efficient statement (s) where @allocation is effectively inserted as a value into the [campaign_RecipShare] column and reduces the need for these declared variables?
Thanks so much for any help you can offer for any question.
; -)
source to share
After the first choice, you can do this to install @allocation
:
set @allocation = @tmp_pledge / @recepients
As far as efficiency gains go, it's already quite efficient - you won't go through any steps, but you can condense the code a bit:
INSERT INTO TBL_CAMPAIGNS (
[campaign_MemberId], [campaign_Title], [campaign_Content],
[campaign_Pledge], [campaign_Date], [campaign_Private],
[campaign_Template], [campaign_AddressBook], [campaign_Recipients],
[capmain_RecipShare])
SELECT
@tmp_Id, @tmp_Title, @tmp_Content,
@tmp_Pledge, @tmp_Date, @tmp_Private,
@tmp_Template, @tmp_AddyBook, addMaster_NoRecipients,
@tmp_Pledge / addMaster_NoReceipients as Allocation
FROM
tbl_AddressBookMaster
WHERE
addMaster_UserId = @tmp_Id
AND addMaster_Key = @tmp_AddyBook
SELECT @tmp_IDENTITY = SCOPE_IDENTITY() --this returns the newly added IDENTITY ID
It also removes the need to evaluate the term @allocation
outside of the statement insert
.
source to share
1) @ tmp_pledge / @ recepients - I am guessing that highlight is some form of numeric field in TBL_CAMPAIGNS containing a number in varchar is not a good idea.
2) You just need to create a selection that returns all values from another table and parameters corresponding to the columns to insert.
insert into TBL_CAMPAIGNS ([campaign_MemberId], [campaign_Title], [campaign_Content], [campaign_Pledge], [campaign_Date], [campaign_Private], [campaign_Template], [campaign_AddressBook], [campaign_Recipients], [campaign_allocation)
select @tmp_Id, @tmp_Title, @tmp_Content, @tmp_Pledge, @tmp_Date, @tmp_Private, @tmp_Template, @tmp_AddyBook, addMaster_NoRecipients, @tmp_pledge / addMaster_NoRecipients
FROM FROM tbl_AddressBookMaster WHERE addMaster_UserId = @tmp_Id AND addMaster_Key = @tmp_AddyBook;
SELECT @tmp_IDENTITY = SCOPE_IDENTITY () - This returns the newly added IDENTITY
source to share