problem using sp_executesql

• Sam ONeill

  SSC Rookie

  Points: 43

  More actions

  November 11, 2003 at 4:47 am

  #358513

  Hi everyone

  I have a stored procedure that is compiled into database A but needs to obtain information from a table in database B so I using the sp_executesql procedure to do the following:

  SET @sSQL = N'INSERT #TempPost SELECT Serial FROM ' + @DBName + '.[dbo].[Transaction_Mapping] WHERE TransType = @sTransType'

  SET @sParm = N'@sTransType varchar(10)'

  SET @sVariable = @sTransType

  EXECUTE sp_executesql @sSQL, @sParm,

  @sTransType = @sVariable

  This works fine and places the Serial value in a temporary table for use within the prodecure however this incurs the hit of having to declare a temporary table, inserting into it and them selecting from it when the Serial value is to be used.

  What I really want to do is to have a string that looks like this:

  SELECT @Serial = Serial FROM ..........

  but I can't see how to get this to work using sp_executesql.

  I am basically looking for the fastest way to obtain and store the value selected from another database. Any ideas much appreciated.

  Sam

  

• David Burrows

  SSC Guru

  Points: 65142

  More actions

  November 11, 2003 at 6:31 am

  #481567

  SET @sSQL = N'SELECT @Serial = Serial FROM ' + 
  
  @DBName + 
  
  '.[dbo].[Transaction_Mapping] WHERE TransType = @sTransType' 
  
  
  
  execute sp_executesql 
  
  @sSQL, 
  
  N'@sTransType varchar(10), @Serial varchar(10) output', 
  
  @sTransType, 
  
  @Serial OUTPUT 
  
   

  p.s. Change @Serial datatype accordingly.

  Edited by - davidburrows on 11/11/2003 06:32:03 AM

  Far away is close at hand in the images of elsewhere.
  Anon.

• Sam ONeill

  SSC Rookie

  Points: 43

  More actions

  November 11, 2003 at 6:48 am

  #481570

  Many thanks for that I will give it a bash.

  Kindest regards

  Sam