How to get the output parameter from an internally called stored procedure??

Hi all,
i have a problem and couldnt find anything even close to it. please help me, here is the description of what i m trying to accomplish:
I have a trigger that is generating a column value and calling a stored procedure after the value is generated. And this stored procedure is setting this generated value as an output parameter. But my problem is:
my asp.net page is only sending an insert parameter to the table with the trigger, trigger is running some code depending on the insert parameter and calling this other stored procedure internally. So basically i m not calling this last stored procedure that sets the output parameter within my web form. How can i get the output parameter in my webform? Everthing is working now, whenever an insert hits the table trigger runs and generates this value and called stored procedure sets it as an output parameter. I can get the output parameter with no problem in query analyzer, so the logic has no problem but i have no idea how this generated output parameter can be passed in my webform since its not initiated there.
any help will greately be appreciated, i m sure asp.net and sql server 2000 is powerful and flexible enough to accomplish this but how??
-shane

Try these links the last link is a tool that will generate the code for you but read the articles first so you know what you are doing. Hope this helps.
http://msdn.microsoft.com/msdnmag/issues/05/05/DataPoints/default.aspx

http://www.codeguru.com/vb/gen/vb_database/sqlserver/article.php/c8671__3/

http://www.adonetexpress.com/default.aspx

|||thanks for the links, there are great information there but my problemis not with calling stored procedures within a page or creating astored procedure. my problem is getting the output parameter of astored procedure that is called within a trigger. From my understandingthis is not possible but i wanted to ask and make sure anyway.
thank you,
-shane

|||Can you post a sample code to demonstrate the issue? No need to post it all, if it is huge but something that would ease up reproing it.|||Hi,
i dont have the full code here but its something similat to below (ignore syntax)
my trigger:
instead of insert
as
declare @.ID int
select @.ID = select ID from inserted
begin
update mytable(mycolumn) --if the inserted parameter(@.ID) exists mycolumn will be updated
select mycolumn=mycolumn + 1
where mytable.myid in (select ID from inserted)
insert into mytable(mycolumn) -- if the inserted parameter doesnt exists it will be inserted with a mycolumn=0
values(0)
where mytable.myid not in (select ID from inserted)
end
myprocedure(@.ID)
go
the above trigger inserts a new row with mycolumn=0 to mytable if theinput parameter doesnt exists in mytable, if it exists instead of aninsert an update fires and the value of mycolumn increments one. Afterinsert/update finished i m calling myprocedure which will set the newvalue of mycolumn as an output parameter.
myprocedure
(@.ID int,
@.mycolumn int output)
select @.mycolumn=mycolumn from mytable
where ID = @.ID
please ignore the syntax mistakes, the actual code is working with no problem.
What i m trying to do is run an insert procedure or sql query with aparameter(@.ID) and get the output parameter which is @.mycolumn. Butthis mycolumn will be set to its final value after the trigger runs.
Is it possible?
thanks,
-shane
|||I am assuming this is what you need, try this link for the code to an undocumented stored proc calledsp_executesql. Hope this helps.
http://support.microsoft.com/default.aspx?scid=kb;en-us;262499|||It looks like something i might need, i will test it asap.
Any idea how to get the output parameter created/kept by sp_execuresql into my asp.net page?
my understanding is sp_executesql will call my stored procedure andkeep its output parameter as an output parameter but i still dont knowhow to use this parameter in my page.
thank you,
-shane
|||Run a search for "Using sp_executesql" in the BOL (books online) it shows some examples of what you can do but there is no ADO.NET code. Hope this helps.|||thank you,
thats what i ll do. thanks again for the link
-shane
sql