sql server - Dates and Dynamic SQL error message -

using ms sql 2012.

i trying query set of tables using dynamic sql , passing through tablename, start date in format of dd/mm/yyyy , end date in format of dd/mm/yyyy.

my code follows.

@tablename nvarchar(50), @startdate date, @enddate date   begin  set nocount on;  declare @query varchar(max) set @query = 'select * ''' + @tablename + ''' convert(date, docdate, 103) >= ''' + cast(@startdate varchar(50)) + ''' , convert(date, docdate, 103) <= ''' + cast(@enddate varchar(50)) + ''  exec @query  end 

the docdate field has data type of date , in format of yyyy-mm-dd.

i following error when run stored procedure.

incorrect syntax near '/'.

what missing?

update

i testing query following entries variables , still same error.

use [testdbs] go  declare @return_value int  exec    @return_value = [dbo].[getresultset]         @tablename = prices,         @startdate = 01/02/2016,         @enddate = 20/02/2016  select  'return value' = @return_value  go 

you should use sp_executesql , pass proper date parameters query, , avoid need casting dates strings @ all:

create procedure dbo.yourprocedure     @tablename sysname,     @startdate date,     @enddate date begin     declare @query nvarchar(max)     set @query = 'select * ' + quotename(@tablename) +                  ' docdate >= @startdateparam                  , docdate <= @enddateparam';      execute sp_executesql                      @query,                      n'@startdateparam date, @enddateparam date',                      @startdateparam = @startdate,                      @enddateparam = @enddate;  end 

i have made couple of other minor tweaks too:

1. change datatype of @tablename nvarchar(50) sysname (synonym `nvarchar(128), maximum length of object name)
2. change data type of @query nvarchar(max) since type sp_executesql expects.
3. wrapped @tablename quotename ensure special characters not cause error.
4. if docdate date, explicit convert not necessary have removed this.

---

addendum

you may wish add validation table name:

-- check table name valid table of view if isnull(object_id(@tablename, 'u'), object_id(@tablename, 'v')) null begin     -- handle invalid name     return; end 

edit

i have changed procedure cater sending through schema qualified table name

create procedure dbo.getresultset     @tablename sysname,     @startdate date,     @enddate date begin     declare @query nvarchar(max)     set @query = 'select * ' +                          quotename(object_schema_name(object_id(@tablename))) + '.' +                  quotename(object_name(object_id(@tablename))) +                 ' docdate >= @startdateparam                  , docdate <= @enddateparam';      execute sp_executesql                      @query,                      n'@startdateparam date, @enddateparam date',                      @startdateparam = @startdate,                      @enddateparam = @enddate;  end 

then use execute it:

set dateformat dmy; declare @return_value int  exec    @return_value = [dbo].[getresultset]         @tablename = 'dbo.prices',         @startdate = '01/02/2016',         @enddate = '20/02/2016'  select  'return value' = @return_value; 

or better still use culture insensitive date format (yyyymmdd) literals:

declare @return_value int  exec    @return_value = [dbo].[getresultset]         @tablename = 'dbo.prices',         @startdate = '20160201',         @enddate = '20160220'  select  'return value' = @return_value;