Error Converting Data Type in Execute Procedure

Question

I'm trying to run an Execute procedure that will insert data into "Table A". For one specific parameter, I've set the parameter type to "Date" to match the data type of the "DateExample" column in "Table A". For some reason, the system is recognizing the field data type in the view data source "ViewExample" as nvarchar, even though the data type is date.

I tried setting the parameter to nvarchar but I received the error below:

Msg 241, Level 16, State 1, Procedure p_example, Line 34 [Batch Start Line 0]
Conversion failed when converting date and/or time from character string

Code:

ALTER Procedure [dbo].[p_example]
,   @DateExample date

AS 

begin

INSERT INTO [DatabaseEx].[dbo].[Table A]
           ([DateExample]

)

values (

    @DateExample


)

end

Select DISTINCT 
    D.DateExample
From DatabaseEx.dbo.ViewExample D

Where 1 = 1

GO



EXECUTE [dbo].[p_example]

    @DateExample = DateExample



GO

Expected results: data will insert into the table without error

Current results:

Msg 8114, Level 16, State 1, Procedure p_example, Line 0 [Batch Start Line 0]
Error converting data type nvarchar to date.

Answer 1

TL;DR at the end of the answer.

Below is the full answer and justification for my changes and suggested approach.

1. The long way around:

There are a couple of adjustments and comments I would make regarding your code:

As per below's definition, your stored procedure is supposed to get a single value of type 'date' as a parameter (so far so good)

ALTER Procedure [dbo].[p_example]
    @DateExample date
AS 
begin

Your insert query takes the @DateExample parameter and adds it to [Table A], so far so good

INSERT INTO [DatabaseEx].[dbo].[Table A]
           ([DateExample])
values (@DateExample)

end

This seems to be the part where you decide which value(s) to pass to the stored procedure. This is the place where I identify the first possible problem.

Select DISTINCT 
    D.DateExample
From DatabaseEx.dbo.ViewExample D
Where 1 = 1

Your SELECT DISTINCT query does not "define" a value stored in "DateExample" which you can later pass to the stored procedure. In order to store that value in a place where you can re-use it, you need to store it into a variable (at least).

So, I would modify the above query to:

DECLARE @DateExample date;

Select DISTINCT 
    @DateExample = D.DateExample
From DatabaseEx.dbo.ViewExample D
Where 1 = 1

However, this isn't everything. The above query will still fail if SELECT DISTINCT will return more than 1 value.

SELECT DISTINCT does not imply return single unique value, however it will return all unique values stored in the DateExample column from the DatabaseEx.dbo.ViewExample view.

If you do want to pass only 1 value from your query to your stored procedure or if you want to pass more than 1 values to your stored procedure, the below version will be better.

DECLARE @DateExampleTable TABLE (dateExample date);

INSERT INTO @DateExampleTable (dateExample)
Select DISTINCT 
    D.DateExample
From DatabaseEx.dbo.ViewExample D
Where 1 = 1

This will cover everything and you won't get any errors if your SELECT DISTINCT will return more than 1 row.

Now, you probably noticed that the variable name and type has changed from DATE to TABLE. This requires us to also changed the code of our stored procedure, so that it accepts this new type of parameter.

In order to pass more rows of data to our stored procedure, we will first have to create a new table type, which we will use to store multiple rows of data as a parameter so that we can later pass it to our stored procedures.

CREATE TYPE DateTableType AS TABLE (DateExample date);

ALTER Procedure [dbo].[p_example]
    @DateExampleParam DateTableType READONLY
AS 
begin

INSERT INTO [DatabaseEx].[dbo].[Table A]
           ([DateExample])
SELECT DateExample
FROM @DateExampleParam 

end

Now that we have our new version of the stored procedure,

EXECUTE [dbo].[p_example]
    @DateExampleParam = @DateTableExample

---

2. TL;DR Summary below:

Now, if we were to summarize everything I just wrote into a single refactored script, it would look like this:

CREATE TYPE DateTableType AS TABLE (DateExample date);
GO    

ALTER Procedure [dbo].[p_example]
    @DateExampleParam DateTableType READONLY
AS 
begin

INSERT INTO [DatabaseEx].[dbo].[Table A]
           ([DateExample])
SELECT DateExample
FROM @DateExampleParam 

end
GO

DECLARE @DateExampleTable TABLE (dateExample date);

INSERT INTO @DateExampleTable (dateExample)
Select DISTINCT 
    D.DateExample
From DatabaseEx.dbo.ViewExample D
Where 1 = 1

EXECUTE [dbo].[p_example]
    @DateExampleParam = @DateExampleTable