Afzaal Khan
asked on
I need to assign NULL value in variable in Stored procedure when it comes NULL and value if it comes with some value
Hi Experts,
The above is the part of query in stored procedure where I need to assign value to @Process variable on the basis of conditions where ID that is not NULL is easily map to value that is coming on variable @ID but When I am mapping "AND Process.Empcode= (@Empcode) )code]" it is not giving me any result, the issue is that Empcode is coming NULL and I need to pass NULL if it is coming NULL and value if any value , So please help how to assign value to Empcode is value is NULL and if value is not NULL it will handle
SET @Process = (select Process_Name from Process_table process
Where (process.ID = @ID)
AND Process.Empcode= @Empcode)
The above is the part of query in stored procedure where I need to assign value to @Process variable on the basis of conditions where ID that is not NULL is easily map to value that is coming on variable @ID but When I am mapping "AND Process.Empcode= (@Empcode) )code]" it is not giving me any result, the issue is that Empcode is coming NULL and I need to pass NULL if it is coming NULL and value if any value , So please help how to assign value to Empcode is value is NULL and if value is not NULL it will handle
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Try this ..
select @Process = Process_Name from Process_table process
Where (process.ID = @ID) AND (Process.Empcode= @Empcode OR Process.Empcode IS NULL)
Hi,
ISNULL(Process.Empcode,-1) = ISNULL(@Empcode,-1)
OR below-
Please try this sample-
Please let me know if any changes are required from this example? your input and expected output.
Hope it helps!
ISNULL(Process.Empcode,-1)
--
select @Process = Process_Name from Process_table process
Where (process.ID = @ID) AND ISNULL(Process.Empcode,-1) = ISNULL(@Empcode,-1)
--
OR below-
Please try this sample-
--
CREATE TABLE testProcess
(
ID INT
,Process_Name VARCHAR(100)
,Empcode VARCHAR(100)
)
GO
INSERT INTO testProcess VALUES (1,'Pawan1','101'),(2,'Pawan2','102'),(3,'Pawan3',NULL)
GO
DECLARE @Process AS VARCHAR(100)
DECLARE @ID AS INT = 3
DECLARE @Empcode AS VARCHAR(100) = NULL
SELECT @Process = Process_Name
FROM testProcess WHERE ID = @ID AND ISNULL(Empcode,-1) = ISNULL(@Empcode,-1)
SELECT @Process
--
Please let me know if any changes are required from this example? your input and expected output.
Hope it helps!
Hi Afzaal,
Any luck with my last comment-
Select @Process = Process_Name from Process_table process
Where (process.ID = @ID) AND ISNULL(Process.Empcode,-1) = ISNULL(@Empcode,-1)
Hope it helps !
Any luck with my last comment-
Select @Process = Process_Name from Process_table process
Where (process.ID = @ID) AND ISNULL(Process.Empcode,-1)
Hope it helps !
ASKER
Thanks Pavan that returned value.
But can you suggest why my below query is not printing
The value coming here is NULL , when value comes NOT NULL it is printing
But can you suggest why my below query is not printing
select @Processcode = rec.Process_code from #TempProcess rec where ROWID = @RecordIncrementor
print @Processcode
The value coming here is NULL , when value comes NOT NULL it is printing
Try this -
select @Processcode = rec.Process_code from #TempProcess rec where ROWID = @RecordIncrementor
SELECT @Processcode
--Below will go in the messages tab
select @Processcode = rec.Process_code from #TempProcess rec where ROWID = @RecordIncrementor
PRINT @Processcode
Hope it helps!
select @Processcode = rec.Process_code from #TempProcess rec where ROWID = @RecordIncrementor
SELECT @Processcode
--Below will go in the messages tab
select @Processcode = rec.Process_code from #TempProcess rec where ROWID = @RecordIncrementor
PRINT @Processcode
Hope it helps!
ASKER
Not working Pavan
What are you getting in SELECT @Processcode
If possible please provide me a screen shot?
If possible please provide me a screen shot?
Any luck sir?
ASKER
Nopes,Please check I am passing below code
select @Processcode = rec.Process_code from #TempProcess rec where ROWID = @RecordIncrementor
SELECT @Processcode
select @Processcode = rec.Process_code from #TempProcess rec where ROWID = @RecordIncrementor
SELECT @Processcode
Can you please provide me few rows from the temp table and the value you are passing in @RecordIncrementor to check what you are doing.?
SELECT @Processcode = rec.Process_code from #TempProcess rec where ISNULL(ROWID,-1) = ISNULL(@RecordIncrementor, -1)
SELECT @Processcode
My query is working fine in all the cases.
SELECT @Processcode = rec.Process_code from #TempProcess rec where ISNULL(ROWID,-1) = ISNULL(@RecordIncrementor,
SELECT @Processcode
My query is working fine in all the cases.
ASKER
Thanks Pavan
ASKER
Open in new window
The above code is fine when value is coming if value is NULL then it is not returing value, it is returning with below command
Open in new window
when value is NULL