Incorrect syntax near 'sp_executesql'

Question

I don't understand why the following is giving me the error. I thought it was related to the commented out section, but @SQL is nvarchar(4000).

BEGIN
  sp_executesql N'SELECT ''td'''
  --sp_executesql @SQL, N'@StartDate DateTime, @EndDate DateTime, @End2 DateTime, @Program varchar(4)', @StartDate, @EndDate, @End2, @Program
END

Brigette

Answer 1

This is why:

-- This works just fine:
BEGIN
  -- You must have an exec before your sp_executesql or it will not work in a block
  exec sp_executesql N'SELECT ''td'''
END

You can't just call a stored proc without an exec when you are in a block.

Converter

You can call a stored proc without and exec, but only when the call to the stored proc is the only statement in the block. – Coenocyte

it has to be the First statement, not necessarily the only one. – Pennate

Answer 2

Why do you have this enclosed in a BEGIN ... END? Running the sp_executesql external the block will work.

Optionally you can put an exec before sp_executesql.

Quartile

Answer 3

On some occasions I had to use master as well:

exec master..sp_executesql 

Wrens