General information
Forum:
Microsoft SQL Server
Category:
Stored procedures, Triggers, UDFs
Environment versions
SQL Server:
SQL Server 2000
Hi Kevin,
Thanks for the reply.
I am looking for 7 fields for update, I put the values in variables depending on which field or fields are updated like
if update(ICN)
set @a = 1
if update(processid)
set @b = 2
if update(provstatus)
set @c = 4
Set @d = @a + @b + @c
Now @d can tell if one field or two fields or all three fields or 1st and 3rd field etc. are updated. To capture all scenerios, I have written like ----
if (@d == 1)
Begin
Set @CColumn_name = 'ICN'
exec dbo.Update_Hist @CColumn_name, @dLastUpdate
End
else
if (@d = 2 )
Begin
Set @CColumn_name = 'processid'
exec Update_Hist @CColumn_name, @dLastUpdate
End
else
if (@d = 4)
Begin
Set @CColumn_name = 'provstatus'
exec Update_Hist @CColumn_name, @dLastUpdate
End
if (@d = 3)
Begin
Set @CColumn_name = 'ICN'
exec Update_Hist @CColumn_name, @dLastUpdate
Set @CColumn_name = 'processid'
exec Update_Hist @CColumn_name, @dLastUpdate
End
----------so instead of writing the insert query for each scenerio, I have written a common stored procedure out side which accepts variables depending on the value of @d. Since inserted and deleted tables are not known in the Update_Hist stored procedure, I can pass the old and new value from these temp tables also as parameters in the stored procedure.
Why I am doing it like this because I am not good in writing scripts.
Regards
bharat
Previous
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only