Case Statement to call Stored Procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Case Statement to call Stored Procedure

Author

Topic

PBoy
Starting Member

22 Posts

Posted - 2012-11-03 : 15:49:14

Hi All,

This maybe a silly questions but I have a stored procedure with a parameter that is executed via a trigger what I want to do is have the trigger work out what value to pass and execute the SP with.

Here is the trigger

ALTER TRIGGER ubrc_update_trees_pm
ON dbo.agldimvalue
FOR INSERT
AS
IF EXISTS (SELECT attribute_id FROM inserted WHERE client = 'RC' and attribute_id IN ('C1','B0'))
BEGIN
EXEC dbo.sp_brc_update_trees_pm '23'
END
go

rather than the value being 23 I would like something like a case statement working out what value to pass over like below.

attribute_id = C1 pass 23
attribute_id = B0 pass 15
attribute_id = GN pass 2

and so on is this just a simple case statement after the EXEC dbo.sp_brc_update_trees_pm?

Just want to check whats the best way of doing this

Cheers
Patrick

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-11-03 : 22:25:13

why do you need to call a proc inside trigger? whats proc doing? I dont think this is a good thing to do?
Can attributeid be passed on to it as a parameter and CASE logic be written inside?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

PBoy
Starting Member

22 Posts

Posted - 2012-11-04 : 08:54:56

Thanks visakh16 for your reply. Basicly the SP is a cursor that update/inserts a record into a table that is used on reports.

Our helpdesk guys create new master file records when required (C1 and B0 etc) instead of us (systems guys) updating the reporting table manually (as we done trust the helpdesk guys to do it correctly) I was looking at away to create the record as soon as they have create a new master file. I did not want to have a cursor in the trigger so just wanted it to be kicked off and run as another process. The SP its self is inserting the new records and updating a counter table as the same time.

Not sure if that makes any more sense ?

Cheers
Patrick

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-11-04 : 11:28:40

do they create it always single row at a time? even then what you could do is to capture the records and do manual oading in a set based manner based on a frequency unless you're looking for a real time reporting solution. you could wrap all the logic in a single procedure for maintainability. using trigger for implementing business logic can be a pain to maintain moving forward.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources