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?
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.
0
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
0
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?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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

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

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!!!
0
É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.
0
RIASAuthor Commented:
You are correct Eric,it does insert value 'NULL'
0
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.
0
RIASAuthor Commented:
True Vitor,that solves my issue
0
É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

1
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
0
RIASAuthor Commented:
Cheers!
0
RIASAuthor Commented:
Eric thanks !
0
Éric MoreauSenior .Net ConsultantCommented:
are you sure you selected the right comment as the answer?
0
RIASAuthor Commented:
Yes,Eric  Vitor solved the issue.
0
É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
0
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.
0
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 .
0
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')
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
What's the error?
0
RIASAuthor Commented:
It insert Just NULL.
there is no error message box but instead of the query its NULL
0
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

1
RIASAuthor Commented:
Ok ,thanks
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.