Stored Proc Paramerter Values at Runtime

Question

Stored Proc Paramerter Values at Runtime

yaweah

Default port

Points: 1489
More actions
February 26, 2004 at 1:44 pm

#109064

Hello all. This is a quick question. Does anyone know if SQL server stores Stored Proc Parameter Values in a table some where in SQL Server? Is it does, when table is it using to store the parameter values. Can these values be retrived outside of the stored proc? Thanks in advance.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

Not quite sure if I understand you, but I guess the answer is NO. Parameter values of a stored procedure can be retrieved by using the OUTPUT keyword as explained in BOL under output parameters -> Returning Data Using OUTPUT Parameters

Is this going your direction?

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

noeld SSC Guru Points: 96590 More actions · Answer 2

you may do something from a trigger at the table level Like:

DBCC INPUTBUFFER(@@SPID) to determine what was run but only the first 255 char will be returned

is that what you are looking for?

* Noel

Jeremy Huppatz SSC-Addicted Points: 420 More actions · Answer 3

The syscomments table in each database stores (amongst other things) the full text of each SQL Server stored procedure.

As I understand it the parameter signiatures for a given stored proc are stored in memory the first time it's run - either since the last SQL Shutdown or since the proc is recompiled.

However, I believe this information is stored in heap memory, so without a stackwalker I don't believe it would be possible to extract easily.

For the record, SQL Server Books Online has great commentaries on this kind of information if you're willing to delve deeply enough.

Regards

Jeremy Huppatz

SQL Server DBA

EDS Adelaide Solutions Centre

jeremy.huppatz@eds.com

yaweah Default port Points: 1489 More actions · Answer 4

Thanks to everyone for you response. I think the DBCC statements suggested by noeld. I get exactly what i want but the data is not in a relation format but i will deal with that myself. Thanks for you help.

noeld SSC Guru Points: 96590 More actions · Answer 5

yaweah , Keep in mind that you are going to put stress on the activity in the table. I would recomend you to Insert those result in a stagging table timestamped and do some post processing outside of the trigger to minimize the impact

HTH

* Noel