Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1706
  • Last Modified:

Stored Procedure Create Variable based on SELECT resultset

I currently have about 50 or so, nearly identical INSTEAD OF INSERT triggers, and I'm looking to see If I can consolidate my code and make the damn things easier to manage.

I'd like to create a table like this:
Category.............FieldName...............FieldType............FieldOrder
Executive.............BranchID................varchar(100)........1
Executive.............ManagerNumber...varcher(50)...........2
Exuective.............ManagerName.........varcher(255).......3

And use it somehow to consolidate my INSTEAD OF TRIGGERS into one piece of code that can handle all the zillions of different fields required by all the different "sales categories"


So How can I go from a command like this:
Delcare field_cursor cursor for select FieldName, FieldType from SalesFields where Category='Executive' Order by FieldOrder

into this: (prepare for butchered psudoecode)
.....fetch loop.....
set @fieldcount = @fieldcount+1
Declare @Field@fieldcount Name =
Declare @Field@fieldcount 1Type ='varchar(100)'
Declare @Field@fieldcount ..?..as @Field1Type...?.
........

Select @Field1 = @field1Name, @field2=@field2Name from INSERTED

.....
etc


Thanks for any help, This seems ridiculous/unfeasible as I type it, we'll see.
ALTER TRIGGER View_Sales_Executive_Insert_Trigger ON View_Sales_Executive
INSTEAD OF INSERT
AS
-- ** These first variables are common to all sales
    Declare @SalesNumber int
    Declare @SalesDate datetime
    Declare @SalesCategory varchar(20)
-- ** These variable are specific to the type of sale
    Declare @BranchID [varchar](100)
    Declare @ManagerNumber [varchar](50)
    Declare @ManagerName [varchar](255)
-- Populate Variables from Inserted
SELECT @SalesNumber=SalesNumber, @SalesCategory=SalesCategory, @BranchID=BranchID, @ManagerNumber=ManagerNumber, @ManagerName=ManagerName
FROM INSERTED
 
--Create the Main Sale
INSERT INTO Sales (SalesCategory, SalesDate) VALUES(@SalesCategory,  @SalesDate)
--Get the SaleNumber
SELECT @SalesNumber=SalesNumber FROM Sales WHERE
SalesCategory=@SalesCategory AND SalesDate=@SalesDate
--Populate the Executive Sales Table
INSERT INTO Sales_Executive_Sales (SalesNumber, BranchID, ManagerNumber, ManagerName) values (@SalesNumber, @BranchID, @ManagerNumber, @ManagerName)
 
 
ALTER TRIGGER View_Sales_IntraDept_Insert_Trigger ON View_Sales_IntraDept
INSTEAD OF INSERT
AS
-- ** These first variables are common to all sales
    Declare @SalesNumber int
    Declare @SalesDate datetime
    Declare @SalesCategory varchar(20)
-- ** These variable are specific to the type of sale
    Declare @FromDept [varchar](100)
    Declare @ToDept [varchar](100)
    Declare @FromDeptTransCode [varchar](50)
    Declare @ToDeptTransCode [varchar](50)
    Declare @FromDeptReasonCode [varchar](10)
    Declare @ToDeptReasonCode [varchar](10)
    Declare @FromDeptAuthorizationNumber [varchar](10)
    Declare @ToDeptAuthorizationNumber [varchar](10)
-- Populate Variables from Inserted
SELECT @SalesNumber=SalesNumber, @SalesCategory=SalesCategory,
@FromDept=FromDept, @ToDept=ToDept, @FromDeptTransCode=FromDeptTransCode,
@ToDeptTransCode=ToDeptTransCode, @FromDeptReasonCode=FromDeptReasonCode,
@ToDeptReasonCode=ToDeptReasonCode, @FromDeptAuthorizationNumber=FromDeptAuthorizationNumber,
@ToDeptAuthorizationNumber=ToDeptAuthorizationNumber
FROM INSERTED
 
--Create the Main Sale
INSERT INTO Sales (SalesCategory, SalesDate) VALUES(@SalesCategory, @SalesDate)
--Get the SaleNumber
SELECT @SalesNumber=SalesNumber FROM Sales WHERE
SalesCategory=@SalesCategory AND SalesDate=@SalesDate
--Populate the IntraDepartment Sales Table
INSERT INTO Sales_IntraDept_Sales (SalesNumber, FromDept, ToDept, FromDeptTransCode, ToDeptTransCodem
FromDeptReasonCode, ToDeptReasonCode, FromDeptAuthorizationNumber, ToDeptAuthorizationNumber)
 values (@SalesNumber, @FromDept, @ToDept, @FromDeptTransCode, @ToDeptTransCodem
@FromDeptReasonCode, @ToDeptReasonCode, @FromDeptAuthorizationNumber, @ToDeptAuthorizationNumber)

Open in new window

0
dban00b
Asked:
dban00b
  • 2
1 Solution
 
dban00bAuthor Commented:
The code snippet is two examples of existing INSTEAD OF INSERT triggers on Views that combine the main sales table with the table for each individual type of sale.  And my front-end inserts into these views rather than into the real tables.
0
 
Daniel WilsonCommented:
>>This seems ridiculous/unfeasible as I type it

Well, ... I've looked at it a few times & don't see how to make it work.  Sorry!
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Hi,
  I was about to cleanup this question...
  ... when it came to my mind that you might consider taking the reverse angle.

  instead of having 1 procedure that get's called by each trigger,
  what about having some small helper app to generate the trigger code by the view definition?

just my 2 cents.
0
 
dban00bAuthor Commented:
That's actually already how I do it, or at least how I initially created all these.  A script spat out the trigger code for me to copy paste.    I was hoping I could find some way to make the code "self-maintain" with changes rather than having to edit and/or regenerated the code.

Oh well.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now