Stored procedure query with if

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
RIASAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
RIASAuthor 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ãoMSSQL Senior EngineerCommented:
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?
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

RIASAuthor Commented:
If the @Extendedto is null or '' .It does not work .
RIASAuthor Commented:
Error is cannot convert varchar to tyoe date
RIASAuthor Commented:
How to write the condition ISNull
RIASAuthor Commented:
'+ isnull(@Extendedto,)+' ) ??
Vitor MontalvãoMSSQL Senior EngineerCommented:
Try this:
ISNULL(@Extendedto, 'NULL')

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RIASAuthor Commented:
Thanks a ton Vitor!!!
Éric MoreauSenior .Net ConsultantCommented:
If I am not mistaken, by using the latest syntax, you will insert the string NULL into the field and not a NULL  value.
RIASAuthor Commented:
You are correct Eric,it does insert value 'NULL'
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
RIASAuthor Commented:
True Vitor,that solves my issue
Éric MoreauSenior .Net ConsultantCommented:
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ãoMSSQL Senior EngineerCommented:
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
RIASAuthor Commented:
Cheers!
RIASAuthor Commented:
Eric thanks !
Éric MoreauSenior .Net ConsultantCommented:
are you sure you selected the right comment as the answer?
RIASAuthor Commented:
Yes,Eric  Vitor solved the issue.
Éric MoreauSenior .Net ConsultantCommented:
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ãoMSSQL Senior EngineerCommented:
@Eric, I already told you in my comment  #a41777931 that she needs the string NULL and not really the NULL value.
RIASAuthor 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 .
RIASAuthor 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ãoMSSQL Senior EngineerCommented:
What's the error?
RIASAuthor Commented:
It insert Just NULL.
there is no error message box but instead of the query its NULL
Vitor MontalvãoMSSQL Senior EngineerCommented:
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

RIASAuthor Commented:
Ok ,thanks
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.