issue calling tSQLt.ResultSetFilter when my sproc is expecting a UDT as parameter

[Ivan Gonzalez]

Hi,

I'm having an issue calling tSQLt.ResultSetFilter when my sproc is expecting a UDT as parameter.

[depletion].[test IG_Test] failed: (Error) A .NET Framework error occurred during execution of user-defined routine or aggregate "ResultSetFilter": 

System.Data.SqlClient.SqlException: Must declare the scalar variable "@vudt".

I have attached  the code below

Thank you

Ivan

CREATE PROC depletion.IG_Test2 @udt dbo.internalUsagePointCodeIdentifier READONLY AS

SELECT --'New_Readings',

    'internalUsagePointCode' internalUsagePointCode,

    'usagePointCode' usagePointCode,

    'meterRead' reading,

    'mr_intervalBegin' intervalStartInclusive,

    'mr_intervalEnd' intervalEndExclusive;

GO

DECLARE @internalUsagePointCodes dbo.internalUsagePointCodeIdentifier,  @internalUsagePointCode UNIQUEIDENTIFIER = '0c3a89ce-ddbe-4ad6-98ad-1b709d23f447';

INSERT INTO @internalUsagePointCodes

VALUES (@internalUsagePointCode);

DECLARE @SQL NVARCHAR(4000), @PARAM NVARCHAR(4000);

SET @SQL = N'EXEC depletion.IG_Test2 ' + N' @vudt ';

SET @SQL = 'EXEC tSQLt.ResultSetFilter 1,''' + @SQL + '''';

SET @PARAM = N' @vudt dbo.internalUsagePointCodeIdentifier readonly';

INSERT INTO ##rs1

( internalUsagePointCode,

    usagePointCode,

    reading,

    intervalBeginInclusive,

    intervalEndExclusive)

EXEC sp_executesql @SQL, @PARAM, @vudt = @internalUsagePointCodes;

[depletion].[test IG_Test] failed: (Error) A .NET Framework error occurred during execution of user-defined routine or aggregate "ResultSetFilter": 

System.Data.SqlClient.SqlException: Must declare the scalar variable "@vudt".

System.Data.SqlClient.SqlException: 

   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

   at System.Data.SqlClient.SqlInternalConnectionSmi.EventSink.DispatchMessages(Boolean ignoreNonFatalMessages)

   at System.Data.SqlClient.SqlDataReaderSmi.InternalNextResult(Boolean ignoreNonFatalMessages)

   at System.Data.SqlClient.SqlDataReaderSmi.NextResult()

   at System.Data.SqlClient.SqlCommand.RunExecuteReaderSmi(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream)

   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, 

TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)

   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)

   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)

   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)

   at tSQLtCLR.TestDatabaseFacade.executeCommand(SqlString Command)

   at tSQLtCLR.ResultSetFilter.sendSelectedResultSetToSqlContext(SqlInt32 resultsetNo, SqlString command)

   at tSQLtCLR.StoredProcedures.ResultSetFilter(SqlInt32 resultSetNo, SqlString command)

.[16,1]{tSQLt.ResultSetFilter,0}

[tS...@sqlity.net]

Hi Ivan,

you cannot pass a table variable into a dynamic SQL string. However, what you can do is include the code to create the table variable and then insert data into it in the same SQL string that also has the call to you proc.

That would look something like this:

SET @SQL = N'DECLARE @internalUsagePointCodes dbo.internalUsagePointCodeIdentifier;INSERT INTO @internalUsagePointCodes VALUES (''0c3a89ce-ddbe-4ad6-98ad-1b709d23f447'');EXEC depletion.IG_Test2 @internalUsagePointCodes';

EXEC tSQLt.ResultSetFilter 1,@SQL;

Sebastian

--
You received this message because you are subscribed to the Google Groups "tSQLt" group.
To unsubscribe from this group and stop receiving emails from it, send an email to tsqlt+un...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/tsqlt/b70ba392-e338-453c-bd39-9b1bc60ed416n%40googlegroups.com.

[Ivan Gonzalez]

Hi Sebastian,

Thank you.

I  will give it a try.

Thank you

Ivan

[Ivan Gonzalez]

Thank you Sebastian,

Your solution worked great!

Sincerely,

Ivan

On Tuesday, December 8, 2020 at 4:35:24 PM UTC-5 tSQLt wrote: