Firing dynamic sql.....

• Prakash Heda

  SSCertifiable

  Points: 5539

  More actions

  August 22, 2002 at 12:57 am

  #101228

  IN MY KNOWLEDGE THERE ARE TWO WAYS TO FIRE A DYNAMIC SQL FROM STORED PROCEDURE......

  FIRST THROUGH SP_EXECUTESQL WHERE LIMITATION IS 4000 CHARACTORS

  CREATE PROC HGFG1 AS

  DECLARE @abc NVARCHAR(4000)

  SET @abc = 'SELECT * FROM DIM_GENDER'

  EXEC SP_EXECUTESQL @abc

  GO

  EXEC HGFG1

  SECOND THROUGH EXEC WHERE LIMITATION IS 8000 CHARS

  CREATE PROC HGFG AS

  DECLARE @abc VARCHAR(8000)

  SET @abc = 'SELECT * FROM DIM_GENDER'

  EXEC (@ABC)

  GO

  EXEC HGFG

  ANYBODY KNOW ANY OTHER WAY TO FIRE A DYNAMIC SQL HAVING MORE THAN 8000 CHARACTERS

  THANKS IN ADVANCE,

  Prakash

  Prakash Heda
  Lead DBA Team - www.sqlfeatures.com
  Video sessions on Performance Tuning and SQL 2012 HA

• Prakash Heda

  SSCertifiable

  Points: 5539

  More actions

  August 22, 2002 at 1:28 am

  #435004

  Well it was simple now the question is there is any limitation of using exec method?????

  DECLARE

  @abc VARCHAR(8000),

  @ABCD VARCHAR(8000),

  @ABCDE VARCHAR(8000),

  @ABCDEF VARCHAR(8000)

  SET @abc = 'SELECT '

  SET @ABCD = ' * '

  SET @ABCDE = ' FROM '

  SET @ABCDEF = ' DIM_GENDER'

  EXEC (@ABC + @ABCD + @ABCDE + @ABCDEF)

  Prakash

  Prakash Heda
  Lead DBA Team - www.sqlfeatures.com
  Video sessions on Performance Tuning and SQL 2012 HA