Dynamic Sql returned from SP returns a int instead of the resultset

[malathyr]

I have a SP and I have dynamic SQL getting executed and it returns a resultset. But when the Reverse POCO generates a method for the SP and it returns the result as int (for @return_value). is there a way it can detect the columns Sample SP

USE [Northwind]
GO
/****** Object:  StoredProcedure [dbo].[DynamicPivot]    Script Date: 08/15/2013 18:41:53 ******/
SET ANSI_NULLS ON
go
SET FMTONLY OFF
GO
SET QUOTED_IDENTIFIER ON
GO
alter PROCEDURE [dbo].[DynamicPivot]
AS
--pivot dinámico
DECLARE @CatPVT AS NVARCHAR(MAX), @Categorias AS varchar(20)
DECLARE @CatID INT
SET @CatID=(SELECT MIN(CategoryID) FROM Categories)
SET @Categorias = ( SELECT CategoryName FROM Categories WHERE CategoryID = @CatID)
SET @CatPVT = N''
WHILE @Categorias IS NOT NULL
BEGIN
  SET @CatPVT = @CatPVT + N',['+ @Categorias +N']'
  SET @Categorias = (SELECT TOP(1) CategoryName
                     FROM Categories WHERE CategoryID > @CatID
                     ORDER BY CategoryID ASC)
  SET @CatID=(SELECT MIN(CategoryID) FROM Categories Where Categoryname=@Categorias)
END
SET @CatPVT = SUBSTRING(@CatPVT, 2, LEN(@CatPVT))

DECLARE @sql AS nvarchar(MAX)
SET @sql = N'SELECT *
            FROM (SELECT P.ProductName, C.CategoryName, (OD.UnitPrice * OD.Quantity) AS Monto
                  FROM Products P
                    INNER JOIN dbo.[Order Details] OD
                        ON P.ProductID=OD.ProductID
                    INNER JOIN Categories C
                    ON C.CategoryID=P.CategoryID
            ) PIV
            PIVOT (SUM(Monto) FOR  CategoryName IN ('+ @CatPVT  + ')) AS Child'

EXEC sp_executesql @sql

Also, if I want to set validation for some columns can I it as partial class and set validation attributes in the partial class created by me?

[sjh37]

Sorry, there is nothing I can do. It is SQL Server that tells me what gets returned. Perhaps you can specify the columns instead of SELECT *, or alternatively, write a wrapper stored proc which calls this one and returns the columns correctly.

[ErikEJ]

Is there not an alternative to SET FMTONLY ON? Which may work better?

[malathyr]

Let me check on this.

Thank you for the quick reply. Appreciate it very much

Thanks Ramesh

On Wed, Aug 2, 2017 at 2:33 PM, Erik Ejlskov Jensen < [email protected]> wrote:

Is there not an alternative to SET FMTONLY ON? Which may work better?

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/sjh37/EntityFramework-Reverse-POCO-Code-First-Generator/issues/316#issuecomment-319774901, or mute the thread https://github.com/notifications/unsubscribe-auth/ADkZCS-Wp0gPIDtIJKoVhEA5-DrL_1wkks5sUM8NgaJpZM4Orhgo .

[malathyr]

Thank you Simon/Eric for the quick reply. Appreciate it very much

is the following possible by any chance

If I want to set validation for some columns can I it as partial class and set validation attributes in the partial class created by me?

[sjh37]

Other cases related to this are: #173, #183, #260

[martijnburgers]

FMTONLY doesn't work with the generator.

My workaround for now is to exclude them from generation and write them myself.

[TimSirmovics]

My workaround for this was to check at the start of the procedure if the parameters passed in are NULL, as that is how the generator passes them in. If so create a table variable representing the actual output, select from that and return immediately.

CREATE PROCEDURE spExample
	@SomeValue varchar(50)
AS
BEGIN
	SET NOCOUNT ON;

	-- Reverse POCO generator
	IF (@SomeValue IS NULL)
	BEGIN
		DECLARE @t TABLE(ColumnOne uniqueidentifier not null, ColumnTwo varchar(20))
		SELECT * FROM @t
		RETURN
	END

	-- Actual SP contents here.

END
GO