SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Case Statement to call Stored Procedure
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

PBoy
Starting Member

United Kingdom
17 Posts

Posted - 11/03/2012 :  15:49:14  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 11/03/2012 :  22:25:13  Show Profile  Reply with Quote
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/

Go to Top of Page

PBoy
Starting Member

United Kingdom
17 Posts

Posted - 11/04/2012 :  08:54:56  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 11/04/2012 :  11:28:40  Show Profile  Reply with Quote
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/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000