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

Question

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.

@action = null could never be true. Try IF @action IS NULL. — Aaron Bertrand
– Aaron Bertrand, Commented Jan 11, 2021 at 3:42

George Joseph · Accepted Answer · 2021-01-11 02:44:24Z

1

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)

answered Jan 11, 2021 at 2:44

George Joseph

5,93213 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

John Michael Over a year ago

I tried changing the '=' sign in @ action into 'IS'. The output is returns the expected table. Thank you for pointing out. The problem is, the 2nd query. I would like to return the table that the sp_executesql makes, not the value that the @ action have. The output result is still the command completed message.

George Joseph Over a year ago

To do that you would remove the sp_executesql portion and write the query directly (ie not in string limiters)

Charlieface Over a year ago

@JohnMichael SQL injection alert. What if someone passes through @documentNo='''; DROP TABLE; SELECT '''

John Michael Over a year ago

@Charlieface, I have end-to-end encryption and data sanitation for passing data and accessing this stored procedure since there's a possibility of injection even when using stored procedure.

Charlieface Over a year ago

@JohnMichael That's the wrong way to go about it. You don't sanitize, you parameterize. Even dynamic can be parameterized

Collectives™ on Stack Overflow

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

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related