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

21 views
Skip to first unread message

Ivan Gonzalez

unread,
Dec 8, 2020, 9:04:51 AM12/8/20
to tSQLt
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

unread,
Dec 8, 2020, 4:35:24 PM12/8/20
to ts...@googlegroups.com
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

unread,
Dec 9, 2020, 8:30:54 AM12/9/20
to tSQLt
Hi Sebastian,

Thank you.
I  will give it a try.

Thank you
Ivan

Ivan Gonzalez

unread,
Dec 9, 2020, 2:05:38 PM12/9/20
to tSQLt
Thank you Sebastian,

Your solution worked great!

Sincerely,
Ivan

On Tuesday, December 8, 2020 at 4:35:24 PM UTC-5 tSQLt wrote:
Reply all
Reply to author
Forward
0 new messages