>>>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.
If it's not broken, fix it until it is.
My Blog