Error concerned with storedprocedure

Question

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[rptGGM]
(
    @FromDate datetime,
    @ToDate datetime,
    @PartyName varchar(50),
    @Type int
)

As
DECLARE @WhrStr VarChar(500)

SET @WhrStr = 
    CASE @Type
    WHEN 1 THEN
     ' And E.ATAAir between '+ @FromDate +' And '+@ToDate

    WHEN 2 THEN
     ' And D.ATASea between '+ @FromDate+ ' And '+@ToDate
    END
exec
(
'SELECT 
      A.FileNumber As [File No],
      S.POString As [PO Numbers],
      G.PartyName As [Exporter Name],
      C.CargoDesc As [Cargo Description],
      Dbo.ActualDate(A.ETA) As ETA,
         Dbo.ActualDate(IsNull(E.ATAAir, D.ATASea)) As ATA,
     S.SIString As [Supplier Invoices],
D.VesselName As [Vessel Name],
      D.VoyageNo As [Voyage No],
     dbo.PackCntDetails(A.FileID) As [Pk/Cnt Details]

FROM
    FileMain A

LEFT JOIN SIPOString S ON S.FileID=A.FileID
LEFT JOIN Party G ON G.PartyID = A.ExporterID
INNER JOIN Cargo C ON C.FileID = A.FileID
LEFT JOIN FileSea D ON D.FileID = A.FileID
LEFT JOIN FileAir E ON E.FileID = A.FileID

WHERE   
     G.PartyName='+@PartyName +' '+@WhrStr


)

While executing above procedure following error is coming Msg 241, Level 16, State 1, Procedure rptGGM, Line 12 Conversion failed when converting datetime from character string. Can anybody to help to solve the error.

Answer 1

You have to quote the datetime values

...
WHEN 1 THEN
    ' And E.ATAAir between '''+ CAST(@FromDate AS varchar(30)) +''' And '''+CAST(@ToDate AS varchar(30)) + ''''

WHEN 2 THEN
    ' And D.ATASea between '''+ CAST(@FromDate AS varchar(30))+ ' And '''+CAST(@ToDate AS varchar(30)) + ''''
END
...

You'll also have the error later on near @partyname:

...
WHERE   
     G.PartyName='''+@PartyName +''' '+@WhrStr

Edit: Added CAST!

Answer 2

you need to do an explicit cast from datetime to varchar when constructing @WhrStr. The following should work:

CAST(@FromDate as varchar)

Answer 3

This would be a lot simpler as a straightforward query without the EXEC. Why did you wrap all this in the EXEC?

Answer 4

Using dynamic SQL is very bad practice, since it not only may fail because of conversion issues and data length, but also allows SQL injection.

Use a proper query like this:

SELECT 
         A.FileNumber As [File No],
         S.POString As [PO Numbers],
         G.PartyName As [Exporter Name],
         C.CargoDesc As [Cargo Description],
         Dbo.ActualDate(A.ETA) As ETA,
         Dbo.ActualDate(IsNull(E.ATAAir, D.ATASea)) As ATA,
        S.SIString As [Supplier Invoices],
D.VesselName As [Vessel Name],
      D.VoyageNo As [Voyage No],
        dbo.PackCntDetails(A.FileID) As [Pk/Cnt Details]

FROM
    FileMain A

LEFT JOIN SIPOString S ON S.FileID=A.FileID
LEFT JOIN Party G ON G.PartyID = A.ExporterID
INNER JOIN Cargo C ON C.FileID = A.FileID
LEFT JOIN FileSea D ON D.FileID = A.FileID
LEFT JOIN FileAir E ON E.FileID = A.FileID

WHERE   
     G.PartyName=@PartyName AND
     (
           ((@Type = 1) And (E.ATAAir Between @FromDate And @ToDate))
     Or
           ((@Type = 2) And (E.ATASea Between @FromDate And @ToDate))
     )