Solved

Stored Procedure Create Variable based on SELECT resultset

Posted on 2008-10-07
4
1,693 Views
Last Modified: 2010-08-05
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
Comment
Question by:dban00b
  • 2
4 Comments
 
LVL 1

Author Comment

by:dban00b
Comment Utility
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
 
LVL 32

Expert Comment

by:Daniel Wilson
Comment Utility
>>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
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
Comment Utility
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
 
LVL 1

Author Comment

by:dban00b
Comment Utility
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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now