Solved

Stored procedure query with if

Posted on 2016-08-31
27
40 Views
Last Modified: 2016-08-31
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
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
  • 15
  • 8
  • 4
27 Comments
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 41777866
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
 

Author Comment

by:RIAS
ID: 41777872
Ah Vitor!

            need to check if             '''+@Extendedto +''' ) is null or '' or empty  
if not then add @Extendedto or else set it to null
0
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 41777883
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:RIAS
ID: 41777887
If the @Extendedto is null or '' .It does not work .
0
 

Author Comment

by:RIAS
ID: 41777889
Error is cannot convert varchar to tyoe date
0
 

Author Comment

by:RIAS
ID: 41777892
How to write the condition ISNull
0
 

Author Comment

by:RIAS
ID: 41777903
'+ isnull(@Extendedto,)+' ) ??
0
 
LVL 50

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 41777906
Try this:
ISNULL(@Extendedto, 'NULL')

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

Author Comment

by:RIAS
ID: 41777910
Thanks a ton Vitor!!!
0
 
LVL 70

Expert Comment

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

Author Comment

by:RIAS
ID: 41777928
You are correct Eric,it does insert value 'NULL'
0
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 41777931
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
 

Author Comment

by:RIAS
ID: 41777933
True Vitor,that solves my issue
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 41777935
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
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 41777937
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
 

Author Closing Comment

by:RIAS
ID: 41777938
Cheers!
0
 

Author Comment

by:RIAS
ID: 41777939
Eric thanks !
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 41777943
are you sure you selected the right comment as the answer?
0
 

Author Comment

by:RIAS
ID: 41777944
Yes,Eric  Vitor solved the issue.
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 41777991
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
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 41777998
@Eric, I already told you in my comment  #a41777931 that she needs the string NULL and not really the NULL value.
0
 

Author Comment

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

Author Comment

by:RIAS
ID: 41778011
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
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 41778025
What's the error?
0
 

Author Comment

by:RIAS
ID: 41778042
It insert Just NULL.
there is no error message box but instead of the query its NULL
0
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 41778060
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
 

Author Comment

by:RIAS
ID: 41778065
Ok ,thanks
0

Featured Post

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SELECT INTO from XML 6 45
How to use Recordset, returned from a SPROC as the recourdsource of a form. 24 45
SQL Job Failed 6 24
T-SQL Query 9 28
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.

742 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