|
|
|
SSC-Addicted
 
Group: General Forum Members
Last Login: Sunday, November 15, 2009 10:32 PM
Points: 414,
Visits: 2,254
|
|
|
|
|
|
SSC Journeyman
Group: General Forum Members
Last Login: Tuesday, November 10, 2009 5:25 AM
Points: 80,
Visits: 150
|
|
Thank you for your article Jacob. It is very useful. I read your other articles and I am impressed by your ability to explain things concisely and clearly. Kind regards,
Vincent
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Wednesday, August 06, 2008 10:55 AM
Points: 7,
Visits: 14
|
|
| Nice Article with clear explanation
|
|
|
|
|
SSC-Addicted
Group: General Forum Members
Last Login: Sunday, November 15, 2009 10:32 PM
Points: 414,
Visits: 2,254
|
|
|
|
|
|
Grasshopper
Group: General Forum Members
Last Login: 2 days ago @ 2:22 AM
Points: 12,
Visits: 62
|
|
Hi Jacob
Nice and well written. You can also pass the data via temporary tables i.e. create the temp table in the caller proc (or prior) and refer to it in the callee proc. I prefer your method, however.
Kind regards
Alun
|
|
|
|
|
SSCarpal Tunnel
Group: General Forum Members
Last Login: Today @ 4:18 AM
Points: 4,023,
Visits: 5,814
|
|
Bad effort because of lack of knowledge.
From BOL:
A trigger is a special type of stored procedure that is not called directly by a user. When the trigger is created, it is defined to execute when a specific type of data modification is made against a specific table or column.
So, create a view. It may be real view or a dummy one:
CREATE VIEW MyView
AS
SELECT
CAST(NULL as int) as Col1,
CAST(NULL AS nvarchar(50) as Col2,
etc.
Create your SP as a INSTEAD OF INSERT trigger on this view.
Perform insert into the view.
Here it is: table passed to stored procedure as system table "inserted".
Works quickly, effectively, in both versions of SQL Server.
|
|
|
|
|
Ten Centuries
Group: General Forum Members
Last Login: Today @ 11:37 AM
Points: 1,474,
Visits: 2,055
|
|
Has anyone tested to see if doing the XML thing is more or less costly than using a temporary table?
Best,
TheSQLGuru
SQL Server MVP
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: Sunday, September 14, 2008 11:18 AM
Points: 121,
Visits: 26
|
|
Question for Sergiy, What if my View is a compund View. Can we update a compund view?
|
|
|
|
|
SSChasing Mays
Group: General Forum Members
Last Login: Today @ 10:30 AM
Points: 638,
Visits: 1,956
|
|
A view is an interesting solution that I haven't seen before. I suspect it probably has as much (if not more) overhead than passing XML, plus an additional effort in creating the table / view / trigger (but obviously no XML code to write). Does anyone have information on how the two approaches scale up?
|
|
|
|
|
Grasshopper
Group: General Forum Members
Last Login: Thursday, October 09, 2008 6:02 AM
Points: 16,
Visits: 2
|
|
I don't think Jacob is the one suffering from a lack of knowledge, Sergiy. What happens when two different users simultaneously call the same stored procedure? Kaboom, that's what happens. And if you think that scenario is unlikely, you haven't worked on OLTP systems.
Scott Gammans
scottgammans.com
|
|
|
|
