Using sp_execute with OpenQuery and Parameters

BCullenward · October 27th, 2008, 03:48 PM

I'm trying to improve some of the performance issues our queries are having as well as drastically improve the readability of several of the stored procedures. One of the recommended things to do was use sp_executesql instead of coding a query with in one giant (N)VARCHAR with the parameters passed in.

For instance...

Code:

   DECLARE @sSQL VARCHAR(4000),
      @var1 VARCHAR(5)

   SET @var1 = 'Cow'

   SET @sSQL = 'SELECT *
                FROM OPENQUERY(ORASRVR1, ''
                     SELECT ORAFld1,
                            ORAFld2
                     FROM tblORATable o
                     WHERE ORAFld1 = '''' + @var1 + ''''
                        AND ORAFld3 = 4'') O'

   EXEC @sSQL

Instead, I am using the following:

Code:

  DECLARE @sSQL NVARCHAR(4000),
      @var1 VARCHAR(5),
      @ParmList NVARCHAR(50)

   SET @var1 = 'Cow'
   SET @ParmList = '@var1 NVARCHAR'

   SET @sSQL = 'SELECT *
                FROM OPENQUERY(ORASRVR1, ''
                     SELECT ORAFld1,
                            ORAFld2
                     FROM tblORATable o
                     WHERE ORAFld1 = @var1
                        AND ORAFld3 = 4'') O'

   EXEC sp_executesql @sSQL, @ParmList, @var1

When I use the sp_executesql I get an "ORA-00936: missing expression" error.

Is there a way to see what SQL is actually going to execute in this instance so I can attempt to track down what the Oracle server thinks is missing?

Thanks