Sql-server – CREATE FUNCTION inside BEGIN TRY and END TRY

The first error is on Line 5, telling you that you cannot use Functions in your parameter's default values.

Pass in NULL as the default value, then sort the parameters out inside the Stored Procedure:

CREATE PROCEDURE dbo.Test
(
  @StartDate As datetime = NULL 
  ,@EndDate As datetime = NULL 
)
AS

BEGIN

-- If you're using SQL Server 2008 or greater use DATEADD(DAY, -8, CAST(GETDATE() AS DATE))
IF @StartDate IS NULL
  SET @StartDate = DATEADD(DAY, DATEDIFF(DAY, 0, getdate()-8), 0);

-- If you're using SQL Server 2008 or greater use DATEADD(DAY, -1, @StartDate)
IF @EndDate IS NULL
  SET @EndDate = DATEADD(DAY, DATEDIFF(DAY, 0, @startdate -1), 0);


END

Once you have fixed this issue, and capitalise the @startDate parameter to read @StartDate, you should be fine.

For reference, there's an open Connect Item (currently 7 years old) that is Microsoft acknowledging it's on their 'ToDo' list.

@id as part of the execution variable @sql is doing nothing. It is not tied to the declared and set variable unless you build the string around it, i.e. concatenate it to the string like this:

set @sql = 'insert into #temphold(dateadded,dateupdated,id) ' +'select getdate(),getdate(),COALESCE(' + @id + ','''') '

Notice the + either side of the @id variable.

At the end of the day, @sql is just a string until it's executed using the EXEC() command. Simply treat it as such until it compiles as fully qualified T-SQL.