• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 77
  • Last Modified:

Stored Procedure error

Hello,
I am getting this error on the below SP:

Error :System.Data.SqlClient.SqlException (0x80131904): The conversion of a nvarchar data type to a datetime data type has created a value out of range.

The date I am passing is '23/10/1999'
 @Date nvarchar(20)= NULL

INSERT INTO DEBUG(SQL) VALUES ( 'UPDATE [Dtable]
            (
                  [RequestedBy],
                  [Ref],
                  [Date],
                  [Auth],                  
                  [PickUpTime],
                  [PatientName],
                  [PickUpAddress],
                  [DoctorClinic],
                  [Address],
                  [Comments],
                  [Interpreter],
                  [CarRequest],
                  [SpecialInstructions],
                  [FaxAuth],
                  [MedicalReportRecieved],
                [EnteredBy],
                  [Attachments],
                [Title],
                  [FaxNumber],
                  [Attn],
                  [ReqBy],
                  [Time]
                  
            )
            VALUES
            (
                  CAST(''' +@RequestedBy + '''  AS  Nvarchar(255)),
                  CAST('''+@Ref +''' AS NVARCHAR(255)),      
                CAST('''+ convert(nvarchar(25), @Date, 121) + '''  AS  varchar(25)),            
                  CAST('''+@Auth +''' AS Nvarchar(255)),                              
                  CAST('''+@PickUpTime +''' AS Nvarchar(255)),
                CAST('''+@PatientName +''' AS Nvarchar(255)),
                CAST('''+@PickUpAddress +''' AS Nvarchar(255)),
                  CAST('''+@DoctorClinic +''' AS Nvarchar(255)),
                  CAST('''+@Address +''' AS Nvarchar(255)),
                  CAST('''+@Comments +''' AS Nvarchar(255)),
                  CAST('''+@Interpreter +''' AS Nvarchar(255)),
                  CAST('''+@CarRequest +''' AS Nvarchar(255)),
                  CAST('''+@SpecialInstructions +''' AS Nvarchar(255)),
                  CAST('''+@FaxAuth +''' AS Nvarchar(255)),
                  CAST('''+@MedicalReportRecieved +''' AS Nvarchar(255)),
                  CAST('''+@EnteredBy +''' AS Nvarchar(255)),
                  CAST('''+@Attachments +''' AS Nvarchar(255)),
                  CAST('''+@Title +''' AS Nvarchar(255)),
                  CAST('''+@FaxNumber +''' AS Nvarchar(255)),
                  CAST('''+@Attn +''' AS Nvarchar(255)),
                  CAST('''+@ReqBy +''' AS Nvarchar(255)),
                  CAST('''+@Time +''' AS Nvarchar(255)),
                    WHERE [Dtable_UID] =''' + @Dtable_UID + '''')
0
RIAS
Asked:
RIAS
  • 22
  • 15
  • 7
  • +1
2 Solutions
 
ste5anSenior DeveloperCommented:
Pass in the date in an unabiguous way like '19991023', then you don't need the CONVERT call. Your hitting an implicit conversion and I guess your default date format is not DMY. See also SET DATEFORMAT.
0
 
lcohanDatabase AnalystCommented:
the error tells you that you have a "varchar" string which is NOT a valid datetime so you can find that if you run a select like:

SELECT ISDATE('23/10/1999');

where you will see that what you pass inc is not a valid date.
0
 
RIASAuthor Commented:
CAST('''+ convert(nvarchar(25), @Date, 121) + '''  AS  nvarchar(25)),       

Date is of datatype  datetime
Any suggestion on  convert(nvarchar(25), @Date, 121)
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Éric MoreauSenior .Net ConsultantCommented:
You need to be very cautious when playing with dates in SQL. The format is really important. Your D/M/Y format is 103. I have done some conversion and that correctly converts:
declare @D varchar(20) =  '23/10/1999'
DECLARE  @Date DATETIME

SELECT @Date, @d
SET @Date = convert(DATETIME, @D , 103)
SELECT @Date, @d

SELECT CAST(convert(nvarchar(25), @Date, 121) AS  varchar(25))
select 'UPDATE [Dtable]
            (
                  [RequestedBy],
                  [Ref],

            VALUES
            (
                CAST('''+ convert(nvarchar(25), @Date, 121) + '''  AS  varchar(25)),            
'

Open in new window

0
 
RIASAuthor Commented:
Because it works on datatype field Date but, fails on datetime datatype
0
 
RIASAuthor Commented:
Eric Thanks will try and get back
0
 
Éric MoreauSenior .Net ConsultantCommented:
The Date datatype was introduced with SQL 2008. I see that your topics are listing SQL 2005. If you are trying to use Date with SQL 2005, it will just fail. You need to use DateTime for this older version.
0
 
RIASAuthor Commented:
Eric i am using sql server 2014
0
 
Éric MoreauSenior .Net ConsultantCommented:
>>Eric i am using sql server 2014

adding sql 2005 and 2008 to your topics is misleading!
0
 
RIASAuthor Commented:
Will take note of this.Wanted to add Sql server.
0
 
RIASAuthor Commented:
Still the same:


      
DECLARE  @DateD DATETIME =null
SET @DateD = convert(DATETIME, @Date , 103)




                  INSERT INTO Dtable(SQL) VALUES ( 'INSERT INTO [DIARY]      
                  [RequestedBy],
                  [Date],
                  [Ref],                  
                  [Auth],                  
                  [PickUpTime],
                  [PatientName],
                  [PickUpAddress],
                  [DoctorClinic],
                  [Address],
                  [Comments],
                  [Interpreter],
                  [CarRequest],
                  [SpecialInstructions],
                  [FaxAuth],
                  [MedicalReportRecieved],
                [EnteredBy],
                  [Attachments],
                [Title],
                  [FaxNumber],
                  [Attn],
                  [ReqBy],
                  [Time]
                  
            )
            VALUES
            (
                  CAST(''' +@RequestedBy + '''  AS  Nvarchar(255)),
                CAST('''+ convert(nvarchar(25), @DateD, 121) + '''  AS  nvarchar(25)),
0
 
ste5anSenior DeveloperCommented:
Have you tried my suggestion?
0
 
Éric MoreauSenior .Net ConsultantCommented:
how and where is declared @Date ?
0
 
RIASAuthor Commented:
 @Date nvarchar(20)= NULL
0
 
RIASAuthor Commented:
I remeber it had worked before.Don.t know suddenly it has errored
0
 
RIASAuthor Commented:
ste5an,

The inputs to the Sp are coming from vb.net project.
So can't really change the format of date .But can change the SP to suit the purpose
0
 
Éric MoreauSenior .Net ConsultantCommented:
again, can you please give us more context? We are really trying to help but just having a single that does not even compile!
0
 
RIASAuthor Commented:
Ok will paste the entire SP
0
 
Éric MoreauSenior .Net ConsultantCommented:
you surely have some other code causing an issue because I have this code running:

declare @Date nvarchar(20) =  '23/10/1999'


DECLARE @RequestedBy NVARCHAR(255) = 'test'

DECLARE  @DateD DATETIME =null
SET @DateD = convert(DATETIME, @Date , 103)

SELECT 'INSERT INTO [DIARY]      
                  [RequestedBy],
                  [Date],
                  [Ref],                  
                  [Auth],                  
                  [PickUpTime],
                  [PatientName],
                  [PickUpAddress],
                  [DoctorClinic],
                  [Address],
                  [Comments],
                  [Interpreter],
                  [CarRequest],
                  [SpecialInstructions],
                  [FaxAuth],
                  [MedicalReportRecieved],
                [EnteredBy],
                  [Attachments],
                [Title],
                  [FaxNumber],
                  [Attn],
                  [ReqBy],
                  [Time]
                  
            ) 
            VALUES
            (
                  CAST(''' +@RequestedBy + '''  AS  Nvarchar(255)),
                CAST('''+ convert(nvarchar(25), @DateD, 121) + '''  AS  nvarchar(25)),'

Open in new window

0
 
ste5anSenior DeveloperCommented:
Why not changing the input date type? Using NVARCHAR here has a code smell.
0
 
RIASAuthor Commented:
USE [server]
GO
/****** Object:  StoredProcedure [dbo].[M_AddDTABLE]    Script Date: 29/08/2016 15:48:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================

-- =============================================
ALTER PROCEDURE  [dbo].[M_AddDTABLE]
 

      @RequestedBy Nvarchar(255)= NULL,@ReqBy Nvarchar(50)= NULL,  @Date nvarchar(20)= NULL  ,@Time nvarchar(16) = NULL ,@PickUpTime nvarchar(16) = NULL ,@PatientName Nvarchar(MAX)=null,
        @Ref Nvarchar(50) ,@Auth Nvarchar(50)=null,@PickUpAddress Nvarchar(50)=null,@Address Nvarchar(50)=null ,@Comments Nvarchar(MAX)=null,
        @Interpreter Nvarchar(50)=null,@CarRequest Nvarchar(50)=null,@SpecialInstructions Nvarchar(MAX)=null ,@FaxAuth Nvarchar(50)=null ,
        @MReportRecieved Nvarchar(50)=null,@EnteredBy Nvarchar(MAX)=null ,@Attachments Nvarchar(50)      =null ,
        @Title Nvarchar(10)= null ,@FaxNumber Nvarchar(50)=null ,@Attn Nvarchar(50)=null,@DoctorClinic Nvarchar(50)=null
        
        
         
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;  

 



            IF NOT EXISTS( SELECT RequestedBy,ReqBy,[Date],[Time],PickUpTime,PatientName,Ref,Auth,PickUpAddress,DoctorClinic,[Address],Comments,Interpreter,CarRequest,
                        SpecialInstructions,FaxAuth,MReportRecieved,EnteredBy,Attachments,Title,FaxNumber,Attn
                        FROM DTABLE WHERE(
                        --( [RequestedBy]  =@RequestedBy  OR RequestedBy is NULL)

                              ([RequestedBy] = @RequestedBy OR
                              ([RequestedBy] is null AND @RequestedBy is null) OR
                              ([RequestedBy] is null AND @RequestedBy ='') OR
                              ([RequestedBy] ='' AND @RequestedBy is null))




                          AND
                         -- ([ReqBy] =@ReqBy  OR ReqBy is NULL)
                               ([ReqBy] = @ReqBy OR
                              ([ReqBy] is null AND @ReqBy is null) OR
                              ([ReqBy] is null AND @ReqBy ='') OR
                              ([ReqBy] ='' AND @ReqBy is null))
                          AND
                           --([Ref] =@Ref  OR Ref is NULL)
                              ([Ref] = @Ref OR
                              ([Ref] is null AND @Ref is null) OR
                              ([Ref] is null AND @Ref ='') OR
                              ([Ref] ='' AND @Ref is null))
                          AND
                          --([Date] = CONVERT(NVARCHAR(20),@Date,112) OR  [Date] IS NULL)

                           ([Date] = @Date OR
                              ([Date] is null AND @Date is null) OR
                              ([Date] is null AND @Date ='') OR
                              ([Date] ='' AND @Date is null))



                          AND
                          -- ([Auth] =@Auth  OR Auth IS NULL)
                                 ([Auth] = @Auth OR
                              ([Auth] is null AND @Auth is null) OR
                              ([Auth] is null AND @Auth ='') OR
                              ([Auth] ='' AND @Auth is null))

                          AND
                          -- (([PickUpTime] = CONVERT(NVARCHAR(16),@PickUpTime,112))   OR  [PickUpTime] IS NULL)

                                 ([PickUpTime] = @PickUpTime OR
                              ([PickUpTime] is null AND @PickUpTime is null) OR
                              ([PickUpTime] is null AND @PickUpTime ='') OR
                              ([PickUpTime] ='' AND @PickUpTime is null))
                          AND
                           --(PatientName=@PatientName OR PatientName IS NULL)
                         
                               ([PatientName] = @PatientName OR
                              ([PatientName] is null AND @PatientName is null) OR
                              ([PatientName] is null AND @PatientName ='') OR
                              ([PatientName] ='' AND @PatientName is null))




                          AND
                          --(PickUpAddress =@PickUpAddress OR PickUpAddress IS NULL)
                                ([PickUpAddress] = @PickUpAddress OR
                              ([PickUpAddress] is null AND @PickUpAddress is null) OR
                              ([PickUpAddress] is null AND @PickUpAddress ='') OR
                              ([PickUpAddress] ='' AND @PickUpAddress is null))

                         
                          AND
                           --(DoctorClinic=@DoctorClinic OR  DoctorClinic IS NULL)
                                 ([DoctorClinic] = @DoctorClinic OR
                              ([DoctorClinic] is null AND @DoctorClinic is null) OR
                              ([DoctorClinic] is null AND @DoctorClinic ='') OR
                              ([DoctorClinic] ='' AND @DoctorClinic is null))

                          AND
                          -- ([Address] =@Address OR [Address] IS NULL))
                                 ([Address] = @Address OR
                              ([Address] is null AND @Address is null) OR
                              ([Address] is null AND @Address ='') OR
                              ([Address] ='' AND @Address is null))

                          AND
                          -- (Comments=@Comments OR [Comments] IS NULL)
                                 ([Comments] = @Comments OR
                              ([Comments] is null AND @Comments is null) OR
                              ([Comments] is null AND @Comments ='') OR
                              ([Comments] ='' AND @Comments is null))

                          AND
                           --(Interpreter=@Interpreter OR [Interpreter] IS NULL)
                                 ([Interpreter] = @Interpreter OR
                              ([Interpreter] is null AND @Interpreter is null) OR
                              ([Interpreter] is null AND @Interpreter ='') OR
                              ([Interpreter] ='' AND @Interpreter is null))

                          AND
                          -- (CarRequest =@CarRequest  OR [CarRequest] IS NULL)
                                 ([CarRequest] = @CarRequest OR
                              ([CarRequest] is null AND @CarRequest is null) OR
                              ([CarRequest] is null AND @CarRequest ='') OR
                              ([CarRequest] ='' AND @CarRequest is null))

                          AND
                          --(SpecialInstructions=@SpecialInstructions OR [SpecialInstructions] IS NULL)
                                ([SpecialInstructions] = @SpecialInstructions OR
                              ([SpecialInstructions] is null AND @SpecialInstructions is null) OR
                              ([SpecialInstructions] is null AND @SpecialInstructions ='') OR
                              ([SpecialInstructions] ='' AND @SpecialInstructions is null))

                          AND
                           --(FaxAuth=@FaxAuth  OR [FaxAuth] IS NULL)
                                 ([FaxAuth] = @FaxAuth OR
                              ([FaxAuth] is null AND @FaxAuth is null) OR
                              ([FaxAuth] is null AND @FaxAuth ='') OR
                              ([FaxAuth] ='' AND @FaxAuth is null))

                          AND
                          -- (MReportRecieved=@MReportRecieved  OR [MReportRecieved] IS NULL)
                                 ([MReportRecieved] = @MReportRecieved OR
                              ([MReportRecieved] is null AND @MReportRecieved is null) OR
                              ([MReportRecieved] is null AND @MReportRecieved ='') OR
                              ([MReportRecieved] ='' AND @MReportRecieved is null))

                          AND
                           --(EnteredBy=@EnteredBy  OR [EnteredBy] IS NULL)
                                 ([EnteredBy] = @EnteredBy OR
                              ([EnteredBy] is null AND @EnteredBy is null) OR
                              ([EnteredBy] is null AND @EnteredBy ='') OR
                              ([EnteredBy] ='' AND @EnteredBy is null))

                          AND
                           --(Attachments=@Attachments OR [Attachments] IS NULL)
                                 ([Attachments] = @Attachments OR
                              ([Attachments] is null AND @Attachments is null) OR
                              ([Attachments] is null AND @Attachments ='') OR
                              ([Attachments] ='' AND @Attachments is null))

                          AND
                           --(Title=@Title OR [Title] IS NULL)
                                 ([Title] = @Title OR
                              ([Title] is null AND @Title is null) OR
                              ([Title] is null AND @Title ='') OR
                              ([Title] ='' AND @Title is null))

                          AND
                           --(FaxNumber=@FaxNumber OR [FaxNumber] IS NULL)
                                 ([FaxNumber] = @FaxNumber OR
                              ([FaxNumber] is null AND @FaxNumber is null) OR
                              ([FaxNumber] is null AND @FaxNumber ='') OR
                              ([FaxNumber] ='' AND @FaxNumber is null))

                          AND
                         -- (Attn=@Attn OR [Attn] IS NULL)
                                ([Attn] = @Attn OR
                              ([Attn] is null AND @Attn is null) OR
                              ([Attn] is null AND @Attn ='') OR
                              ([Attn] ='' AND @Attn is null))

                          AND
                           --(DoctorClinic=@DoctorClinic  OR [DoctorClinic] IS NULL)
                                 ([DoctorClinic] = @DoctorClinic OR
                              ([DoctorClinic] is null AND @DoctorClinic is null) OR
                              ([DoctorClinic] is null AND @DoctorClinic ='') OR
                              ([DoctorClinic] ='' AND @DoctorClinic is null))

                          AND
                         -- (([Time] = CONVERT(NVARCHAR(16),@Time,112))  OR [Time] IS NULL))      
                                ([Time] = @Time OR
                              ([Time] is null AND @Time is null) OR
                              ([Time] is null AND @Time ='') OR
                              ([Time] ='' AND @Time is null))
      

                  
            
      --             IF NOT EXISTS( SELECT Ref,RequestedBy
      --                  FROM DTABLE WHERE( [Ref] = @Ref AND  ([RequestedBy] = @RequestedBy OR
 --   ([RequestedBy] is null )
 ))            

      

      BEGIN
       DECLARE @SQL NVARCHAR(MAX)
         DECLARE @Year_Ref INTEGER
            SET @Year_Ref = (SELECT [Year_Ref] FROM YEAR_LOOKUP  WHERE [Year_no]= YEAR(GETDATE()))

 

    SET @SQL ='      INSERT INTO  [DTABLE] ( '
        IF  @Year_Ref <> ''
            SET @SQL = @SQL + ' [Year_Ref] '      
        IF  @RequestedBy <> ''
            SET @SQL =@SQL + ',' + '  [RequestedBy]  '
        IF  @Ref <> ''
            SET @SQL = @SQL + ',' +  ' [Ref] '
        IF  @Auth <> ''
            SET @SQL = @SQL + ',' + ' [Auth] '
      IF  @Date <> ''
            SET @SQL =@SQL + ',' + '[Date] '                         
        IF  @PickUpTime <> ''
            SET @SQL = @SQL + ',' + '[PickUpTime] '      
        IF  @PatientName <> ''
            SET @SQL = @SQL + ',' +' [PatientName] '      
        IF  @PickUpAddress <> ''
            SET @SQL = @SQL + ',' +' [PickUpAddress] '      
        IF  @DoctorClinic <> ''
            SET @SQL = @SQL + ',' +' [DoctorClinic] '            
        IF  @Address <> ''
            SET @SQL = @SQL+ ',' +' [Address] '            
        IF  @Comments <> ''
            SET @SQL = @SQL+ ',' +' [Comments] '      
        IF  @Interpreter <> ''
            SET @SQL = @SQL+ ',' +' [Interpreter] '      
        IF  @CarRequest <> ''
            SET @SQL = @SQL + ',' +' [CarRequest] '      
        IF  @SpecialInstructions <> ''
            SET @SQL = @SQL + ',' +' [SpecialInstructions] '                  
        IF  @FaxAuth <> ''
            SET @SQL = @SQL + ',' +' [FaxAuth] '      
        IF  @MReportRecieved <> ''
            SET @SQL = @SQL + ',' +' [MReportRecieved] '            
        IF  @EnteredBy <> ''
            SET @SQL = @SQL + ',' +' [EnteredBy] '                        
        IF  @Attachments <> ''
            SET @SQL = @SQL + ',' +' [Attachments] '                  
        IF  @Title <> ''
            SET @SQL = @SQL + ',' +' [Title] '
        IF  @FaxNumber <> ''
            SET @SQL = @SQL + ',' +' [FaxNumber] '
        IF  @Attn <> ''
            SET @SQL = @SQL + ',' +' [Attn] '      
        IF  @ReqBy <> ''
            SET @SQL = @SQL + ',' +' [ReqBy] '      
        IF  @Time <> ''
            SET @SQL = @SQL + ',' +' [Time] '            
             SET @SQL = @SQL +')' + '      
     
     VALUES
       ('
             IF  @Year_Ref   <> ''
               SET @SQL = @SQL + '''' +   CAST(@Year_Ref  AS Nvarchar(20) )  + ''''            
             IF  @RequestedBy   <> ''
               SET @SQL = @SQL + ' , ''' +  CAST(@RequestedBy  AS Nvarchar(255) )  + ''''
            IF  @Ref <> ''
            SET @SQL =@SQL + ' , ''' +   CAST(@Ref  AS  Nvarchar(50))  + ''''       
            IF  @Auth <> ''
            SET @SQL =@SQL + ' , ''' +   CAST(@Auth  AS  Nvarchar(50))  + ''''            
            IF  @Date <> ''
            SET @SQL =@SQL + ' , ''' +   CAST(@Date  AS  Nvarchar(20))  + ''''                          
             IF  @PickUpTime   <> ''
               SET @SQL = @SQL  + ' , ''' +  CAST(@PickUpTime AS Nvarchar(20) )  + ''''                     
             IF  @PatientName   <> ''
               SET @SQL = @SQL  + ' , ''' +  CAST(@PatientName AS Nvarchar(max) )  + ''''            
            IF  @PickUpAddress   <> ''
               SET @SQL = @SQL  + ' , ''' +  CAST(@PickUpAddress AS Nvarchar(50) )  + ''''
            IF  @DoctorClinic  <> ''
               SET @SQL = @SQL  + ' , ''' +  CAST(@DoctorClinic AS Nvarchar(50) )  + ''''            
            IF  @Address  <> ''
               SET @SQL = @SQL  + ' , ''' +  CAST(@Address AS Nvarchar(50) )  + ''''
               IF  @Comments   <> ''
               SET @SQL = @SQL  + ' , ''' +  CAST(@Comments  AS Nvarchar(max) )  + ''''
               IF  @Interpreter   <> ''
               SET @SQL = @SQL  + ' , ''' +  CAST(@Interpreter   AS Nvarchar(50) )  + ''''      
            IF  @CarRequest   <> ''
               SET @SQL = @SQL  + ' , ''' +  CAST(@CarRequest   AS Nvarchar(50) )  + ''''            
            IF  @SpecialInstructions   <> ''
               SET @SQL = @SQL  + ' , ''' +  CAST(@SpecialInstructions    AS Nvarchar(max) )  + ''''                        
            IF  @FaxAuth   <> ''
               SET @SQL = @SQL  + ' , ''' +  CAST(@FaxAuth    AS Nvarchar(50) )  + ''''             
            IF  @MReportRecieved    <> ''
               SET @SQL = @SQL  + ' , ''' +  CAST(@MReportRecieved     AS Nvarchar(max) )  + ''''       
             IF  @EnteredBy    <> ''
               SET @SQL = @SQL  + ' , ''' +  CAST(@EnteredBy   AS Nvarchar(max) )  + ''''       
          IF  @Attachments   <> ''
               SET @SQL = @SQL  + ' , ''' +  CAST(@Attachments   AS Nvarchar(50) )  + ''''
            IF  @Title  <> ''
               SET @SQL = @SQL  + ' , ''' +  CAST(@Title   AS Nvarchar(10) )  + ''''       
            IF  @FaxNumber  <> ''
               SET @SQL = @SQL  + ' , ''' +  CAST(@FaxNumber  AS Nvarchar(50) )  + ''''       
            IF  @Attn  <> ''
               SET @SQL = @SQL  + ' , ''' +  CAST(@Attn  AS Nvarchar(50) )  + ''''                  
            IF  @ReqBy  <> ''
               SET @SQL = @SQL  + ' , ''' +  CAST(@ReqBy  AS Nvarchar(50) )  + ''''       
            IF  @Time  <> ''
               SET @SQL = @SQL  + ' , ''' +  CAST(@Time   AS Nvarchar(20) )  + ''''

               SET @SQL =  @SQL + ')'

        EXECUTE (@SQL)
          
            
            --INSERT INTO M_DEBUG(SQL) VALUES (@SQL)
      





                  INSERT INTO M_DEBUG(SQL) VALUES ( 'INSERT INTO [DTABLE]      
                  [RequestedBy],
                  [Date],
                  [Ref],                  
                  [Auth],                  
                  [PickUpTime],
                  [PatientName],
                  [PickUpAddress],
                  [DoctorClinic],
                  [Address],
                  [Comments],
                  [Interpreter],
                  [CarRequest],
                  [SpecialInstructions],
                  [FaxAuth],
                  [MReportRecieved],
                [EnteredBy],
                  [Attachments],
                [Title],
                  [FaxNumber],
                  [Attn],
                  [ReqBy],
                  [Time]
                  
            )
            VALUES
            (
                  CAST(''' +@RequestedBy + '''  AS  Nvarchar(255)),
                CAST('''+ convert(nvarchar(25), @Date, 103) + '''  AS  nvarchar(25)),      
                  CAST('''+@Ref +''' AS NVARCHAR(255)),                         
                  CAST('''+@Auth +''' AS Nvarchar(255)),                              
                  CAST('''+@PickUpTime +''' AS Nvarchar(255)),
                CAST('''+@PatientName +''' AS Nvarchar(255)),
                CAST('''+@PickUpAddress +''' AS Nvarchar(255)),
                  CAST('''+@DoctorClinic +''' AS Nvarchar(255)),
                  CAST('''+@Address +''' AS Nvarchar(255)),
                  CAST('''+@Comments +''' AS Nvarchar(255)),
                  CAST('''+@Interpreter +''' AS Nvarchar(255)),
                  CAST('''+@CarRequest +''' AS Nvarchar(255)),
                  CAST('''+@SpecialInstructions +''' AS Nvarchar(255)),
                  CAST('''+@FaxAuth +''' AS Nvarchar(255)),
                  CAST('''+@MReportRecieved +''' AS Nvarchar(255)),
                  CAST('''+@EnteredBy +''' AS Nvarchar(255)),
                  CAST('''+@Attachments +''' AS Nvarchar(255)),
                  CAST('''+@Title +''' AS Nvarchar(255)),
                  CAST('''+@FaxNumber +''' AS Nvarchar(255)),
                  CAST('''+@Attn +''' AS Nvarchar(255)),
                  CAST('''+@ReqBy +''' AS Nvarchar(255)),
                  CAST('''+@Time +''' AS Nvarchar(255)))'
            
            )
            
      Return @@Rowcount
      END
      
      
      ELSE       
      BEGIN

      
        Declare   @UID Nvarchar(MAX)=null
        -- NONCLUSTERED INDEX [NIX__UNQ__UID_]
        SET @UID  = (SELECT [DTABLE_UID]
                        FROM DTABLE WHERE( [RequestedBy] =@RequestedBy AND [Ref] =@Ref AND [Date] = CONVERT(NVARCHAR(20),@Date,112) AND [Auth] =@Auth) AND  [PickUpTime] = CONVERT(NVARCHAR(16),@PickUpTime,112) AND PatientName=@PatientName
                        AND PickUpAddress =@PickUpAddress AND DoctorClinic=@DoctorClinic AND  [Address] =@Address AND Comments=@Comments AND  Interpreter=@Interpreter AND
                        CarRequest =@CarRequest AND SpecialInstructions=@SpecialInstructions AND FaxAuth=@FaxAuth AND MReportRecieved=@MReportRecieved AND
                        EnteredBy=@EnteredBy AND Attachments=@Attachments AND Title=@Title AND  FaxNumber=@FaxNumber AND Attn=@Attn AND DoctorClinic=@DoctorClinic AND  [Time] = CONVERT(NVARCHAR(16),@Time,112))                  

        SET @SQL = 'UPDATE  DTABLE  SET  [DeleteRecord] = '''+ ''  + '''
                    WHERE  [DTABLE_UID] = '''+ @UID  + ''''
                         
        EXECUTE(@SQL)
      
      
          Return -1
      END
0
 
ste5anSenior DeveloperCommented:
Please embed code in the CODE tag from the posts toolbar. And format your code. Also remove dead code before posting.

Ad much worse:

SET @SQL ='      INSERT INTO  [DTABLE] ( ';
IF @Year_Ref <> ''		SET @SQL = @SQL + ' [Year_Ref] '    ;  
IF @RequestedBy <> ''	SET @SQL =@SQL + ',' + '  [RequestedBy]  ;

Open in new window


This will result in values being stored in different columns. What is your idea behind that??
0
 
RIASAuthor Commented:
USE [server]
GO
/****** Object:  StoredProcedure [dbo].[M_AddDTABLE]    Script Date: 29/08/2016 15:48:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================

-- =============================================
ALTER PROCEDURE  [dbo].[M_AddDTABLE]
  

      @RequestedBy Nvarchar(255)= NULL,@ReqBy Nvarchar(50)= NULL,  @Date nvarchar(20)= NULL  ,@Time nvarchar(16) = NULL ,@PickUpTime nvarchar(16) = NULL ,@PatientName Nvarchar(MAX)=null,
	  @Ref Nvarchar(50) ,@Auth Nvarchar(50)=null,@PickUpAddress Nvarchar(50)=null,@Address Nvarchar(50)=null ,@Comments Nvarchar(MAX)=null,
	  @Interpreter Nvarchar(50)=null,@CarRequest Nvarchar(50)=null,@SpecialInstructions Nvarchar(MAX)=null ,@FaxAuth Nvarchar(50)=null ,
	  @MReportRecieved Nvarchar(50)=null,@EnteredBy Nvarchar(MAX)=null ,@Attachments Nvarchar(50)	=null ,
	  @Title Nvarchar(10)= null ,@FaxNumber Nvarchar(50)=null ,@Attn Nvarchar(50)=null,@DoctorClinic Nvarchar(50)=null
	  
	  
         
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;  

  



		IF NOT EXISTS( SELECT RequestedBy,ReqBy,[Date],[Time],PickUpTime,PatientName,Ref,Auth,PickUpAddress,DoctorClinic,[Address],Comments,Interpreter,CarRequest,
				SpecialInstructions,FaxAuth,MReportRecieved,EnteredBy,Attachments,Title,FaxNumber,Attn
				FROM DTABLE WHERE(
				--( [RequestedBy]  =@RequestedBy  OR RequestedBy is NULL)

					([RequestedBy] = @RequestedBy OR 
					([RequestedBy] is null AND @RequestedBy is null) OR
					([RequestedBy] is null AND @RequestedBy ='') OR
					([RequestedBy] ='' AND @RequestedBy is null))




				  AND 
				 -- ([ReqBy] =@ReqBy  OR ReqBy is NULL) 
				 	([ReqBy] = @ReqBy OR 
					([ReqBy] is null AND @ReqBy is null) OR
					([ReqBy] is null AND @ReqBy ='') OR
					([ReqBy] ='' AND @ReqBy is null))
				  AND
				   --([Ref] =@Ref  OR Ref is NULL)
					([Ref] = @Ref OR 
					([Ref] is null AND @Ref is null) OR
					([Ref] is null AND @Ref ='') OR
					([Ref] ='' AND @Ref is null))
				  AND
				  --([Date] = CONVERT(NVARCHAR(20),@Date,112) OR  [Date] IS NULL) 

				   ([Date] = @Date OR 
					([Date] is null AND @Date is null) OR
					([Date] is null AND @Date ='') OR
					([Date] ='' AND @Date is null))



				  AND
				  -- ([Auth] =@Auth  OR Auth IS NULL)
				   	([Auth] = @Auth OR 
					([Auth] is null AND @Auth is null) OR
					([Auth] is null AND @Auth ='') OR
					([Auth] ='' AND @Auth is null))

				  AND
				  -- (([PickUpTime] = CONVERT(NVARCHAR(16),@PickUpTime,112))   OR  [PickUpTime] IS NULL)

				   	([PickUpTime] = @PickUpTime OR 
					([PickUpTime] is null AND @PickUpTime is null) OR
					([PickUpTime] is null AND @PickUpTime ='') OR
					([PickUpTime] ='' AND @PickUpTime is null))
				  AND
				   --(PatientName=@PatientName OR PatientName IS NULL)
				 
				 	([PatientName] = @PatientName OR 
					([PatientName] is null AND @PatientName is null) OR
					([PatientName] is null AND @PatientName ='') OR
					([PatientName] ='' AND @PatientName is null))




				  AND 
				  --(PickUpAddress =@PickUpAddress OR PickUpAddress IS NULL)
				  	([PickUpAddress] = @PickUpAddress OR 
					([PickUpAddress] is null AND @PickUpAddress is null) OR
					([PickUpAddress] is null AND @PickUpAddress ='') OR
					([PickUpAddress] ='' AND @PickUpAddress is null))

				 
				  AND
				   --(DoctorClinic=@DoctorClinic OR  DoctorClinic IS NULL)
				   	([DoctorClinic] = @DoctorClinic OR 
					([DoctorClinic] is null AND @DoctorClinic is null) OR
					([DoctorClinic] is null AND @DoctorClinic ='') OR
					([DoctorClinic] ='' AND @DoctorClinic is null))

				  AND
				  -- ([Address] =@Address OR [Address] IS NULL))
				   	([Address] = @Address OR 
					([Address] is null AND @Address is null) OR
					([Address] is null AND @Address ='') OR
					([Address] ='' AND @Address is null))

				  AND
				  -- (Comments=@Comments OR [Comments] IS NULL)
				   	([Comments] = @Comments OR 
					([Comments] is null AND @Comments is null) OR
					([Comments] is null AND @Comments ='') OR
					([Comments] ='' AND @Comments is null))

				  AND
				   --(Interpreter=@Interpreter OR [Interpreter] IS NULL)
				   	([Interpreter] = @Interpreter OR 
					([Interpreter] is null AND @Interpreter is null) OR
					([Interpreter] is null AND @Interpreter ='') OR
					([Interpreter] ='' AND @Interpreter is null))

				  AND
				  -- (CarRequest =@CarRequest  OR [CarRequest] IS NULL)
				   	([CarRequest] = @CarRequest OR 
					([CarRequest] is null AND @CarRequest is null) OR
					([CarRequest] is null AND @CarRequest ='') OR
					([CarRequest] ='' AND @CarRequest is null))

				  AND 
				  --(SpecialInstructions=@SpecialInstructions OR [SpecialInstructions] IS NULL)
				  	([SpecialInstructions] = @SpecialInstructions OR 
					([SpecialInstructions] is null AND @SpecialInstructions is null) OR
					([SpecialInstructions] is null AND @SpecialInstructions ='') OR
					([SpecialInstructions] ='' AND @SpecialInstructions is null))

				  AND
				   --(FaxAuth=@FaxAuth  OR [FaxAuth] IS NULL)
				   	([FaxAuth] = @FaxAuth OR 
					([FaxAuth] is null AND @FaxAuth is null) OR
					([FaxAuth] is null AND @FaxAuth ='') OR
					([FaxAuth] ='' AND @FaxAuth is null))

				  AND
				  -- (MReportRecieved=@MReportRecieved  OR [MReportRecieved] IS NULL)
				   	([MReportRecieved] = @MReportRecieved OR 
					([MReportRecieved] is null AND @MReportRecieved is null) OR
					([MReportRecieved] is null AND @MReportRecieved ='') OR
					([MReportRecieved] ='' AND @MReportRecieved is null))

				  AND
				   --(EnteredBy=@EnteredBy  OR [EnteredBy] IS NULL)
				   	([EnteredBy] = @EnteredBy OR 
					([EnteredBy] is null AND @EnteredBy is null) OR
					([EnteredBy] is null AND @EnteredBy ='') OR
					([EnteredBy] ='' AND @EnteredBy is null))

				  AND
				   --(Attachments=@Attachments OR [Attachments] IS NULL)
				   	([Attachments] = @Attachments OR 
					([Attachments] is null AND @Attachments is null) OR
					([Attachments] is null AND @Attachments ='') OR
					([Attachments] ='' AND @Attachments is null))

				  AND
				   --(Title=@Title OR [Title] IS NULL)
				   	([Title] = @Title OR 
					([Title] is null AND @Title is null) OR
					([Title] is null AND @Title ='') OR
					([Title] ='' AND @Title is null))

				  AND
				   --(FaxNumber=@FaxNumber OR [FaxNumber] IS NULL)
				   	([FaxNumber] = @FaxNumber OR 
					([FaxNumber] is null AND @FaxNumber is null) OR
					([FaxNumber] is null AND @FaxNumber ='') OR
					([FaxNumber] ='' AND @FaxNumber is null))

				  AND 
				 -- (Attn=@Attn OR [Attn] IS NULL)
				  	([Attn] = @Attn OR 
					([Attn] is null AND @Attn is null) OR
					([Attn] is null AND @Attn ='') OR
					([Attn] ='' AND @Attn is null))

				  AND
				   --(DoctorClinic=@DoctorClinic  OR [DoctorClinic] IS NULL)
				   	([DoctorClinic] = @DoctorClinic OR 
					([DoctorClinic] is null AND @DoctorClinic is null) OR
					([DoctorClinic] is null AND @DoctorClinic ='') OR
					([DoctorClinic] ='' AND @DoctorClinic is null))

				  AND
				 -- (([Time] = CONVERT(NVARCHAR(16),@Time,112))  OR [Time] IS NULL))	
				  	([Time] = @Time OR 
					([Time] is null AND @Time is null) OR
					([Time] is null AND @Time ='') OR
					([Time] ='' AND @Time is null))
	

			
		
	--	 	IF NOT EXISTS( SELECT Ref,RequestedBy
	--			FROM DTABLE WHERE( [Ref] = @Ref AND  ([RequestedBy] = @RequestedBy OR 
 --   ([RequestedBy] is null )
 ))		

	

	BEGIN 
	 DECLARE @SQL NVARCHAR(MAX)
	   DECLARE @Year_Ref INTEGER
		SET @Year_Ref = (SELECT [Year_Ref] FROM YEAR_LOOKUP  WHERE [Year_no]= YEAR(GETDATE()))

 

    SET @SQL ='	INSERT INTO  [DTABLE] ( '
	  IF  @Year_Ref <> ''
            SET @SQL = @SQL + ' [Year_Ref] '	
	  IF  @RequestedBy <> ''
            SET @SQL =@SQL + ',' + '  [RequestedBy]  ' 
	  IF  @Ref <> ''
            SET @SQL = @SQL + ',' +  ' [Ref] '
	  IF  @Auth <> ''
            SET @SQL = @SQL + ',' + ' [Auth] '
      IF  @Date <> ''
            SET @SQL =@SQL + ',' + '[Date] ' 				
	  IF  @PickUpTime <> ''
            SET @SQL = @SQL + ',' + '[PickUpTime] '	
	  IF  @PatientName <> ''
            SET @SQL = @SQL + ',' +' [PatientName] '	
	  IF  @PickUpAddress <> ''
            SET @SQL = @SQL + ',' +' [PickUpAddress] '	
	  IF  @DoctorClinic <> ''
            SET @SQL = @SQL + ',' +' [DoctorClinic] '		
	  IF  @Address <> ''
            SET @SQL = @SQL+ ',' +' [Address] '		
	  IF  @Comments <> ''
            SET @SQL = @SQL+ ',' +' [Comments] '	
	  IF  @Interpreter <> ''
            SET @SQL = @SQL+ ',' +' [Interpreter] '	
	  IF  @CarRequest <> ''
            SET @SQL = @SQL + ',' +' [CarRequest] '	
	  IF  @SpecialInstructions <> ''
            SET @SQL = @SQL + ',' +' [SpecialInstructions] '			
	  IF  @FaxAuth <> ''
            SET @SQL = @SQL + ',' +' [FaxAuth] '	
	  IF  @MReportRecieved <> ''
            SET @SQL = @SQL + ',' +' [MReportRecieved] '		
	  IF  @EnteredBy <> ''
            SET @SQL = @SQL + ',' +' [EnteredBy] '				
	  IF  @Attachments <> ''
            SET @SQL = @SQL + ',' +' [Attachments] '			
	  IF  @Title <> ''
            SET @SQL = @SQL + ',' +' [Title] '
	  IF  @FaxNumber <> ''
            SET @SQL = @SQL + ',' +' [FaxNumber] '
	  IF  @Attn <> ''
            SET @SQL = @SQL + ',' +' [Attn] '	
	  IF  @ReqBy <> ''
            SET @SQL = @SQL + ',' +' [ReqBy] '	
	  IF  @Time <> ''
            SET @SQL = @SQL + ',' +' [Time] '		
		 SET @SQL = @SQL +')' + '      
     
     VALUES
	 ('
	 	IF  @Year_Ref   <> ''
		   SET @SQL = @SQL + '''' +   CAST(@Year_Ref  AS Nvarchar(20) )  + ''''		 
	 	IF  @RequestedBy   <> ''
		   SET @SQL = @SQL + ' , ''' +  CAST(@RequestedBy  AS Nvarchar(255) )  + ''''
		IF  @Ref <> ''
            SET @SQL =@SQL + ' , ''' +   CAST(@Ref  AS  Nvarchar(50))  + '''' 	
		IF  @Auth <> ''
            SET @SQL =@SQL + ' , ''' +   CAST(@Auth  AS  Nvarchar(50))  + ''''		
		IF  @Date <> ''
            SET @SQL =@SQL + ' , ''' +   CAST(@Date  AS  Nvarchar(20))  + '''' 		 		
	 	IF  @PickUpTime   <> ''
		   SET @SQL = @SQL  + ' , ''' +  CAST(@PickUpTime AS Nvarchar(20) )  + ''''  	 		
	 	IF  @PatientName   <> ''
		   SET @SQL = @SQL  + ' , ''' +  CAST(@PatientName AS Nvarchar(max) )  + ''''		
		IF  @PickUpAddress   <> ''
		   SET @SQL = @SQL  + ' , ''' +  CAST(@PickUpAddress AS Nvarchar(50) )  + ''''
		IF  @DoctorClinic  <> ''
		   SET @SQL = @SQL  + ' , ''' +  CAST(@DoctorClinic AS Nvarchar(50) )  + ''''		
		IF  @Address  <> ''
		   SET @SQL = @SQL  + ' , ''' +  CAST(@Address AS Nvarchar(50) )  + ''''
	   	IF  @Comments   <> ''
		   SET @SQL = @SQL  + ' , ''' +  CAST(@Comments  AS Nvarchar(max) )  + ''''
	   	IF  @Interpreter   <> ''
		   SET @SQL = @SQL  + ' , ''' +  CAST(@Interpreter   AS Nvarchar(50) )  + ''''	
		IF  @CarRequest   <> ''
		   SET @SQL = @SQL  + ' , ''' +  CAST(@CarRequest   AS Nvarchar(50) )  + ''''		
		IF  @SpecialInstructions   <> ''
		   SET @SQL = @SQL  + ' , ''' +  CAST(@SpecialInstructions    AS Nvarchar(max) )  + ''''				
		IF  @FaxAuth   <> ''
		   SET @SQL = @SQL  + ' , ''' +  CAST(@FaxAuth    AS Nvarchar(50) )  + ''''	 	
		IF  @MReportRecieved    <> ''
		   SET @SQL = @SQL  + ' , ''' +  CAST(@MReportRecieved     AS Nvarchar(max) )  + ''''	 
	 	IF  @EnteredBy    <> ''
		   SET @SQL = @SQL  + ' , ''' +  CAST(@EnteredBy   AS Nvarchar(max) )  + ''''	 
	    IF  @Attachments   <> ''
		   SET @SQL = @SQL  + ' , ''' +  CAST(@Attachments   AS Nvarchar(50) )  + ''''
		IF  @Title  <> ''
		   SET @SQL = @SQL  + ' , ''' +  CAST(@Title   AS Nvarchar(10) )  + ''''	 
		IF  @FaxNumber  <> ''
		   SET @SQL = @SQL  + ' , ''' +  CAST(@FaxNumber  AS Nvarchar(50) )  + ''''	 
		IF  @Attn  <> ''
		   SET @SQL = @SQL  + ' , ''' +  CAST(@Attn  AS Nvarchar(50) )  + ''''			
		IF  @ReqBy  <> ''
		   SET @SQL = @SQL  + ' , ''' +  CAST(@ReqBy  AS Nvarchar(50) )  + '''' 	
		IF  @Time  <> ''
		   SET @SQL = @SQL  + ' , ''' +  CAST(@Time   AS Nvarchar(20) )  + ''''

		   SET @SQL =  @SQL + ')'

        EXECUTE (@SQL)
	    
		
		--INSERT INTO M_DEBUG(SQL) VALUES (@SQL)
	





			INSERT INTO M_DEBUG(SQL) VALUES ( 'INSERT INTO [DTABLE]	
			[RequestedBy],
			[Date],
			[Ref],			
			[Auth],			
			[PickUpTime],
			[PatientName],
			[PickUpAddress],
			[DoctorClinic],
			[Address],
			[Comments],
			[Interpreter],
			[CarRequest],
			[SpecialInstructions],
			[FaxAuth],
			[MReportRecieved],
		    [EnteredBy],
			[Attachments],
		    [Title],
			[FaxNumber],
			[Attn],
			[ReqBy],
			[Time]
			
		) 
		VALUES
		(
			CAST(''' +@RequestedBy + '''  AS  Nvarchar(255)),
		    CAST('''+ convert(nvarchar(25), @Date, 103) + '''  AS  nvarchar(25)),	
			CAST('''+@Ref +''' AS NVARCHAR(255)),			 	
			CAST('''+@Auth +''' AS Nvarchar(255)),					
			CAST('''+@PickUpTime +''' AS Nvarchar(255)),
		    CAST('''+@PatientName +''' AS Nvarchar(255)),
		    CAST('''+@PickUpAddress +''' AS Nvarchar(255)),
			CAST('''+@DoctorClinic +''' AS Nvarchar(255)),
			CAST('''+@Address +''' AS Nvarchar(255)),
			CAST('''+@Comments +''' AS Nvarchar(255)),
			CAST('''+@Interpreter +''' AS Nvarchar(255)),
			CAST('''+@CarRequest +''' AS Nvarchar(255)),
			CAST('''+@SpecialInstructions +''' AS Nvarchar(255)),
			CAST('''+@FaxAuth +''' AS Nvarchar(255)),
			CAST('''+@MReportRecieved +''' AS Nvarchar(255)),
			CAST('''+@EnteredBy +''' AS Nvarchar(255)),
			CAST('''+@Attachments +''' AS Nvarchar(255)),
			CAST('''+@Title +''' AS Nvarchar(255)),
			CAST('''+@FaxNumber +''' AS Nvarchar(255)),
			CAST('''+@Attn +''' AS Nvarchar(255)),
			CAST('''+@ReqBy +''' AS Nvarchar(255)),
			CAST('''+@Time +''' AS Nvarchar(255)))'
		
		)
		
	Return @@Rowcount
	END
	
	
	ELSE 	
	BEGIN

	
	  Declare   @UID Nvarchar(MAX)=null
	  -- NONCLUSTERED INDEX [NIX__UNQ__UID_]
	  SET @UID  = (SELECT [DTABLE_UID]
				FROM DTABLE WHERE( [RequestedBy] =@RequestedBy AND [Ref] =@Ref AND [Date] = CONVERT(NVARCHAR(20),@Date,112) AND [Auth] =@Auth) AND  [PickUpTime] = CONVERT(NVARCHAR(16),@PickUpTime,112) AND PatientName=@PatientName
				AND PickUpAddress =@PickUpAddress AND DoctorClinic=@DoctorClinic AND  [Address] =@Address AND Comments=@Comments AND  Interpreter=@Interpreter AND 
				CarRequest =@CarRequest AND SpecialInstructions=@SpecialInstructions AND FaxAuth=@FaxAuth AND MReportRecieved=@MReportRecieved AND 
				EnteredBy=@EnteredBy AND Attachments=@Attachments AND Title=@Title AND  FaxNumber=@FaxNumber AND Attn=@Attn AND DoctorClinic=@DoctorClinic AND  [Time] = CONVERT(NVARCHAR(16),@Time,112))			

	  SET @SQL = 'UPDATE  DTABLE  SET  [DeleteRecord] = '''+ ''  + '''
	              WHERE  [DTABLE_UID] = '''+ @UID  + ''''
				 
	  EXECUTE(@SQL)
	 
	
	    Return -1
	END

Open in new window

0
 
Éric MoreauSenior .Net ConsultantCommented:
I see no trace of your Conversion from format 103. Where is your @DateD?
0
 
RIASAuthor Commented:
Eric,

I have posted my oroginal SP .
0
 
Éric MoreauSenior .Net ConsultantCommented:
do you know you could replace:
([RequestedBy] = @RequestedBy OR 
([RequestedBy] is null AND @RequestedBy is null) OR
([RequestedBy] is null AND @RequestedBy ='') OR
([RequestedBy] ='' AND @RequestedBy is null))

Open in new window


with:
(isnull([RequestedBy], '') = isnull(@RequestedBy, ''))

Open in new window

0
 
RIASAuthor Commented:
Cool! Will pase the new SP
0
 
Éric MoreauSenior .Net ConsultantCommented:
>>I have posted my oroginal SP .

please integrate the suggested modifications
0
 
ste5anSenior DeveloperCommented:
Using only NVARHCAR as parameter type is not a good idea. It allows to pass in any arbitrary values.

The correct approach is to use the correct data types here and do the formatting - if necessary - in the upper layer. Especially as you pass in dates and numbers, but not the used culture. This means loose coupling by relying on the correct settings of your database, like SET DATEFORMAT, and the use of the corresponding culture in your .NET application.
1
 
Éric MoreauSenior .Net ConsultantCommented:
>>The correct approach is to use the correct data types here and do the formatting - if necessary - in the upper layer. Especially as you pass in dates and numbers, but not the used culture.

I fully agree.
0
 
RIASAuthor Commented:
USE [server]
GO
/****** Object:  StoredProcedure [dbo].[M_AddDTABLE]    Script Date: 29/08/2016 15:48:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================

-- =============================================
ALTER PROCEDURE  [dbo].[M_AddDTABLE]
  

      @RequestedBy Nvarchar(255)= NULL,@ReqBy Nvarchar(50)= NULL,  @Date nvarchar(20)= NULL  ,@Time nvarchar(16) = NULL ,@PickUpTime nvarchar(16) = NULL ,@PatientName Nvarchar(MAX)=null,
	  @Ref Nvarchar(50) ,@Auth Nvarchar(50)=null,@PickUpAddress Nvarchar(50)=null,@Address Nvarchar(50)=null ,@Comments Nvarchar(MAX)=null,
	  @Interpreter Nvarchar(50)=null,@CarRequest Nvarchar(50)=null,@SpecialInstructions Nvarchar(MAX)=null ,@FaxAuth Nvarchar(50)=null ,
	  @MReportRecieved Nvarchar(50)=null,@EnteredBy Nvarchar(MAX)=null ,@Attachments Nvarchar(50)	=null ,
	  @Title Nvarchar(10)= null ,@FaxNumber Nvarchar(50)=null ,@Attn Nvarchar(50)=null,@DoctorClinic Nvarchar(50)=null
	  
	  
         
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;  

  



		IF NOT EXISTS( SELECT RequestedBy,ReqBy,[Date],[Time],PickUpTime,PatientName,Ref,Auth,PickUpAddress,DoctorClinic,[Address],Comments,Interpreter,CarRequest,
				SpecialInstructions,FaxAuth,MReportRecieved,EnteredBy,Attachments,Title,FaxNumber,Attn
				FROM DTABLE WHERE(
				--( [RequestedBy]  =@RequestedBy  OR RequestedBy is NULL)

					([RequestedBy] = @RequestedBy OR 
					([RequestedBy] is null AND @RequestedBy is null) OR
					([RequestedBy] is null AND @RequestedBy ='') OR
					([RequestedBy] ='' AND @RequestedBy is null))




				  AND 
				 -- ([ReqBy] =@ReqBy  OR ReqBy is NULL) 
				 	([ReqBy] = @ReqBy OR 
					([ReqBy] is null AND @ReqBy is null) OR
					([ReqBy] is null AND @ReqBy ='') OR
					([ReqBy] ='' AND @ReqBy is null))
				  AND
				   --([Ref] =@Ref  OR Ref is NULL)
					([Ref] = @Ref OR 
					([Ref] is null AND @Ref is null) OR
					([Ref] is null AND @Ref ='') OR
					([Ref] ='' AND @Ref is null))
				  AND
				  --([Date] = CONVERT(NVARCHAR(20),@Date,112) OR  [Date] IS NULL) 

				   ([Date] = @Date OR 
					([Date] is null AND @Date is null) OR
					([Date] is null AND @Date ='') OR
					([Date] ='' AND @Date is null))



				  AND
				  -- ([Auth] =@Auth  OR Auth IS NULL)
				   	([Auth] = @Auth OR 
					([Auth] is null AND @Auth is null) OR
					([Auth] is null AND @Auth ='') OR
					([Auth] ='' AND @Auth is null))

				  AND
				  -- (([PickUpTime] = CONVERT(NVARCHAR(16),@PickUpTime,112))   OR  [PickUpTime] IS NULL)

				   	([PickUpTime] = @PickUpTime OR 
					([PickUpTime] is null AND @PickUpTime is null) OR
					([PickUpTime] is null AND @PickUpTime ='') OR
					([PickUpTime] ='' AND @PickUpTime is null))
				  AND
				   --(PatientName=@PatientName OR PatientName IS NULL)
				 
				 	([PatientName] = @PatientName OR 
					([PatientName] is null AND @PatientName is null) OR
					([PatientName] is null AND @PatientName ='') OR
					([PatientName] ='' AND @PatientName is null))




				  AND 
				  --(PickUpAddress =@PickUpAddress OR PickUpAddress IS NULL)
				  	([PickUpAddress] = @PickUpAddress OR 
					([PickUpAddress] is null AND @PickUpAddress is null) OR
					([PickUpAddress] is null AND @PickUpAddress ='') OR
					([PickUpAddress] ='' AND @PickUpAddress is null))

				 
				  AND
				   --(DoctorClinic=@DoctorClinic OR  DoctorClinic IS NULL)
				   	([DoctorClinic] = @DoctorClinic OR 
					([DoctorClinic] is null AND @DoctorClinic is null) OR
					([DoctorClinic] is null AND @DoctorClinic ='') OR
					([DoctorClinic] ='' AND @DoctorClinic is null))

				  AND
				  -- ([Address] =@Address OR [Address] IS NULL))
				   	([Address] = @Address OR 
					([Address] is null AND @Address is null) OR
					([Address] is null AND @Address ='') OR
					([Address] ='' AND @Address is null))

				  AND
				  -- (Comments=@Comments OR [Comments] IS NULL)
				   	([Comments] = @Comments OR 
					([Comments] is null AND @Comments is null) OR
					([Comments] is null AND @Comments ='') OR
					([Comments] ='' AND @Comments is null))

				  AND
				   --(Interpreter=@Interpreter OR [Interpreter] IS NULL)
				   	([Interpreter] = @Interpreter OR 
					([Interpreter] is null AND @Interpreter is null) OR
					([Interpreter] is null AND @Interpreter ='') OR
					([Interpreter] ='' AND @Interpreter is null))

				  AND
				  -- (CarRequest =@CarRequest  OR [CarRequest] IS NULL)
				   	([CarRequest] = @CarRequest OR 
					([CarRequest] is null AND @CarRequest is null) OR
					([CarRequest] is null AND @CarRequest ='') OR
					([CarRequest] ='' AND @CarRequest is null))

				  AND 
				  --(SpecialInstructions=@SpecialInstructions OR [SpecialInstructions] IS NULL)
				  	([SpecialInstructions] = @SpecialInstructions OR 
					([SpecialInstructions] is null AND @SpecialInstructions is null) OR
					([SpecialInstructions] is null AND @SpecialInstructions ='') OR
					([SpecialInstructions] ='' AND @SpecialInstructions is null))

				  AND
				   --(FaxAuth=@FaxAuth  OR [FaxAuth] IS NULL)
				   	([FaxAuth] = @FaxAuth OR 
					([FaxAuth] is null AND @FaxAuth is null) OR
					([FaxAuth] is null AND @FaxAuth ='') OR
					([FaxAuth] ='' AND @FaxAuth is null))

				  AND
				  -- (MReportRecieved=@MReportRecieved  OR [MReportRecieved] IS NULL)
				   	([MReportRecieved] = @MReportRecieved OR 
					([MReportRecieved] is null AND @MReportRecieved is null) OR
					([MReportRecieved] is null AND @MReportRecieved ='') OR
					([MReportRecieved] ='' AND @MReportRecieved is null))

				  AND
				   --(EnteredBy=@EnteredBy  OR [EnteredBy] IS NULL)
				   	([EnteredBy] = @EnteredBy OR 
					([EnteredBy] is null AND @EnteredBy is null) OR
					([EnteredBy] is null AND @EnteredBy ='') OR
					([EnteredBy] ='' AND @EnteredBy is null))

				  AND
				   --(Attachments=@Attachments OR [Attachments] IS NULL)
				   	([Attachments] = @Attachments OR 
					([Attachments] is null AND @Attachments is null) OR
					([Attachments] is null AND @Attachments ='') OR
					([Attachments] ='' AND @Attachments is null))

				  AND
				   --(Title=@Title OR [Title] IS NULL)
				   	([Title] = @Title OR 
					([Title] is null AND @Title is null) OR
					([Title] is null AND @Title ='') OR
					([Title] ='' AND @Title is null))

				  AND
				   --(FaxNumber=@FaxNumber OR [FaxNumber] IS NULL)
				   	([FaxNumber] = @FaxNumber OR 
					([FaxNumber] is null AND @FaxNumber is null) OR
					([FaxNumber] is null AND @FaxNumber ='') OR
					([FaxNumber] ='' AND @FaxNumber is null))

				  AND 
				 -- (Attn=@Attn OR [Attn] IS NULL)
				  	([Attn] = @Attn OR 
					([Attn] is null AND @Attn is null) OR
					([Attn] is null AND @Attn ='') OR
					([Attn] ='' AND @Attn is null))

				  AND
				   --(DoctorClinic=@DoctorClinic  OR [DoctorClinic] IS NULL)
				   	([DoctorClinic] = @DoctorClinic OR 
					([DoctorClinic] is null AND @DoctorClinic is null) OR
					([DoctorClinic] is null AND @DoctorClinic ='') OR
					([DoctorClinic] ='' AND @DoctorClinic is null))

				  AND
				 -- (([Time] = CONVERT(NVARCHAR(16),@Time,112))  OR [Time] IS NULL))	
				  	([Time] = @Time OR 
					([Time] is null AND @Time is null) OR
					([Time] is null AND @Time ='') OR
					([Time] ='' AND @Time is null))
	

			
		
	--	 	IF NOT EXISTS( SELECT Ref,RequestedBy
	--			FROM DTABLE WHERE( [Ref] = @Ref AND  ([RequestedBy] = @RequestedBy OR 
 --   ([RequestedBy] is null )
 ))		

	

	BEGIN 
	 DECLARE @SQL NVARCHAR(MAX)
	   DECLARE @Year_Ref INTEGER
		SET @Year_Ref = (SELECT [Year_Ref] FROM YEAR_LOOKUP  WHERE [Year_no]= YEAR(GETDATE()))

 

    SET @SQL ='	INSERT INTO  [DTABLE] ( '
	  IF  @Year_Ref <> ''
            SET @SQL = @SQL + ' [Year_Ref] '	
	  IF  @RequestedBy <> ''
            SET @SQL =@SQL + ',' + '  [RequestedBy]  ' 
	  IF  @Ref <> ''
            SET @SQL = @SQL + ',' +  ' [Ref] '
	  IF  @Auth <> ''
            SET @SQL = @SQL + ',' + ' [Auth] '
      IF  @Date <> ''
            SET @SQL =@SQL + ',' + '[Date] ' 				
	  IF  @PickUpTime <> ''
            SET @SQL = @SQL + ',' + '[PickUpTime] '	
	  IF  @PatientName <> ''
            SET @SQL = @SQL + ',' +' [PatientName] '	
	  IF  @PickUpAddress <> ''
            SET @SQL = @SQL + ',' +' [PickUpAddress] '	
	  IF  @DoctorClinic <> ''
            SET @SQL = @SQL + ',' +' [DoctorClinic] '		
	  IF  @Address <> ''
            SET @SQL = @SQL+ ',' +' [Address] '		
	  IF  @Comments <> ''
            SET @SQL = @SQL+ ',' +' [Comments] '	
	  IF  @Interpreter <> ''
            SET @SQL = @SQL+ ',' +' [Interpreter] '	
	  IF  @CarRequest <> ''
            SET @SQL = @SQL + ',' +' [CarRequest] '	
	  IF  @SpecialInstructions <> ''
            SET @SQL = @SQL + ',' +' [SpecialInstructions] '			
	  IF  @FaxAuth <> ''
            SET @SQL = @SQL + ',' +' [FaxAuth] '	
	  IF  @MReportRecieved <> ''
            SET @SQL = @SQL + ',' +' [MReportRecieved] '		
	  IF  @EnteredBy <> ''
            SET @SQL = @SQL + ',' +' [EnteredBy] '				
	  IF  @Attachments <> ''
            SET @SQL = @SQL + ',' +' [Attachments] '			
	  IF  @Title <> ''
            SET @SQL = @SQL + ',' +' [Title] '
	  IF  @FaxNumber <> ''
            SET @SQL = @SQL + ',' +' [FaxNumber] '
	  IF  @Attn <> ''
            SET @SQL = @SQL + ',' +' [Attn] '	
	  IF  @ReqBy <> ''
            SET @SQL = @SQL + ',' +' [ReqBy] '	
	  IF  @Time <> ''
            SET @SQL = @SQL + ',' +' [Time] '		
		 SET @SQL = @SQL +')' + '      
     
     VALUES
	 ('
	 	IF  @Year_Ref   <> ''
		   SET @SQL = @SQL + '''' +   CAST(@Year_Ref  AS Nvarchar(20) )  + ''''		 
	 	IF  @RequestedBy   <> ''
		   SET @SQL = @SQL + ' , ''' +  CAST(@RequestedBy  AS Nvarchar(255) )  + ''''
		IF  @Ref <> ''
            SET @SQL =@SQL + ' , ''' +   CAST(@Ref  AS  Nvarchar(50))  + '''' 	
		IF  @Auth <> ''
            SET @SQL =@SQL + ' , ''' +   CAST(@Auth  AS  Nvarchar(50))  + ''''		
		IF  @Date <> ''
            SET @SQL =@SQL + ' , ''' +   CAST(@Date  AS  Nvarchar(20))  + '''' 		 		
	 	IF  @PickUpTime   <> ''
		   SET @SQL = @SQL  + ' , ''' +  CAST(@PickUpTime AS Nvarchar(20) )  + ''''  	 		
	 	IF  @PatientName   <> ''
		   SET @SQL = @SQL  + ' , ''' +  CAST(@PatientName AS Nvarchar(max) )  + ''''		
		IF  @PickUpAddress   <> ''
		   SET @SQL = @SQL  + ' , ''' +  CAST(@PickUpAddress AS Nvarchar(50) )  + ''''
		IF  @DoctorClinic  <> ''
		   SET @SQL = @SQL  + ' , ''' +  CAST(@DoctorClinic AS Nvarchar(50) )  + ''''		
		IF  @Address  <> ''
		   SET @SQL = @SQL  + ' , ''' +  CAST(@Address AS Nvarchar(50) )  + ''''
	   	IF  @Comments   <> ''
		   SET @SQL = @SQL  + ' , ''' +  CAST(@Comments  AS Nvarchar(max) )  + ''''
	   	IF  @Interpreter   <> ''
		   SET @SQL = @SQL  + ' , ''' +  CAST(@Interpreter   AS Nvarchar(50) )  + ''''	
		IF  @CarRequest   <> ''
		   SET @SQL = @SQL  + ' , ''' +  CAST(@CarRequest   AS Nvarchar(50) )  + ''''		
		IF  @SpecialInstructions   <> ''
		   SET @SQL = @SQL  + ' , ''' +  CAST(@SpecialInstructions    AS Nvarchar(max) )  + ''''				
		IF  @FaxAuth   <> ''
		   SET @SQL = @SQL  + ' , ''' +  CAST(@FaxAuth    AS Nvarchar(50) )  + ''''	 	
		IF  @MReportRecieved    <> ''
		   SET @SQL = @SQL  + ' , ''' +  CAST(@MReportRecieved     AS Nvarchar(max) )  + ''''	 
	 	IF  @EnteredBy    <> ''
		   SET @SQL = @SQL  + ' , ''' +  CAST(@EnteredBy   AS Nvarchar(max) )  + ''''	 
	    IF  @Attachments   <> ''
		   SET @SQL = @SQL  + ' , ''' +  CAST(@Attachments   AS Nvarchar(50) )  + ''''
		IF  @Title  <> ''
		   SET @SQL = @SQL  + ' , ''' +  CAST(@Title   AS Nvarchar(10) )  + ''''	 
		IF  @FaxNumber  <> ''
		   SET @SQL = @SQL  + ' , ''' +  CAST(@FaxNumber  AS Nvarchar(50) )  + ''''	 
		IF  @Attn  <> ''
		   SET @SQL = @SQL  + ' , ''' +  CAST(@Attn  AS Nvarchar(50) )  + ''''			
		IF  @ReqBy  <> ''
		   SET @SQL = @SQL  + ' , ''' +  CAST(@ReqBy  AS Nvarchar(50) )  + '''' 	
		IF  @Time  <> ''
		   SET @SQL = @SQL  + ' , ''' +  CAST(@Time   AS Nvarchar(20) )  + ''''

		   SET @SQL =  @SQL + ')'

        EXECUTE (@SQL)
	    
		
		
	DECLARE  @DateD DATETIME =null
    SET @DateD = convert(DATETIME, @Date , 103)



			INSERT INTO M_DEBUG(SQL) VALUES ( 'INSERT INTO [DTABLE]	
			[RequestedBy],
			[Date],
			[Ref],			
			[Auth],			
			[PickUpTime],
			[PatientName],
			[PickUpAddress],
			[DoctorClinic],
			[Address],
			[Comments],
			[Interpreter],
			[CarRequest],
			[SpecialInstructions],
			[FaxAuth],
			[MReportRecieved],
		    [EnteredBy],
			[Attachments],
		    [Title],
			[FaxNumber],
			[Attn],
			[ReqBy],
			[Time]
			
		) 
		VALUES
		(
			CAST(''' +@RequestedBy + '''  AS  Nvarchar(255)),
		    CAST('''+ convert(nvarchar(25), @DateD, 121) + '''  AS  nvarchar(25)),	
			CAST('''+@Ref +''' AS NVARCHAR(255)),			 	
			CAST('''+@Auth +''' AS Nvarchar(255)),					
			CAST('''+@PickUpTime +''' AS Nvarchar(255)),
		    CAST('''+@PatientName +''' AS Nvarchar(255)),
		    CAST('''+@PickUpAddress +''' AS Nvarchar(255)),
			CAST('''+@DoctorClinic +''' AS Nvarchar(255)),
			CAST('''+@Address +''' AS Nvarchar(255)),
			CAST('''+@Comments +''' AS Nvarchar(255)),
			CAST('''+@Interpreter +''' AS Nvarchar(255)),
			CAST('''+@CarRequest +''' AS Nvarchar(255)),
			CAST('''+@SpecialInstructions +''' AS Nvarchar(255)),
			CAST('''+@FaxAuth +''' AS Nvarchar(255)),
			CAST('''+@MReportRecieved +''' AS Nvarchar(255)),
			CAST('''+@EnteredBy +''' AS Nvarchar(255)),
			CAST('''+@Attachments +''' AS Nvarchar(255)),
			CAST('''+@Title +''' AS Nvarchar(255)),
			CAST('''+@FaxNumber +''' AS Nvarchar(255)),
			CAST('''+@Attn +''' AS Nvarchar(255)),
			CAST('''+@ReqBy +''' AS Nvarchar(255)),
			CAST('''+@Time +''' AS Nvarchar(255)))'
		
		)
		
	Return @@Rowcount
	END
	
	
	ELSE 	
	BEGIN

	
	  Declare   @UID Nvarchar(MAX)=null
	  -- NONCLUSTERED INDEX [NIX__UNQ__UID_]
	  SET @UID  = (SELECT [DTABLE_UID]
				FROM DTABLE WHERE( [RequestedBy] =@RequestedBy AND [Ref] =@Ref AND [Date] = CONVERT(NVARCHAR(20),@Date,112) AND [Auth] =@Auth) AND  [PickUpTime] = CONVERT(NVARCHAR(16),@PickUpTime,112) AND PatientName=@PatientName
				AND PickUpAddress =@PickUpAddress AND DoctorClinic=@DoctorClinic AND  [Address] =@Address AND Comments=@Comments AND  Interpreter=@Interpreter AND 
				CarRequest =@CarRequest AND SpecialInstructions=@SpecialInstructions AND FaxAuth=@FaxAuth AND MReportRecieved=@MReportRecieved AND 
				EnteredBy=@EnteredBy AND Attachments=@Attachments AND Title=@Title AND  FaxNumber=@FaxNumber AND Attn=@Attn AND DoctorClinic=@DoctorClinic AND  [Time] = CONVERT(NVARCHAR(16),@Time,112))			

	  SET @SQL = 'UPDATE  DTABLE  SET  [DeleteRecord] = '''+ ''  + '''
	              WHERE  [DTABLE_UID] = '''+ @UID  + ''''
				 
	  EXECUTE(@SQL)
	 
	
	    Return -1
	END

Open in new window

0
 
RIASAuthor Commented:
Can you suggest little bit here of using correct datatypes and set culture
0
 
Éric MoreauSenior .Net ConsultantCommented:
and even your latest SP is not working? Same error message?

What is the datatype of your Date field in the M_Debug table?

Can you confirm you have a valid value in your @DateD variable after you have set the value in it?
0
 
ste5anSenior DeveloperCommented:
E.g.

ALTER PROCEDURE [dbo].[M_AddDTABLE]
    @RequestedBy NVARCHAR(255) = NULL ,
    @ReqBy NVARCHAR(50) = NULL ,
    @Date DATE = NULL ,
    @Time TIME = NULL ,
    @PickUpTime TIME = NULL ,
    @PatientName NVARCHAR(MAX) = NULL ,
    @Ref NVARCHAR(50) ,
    @Auth NVARCHAR(50) = NULL ,
    @PickUpAddress NVARCHAR(50) = NULL ,
    @Address NVARCHAR(50) = NULL ,
    @Comments NVARCHAR(MAX) = NULL ,
    @Interpreter NVARCHAR(50) = NULL ,
    @CarRequest NVARCHAR(50) = NULL ,
    @SpecialInstructions NVARCHAR(MAX) = NULL ,
    @FaxAuth NVARCHAR(50) = NULL ,
    @MReportRecieved NVARCHAR(50) = NULL ,
    @EnteredBy NVARCHAR(MAX) = NULL ,
    @Attachments NVARCHAR(50) = NULL ,
    @Title NVARCHAR(10) = NULL ,
    @FaxNumber NVARCHAR(50) = NULL ,
    @Attn NVARCHAR(50) = NULL ,
    @DoctorClinic NVARCHAR(50) = NULL
AS 
    SET NOCOUNT ON;
    -- your code here

Open in new window


btw, it also makes no sense, that all parameters can be NULL.
0
 
RIASAuthor Commented:
Dated is datetime
0
 
Éric MoreauSenior .Net ConsultantCommented:
you are not answering my questions. Read again and please answer accordingly. Otherwise I will just help other people!
0
 
Éric MoreauSenior .Net ConsultantCommented:
we are currently at 36 comments on this question which should have already been solved if we would have had all the info in a timely manner!
0
 
RIASAuthor Commented:
Answer1 : yes its not working same error message
Answer 2: The date type is datetime
Answer3 : Yes, I have a valid value like ' 23/07/1998'
0
 
RIASAuthor Commented:
I have seen the original SP work before but suddenly it has failed today. I don't know if its to with the sql server setting.
0
 
Éric MoreauSenior .Net ConsultantCommented:
Are you sure the error you are reporting is happening on the row you have highlighted? The reason I am asking is that this row is just adding a big string (your query) into the SQL field of the M_DEBUG table.

just to prove it, comment out these lines:
   IF  @Date <> ''
            SET @SQL =@SQL + ',' + '[Date] ' 		
		IF  @Date <> ''
            SET @SQL =@SQL + ' , ''' +   CAST(@Date  AS  Nvarchar(20))  + '''' 	

Open in new window

0
 
ste5anSenior DeveloperCommented:
I have seen the original SP work before but suddenly it has failed today. I don't know if its to with the sql server setting.

It fails, because you accept NVARCHAR values. And the upper layer does not use the correct format to hand over the date and time values.

So either the upper layer has changed here, or your SQL Server settings have changed.

Your code itself is part of the problem, because you have not used strict data types.

In short: Just because "code works", does not mean that it is correct.
0
 
RIASAuthor Commented:
Yes Eric. It is definitely the same line. When I comment it it does wirk
0
 
Éric MoreauSenior .Net ConsultantCommented:
What is the datatype of your SQL Field in your M_DEBUG table?

can you try something simple? Comment out everything in your SP and just include a dummy statement like this:
			INSERT INTO M_DEBUG(SQL) VALUES ( 'INSERT INTO [DTABLE]	
			[RequestedBy],
			[Date],
			[Ref],			
			[Auth],			
			[PickUpTime],
			[PatientName],
			[PickUpAddress],
			[DoctorClinic],
			[Address],
			[Comments],
			[Interpreter],
			[CarRequest],
			[SpecialInstructions],
			[FaxAuth],
			[MReportRecieved],
		    [EnteredBy],
			[Attachments],
		    [Title],
			[FaxNumber],
			[Attn],
			[ReqBy],
			[Time]
			
		) 
		VALUES
		('

Open in new window


If you have ONLY that statement, does that work?

And if it is working, start adding some values one line at a time:
			INSERT INTO M_DEBUG(SQL) VALUES ( 'INSERT INTO [DTABLE]	
			[RequestedBy],
			[Date],
			[Ref],			
			[Auth],			
			[PickUpTime],
			[PatientName],
			[PickUpAddress],
			[DoctorClinic],
			[Address],
			[Comments],
			[Interpreter],
			[CarRequest],
			[SpecialInstructions],
			[FaxAuth],
			[MReportRecieved],
		    [EnteredBy],
			[Attachments],
		    [Title],
			[FaxNumber],
			[Attn],
			[ReqBy],
			[Time]
			
		) 
		VALUES
		(
			CAST(''' +@RequestedBy + '''  AS  Nvarchar(255)),'

Open in new window


And BTW, why do you need to cast what is already of a correct datatype. Try:
			INSERT INTO M_DEBUG(SQL) VALUES ( 'INSERT INTO [DTABLE]	
			[RequestedBy],
			[Date],
			[Ref],			
			[Auth],			
			[PickUpTime],
			[PatientName],
			[PickUpAddress],
			[DoctorClinic],
			[Address],
			[Comments],
			[Interpreter],
			[CarRequest],
			[SpecialInstructions],
			[FaxAuth],
			[MReportRecieved],
		    [EnteredBy],
			[Attachments],
		    [Title],
			[FaxNumber],
			[Attn],
			[ReqBy],
			[Time]
			
		) 
		VALUES
		(
			''' +@RequestedBy + '''),'

Open in new window

1
 
RIASAuthor Commented:
Thanks Eric will try and get back
0
 
RIASAuthor Commented:
Eric it worked when I did add the values individually.I changed the code removing all the converts and it worked like charm! Can't thank you enough for being so patient and pointing in the right direction.Really world requires more people like you.

Cheers!
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

  • 22
  • 15
  • 7
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now