Solved

Stored Procedure error

Posted on 2016-08-29
45
62 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 22
  • 15
  • 7
  • +1
45 Comments
 
LVL 34

Expert Comment

by:ste5an
ID: 41774790
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 40

Expert Comment

by:lcohan
ID: 41774791
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
ID: 41774815
CAST('''+ convert(nvarchar(25), @Date, 121) + '''  AS  nvarchar(25)),       

Date is of datatype  datetime
Any suggestion on  convert(nvarchar(25), @Date, 121)
0
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
LVL 70

Expert Comment

by:Éric Moreau
ID: 41774816
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
ID: 41774818
Because it works on datatype field Date but, fails on datetime datatype
0
 

Author Comment

by:RIAS
ID: 41774821
Eric Thanks will try and get back
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 41774822
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
ID: 41774823
Eric i am using sql server 2014
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 41774841
>>Eric i am using sql server 2014

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

Author Comment

by:RIAS
ID: 41774846
Will take note of this.Wanted to add Sql server.
0
 

Author Comment

by:RIAS
ID: 41774849
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 34

Expert Comment

by:ste5an
ID: 41774858
Have you tried my suggestion?
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 41774860
how and where is declared @Date ?
0
 

Author Comment

by:RIAS
ID: 41774864
 @Date nvarchar(20)= NULL
0
 

Author Comment

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

Author Comment

by:RIAS
ID: 41774867
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 70

Expert Comment

by:Éric Moreau
ID: 41774870
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
ID: 41774871
Ok will paste the entire SP
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 41774873
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 34

Expert Comment

by:ste5an
ID: 41774883
Why not changing the input date type? Using NVARCHAR here has a code smell.
0
 

Author Comment

by:RIAS
ID: 41774886
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 34

Expert Comment

by:ste5an
ID: 41774893
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
 

Author Comment

by:RIAS
ID: 41774902
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 70

Expert Comment

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

Author Comment

by:RIAS
ID: 41774929
Eric,

I have posted my oroginal SP .
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 41774931
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
ID: 41774935
Cool! Will pase the new SP
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 41774937
>>I have posted my oroginal SP .

please integrate the suggested modifications
0
 
LVL 34

Expert Comment

by:ste5an
ID: 41774940
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 70

Expert Comment

by:Éric Moreau
ID: 41774944
>>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
ID: 41774953
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
ID: 41774958
Can you suggest little bit here of using correct datatypes and set culture
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 41774972
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 34

Assisted Solution

by:ste5an
ste5an earned 250 total points
ID: 41774975
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
ID: 41774978
Dated is datetime
0
 
LVL 70

Expert Comment

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

Expert Comment

by:Éric Moreau
ID: 41774981
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
ID: 41774986
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
ID: 41774991
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 70

Expert Comment

by:Éric Moreau
ID: 41775005
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 34

Expert Comment

by:ste5an
ID: 41775017
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
ID: 41775047
Yes Eric. It is definitely the same line. When I comment it it does wirk
0
 
LVL 70

Accepted Solution

by:
Éric Moreau earned 250 total points
ID: 41775097
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
ID: 41775193
Thanks Eric will try and get back
0
 

Author Closing Comment

by:RIAS
ID: 41775344
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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
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
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

734 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