Execute the SQL generated as a Resultset

• kshemankar

  SSC Enthusiast

  Points: 129

  More actions

  December 20, 2003 at 10:08 am

  #177382

  Hi,

  I am writing following code:

  SET QUOTED_IDENTIFIER OFF

  Drop Table #T1

  Create Table #T1 (UID int,

  Qid int,

  DL int)

  Insert Into #T1

  SELECT QB.Unit_No,QB.QID,QB_MCQ.Diff_Level

  FROM QB INNER JOIN QB_MCQ

  ON QB.QID = QB_MCQ.QID

  WHERE (QB.TEAM_ID =1) Order By Unit_No,Diff_Level

  Drop Table #T2

  Create Table #T2 (UID int,

  RowCnt Decimal(28,9))

  INSERT INTO #T2

  SELECT UID,Count(*) as RowCnt FROM #T1 Group By UID

  Update #T2 Set RowCnt = round((0.4 * RowCnt),0)

  SELECT * FROM #T2

  SELECT "SELECT TOP " + rtrim(RowCnt) + " * FROM #T1 A , #T2 B WHERE A.UID = B.UID Group By A.UID,RowCnt " FROM #T2

  After running this code I get following output:

  SELECT TOP 10.000000000 * FROM #T1 A , #T2 B WHERE A.UID = B.UID Group By A.UID,RowCnt

  SELECT TOP 4.000000000 * FROM #T1 A , #T2 B WHERE A.UID = B.UID Group By A.UID,RowCnt

  Now, Instead of getting these two SQL statements, I want to execute these statements and get their resultant output. How do I achieve that?

  Thanks in advance

• Gabor Nyul

  SSCrazy Eights

  Points: 9459

  More actions

  December 21, 2003 at 2:34 am

  #487355

  quote:

  ---

  SELECT "SELECT TOP " + rtrim(RowCnt) + " * FROM #T1 A , #T2 B WHERE A.UID = B.UID Group By A.UID,RowCnt " FROM #T2

  ---

  you have to put the result in a cursor and trhou this in a varchar variable and execute it via exec()

  Example:

  declare @sql varchar(500)

  declare C cursor for SELECT "SELECT TOP " + rtrim(RowCnt) + " * FROM #T1 A , #T2 B WHERE A.UID = B.UID Group By A.UID,RowCnt " FROM #T2

  open C

  fetch C into @sql

  while fetch_status = 0

  begin

  exec(@sql)

  fetch C into @sql

  end

  close C

  deallocate C

  Bye

  Gabor

  
  
  Bye
  Gabor

• noeld

  SSC Guru

  Points: 96590

  More actions

  December 22, 2003 at 10:13 am

  #487454

  OR

  at the end of your query you could use

  DECLARE @qry varchar(8000)

  SELECT @qry = COALESCE (@qry, ' ') + "SELECT TOP " + LEFT(RowCnt,1) + " * FROM #T1 A , #T2 B WHERE A.UID = B.UID Group By A.UID,RowCnt " FROM #T2

  Exec( @qry )

  with 8000 char limitation.

  BTW Make sure the queries you build have valid syntax

  
  * Noel