Retrieve data into Local variable Through dynamic Query

• chethan RH

  SSC Veteran

  Points: 298

  More actions

  October 25, 2006 at 5:25 am

  #69429

  Hi,

  In a Stored PROC I have a query which retrieves value into a local variable.

  Now the query has be converted into Dynamic.

  This is causing a problem.

  I am not able to retreive the value from dynamic query into variable.

  Ex: Select @Variable=Name FROM Employee

  The SELECT Clause needs to be converted into Dynamic,

  and retreive the value into @Variable. Is This is Posssible in SQL2000?

  NOTE: Retreiving the value into variable is important as it is used in other dynamic queries

  Thanks and Regards

  Chethan!!

• ijaz

  SSCommitted

  Points: 1912

  More actions

  October 25, 2006 at 6:11 am

  #667821

  Hi,

  Bellow is the code to get the return value from dyanamic SQL

  DECLARE @SQLString NVARCHAR(500)

  , @ParmDefinition NVARCHAR(500)

  , @Lastlname varchar(30)

  SET @SQLString = N' SELECT @LastlnameOUT = Name FROM Employee '                    

  SET @ParmDefinition = N'@LastlnameOUT varchar(30) OUTPUT'

  EXECUTE sp_executesql @SQLString, @ParmDefinition, @LastlnameOUT=@Lastlname OUTPUT

  SELECT @Lastlname

  Hope this will slove your problem

  cheers

• chethan RH

  SSC Veteran

  Points: 298

  More actions

  October 25, 2006 at 8:28 am

  #667876

  Hi,

  Thank You 🙂