checking the value of a parameter passed to stored proc

[achmo]

hello,

I am building an asp site, which invokes stored procs on SQL Server. Is there a way to track from SQL Server the values of the parameters recieved by a stored procedure? (I have a parameter which seems not pass correctly)

Thank you,

Yael

 

[osjohnm]

Hi

You could try the following:

CREATE PROCEDURE dbo.GET_DATA @name varchar(50), @startdate datetime, @enddate datetime
AS

PRINT @name
PRINT @startdate
PRINT @enddate

---your procedure code
GO

-- test the variables
exec get_data 'John', '2002-11-29', '2002-12-03'

This will print the values in the variables into the Query Analyser results pane if you exec the proc in Query Analyser.

Hope this helps

John

 

[achmo]

Thanks, but I need something else - checking from within SQL Server the value of parameters passed to the sproc from outside (in this case, from an asp page). Is there some kind of a biult-in monitor for things like that?

Yael

 

[mwolf00]

On your asp side you can write each parameter's value....

set objCmd = server.createobject("adodb.command&quot
Set objCmd.ActiveConnection = objConn
with objCmd
.commandText = "sp_templateComplete"
.CommandType = adCmdStoredProc
.parameters(0).direction = adParamReturnValue
.parameters(1) = tempID
.parameters(2) = form1Val
.parameters(3) = personID
.parameters(4) = null

'For debugging only
response.write &quot;1 = &quot; & tempID & &quot;<br>&quot;
response.write &quot;2 = &quot; & form1Val & &quot;<br>&quot;
response.write &quot;3 = &quot; & personID& &quot;<br>&quot;
response.write &quot;3 = &quot; & .parameters(4).value & &quot;<br>&quot; 'I haven't tried this - May not work....
response.end

.Execute
End With
---------------------------------------
HAPPY THANKSGIVING!!!!
mikewolf@tst-us.com

 

[mwolf00]

Another option would be to create a table in your db and write the passed values to it from within your stored proc.

create table debug (
col1 varchar(50),
col2 varchar(50)
)


create proc dv_mySP (@inVal1 varchar(50), @inVal2 varChar(50))

AS
SET NOCOUNT ON

INSERT INTO debug VALUES (@inVal1, @inVal2)

--continue with your code.
---------------------------------------
HAPPY THANKSGIVING!!!!
mikewolf@tst-us.com

 

[achmo]

I have already checked the values on the asp side and they are ok. And when I execute the sproc directly from Query Analyzer with the correct values it also seems ok. So I wanted to trap what is beeing passed. I'll try the table sugestion.

Thanks!

Yael