Not sure that distinct will work without a group by... this may work better :

Select Week_number, sum(Total) from dbo.YourTable group by week_number

BTW, why r u keeping the week number...

Declare @Output as datetime

set @Output = '2005-06-13'

exec TrendPeakTotal '11', '2000-01-01', @Output output

Select @Output

HTH, again .

You tell me. I can't see what's the result of the execution.

Create proc .... @UpdateDate output = GetDate()

then supply a null value when you create this parameter... You should be fine this fine.

The temp table #Test must be created before you can run this code.

Did you copy our exemples exactly?

Did you forget to swap the table names?

Also note that it's a best pratice to always specify the target columns.

Insert into #mat_samples (col1, col2, id) select col1, col2, id from mat_sample where...

Anybody wanna suggest another approach than dynamic sql to this problem??

The Curse and Blessings of Dynamic SQL

We can't provide a solution when none exists... we often have to resort to such workarounds.

I've never found any way around that. At least the default still works and once it's coded you don't have any more work to do.

It's already been posted in the previous messages... even if it's not crystal clear .

It's not the same behavior with functions... you must specify that you want the default parameter, like this :

Select * from dbo.Fn_myDate(default)

Can you run the profiler and see what query is ran when that happens?

Nothing... I thaught you still needed help.

Nice work.