Using Dynamic SQL, OPENROWSET, output variable

• j saile

  Valued Member

  Points: 74

  More actions

  June 22, 2006 at 2:20 pm

  #366484

  I'm befuddled. I have code that runs fine on the server on which it was developed. I have moved it to another server and have modified the code to utilize OPENROWSET to go after the data on the former server. After reading and reading I still can't figure out why I can't get to the result. Finally, in desparation, I copied code from the MS support site and then tried to modify it to conform to my situation. The unmodifed code ran fine. The modified code does not - someone please show me what I'm missing. Thanks. BTW, the unmodified code returns 'Lincoln" and the modified code returns NULL.

  Here's the unmodified code

  DECLARE @SQLString NVARCHAR(500) 
  DECLARE @ParmDefinition NVARCHAR(500) 
  DECLARE @IntVariable INT 
  DECLARE @Lastlname varchar(30) 
  SET @SQLString = N'SELECT @LastlnameOUT = max(lname) 
                     FROM pubs.dbo.employee WHERE job_lvl = @level' 
  SET @ParmDefinition = N'@level tinyint, 
                          @LastlnameOUT varchar(30) OUTPUT' 
  SET @IntVariable = 35 
  EXECUTE sp_executesql 
  @SQLString, 
  @ParmDefinition, 
  @level = @IntVariable, 
  @LastlnameOUT=@Lastlname OUTPUT 
  SELECT @Lastlname

  And here's the modified code

  DECLARE @SQLString NVARCHAR(500) 
  DECLARE @ParmDefinition NVARCHAR(500) 
  DECLARE @IntVariable INT 
  DECLARE @Lastlname varchar(30) 
  DECLARE @level int 
  SET @SQLString = N' 
  SELECT @LastlnameOUT = * 
  FROM OPENROWSET(''SQLOLEDB'',''servername'';''admin'';''pw'',  
  ''SELECT max(lname) 
                     FROM pubs.dbo.employee WHERE job_lvl = ''''' + cast(@level as nvarchar) + ''''' '')' 
                      
  SET @ParmDefinition = N'@level tinyint, 
                          @LastlnameOUT varchar(30) OUTPUT' 
  SET @IntVariable = 35 
  EXECUTE sp_executesql 
  @SQLString, 
  @ParmDefinition, 
  @level = @IntVariable, 
  @LastlnameOUT=@Lastlname OUTPUT 
  SELECT @Lastlname

• Jeff Moden

  SSC Guru

  Points: 1004654

  More actions

  June 22, 2006 at 8:41 pm

  #645430

  You cannot say SELECT @somevariable = * and get away with it...

  While OPENROWSET is fine, it exposes passwords and logins... it would be far better to create a Linked Server.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)