asked on

Stored procedure to find existing record

Hello,
I have a stored procedure to find existing record in the table.if it exists I am updating thetable and if not I am inserting the row.
It works fine for varchar/nvarchar but fais for money datatype.
Table1 has colmoney1 which is null and I try to insert another row which has value null it still tries to insert the record though similar null erecord already exist in table
total is the money column

Any conversion need to be done for total to check null ?

RIAS

ASKER

IF NOT EXISTS( SELECT  PreparedBy,[Date],PayableTo,PaymentType,Code,Curr,Total

					FROM [table1] WHERE( [PreparedBy] =@PreparedBy AND  [Date] = CONVERT(NVARCHAR(16),@Date,112) AND 
					[PayableTo] =@PayableTo AND [PaymentType] =@PaymentType  AND [Code] =@Code  AND 
					[Curr] =@Curr AND  [Total] = CASE when @Total='' then NULL else CAST(@Total  AS  nvarchar(max)) end))
		


					
		
	BEGIN 
	 DECLARE @SQL NVARCHAR(MAX)


    SET @SQL ='	INSERT INTO [dbo].[table1] ( '

  	
    IF  @SPR# <> ''
            SET @SQL = @SQL + '  [SPR#] '				
    IF  @PreparedBy <> ''
            SET @SQL =@SQL + ',' + '  [PreparedBy]  ' 			 
   	IF  @Date <> ''
            SET @SQL =@SQL + ',' + '  [Date] ' 	 
    IF  @PayableTo <> ''
            SET @SQL =@SQL + ',' + '  [PayableTo]  ' 			 
     IF  @PaymentType <> ''
            SET @SQL =@SQL + ',' + '  [PaymentType]  ' 			 
     IF  @Code <> ''
            SET @SQL =@SQL + ',' + '  [Code]  ' 			 
     IF  @Curr <> ''
            SET @SQL =@SQL + ',' + '  [Curr] ' 			 
     IF  @Total <> ''
            SET @SQL =@SQL + ',' + '  [Total] ' 			 
    
	 SET @SQL = @SQL +')' + '      
     
     VALUES
	 ('

	 	
	    IF  @SPR# <> ''
            SET @SQL =@SQL + '''' +   CAST(@SPR#  AS  Nvarchar(50))  + '''' 		 
     	IF  @PreparedBy <> ''
            SET @SQL = @SQL + ' , ''' +  CAST(@PreparedBy   AS  Nvarchar(50))  + ''''
	    IF  @Date <> ''
            SET @SQL = @SQL + ' , ''' +  CAST(@Date   AS  Nvarchar(20))  + ''''  
   		IF  @PayableTo <> ''
            SET @SQL = @SQL + ' , ''' +  CAST(@PayableTo   AS  Nvarchar(50))  + '''' 		
	    IF  @PaymentType <> ''
            SET @SQL = @SQL + ' , ''' +  CAST(@PaymentType   AS  Nvarchar(50))  + ''''			
		IF  @Code  <> ''
            SET @SQL = @SQL + ' , ''' +  CAST(@Code   AS  Nvarchar(50))  + '''' 	
	    IF  @Curr  <> ''
            SET @SQL = @SQL + ' , ''' +  CAST(@Curr    AS  Nvarchar(MAX))  + ''''  
	    IF  @Total  <> ''
            SET @SQL = @SQL + ' , ''' +  CAST(@Total  AS  Nvarchar(MAX))  + '''' 
	   
		
	  SET @SQL =  @SQL + ')'

       EXECUTE (@SQL)

		Return @@Rowcount
	END
	
	ELSE 	
	BEGIN
	  DECLARE @SP# nvarchar(MAX)

		SET @SP#  = (SELECT [SPR#] FROM  [table1]    WHERE( [PreparedBy] =@PreparedBy AND  [Date] = CONVERT(NVARCHAR(16),@Date,112) AND 
					[PayableTo] =@PayableTo AND [PaymentType] =@PaymentType  AND [Code] =@Code  AND 
					[Curr] =@Curr AND [Total] =@Total  ))

	  -- NONCLUSTERED INDEX [NIX__UNQ__UID_]
	
	  SET @sql = 'UPDATE [table1]  SET  [DeleteRecord] = '''+ ''  + '''
	              WHERE  [SPR#] = '''+ @SP#  + ''''
				 
	  EXEC(@sql)
	
	    Return @@Rowcount
	END
	END

Open in new window

RIAS

ASKER

Total is a money datatype column

Arana (G.P.)

try:
AND coalesce([Total],"") = @Total
(assuming your parameter is not null but an empty string)

RIAS

ASKER

Arana,Cheers! will try and get back mate

RIAS

ASKER

Still the same,the parameter looks like null .
I get correct correct results when I query total is null and no results if I query total =''

ASKER CERTIFIED SOLUTION

mankowitz

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Chris Luttrell

Any conversion need to be done for total to check null ?

Yes!
Where is @Total declared and what type is it? Are you meaning the @Total value to check is NULL or the column value you are checking against in the database is NULL or both?
You say "Table1 has colmoney1" but I do not see that column anywhere in your query so I do not know what needs to be looked at to correct. You reference total or @total several places.

RIAS

ASKER

"Table1 has colmoney1" was just an example.

Total is the money column .

Need to check against in the database is NULL

SOLUTION

Arana (G.P.)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

RIAS

ASKER

Sure

RIAS

ASKER

Chris Special thanks for the concern

RIAS

ASKER

Arana,
This worked :

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

Chris Luttrell

I was going to suggest the same thing as Arana, that's why I marked it as a good comment. Glad you got it working!
*edit