Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2005
    Location
    Milwaukee, WI
    Posts
    105

    Unanswered: Passing Variable within OPENQUERY, Dynamic Where Clause, FoxPro Linked Server

    I'm posting this because I found this solution after much digging.

    The goal here is to incorporate a variable parameter within a OPENQUERY and, ultimately build a dynamic Where clause for use within a OPENQUERY linked server routine. I'm posting because I spent a lot of time trying to get this to work and also, have seen other posts here that hinted it wasn't doable.

    First of all - there a good quick article that gets close for FoxPro and possibly works as is for ACCESS:

    http://support.microsoft.com/default...b;en-us;314520

    Here's code for a solution:

    DECLARE @OPENQUERY nvarchar(4000),
    @TSQL nvarchar(4000),
    @FAMILY CHAR(10)

    SET @FAMILY='Touring'
    SET @OPENQUERY = 'SELECT * FROM OPENQUERY(VFP,'''

    SET @TSQL = 'select cov,family,model from vinmast where family='+'['+@FAMILY+']'')'

    EXEC (@OPENQUERY+@TSQL)


    All shown are single quotes.

    In Visual Foxpro, ' ' or " " or [ ] can be used a delimeters

    In addition, if wanting to build a dynamic where clause, you could do something like:

    SET @TSQL = 'select cov,family,model from vinmast '
    IF <some condition met to include FAMILY filter>
    Begin
    SET @TSQL=@TSQL+'where family=['+@DUTFAMILY+']'''
    SET @TSQL=@TSQL+ ')'
    End

    -----------------
    Here's the entire Stored Procedure:


    CREATE PROCEDURE dbo.ewo_sp_DUTLookup
    (
    @DUTPROJECT char(25)=NULL, --Project
    @DUTFAMILY char(10)=NULL, --Family
    @DUTMODEL char(20)=NULL, --Model
    @DUTYEAR char(4)=NULL, --Model Year
    @DUTBEGIN char(25)=NULL, --Beginning of COV/DUT number
    @DEBUG int=0
    )


    AS

    DECLARE @OPENQUERY varchar(4000),
    @TSQL varchar(4000),
    @TWHERE varchar(4000),
    @intErrorCode int

    select @intErrorCode = @@ERROR,
    @TSQL='',
    @TWHERE=''


    IF @intErrorCode=0
    Begin
    SET @OPENQUERY = 'SELECT * FROM OPENQUERY(VFP,'''
    SET @TSQL = ' select dut_pk,cov,family,model,project,modelyr from vinmast '
    End

    set @intErrorCode = @@ERROR

    IF @intErrorCode = 0 and
    @DUTFAMILY is not NULL or
    @DUTMODEL is not NULL or
    @DUTPROJECT is not NULL or
    @DUTYEAR is not NULL or
    @DUTBEGIN is not NULL
    set @TWHERE=' where '



    -- Check for Family criteria
    If @intErrorCode = 0 and @DUTFAMILY is not NULL and Len(@TWHERE)>0
    SET @TWHERE=@TWHERE+' family=['+@DUTFAMILY+'] AND '
    set @intErrorCode = @@ERROR

    -- Check for Model criteria
    If @intErrorCode = 0 and @DUTMODEL is not NULL and Len(@TWHERE)>0
    SET @TWHERE=@TWHERE+' model=['+@DUTMODEL+'] AND '
    set @intErrorCode = @@ERROR

    --Check for Project criteria
    If @intErrorCode = 0 and @DUTPROJECT is not NULL and Len(@TWHERE)>0
    SET @TWHERE=@TWHERE+' project=['+@DUTPROJECT+'] AND '
    set @intErrorCode = @@ERROR

    --Check for Model Year
    If @intErrorCode = 0 and @DUTYEAR is not NULL and Len(@TWHERE)>0
    SET @TWHERE=@TWHERE+' modelyr=['+@DUTYEAR+'] AND '
    set @intErrorCode = @@ERROR

    --Check for beginning of DUT
    If @intErrorCode = 0 and @DUTBEGIN is not NULL and Len(@TWHERE)>0
    Begin
    SET @DUTBEGIN=RTRIM(@DUTBEGIN)
    SET @TWHERE=@TWHERE+' substr(cov,1,'+cast(len(@DUTBEGIN) as char(20))+')=['+@DUTBEGIN+'] AND '
    End
    set @intErrorCode = @@ERROR


    IF @intErrorCode=0 AND substring(@TWHERE,Len(@TWHERE)-3,4)=' AND '
    Begin
    set @TWHERE=Substring(@TWHERE,1,Len(@TWHERE)-3)
    select @intErrorCode=@@ERROR
    End



    SET @TWHERE=@TWHERE+''')'

    IF @debug<>0 and @intErrorCode=0
    Begin
    print @intErrorCode
    print @OPENQUERY
    print @TSQL
    print @TWHERE
    print @OPENQUERY+@TSQL+@TWHERE
    End

    IF @intErrorCode=0
    EXEC (@OPENQUERY+@TSQL+@TWHERE)
    GO

    Peter
    Last edited by Pdiotte; 03-09-05 at 15:34. Reason: Show all code from SP

  2. #2
    Join Date
    Feb 2005
    Posts
    25
    Of course this can be done. But can you get the column names using this method?

  3. #3
    Join Date
    Feb 2005
    Location
    Milwaukee, WI
    Posts
    105
    I'm not sure I understand your question - please elaborate.

    I'm pulling a fixed set of columns. I could build that list dynamically too if I had to.

    What I'm varying is the criteria of the pull.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •