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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy