sql server - Setting variables in SQL functions/probs

Question

Assume I have a normal SQL procedure which has a few arguments.

During debugging it would be nice if i could assign some values to these arguments so that I can just highlight the body of the proc and execute it (as opposed to manually replace the variable with values.

Is there any way to do this? I tried:

@Date1 datetime,
@Date2 datetime

SET @Date1 = '2012-03-23'

but it doesn't like it??

Answer 1

If you're going to do this, I suggest you consider adding a @Debug parameter to your procedures:

create procedure dbo.SomeProc @p1 int, @p2 int, @Debug bit = 0x0
as
set nocount on
begin

if @Debug = 0x1 -- set test values only if debugging
begin
print 'Start debugging'
set @p1 = 1
set @p2 = 2
end

/* your code continues here... */

end

Then when you want to test your code, just execute the procedure with @Debug = 0x1 to execute the debugging code.