>>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