Solved

Stored Procedure error

Posted on 2016-08-29
45
39 Views
Last Modified: 2016-08-29
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
Comment
Question by:RIAS
  • 22
  • 15
  • 7
  • +1
45 Comments
 
LVL 32

Expert Comment

by:Stefan Hoffmann
Comment Utility
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
 
LVL 39

Expert Comment

by:lcohan
Comment Utility
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
 

Author Comment

by:RIAS
Comment Utility
CAST('''+ convert(nvarchar(25), @Date, 121) + '''  AS  nvarchar(25)),       

Date is of datatype  datetime
Any suggestion on  convert(nvarchar(25), @Date, 121)
0
 
LVL 69

Expert Comment

by:Éric Moreau
Comment Utility
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
 

Author Comment

by:RIAS
Comment Utility
Because it works on datatype field Date but, fails on datetime datatype
0
 

Author Comment

by:RIAS
Comment Utility
Eric Thanks will try and get back
0
 
LVL 69

Expert Comment

by:Éric Moreau
Comment Utility
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
 

Author Comment

by:RIAS
Comment Utility
Eric i am using sql server 2014
0
 
LVL 69

Expert Comment

by:Éric Moreau
Comment Utility
>>Eric i am using sql server 2014

adding sql 2005 and 2008 to your topics is misleading!
0
 

Author Comment

by:RIAS
Comment Utility
Will take note of this.Wanted to add Sql server.
0
 

Author Comment

by:RIAS
Comment Utility
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
 
LVL 32

Expert Comment

by:Stefan Hoffmann
Comment Utility
Have you tried my suggestion?
0
 
LVL 69

Expert Comment

by:Éric Moreau
Comment Utility
how and where is declared @Date ?
0
 

Author Comment

by:RIAS
Comment Utility
 @Date nvarchar(20)= NULL
0
 

Author Comment

by:RIAS
Comment Utility
I remeber it had worked before.Don.t know suddenly it has errored
0
 

Author Comment

by:RIAS
Comment Utility
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
 
LVL 69

Expert Comment

by:Éric Moreau
Comment Utility
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
 

Author Comment

by:RIAS
Comment Utility
Ok will paste the entire SP
0
 
LVL 69

Expert Comment

by:Éric Moreau
Comment Utility
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
 
LVL 32

Expert Comment

by:Stefan Hoffmann
Comment Utility
Why not changing the input date type? Using NVARCHAR here has a code smell.
0
 

Author Comment

by:RIAS
Comment Utility
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
 
LVL 32

Expert Comment

by:Stefan Hoffmann
Comment Utility
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:RIAS
Comment Utility
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
 
LVL 69

Expert Comment

by:Éric Moreau
Comment Utility
I see no trace of your Conversion from format 103. Where is your @DateD?
0
 

Author Comment

by:RIAS
Comment Utility
Eric,

I have posted my oroginal SP .
0
 
LVL 69

Expert Comment

by:Éric Moreau
Comment Utility
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
 

Author Comment

by:RIAS
Comment Utility
Cool! Will pase the new SP
0
 
LVL 69

Expert Comment

by:Éric Moreau
Comment Utility
>>I have posted my oroginal SP .

please integrate the suggested modifications
0
 
LVL 32

Expert Comment

by:Stefan Hoffmann
Comment Utility
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
 
LVL 69

Expert Comment

by:Éric Moreau
Comment Utility
>>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
 

Author Comment

by:RIAS
Comment Utility
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
 

Author Comment

by:RIAS
Comment Utility
Can you suggest little bit here of using correct datatypes and set culture
0
 
LVL 69

Expert Comment

by:Éric Moreau
Comment Utility
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
 
LVL 32

Assisted Solution

by:Stefan Hoffmann
Stefan Hoffmann earned 250 total points
Comment Utility
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
 

Author Comment

by:RIAS
Comment Utility
Dated is datetime
0
 
LVL 69

Expert Comment

by:Éric Moreau
Comment Utility
you are not answering my questions. Read again and please answer accordingly. Otherwise I will just help other people!
0
 
LVL 69

Expert Comment

by:Éric Moreau
Comment Utility
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
 

Author Comment

by:RIAS
Comment Utility
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
 

Author Comment

by:RIAS
Comment Utility
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
 
LVL 69

Expert Comment

by:Éric Moreau
Comment Utility
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
 
LVL 32

Expert Comment

by:Stefan Hoffmann
Comment Utility
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
 

Author Comment

by:RIAS
Comment Utility
Yes Eric. It is definitely the same line. When I comment it it does wirk
0
 
LVL 69

Accepted Solution

by:
Éric Moreau earned 250 total points
Comment Utility
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
 

Author Comment

by:RIAS
Comment Utility
Thanks Eric will try and get back
0
 

Author Closing Comment

by:RIAS
Comment Utility
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now