Link to home
Start Free TrialLog in
Avatar of Afzaal Khan
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,

SET @Process = (select  Process_Name  from Process_table process
	                Where  (process.ID = @ID)
			AND Process.Empcode= @Empcode) 

Open in new window



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
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Afzaal Khan
Afzaal Khan

ASKER

Thanks Pavan but the issue is with NULL value only.

select  @Process = Process_Name  from Process_table process
Where  (process.ID = @ID) AND Process.Empcode= @Empcode) 

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
select  @Process = Process_Name  from Process_table process
Where  (process.ID = @ID) AND Process.Empcode IS NULL) 

Open in new window

when value is NULL
Try this ..

select  @Process = Process_Name  from Process_table process
Where  (process.ID = @ID) AND (Process.Empcode= @Empcode OR Process.Empcode IS NULL) 

Open in new window

Hi,

ISNULL(Process.Empcode,-1) = ISNULL(@Empcode,-1)

--
select  @Process = Process_Name  from Process_table process
Where  (process.ID = @ID) AND ISNULL(Process.Empcode,-1) = ISNULL(@Empcode,-1)
--

Open in new window


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

--

Open in new window


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 !
Thanks Pavan that returned value.

But can you suggest why my below query is not printing

select @Processcode = rec.Process_code  from #TempProcess rec	   where ROWID = @RecordIncrementor
print @Processcode

Open in new window


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!
Not working Pavan
What are you getting in SELECT @Processcode

If possible please provide me a screen shot?
Any luck sir?
Nopes,Please check I am passing below code


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.
Thanks Pavan