Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
ryand009
Frequent Visitor

sql_variant data type in Lookup activity

Hello

 

I’ve got a stored procedure that can return a student’s date of birth either as a date or as in bigInt (i.e.  number of seconds from Jan 1 1970).  The @outputValue in the procedure is a SQL_VARIANT data type

 

The date/ bigInt return value is based on the input parameter @returnType, see below ...

 

Trouble is Microsoft Fabric doesn’t seem to like SQL_VARIANT. I’m using the query below in a Lookup activity, but it keeps falling over because of the SQL_VARIANT

 

DECLARE @outputValue sql_variant;

 

EXEC [wm].[sp_GetStudentDoB]

    @SchemaName = 'dbo',

    @TableName = 'tblStudents',

    @returnType = 'date', --BigInt

    @outputValue = @outputValue OUTPUT;

 

SELECT CONVERT(DATETIME, @outputValue) AS NewDate;

 

Is there any work around for this?

Any suggestions much appreciated.

Thanks in advance 

Roy

 

1 ACCEPTED SOLUTION
v-hashadapu
Community Support
Community Support

Hi @ryand009 , Thank you for reaching out to the Microsoft Community Forum.

 

Microsoft Fabric doesn't support the sql_variant data type in pipeline activities like Lookup. When your stored procedure returns a sql_variant as an output parameter, Fabric fails to interpret it since it expects outputs to have a clearly defined, supported SQL type, like datetime, bigint, varchar, etc.

 

To work around this, you need to introduce a wrapper stored procedure. The wrapper should call your original procedure and explicitly convert the sql_variant output to a supported type before returning it. Instead of returning the value as an output parameter, the wrapper should return the final result using a SELECT statement. This way, Fabric can read the output as a standard, well-defined scalar value, which will work smoothly in your Lookup activity.

 

If you have control over the original stored procedure, the better long-term solution would be to avoid using sql_variant altogether and return the result directly with a SELECT, already cast to the appropriate type.

 

Preprocess data with a stored procedure before loading into Lakehouse - Microsoft Fabric | Microsoft...

View solution in original post

1 REPLY 1
v-hashadapu
Community Support
Community Support

Hi @ryand009 , Thank you for reaching out to the Microsoft Community Forum.

 

Microsoft Fabric doesn't support the sql_variant data type in pipeline activities like Lookup. When your stored procedure returns a sql_variant as an output parameter, Fabric fails to interpret it since it expects outputs to have a clearly defined, supported SQL type, like datetime, bigint, varchar, etc.

 

To work around this, you need to introduce a wrapper stored procedure. The wrapper should call your original procedure and explicitly convert the sql_variant output to a supported type before returning it. Instead of returning the value as an output parameter, the wrapper should return the final result using a SELECT statement. This way, Fabric can read the output as a standard, well-defined scalar value, which will work smoothly in your Lookup activity.

 

If you have control over the original stored procedure, the better long-term solution would be to avoid using sql_variant altogether and return the result directly with a SELECT, already cast to the appropriate type.

 

Preprocess data with a stored procedure before loading into Lakehouse - Microsoft Fabric | Microsoft...

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June FBC25 Carousel

Fabric Monthly Update - June 2025

Check out the June 2025 Fabric update to learn about new features.