In writing the stored procedure we always need to work with the default value for the proper execution of the programs and the stored procedure we have made for the application.

So for doing it sometimes we have scenarios when we don't have to get anything from  the column in that case we can assign it with the default value.

Doing that will treat that value as the column value and nothing is fetched from that column.

ALTER PROC [dbo].[uspGetNudgesDetails]
@AccessToken VARCHAR(50),
@BeaconID VARCHAR(50)=NULL,
@BeaconMajorID as VARCHAR(20)=NULL,
@BeaconMinorID as VARCHAR(20)=NULL
AS

BEGIN

	DECLARE @CompanyID BIGINT =0
	DECLARE @TargetUserID BIGINT=0
	DECLARE @UserName varchar(50)


-- Get user and company

	SELECT @UserName=[FirstName] + ' ' + LastName, 
			@TargetUserID=[ID],
			@CompanyID=[CompanyID]
		FROM [User] WHERE AccessToken=@AccessToken



		UPDATE NUDGE
		SET DaysTillExpire = CASE  WHEN DATEDIFF(day, GETDATE(), ExpiryDate) < 0 THEN 0
									ELSE DATEDIFF(day, GETDATE(), ExpiryDate)
							END				
		,
		HoursTillExpire = CASE  WHEN DATEDIFF( hour, GETDATE(), ExpiryDate) < 0 THEN 0
									ELSE DATEDIFF(day, GETDATE(), ExpiryDate)
							END				

		,
		IsExpired = CASE WHEN DATEDIFF(day, GETDATE(), ExpiryDate) < 0 AND DATEDIFF( hour, GETDATE(), ExpiryDate) < 0 THEN 1
							ELSE 0
							END
-- For general message with no location

	SELECT N.[ID],
		N.[Message],
		@BeaconID as [BeaconID],
        N.[LocationID],
		NULL AS Location,
		N.[TargetUserID],
		@UserName as [TargetUser],	
		N.[IsCompleted],
		N.[IsDelivered],
		N.[IsExpired],
		N.[IsDeleted],
		N.[Url],		
		N.[DaysTillExpire],
		N.[HoursTillExpire],
		--DATEDIFF( day, GETDATE(), N.ExpiryDate ) AS DaysTillExpire,
		--DATEDIFF( hour, GETDATE(), N.ExpiryDate ) AS HoursTillExpire,
		(U.[FirstName] + ' ' + U.[LastName]) AS [CreatedBy],
		CONVERT(VARCHAR(20), N.[CreatedDate], 103) as [CreatedDate],
		@CompanyID as [CompanyID]
		
		FROM [NUDGE] AS N 
				INNER JOIN  [User] AS U ON N.[CreatedBy]=U.[ID] 
		WHERE N.[LocationID]=0 
				AND N.[TargetUserID]=@TargetUserID
				AND N.[IsDeleted]=0 
				AND N.[IsExpired]=0 
				AND N.[IsCompleted]=0 

	UNION

-- For location dependent messages

	SELECT  N.[ID],
		N.[Message],
		@BeaconID as [BeaconID],
		N.[LocationID],
		L.[Location],
		N.[TargetUserID],
		@UserName as [TargetUser],	
		N.[IsCompleted],
		N.[IsDelivered],
		N.[IsExpired],
		N.[IsDeleted],		
		N.[Url],		
		N.[DaysTillExpire],
		N.[HoursTillExpire],
		--DATEDIFF( day, GETDATE(), N.ExpiryDate ) AS DaysTillExpire,
		--DATEDIFF( hour, GETDATE(), N.ExpiryDate ) AS HoursTillExpire,	
		(U.[FirstName] + ' ' + U.[LastName]) AS [CreatedBy],
		CONVERT(VARCHAR(20), N.[CreatedDate], 103) as [CreatedDate],
		@CompanyID as [CompanyID]
		
		
		FROM [NUDGE] AS N 
			INNER JOIN  [User] AS U ON N.[CreatedBy]=U.[ID] 
			INNER JOIN [LOCATION] AS L ON L.[ID]=N.[LocationID] 
		WHERE N.[LocationID] IN (SELECT [LocationID] FROM [Location] WHERE [BeaconUUID]=@BeaconID and [CompanyID]=@CompanyID AND [BeaconMajor]=@BeaconMajorID AND [BeaconMinor]=@BeaconMinorID)
			AND N.[TargetUserID]=@TargetUserID
			AND N.[IsDeleted]=0 
			AND N.[IsExpired]=0 
			AND N.[IsCompleted]=0 
END

Here we have assigned the location column with the NULL value.

We have fetched the nudges/messages from the location table means we are fetching messages that is meant for the specific location wifi device called beacons and if any message contains no location then it will be fetched without finding any location.