Selecting specified query from multiple select statement inside a single stored procedure

Good Day everyone!

I’ve been searching everywhere on how to get a specified query using if-else statement inside a single stored procedure but still no luck on how to achieve those function.

Here’s the T-Sql

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[getHistoryList]
@documentNo varchar(150)=null,
@material varchar(150)=null,
@action varchar(10)=null
as

declare @query nvarchar(max);

if (@action = null)
begin
set @query = 'select top 50 * from s_HistoryList order by documentDate desc'
exec sp_executesql @statement=@query
end 

else if (@action = 'search')
begin
set @query = 'select * from s_HistoryList where documentNo like %'+@documentNo+'% or materialName like %'+@material+'%'
exec sp_executesql @statement=@query
end
go

When exec command is called, it only return Command(s) completed successfully instead the value/table of the selected command. Since the @action is null, it should return the following query and when @ action is supplied with value, it should return the 2nd query.

When if-else statement is removed, it returns the first query but there’s no means of accessing the 2nd query.

I saw this method here.

Thank you and I appreaciate any help that this post can get.

Answer

You would need to print the variable to the value of the variable @action. Eg:

begin
set @query = 'select * from s_HistoryList where documentNo like %'+@documentNo+'% or materialName like %'+@material+'%'
exec sp_executesql @statement=@query
print 'Below is the value of @action'
print @action
end


A point i would make is that NULL comparison wouldnt work with = sign. 

if (@action = null) to be changed to 
if (@action IS NULL)