asked on Stored procedure query with if
Hello,
How can i add a validation in the query below :
Cheers
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 + '''')
Cheers
Microsoft SQL ServerMicrosoft SQL Server 2005Microsoft SQL Server 2008
Last Comment
RIAS
ASKER
Ah Vitor!
need to check if '''+@Extendedto +''' ) is null or '' or empty
if not then add @Extendedto or else set it to null
need to check if '''+@Extendedto +''' ) is null or '' or empty
if not then add @Extendedto or else set it to null
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?
What happens if one of the previous variables are NULL?
ASKER
If the @Extendedto is null or '' .It does not work .
ASKER
Error is cannot convert varchar to tyoe date
ASKER
How to write the condition ISNull
ASKER
'+ isnull(@Extendedto,)+' ) ??
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thanks a ton Vitor!!!
If I am not mistaken, by using the latest syntax, you will insert the string NULL into the field and not a NULL value.
ASKER
You are correct Eric,it does insert value 'NULL'
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.
ASKER
True Vitor,that solves my issue
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
True Vitor,that solves my issueGood.
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
ASKER
Cheers!
ASKER
Eric thanks !
are you sure you selected the right comment as the answer?
ASKER
Yes,Eric Vitor solved the issue.
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 :
so it is really up to you do decide if you need a real null value or a string containing the characters 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
so it is really up to you do decide if you need a real null value or a string containing the characters NULL
@Eric, I already told you in my comment #a41777931 that she needs the string NULL and not really the NULL value.
ASKER
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 .
ASKER
Vitor,
Is there any change for the int field as the code works for date field but fails on int field
ISNULL(@Extendedto, 'NULL')
Is there any change for the int field as the code works for date field but fails on int field
ISNULL(@Extendedto, 'NULL')
What's the error?
ASKER
It insert Just NULL.
there is no error message box but instead of the query its NULL
there is no error message box but instead of the query its NULL
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')+''' )
ASKER
Ok ,thanks
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.