Dynamic SQL is the solution.

Generate the query in your sp and Execute it with EXEC.

I tried by the following code;

Declare @Sql varchar(max)

Set @Sql = 'Select * from ' + @TableName + ' tn

inner join tis_vs vs on tn.pnref=vs.pnref

and agentCode '

if @SubProducerFlag='Y' Set @Sql = @Sql + ' like ''' + @AgentCode + '%'''

if @SubProducerFlag !='Y' Set @Sql = @Sql + ' = ''' + @AgentCode + ''''

Set @Sql = @Sql + ' and convert(char(10), vs.txndatetime,126) between ''' + convert(char(10), @StartDate ,126) + '''

and ''' + convert(char(10), @EndDate , 126) + ''' order by vs.txndatetime '

Print @Sql

Exec @Sql

I hope it will help you. Remember that there are alot of issues regarding string processing like SQL injection.

Atif Sheikh