April 13, 2015 at 10:09 am
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.
April 14, 2015 at 6:37 am
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.
April 14, 2015 at 7:15 am
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