CODEWITHSUNDEEP
sqlsql-serverstored-procedurestable-valued-parameters
Pro-n-apps
Pro-n-apps

Reputation: 55

Table Valued Must declare the scalar variable

I have stored procedure that accepts a table-valued parameter.

Here is code for that:

ALTER PROCEDURE [dbo].[ConsolidateInspection]
    @TblConsolidation DTProductCodePO READONLY
AS
BEGIN
    BEGIN TRANSACTION
        DECLARE @CurrentFormNo AS BIGINT = 0

        INSERT INTO [InspectionDB].[dbo].[FormHeader] 
                      (PONumber, POSuffix, [Productcode], [Stratum], [SpecNumber], [Engineer],
                       [ProductManager], [CountryCode], [SupplierCode], [Supplier],
                       [QTY], [EAGTIN], [INGTIN], [OUGTIN], [InspectionType], [InspectorName])
            SELECT 
                @CurrentFormNo = P.FormNo   
            FROM 
                FormHeader P 
            INNER JOIN 
               @TblConsolidation T ON P.PONumber = T.PO 
                                   AND P.POSuffix = T.POSuffix 
                                   AND P.Productcode = T.ProductCode
                                   AND P.CountryCode = T.CountryCode
           ORDER BY 
               PO, P.POSuffix, P.Productcode


        COMMIT TRANSACTION
    END

I am trying to execute above stored procedure:

exec ConsolidateInspection @TblConsolidation dbo.Temp

dbo.Temp is the name of a table that has same properties

I tried 

exec ConsolidateInspection @TblConsolidation Select * from Temp

It is throwing an error saying

Must declare the scalar variable "@TblConsolidation"

Any help?

Upvotes: 0

Views: 996

Answers (1)

Siamak Ferdos
Siamak Ferdos

Reputation: 3299

dbo.Temp is not same as DTProductCodePO! DTProductCodePO is a table type which you should define a variable of it then fill it, then us it as a parameter like this:

Declare @Tbl DTProductCodePO

Insert into @Tbl(your fields here ...) 
SELECT ...SAME FIELD HERE... from dbo.Temp

exec ConsolidateInspection @Tbl

Upvotes: 3

Related Questions