Return value from dynamic SQL

[woogoo]

Given this:

CREATE FUNCTION [dbo].[GetCSVExpectedF] 
(
	@Table nvarchar(32),
	@Type char(1)
)
RETURNS
datetime
AS
BEGIN
	DECLARE @ID bigint ;
	DECLARE @Ref nvarchar(32) ;
	DECLARE @Result datetime ;
	DECLARE @Query nvarchar(1024) ;
	SELECT @ref = Reference FROM CSV_LookUp WHERE ((FileName = @Table) AND (Type = @Type)) ;
	SELECT @ID = MAX(ID) from CSV_Schedule WHERE (Settlement_Date < GETDATE()) ;
	SET @Query = N'SELECT @Result = ' + @ref + ' FROM CSV_Schedule where ID = (' + @ID + ');' ;
	EXEC @Query ;
	RETURN @Result ;
END

Where @ref is a Column within another table, @Table is a table name and @Type is the Table type.

How can I get the value of this function:

Executing it with:

SELECT [dbo].[GetCSVExpectedF] (N'Info', N'P') ;

Results in this: Error converting data type nvarchar to bigint.
Or depening on how the @Query is constructed:
Could not find stored procedure 'SELECT @Result = MAX(SettlementDay) FROM Headers_Info WHERE (Type = 'P')'.

However, copying and pasting the SQL in the '' from the error message results in the correct answer!

Am I making sense?

--
Bubba



woogoo

 

[djj55]

You need to convert @ID to a character so the @Query does not end up an integer

SET @Query = N'SELECT @Result = ' + @ref + ' FROM CSV_Schedule where ID = (' + [red]cast([/red]@ID [red]as varchar(10))[/red] + ');' ;

djj

 

[bborissov]

First you can't get a result if you use EXEC. If you use sp_executesql you could use OUTPUT parameter to get what you want.
Try this (not tested)

CREATE FUNCTION [dbo].[GetCSVExpectedF]
(
    @Table nvarchar(32),
    @Type char(1)
)
RETURNS
datetime
AS
BEGIN
    DECLARE @ID bigint ;
    DECLARE @Ref nvarchar(32) ;
    DECLARE @Result datetime ;
    DECLARE @Query nvarchar(1024) ;
    SELECT @ref = Reference FROM CSV_LookUp WHERE ((FileName = @Table) AND (Type = @Type)) ;
    SELECT @ID = MAX(ID) from CSV_Schedule WHERE (Settlement_Date < GETDATE()) ;
    SET @Query = N'SELECT @Result = ' + @ref + ' FROM CSV_Schedule where ID = @ID;' ;
    EXEC sp_executesql @Query, N'@ID bigint, @result datetime OUTPUT', @Id, @result = @result OUTPUT
    RETURN @Result ;
END

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[bborissov]

O, one more thing, if you use SQL Server 2000 or smaller GETDATE() is not allowed in function.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[woogoo]

Thanks.

bborissov, trying your way I get:
Only functions and extended stored procedures can be executed from within a function.

And without it I get:
Could not find stored procedure 'SELECT @Result = MAX(SettlementDay) FROM Headers_Info WHERE (Type = 'P')'.

I think it's back to the drawing board with this one.

woogoo

 

[bborissov]

Then instead of function make this a Stored Procedure:

CREATE PROCEDURE [dbo].[GetCSVExpectedF]
(
    @Table nvarchar(32),
    @Type char(1)
)
AS
BEGIN
    DECLARE @ID bigint ;
    DECLARE @Ref nvarchar(32) ;
    DECLARE @Result datetime ;
    DECLARE @Query nvarchar(1024) ;
    SELECT @ref = Reference FROM CSV_LookUp WHERE ((FileName = @Table) AND (Type = @Type)) ;
    SELECT @ID = MAX(ID) from CSV_Schedule WHERE (Settlement_Date < GETDATE()) ;
    SET @Query = N'SELECT @Result = ' + @ref + ' FROM CSV_Schedule where ID = @ID;' ;
    EXEC sp_executesql @Query, N'@ID bigint, @result datetime OUTPUT', @Id, @result = @result OUTPUT
    SELECT @Result ;
END

and call it:

EXEC [dbo].[GetCSVExpectedF] N'Info', 'P'

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.