Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2012
    Posts
    188

    Unanswered: Dynamic SQL With Update

    I am running this sql syntax, and it works perfect, but I want to be able to pass in parameters to it to make it more flexible. This works
    Code:
    ; with cte As
    (Select studentname, PlacementLvl
    FROM OPENQUERY([192.168.500.199], 
    'Select 
    a.studentname studentname
    ,b.placementscore PlacementLvl
    from studentdata a
    inner join scores b
    on a.studID = b.studentID
    WHERE a.studentname IN (''Mitchell Kane'')
    AND a.testtakendate BETWEEN (''01/01/2016'')
    AND (''01/30/2016'')'
      ))
      UPDATE t
      SET student = cte.studentname
      ,placementlevel = cte.PlacementLvl
      FROM FinalResults t
      INNER JOIN cte 
      on t.name = cte.studentname;
    However, anytime i try to alter it to pass in parameters into dynamic sql I get error after error. I want to essentially be able to do this.
    Code:
    Declare @testdate1 date, @testdate2 date, @studentname varchar(100)
     Set @studentname = 'Mitchell Kane'
     Set @testdate1 = '2016/01/01'
     Set @testdate2 = '2016/01/30'
      ; with cte As
    (Select studentname, PlacementLvl
    FROM OPENQUERY([192.168.500.199], 
    'Select 
    a.studentname studentname
    ,b.placementscore PlacementLvl
    from studentdata a
    inner join scores b
    on a.studID = b.studentID
    WHERE a.studentname IN (@studentname)
    AND a.testtakendate BETWEEN (@testdate1)
    AND (@testdate2)'
      ))
      UPDATE t
      SET student = cte.studentname
      ,placementlevel = cte.PlacementLvl
      FROM FinalResults t
      INNER JOIN cte 
      on t.name = cte.studentname;
    What is the proper SQL Server 2008 R2 syntax to be able to accomplish what I am after above? Update statement using Openquery with parameters?

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Can't you move the parameters to the WHERE clause that's outside of the OPENQUERY call?

    Air-code:
    Code:
    SELECT studentname
         , PlacementLvl
    FROM   OpenQuery([192.168.500.199], 
           'SELECT a.studentname studentname
                 , b.placementscore PlacementLvl
                 , a.testtakendate
            FROM   studentdata a
             INNER
              JOIN scores b
                ON a.studID = b.studentID'
            ) AS x
    WHERE   studentname IN ('Mitchell Kane')
    AND     testtakendate BETWEEN '2016-01-01' AND '2016-01-30'
    George
    Home | Blog

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Alternative option is to use a parameterised query into a table variable or temporary table:

    More untested air-code:
    Code:
    DECLARE @x table (
       studentname  varchar(100)
     , PlacementLvl int
    );
    
    DECLARE @testdate1   date         = '2016-01-01'
          , @testdate2   date         = '2016-01-30'
          , @studentname varchar(100) = 'Mitchell Kane'
    ;
    
    INSERT INTO @x (studentname, PlacementLvl)
    EXECUTE [192.168.500.199].master.dbo.sp_executesql N'
    SELECT a.studentname studentname
         , b.placementscore PlacementLvl
    FROM   studentdata a
     INNER
      JOIN scores b
        ON a.studID = b.studentID
    WHERE a.studentname IN (@studentname)
    AND   a.testtakendate BETWEEN @testdate1 AND @testdate2
    '
    , N'@studentname varchar(100), @testdate1 date, @testdate2 date'
    , @studentname, @testdate1, @testdate2
    ;
    
    SELECT studentname
         , PlacementLvl
    FROM   @x
    ;
    George
    Home | Blog

Posting Permissions

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