>>>>Hi All,
>>>>
>>>>I need some help with an sql stored procedure. The procedure listed here passes a parameter to the Table Name and works as it should. What I also want to be able to do is to add a WHERE clause that passes two datetime parameters to filter the results to return rows that have a date field between the datetime parameters. I cannot seem to get thw where clause to work. Any help would be appreciated.
>>>>
>>>Jim,
>>>
>>>Try
>>>
>>>
>>>ALTER PROCEDURE [dbo].[GetWellData]
>>> -- Add the parameters for the stored procedure here
>>> @Tablename sysname,
>>> @StartDate datetime,
>>> @EndDate datetime
>>>AS
>>>BEGIN
>>> -- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
>>> SET NOCOUNT ON;
>>> SET @TableName = RTRIM(@TableName)
>>>
>>> Declare @cmd AS NVARCHAR(max)
>>> DECLARE @ParmDefinition NVARCHAR(500)
>>> SET @ParmDefinition = N'@InnerStartDate datetime ,@InnerEndDate datetime'
>>>
>>> SET @cmd = N'Select * from ' + QUOTENAME(@TableName) + ' where DateField between @StartDate and @EndDate',
>>>
>>> exec sp_executesql @cmd, @ParmDefinition,
>>> @InnerStartDate = @StartDate,
>>> @InnerEndDate = @EndDate
>>>END
>>>
>>
>>
>>Thanks Naomi,
>>
>>I typed in you suggestion and received the following errors:
>>
>>Msg 102, Level 15, State 1, Procedure GetWellData, Line 9
>>Incorrect syntax near '@StartDate'.
>>Msg 137, Level 15, State 2, Procedure GetWellData, Line 15
>>Must declare the scalar variable "@TableName".
>>Msg 137, Level 15, State 2, Procedure GetWellData, Line 22
>>Must declare the scalar variable "@TableName".
>>Msg 137, Level 15, State 2, Procedure GetWellData, Line 25
>>Must declare the scalar variable "@StartDate".
>>
>>
>>I'm sure it is somethinf simple, but I just can't see it.
>>
>>Jim
>
>I didn't look too close yet, but looks like there is an extra comma at the end of cmd line - not sure how did it get there.
Right, I took out the extra commas, and it saved successfully, but when I tried to run it, I get this:
Must declare the scalar variable "@StartDate".