How can you use an execute statement before your SELECT statement in an SSRS dataset without getting the fields from the first stored procedure?

Question

I have a drop-down list that I want to populate in SSRS. The stored procedure that I am using to populate the drop-down list executes another stored procedure that refreshes a table and return values. When I use this new stored procedure to call the first one and return only one field, I get a row for every row in the first stored procedure. How can I only get the one field (which is a distinct select) in my drop-down list?

Example:

Stored Procedure 1


CREATE STORED PROC sp_UpdateTable
	EXEC sp_Table1
	SELECT A,B,C FROM Table1
END PROC

Stored Procedure 2
CREATE STORED PROC sp_UpdateTable
   	EXEC sp_Table1
 	SELECT DISTINCT B FROM Table1
END PROC

The desired result is that I want my drop-down to just show the unique refreshed values from column B, but I am getting A,B, and C returned in my dataset.

Answer 1

Hi @Sherri Norris

Are you trying to create a parameter dropdown? I don't know T-sql very well, but according to the information I searched, the statement to create a stored procedure is:

CREATE PROCEDURE SelectAllCustomers
AS
SELECT * FROM Customers
GO;

My test:

For more details, you can refer to this link: https://www.tutorialgateway.org/create-ssrs-report-using-stored-procedure/.

If the answer is helpful, please click Accept Answer and Up-Vote for the same. If you have any questions, please feel free to let me know.

Best regards,

Aniya