January 14, 2004 at 1:52 pm
Hello All,
Here's the scenario: a sproc definition with several parameters, all of them with default values, e.g.
create procedure ksp_MySproc @p1 = null, @p2 = null, @p3 = null as
sproc code, etc...
return
At some time in the distant past I thought I had seen a way to determine whether a parameter was either passed in on invocation with a value or had the default set, but I can't find this anywhere in BOL. Could be I was just kinda wishing for this ability at some point, I dunno.
Vik
January 14, 2004 at 2:36 pm
Use DBCC InputBuffer(@@SPID) within the stored procedure and it will tell you the exact query used to call the sp. You have to get a little tricky to be able to use the data it returns though. This is what I came up with:
Create Procedure dbo.TestSPID
@Var1 int = null,
@Var2 int = 1,
@Var3 int = null
As
Declare @Query varchar(1000)
Create Table #tmpSPIDCheck(EventType nvarchar(30),
Parameters int,
EventInfo nvarchar(255))
Insert Into #tmpSPIDCheck(EventType, Parameters, EventInfo)
Exec('DBCC INPUTBUFFER (' + @@SPID + ')')
Select @Query = EventInfo
From #tmpSPIDCheck
Drop Table #tmpSPIDCheck
-- Do stuff with @Query
Select @Query
Go
Then you can test out the results you get passing in the differnet parameters in different ways.
January 15, 2004 at 6:31 am
Depending on the situation using an IF or CASE maybe simpler.....
IF @p1 IS NULL PRINT 'PARAMETER_IS_NULL' -- No or NULL parameter passed
ELSE PRINT 'PARAMETER_VALUE = ' + @p1 --parameter has a value
January 29, 2004 at 11:41 am
Thanks for the replies, guys, will check things out.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply