Using sp_executesql in order to call a stored procedure with table type input parameter, from different Database

  • Hello,

    I would like to kindly ask for some help on the following situation.

    I have 2 databases into one server. DB1 and DB2.

    From DB1 I want to call a stored procedure (TestProc) in DB2, as follows:

    USE DB1

    DECLARE

    @T TestTableType /*this table type exists both in DB1 and DB2*/

    INSERT INTO @T VALUES ('HI')

    exec sp_executesql N'exec DB2.dbo.TestProc @T', N'@T TestTableType readonly', @T

    This fails with a message: Operand type clash: TestTableType is incompatible with TestTableType

    I was wondering if I can still use sp_executesql somehow.

    I wouldn't like to use EXEC with dynamic SQL and temp tables, in order to avoid injection scenarios.

    Any ideas appreciated! Thanks.

  • Why are you using sp_execute to execute a SP?

    You can directly use:

    exec DB2.dbo.TestProc @T

    As you are using Dynamic SQL the table type variable @T is out of scope w.r.t to the exec statement.

  • Hello Manoj,

    Thanks a ton for your answer!

    To answer your question.. I don't use: exec DB2.dbo.TestProc @T because I don't want to hardcode the database name.

    I realized that my description was not very clear so I made a script to demonstrate what I look for.

    In general I want to be able to execute a stored procedure dynamically by passing the database name and at the same time minimizing the risk for SQL injection if I also pass other parameters (like @Var1 in the example below). That's why I used sp_executesql instead of using a simple EXEC.

    Please note that Example 1 and 2 work fine. Example 3 (where I use sp_executesql) fails because I cannot pass table valued parameters across databases. But this leaves me with the option of Example 2, that I use temptables, but i think it is not safe because of simple string concatenation.

    I hope the code with comment below helps:

    --==============/*GENERAL NOTES*/================

    -- ##############################################

    /* For this example I used 2 databases:[DB_AMERICAS] and [DB_EUROPE] */

    /* The assumption is that these 2 databases are exactly the same containinig different data*/

    /* They have same structure, same number of tables, same Stored Procs but just different data, */

    /* since they come from different origins, e.g AMERICAS and EUROPE*/

    /* They both have the same SP called dbo.TestProc defined as follows */

    --===============================================

    -- execute this on both databases, in order to create the SP. (NOTE: run the table type scripts creation first --see below)

    /*CREATE PROCEDURE [dbo].[TestProc]

    @Var1 nvarchar(10),

    @Var2 dbo.TestTableType2 READONLY

    AS

    BEGIN

    SELECT @Var1

    SELECT * FROM @Var2

    END

    */

    --=================================

    /*This SP has as input an NVARCHAR and a Table Type Parameter*/

    /*The table type parameter is defined as*/

    --================================

    /*

    CREATE TYPE [dbo].[TestTableType2] AS TABLE(

    [id] [nvarchar](max) NULL

    )

    */

    --================================

    -- GOAL: Being able to dynamically execute the TestProc SP

    -- By switching the database name.

    --===============================================

    -- ##############################################

    --*--

    --Example 1) Simple Execution on just one database

    ------------------------------------------

    USE [DB_AMERICAS] --here I just define the database.

    GO

    DECLARE

    @Var1 nvarchar(10) ='Hello',

    @Var2 dbo.TestTableType2

    INSERT INTO @Var2 VALUES ('There')

    EXEC[dbo].[TestProc] @Var1, @Var2

    GO

    --Example 2) Simple Dynamic Execution using EXEC. Database is defined by the database variable

    ------------------------------------------

    DECLARE

    @Var1 nvarchar(5) ='Hello',

    @Var2 dbo.TestTableType2,

    /*Here I set the database name. But I can also switch database, for example use the 'DB_EUROPE' value*/

    @DbName NVARCHAR(max) = 'DB_AMERICAS'

    INSERT INTO @Var2 VALUES ('There')

    SELECT * INTO #TempTable FROM @Var2 -- Temp table used for execution scope reasons

    EXEC ('USE '+ @DbName + --@DBName is always passed by a SQL table, No worries for injection here. No user input.

    ' DECLARE @Var2 dbo.TestTableType2'+

    ' INSERT INTO @Var2 SELECT * FROM #TempTable'+

    ' EXEC [dbo].[TestProc] ' +@Var1 + ',@Var2') --But @Var1 comes from user input and it is nvarchar. So risk for injection

    DROP TABLE #TempTable

    GO

    --Example 3) Dynamic Execution using SP_executesql

    --------------------------------------------------

    DECLARE

    @Var1 nvarchar(10) ='Hello',

    @DbName1 NVARCHAR(max) = 'DB_AMERICAS',

    @Var2 dbo.TestTableType2,

    @SQL NVARCHAR(max)

    SELECT @SQL = 'EXEC ' + @DbName1 + '.[dbo].[TestProc] @Var1,@Var2'

    PRINT @SQL

    INSERT INTO @Var2 VALUES ('There')

    EXEC sp_executesql @SQL, N'@Var1 NVARCHAR(max), @Var2 dbo.TestTableType2 READONLY',@Var1, @Var2

    Example 3 works fine as long as I don't use it across databases.

    So if i am in the DB_AMERICAS and set the variable @DBName1 = 'DB_AMERICAS' it works

    If I am in the DB_AMERICAS and set the variable @DBName1 = 'DB_EUROPE' it doesn't work

    due to the fact that I cannot pass table type variables across databases. This takes me back to example 2

    where I created a temptable and fill it with the @Var2 table variable values,

    in order to eliminate that limitation by expanding the execution scope.

    My question is: Can I use Example 3 in a setting across databases? (e.g. run the query in DB_AMERICAS and call an SP that lies on DB_EUROPE? The reason why I want to use Example 3 is because by using the sp_executesql I minimize injection risks.

    What do you think?

    Thanks again!!

    Serlal

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply