jhager (4/17/2013)
Thanks...I've been able to use sp_executesql to execute SELECT, INSERT, etc., but NOT execute a procedure. Maybe it's not possible because of the parameter scope.I appreciate the response, though.
~ Jeff
Actually, you may want to read the sp_executesql entry in Books Online a little more carefully, it shows you exactly how to use it. Assuming that the @SQLParmN variables are the parameters being used in the dynamic sql, this is how it should look (hopefully, since I have nothing with which to test):
DECLARE @SQL NVARCHAR(2048);
DECLARE @SQLParms NVARCHAR(2048);
DECLARE @Parm1 INT;
DECLARE @Parm2 VARCHAR(10);
DECLARE @Parm3 DATETIME;
SET @Parm1 = 1;
SET @Parm2 = 'ABC';
SET @Parm3 = '1958-11-14 04:25';
SET @SQL = N'TestProc @SQLParm1, @SQLParm2, @SQLParm3;'; -- The Dynamic SQL
SET @SQLParams = N'@SQLParm1 INT, @SQLParm2 VARCHAR(10), @SQLParm3 DATETIME'; -- Define the variables used in the Dynamic SQL
EXECUTE sys.sp_executesql @SQL, @SQLParms, @SQLParm1 = @Parm1, @SQLParm2 = @Parm2, @SQLParm3 = @Parm3;