We have a stored procedure mentioned below in SQL Server. Which returns two different tables depending on @pagename parameter. Radzen is able to read the table only for stored procedure 1. However, when we introduce a set of IF statements to return different tables depending on the selected @pagename parameter. Radzen is unable to read the table returned by the stored procedure. We have two separate pages for the two tables and depending on @pagename parameter we want the individual pages to display the correct table. The stored procedures work correctly when executed on SQL server
STORED PROCEDURE 1:
ALTER PROCEDURE [dbo].[usp_Master_GetData_DataGrids_JCM]
-- Add the parameters for the stored procedure here
@pagename varchar(100),
@runid int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
Declare @uspname varchar(MAX) = NULL, @ResultfromSP varchar(4192)=NULL
BEGIN TRY
SELECT * from ufn_Get_SurfaceTreatmentData_JCM(@runid)
END TRY
BEGIN CATCH
SELECT @ResultfromSP = 'Error Line , '+ Convert(char,ERROR_LINE()) + ', Error Message , '+ ERROR_MESSAGE() + ', Error Number , '+ CONVERT(char,ERROR_NUMBER())
END CATCH
END
STORED PROCEDURE 2:
ALTER PROCEDURE [dbo].[usp_Master_GetData_DataGrids_JCM]
-- Add the parameters for the stored procedure here
@pagename varchar(100),
@runid int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
Declare @uspname varchar(MAX) = NULL, @ResultfromSP varchar(4192)=NULL
BEGIN TRY
IF @pagename = 'surfacetreatment'
BEGIN
SELECT * from ufn_Get_SurfaceTreatmentData_JCM(@runid)
END
IF @pagename = 'isrepair'
BEGIN
SELECT * from ufn_Get_ISrepairData_JCM(@runid)
END
END TRY
BEGIN CATCH
SELECT @ResultfromSP = 'Error Line , '+ Convert(char,ERROR_LINE()) + ', Error Message , '+ ERROR_MESSAGE() + ', Error Number , '+ CONVERT(char,ERROR_NUMBER())
END CATCH
END
Regards,
Abhishek