I need a SQL guru, this is driving me crazy... I'm getting an error when executing dynamic SQL with a user-defined Table Type:
Error: Operand type clash: VariableTableType is incompatible with VariableTableType
Problem: It doesn’t seem to really declare the VariableTableType in the [Database1] database even though the Use [Database1] is right above it. The XT function has to run in the [Database1] database for it to work.
If I run the exec with the dbname, it works- ex: exec database1.dbo.sp_executesql @cmdTest – but I have many client databases that have the same database structure (different data) where I have to pass in the client database name so I can’t hardcode it that way. If I make @dbname=’MainDatabase’ it will run – just doesn’t run against the right database.
[MainDatabase] Contains main stored proc that has the dynamic SQL: @dbname=’Database1’
DECLARE @cmdTEST NVARCHAR(MAX)=N'USE '+@dbname +'
DECLARE @t as dbo.VariableTableType
INSERT @t
SELECT VariableName,[Value],InstanceID FROM #tVarTble
SELECT dbo.XT(''Test string'',7153,45,@t)RtnValue
'
exec sp_executesql @cmdTEST
Also tried passing in the VariableTableType and put the declaration outside of the dynamic sql. --exec sp_executesql @cmdTEST,N'@t VariableTableType READONLY',t=@t
[Database1] (this function also exists in the [MainDatabase]
ALTER FUNCTION [dbo].[XT]
(
@text nvarchar(max),
@instanceID INT,
@storeID INT,
@vt dbo.VariableTableType READONLY
)
RETURNS nvarchar(max)
AS BEGIN
… code here
RETURN @return
END
VariableTableType exists in the [MainDatabase] and [Database1]
CREATE TYPE [dbo].[VariableTableType] AS TABLE(
[VariableName] [NVARCHAR](50) NULL,
[Value] [NVARCHAR](50) NULL,
[InstanceID] [INT] NULL
)
