CODEWITHSUNDEEP
sqlsql-serverwinforms
Riain McAtamney
Riain McAtamney

Reputation: 6432

sp_executesql vs manually executing gives different results

I'm calling a stored procedure and passing in 2 dates as parameters from my windows application. Its returning all rows rather than 2 rows that I'm expecting.

The stored procedure is:

ALTER procedure [dbo].[Get_Entries]
    @Start_Date datetime=null,
    @End_Date datetime=null
as
begin
    SELECT *
    FROM MyTable
    WHERE (MyTable.Date BETWEEN @Start_Date AND @End_Date 
       OR (@Start_Date IS NULL AND @End_Date IS NULL)) 
    ORDER BY MyTable.Date desc
end

The following sp_executesql query returns all rows:

exec sp_executesql N'Get_Entries', N'@Start_Date datetime, @End_Date datetime',
                   @Start_Date='2015-06-06 11:35:06.437',
                   @End_Date='2015-07-06 11:35:06.437'

However if I run the stored procedure manually from Management Studio I get the expected 2 rows:

USE [MyDatabase]
GO

DECLARE @return_value int

EXEC    @return_value = [dbo].[Get_Entries]
        @Start_Date = N'2015-06-06 11:35:06.437',
        @End_Date = N'2015-07-06 11:35:06.437'

SELECT  'Return Value' = @return_value
GO

Any ideas why sp_executesql isn't returning the filtered list? Its returning all rows.

Upvotes: 2

Views: 1413

Answers (3)

Paul Williams
Paul Williams

Reputation: 17030

Let's take a look at your sp_executesql statement:

exec sp_executesql N'Get_Entries',
    N'@Start_Date datetime, @End_Date datetime',
    @Start_Date='2015-06-06 11:35:06.437',
    @End_Date='2015-07-06 11:35:06.437'

This query tells SQL Server to execute the following query:

'Get_Entries'

The way you are invoking sp_executesql says the query uses the following parameters:

'@Start_Date datetime,@End_Date datetime'

However, the query text string 'Get_Entries' does not use these parameters. Therefore, SQL Server will not put the parameters into the query. The result query is equivalent to the following code:

exec Get_Entries

Without specifying any parameters, your stored procedure will return all rows.

To use the parameters, you need to place them in your dynamic SQL query like below. I renamed the dynamic SQL parameters to make it clearer where they are used in the query:

exec sp_executesql N'Get_Entries @Start_Date = @StartDateParm, @End_Date = @EndDateParm',
    N'@StartDateParm datetime, @EndDateParm datetime',
    @StartDateParm='2015-06-06 11:35:06.437',
    @EndDateParm='2015-07-06 11:35:06.437'

Note that you don't need to put a stored procedure call in a call to sp_executesql. It is more efficient to call the procedure directly.

Upvotes: 1

Abdul Waheed Khan
Abdul Waheed Khan

Reputation: 51

In your case values are not passed to stored procedure and it runs with default values.

If you remove the default values in your stored procedure you will get following error: Procedure or function 'Get_Entries' expects parameter '@Start_Date', which was not supplied.

sp_executesql is used to make a code reusable. Using sp_executesql to execute stored procedure gives no benefit.

sp_executesql executes a Transact-SQL statement or batch that can be reused many times, or that has been built dynamically.

You can find a way around using code given below

Declare @statement nvarchar(max)
set @statement = N'Get_Entries ''2010-06-06 11:35:06.437'', ''2015-07-06 11:35:06.437'''
exec sp_executesql @statement

Upvotes: 1

marc_s
marc_s

Reputation: 754963

The way to solve this is to use the (slightly adapted) ISO-8601 date format that is supported by SQL Server - this format works always - regardless of your SQL Server language and dateformat settings.

The ISO-8601 format is supported by SQL Server comes in two flavors:

  • YYYYMMDD for just dates (no time portion); note here: no dashes!, that's very important! YYYY-MM-DD is NOT independent of the dateformat settings in your SQL Server and will NOT work in all situations!

or:

  • YYYY-MM-DDTHH:MM:SS for dates and times - note here: this format has dashes (but they can be omitted), and a fixed T as delimiter between the date and time portion of your DATETIME.

This is valid for SQL Server 2000 and newer.

If you use SQL Server 2008 or newer and the DATE datatype (only DATE - not DATETIME!), then you can indeed also use the YYYY-MM-DD format and that will work, too, with any settings in your SQL Server.

Don't ask me why this whole topic is so tricky and somewhat confusing - that's just the way it is. But with the YYYYMMDD format, you should be fine for any version of SQL Server and for any language and dateformat setting in your SQL Server.

My recommendation for SQL Server 2008 and newer is to use DATE if you only need the date portion, and DATETIME2(n) when you need both date and time. You should try to start phasing out the DATETIME datatype if ever possible

Upvotes: 3

Related Questions