Stored procedure query with if

RIAS
RIAS used Ask the Experts™
on
Hello,
How can i add a validation in the query below :
             
	 	@Ref nvarchar(20)= NULL,
	@BookingType nvarchar(50)= NULL,
	@CorresType nvarchar(50)= NULL,
	@Date nvarchar(20) =NULL,
    @SentTo nvarchar(50) =NULL,
	@Attention nvarchar (255)= NULL,
	@FaxNumber nvarchar(50)= NULL,
	@Regarding nvarchar(255)= NULL,
	@Authority  nvarchar (50)= NULL,
	@SentBy nvarchar (50) =NULL,
	@Comments nvarchar(255)= NULL,	
	@RequestedBy nvarchar (255)= NULL,
	@GuestName nvarchar(255)= NULL,
	@RoomTypeRouting nvarchar(50)= NULL,
	@ArrivalDate VARCHAR(20)= NULL,	
	@DurationofStay nvarchar(50) =NULL,
	@Extendedto nvarchar(20)= NULL,
	@ConfirmationNumber nvarchar(255)= NULL,
	@e_UID Nvarchar(MAX)=null
	 


 INSERT INTO DEBUG(SQL) VALUES ( 'UPDATE  table1
		(
			   [Ref]
      ,[BookingType]
      ,[CorresType]    
      ,[SentTo]
      ,[Attention]
      ,[FaxNumber]
      ,[Regarding]
      ,[Authority]
      ,[SentBy]
      ,[Comments]    
      ,[RequestedBy]
      ,[GuestName]
      ,[RoomTypeRouting]
      ,[ArrivalDate]
      ,[DurationofStay]
      ,[Extendedto]
	  ,[Date]
      ,[ConfirmationNumber]   
		) 
		VALUES
		(
			''' +@Ref + ''' )
			  '''+ @BookingType + ''' ),
			'''+@CorresType +''' ),		
		    '''+@SentTo +''' ),
			'''+@Attention +''' ),
			'''+@FaxNumber + ''')
			'''+@Regarding + '''),
			'''+@Authority +''' ),
			'''+@Comments +''' ),
			'''+@SentBy +''' ),
			'''+@Comments +''' ),
			'''+@RequestedBy +''' ),
			'''+@GuestName +''' )
			'''+@RoomTypeRouting +''' )
		    '''+@ArrivalDate +''' )
		    '''+@DurationofStay +''' )
validation required [b]if @extendedto is not ''[/b]
				'''+@Extendedto +''' )
				    WHERE [e_UID] =''' + @e_UID + '''')	
			
	  

Open in new window


Cheers
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Which kind of validation?
If it's outside the INSERT then a simple IF..ELSE will be fine.
If it's inside the INSERT then you should use a CASE statement.

Author

Commented:
Ah Vitor!

            need to check if             '''+@Extendedto +''' ) is null or '' or empty  
if not then add @Extendedto or else set it to null
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Use ISNULL then. Even I guess if you don't do nothing it will work.
What happens if one of the previous variables are NULL?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
If the @Extendedto is null or '' .It does not work .

Author

Commented:
Error is cannot convert varchar to tyoe date

Author

Commented:
How to write the condition ISNull

Author

Commented:
'+ isnull(@Extendedto,)+' ) ??
IT Engineer
Distinguished Expert 2017
Commented:
Try this:
ISNULL(@Extendedto, 'NULL')

It will return 'NULL' to be concatenated to the main string.

Author

Commented:
Thanks a ton Vitor!!!
Éric MoreauSenior .Net Consultant
Top Expert 2016

Commented:
If I am not mistaken, by using the latest syntax, you will insert the string NULL into the field and not a NULL  value.

Author

Commented:
You are correct Eric,it does insert value 'NULL'
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
If I am not mistaken, by using the latest syntax, you will insert the string NULL into the field and not a NULL  value.
I guess is what is pretended. The INSERT is a single string that has an UPDATE command. For auditing purposes I'll also guess.

Author

Commented:
True Vitor,that solves my issue
Éric MoreauSenior .Net Consultant
Top Expert 2016

Commented:
this is the kind of syntax you need to really insert a NULL value (instead of a string) into your query:
DECLARE @Extendedto VARCHAR(10) = NULL -- 'test'
DECLARE @x VARCHAR(100) 
SET @x = 'insert into (f1,f2,f3) values(''v1'', ''v2'', ' + ISNULL('''' + @Extendedto + '''', 'null') + ')'
SELECT @x

Open in new window

Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
True Vitor,that solves my issue
Good.
Then please close this question by choosing the comment that helped you so others Experts can know this is solved and don't loose time that can be used to help others.
Thank you

Author

Commented:
Cheers!

Author

Commented:
Eric thanks !
Éric MoreauSenior .Net Consultant
Top Expert 2016

Commented:
are you sure you selected the right comment as the answer?

Author

Commented:
Yes,Eric  Vitor solved the issue.
Éric MoreauSenior .Net Consultant
Top Expert 2016

Commented:
I am not sure you see the difference. Vitor answer will insert a string value containing NULL.

What I am proposing is to insert a real database null value (not a string).

so later if you do :
select * from debug where Extendedto is null

Open in new window

the code from vitor won't work but mine will.

so it is really up to you do decide if you need a real null value or a string containing the characters NULL
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
@Eric, I already told you in my comment  #a41777931 that she needs the string NULL and not really the NULL value.

Author

Commented:
Eric, Its only for Audit and its not an actual insert in the table.It just stores the information as what the user is doing .

Author

Commented:
Vitor,
Is there any change for the int field as the code works for date field but fails on int field


ISNULL(@Extendedto, 'NULL')
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
What's the error?

Author

Commented:
It insert Just NULL.
there is no error message box but instead of the query its NULL
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
When you concatenate NULL to a value the result is always NULL so you'll need to use the same solution to all variables:
INSERT INTO DEBUG(SQL) VALUES ( 'UPDATE  table1
		(
(...)
	'''+ISNULL(@DurationofStay, 'NULL')+''' )
	'''+ISNULL(@Extendedto, 'NULL')+''' )

Open in new window

Author

Commented:
Ok ,thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial