Error while Running Predict Function in Synapse Studio

Question

Getting an error Incorrect Syntax near 'Model' while running the Prediction function in T-SQL statement. The below query was tried on Azure Synapse Studio as well SSMS and did not work

DECLARE @modelvarbinary(max) = (SELECT Model FROM [wwi_mcw].[ASAMCWMLModel] WHERE Id = (SELECT Top(1) max(ID)
FROM [wwi_mcw].[ASAMCWMLModel]));

Select d.,p. from PREDICT(MODEL=@model,DATA=[wwi_mcw].[ProductPCA]as d) WITH(prediction real)as p;

GitHub link Referenced for the above example: https://github.com/microsoft/MCW-Azure-Synapse-Analytics-end-to-end-solution

Answer 1

Hello @SnehaSheshadri-7717,

Thanks for the question and also for using this forum.

On a very quick glance I can see the issue with the line

DECLARE @modelvarbinary(max) = (SELECT Model FROM [wwi_mcw].[ASAMCWMLModel] WHERE Id = (SELECT Top(1) max(ID)
FROM [wwi_mcw].[ASAMCWMLModel]));

It should be ( please beware that there is a space between @model & varbinary )

DECLARE @model varbinary(max) = (SELECT Model FROM [wwi_mcw].[ASAMCWMLModel] WHERE Id = (SELECT Top(1) max(ID)
FROM [wwi_mcw].[ASAMCWMLModel]));

Please do let em know how it goes .

Thanks & stay safe
Himanshu
Please do consider to click on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community members

Answer 2

Running into the same issue :

CREATE PROCEDURE [dbo].[score_model_proc]
AS
BEGIN
-- Select input scoring data and assign aliases.

declare @model VARBINARY( max )
select @model = model from dbo.model_store where id = 'nyc_taxi_tip_predict:1'

SELECT
        CAST([fareAmount] AS [real]) AS [fareAmount],
        CAST([paymentType] AS [bigint]) AS [paymentType],
        CAST([passengerCount] AS [bigint]) AS [passengerCount],
        CAST([tripDistance] AS [real]) AS [tripDistance],
        [tripTimeSecs],
        CAST([pickupTimeBin] AS [varchar]) AS [pickupTimeBin]
into [dbo].[nyctx_input]
FROM [dbo].[nyc_taxi]

-- Using T-SQL Predict command to score machine learning models. 
SELECT *
FROM PREDICT(
    MODEL = @model,
    DATA = [dbo].[nyctx_input],
    RUNTIME = ONNX) 
WITH ([output_label] [bigint])
END
GO

-- Execute the above stored procedure.
EXEC [dbo].[score_model_proc]

/*
Error :

Started executing query at Line 1
Parse error at line: 23, column: 11: Incorrect syntax near '='.
Total execution time: 00:00:01.270
*/