CODEWITHSUNDEEP
sql-servert-sqltable-valued-parameters
Dan
Dan

Reputation: 97

Table-Valued Parameter Recieving Invalid Data Type Error

I have an array of data from a console app I've developed that I need to transfer into a SQL database. As SQL injections are volatile, I decided to try passing it through a stored procedure via table-valued parameter. I created the table type with no issues and it is listed under 'User-Defined Table Types' folder. The issue I am having is when I try to create my stored procedure, I receive this error:

Msg 2715, Level 16, State 3, Procedure sp_IsertBackupData, Line 2 [Batch Start Line 0] Column, parameter, or variable #1: Cannot find data type VeeamTableType. Parameter or variable '@VeeamTableType' has an invalid data type.

Does anyone have any insight on what could be the issue here?

I have already tried refreshing the local cache. 

CREATE PROCEDURE sp_IsertBackupData

@VeeamTableType VeeamTableType READONLY 

AS 

BEGIN

    DECLARE @q varchar(1000)
    SET @q= 'SELECT * FROM' + @VeeamTableType


    INSERT INTO ASManagement.dbo.VeeamBackupResults 
    (
    Id, 
    Server,
    BackupLogFileName,
    BackupLogFileSize,
    CreatedOn 
    )

    EXEC (@q)

END


SELECT * FROM ASManagement.VeeamBackupResults

CREATE TYPE VeeamBackupResults.VeeamTableType as TABLE

(

    Id int primary key,

    Server varchar(500) null,

    BackupLogFileName varchar (500) null, 

    BackupLogFileSize float null, 

    CreatedOn datetime null

)

Upvotes: 0

Views: 3412

Answers (2)

Serg
Serg

Reputation: 22811

You need no dynamic sql

CREATE PROCEDURE sp_IsertBackupData   
      @VeeamTableType VeeamTableType READONLY 
AS    
BEGIN
    INSERT INTO ASManagement.dbo.VeeamBackupResults 
    (
      Id, 
      Server,
      BackupLogFileName,
      BackupLogFileSize,
      CreatedOn 
    )
    SELECT * 
    FROM @VeeamTableType;
END

Fiddle to create proc

Upvotes: 0

ahmed abdelqader
ahmed abdelqader

Reputation: 3560

The root cause of appearing the error is the stored Procedure cannot find VeeamTableType because you create it under VeeamBackupResults schema and you are trying to create the Procedure under default schema (dbo at most of you dose not change the default to be another one )

So for fixing the error, you should follow one of next solutions:-

1) Create procedure under the VeeamBackupResults schema

Try CREATE PROCEDURE VeeamBackupResults.sp_IsertBackupData

instead of CREATE PROCEDURE sp_IsertBackupData

Or 2) pass VeeamTableType with its correct schema

Try: @VeeamTableType VeeamBackupResults.VeeamTableType READONLY

instead of: @VeeamTableType VeeamTableType READONLY

Or 3) Create the user definde table variable into default schema

Try: CREATE TYPE VeeamTableType as TABLE

instead of: CREATE TYPE VeeamBackupResults.VeeamTableType as TABLE

Upvotes: 1

Related Questions