Removing cursor..help please

• VSSGeorge

  SSCrazy Eights

  Points: 8151

  More actions

  October 16, 2015 at 6:31 am

  #305518

  Hello

  I need help in removing cursor in my table valued function with alternate code. Thanks in advance.

  ALTER FUNCTION [dbo].[eufn_e5_eSM_SE_GetCurrentContentForContainer]

  (

  @containerSqlId SMALLINT,

  @containerIncId INT

  )

  RETURNS @Results TABLE

  (

  itemSqlId SMALLINT,

  itemIncId INT,

  itemMetaTableSqlId SMALLINT,

  itemMetaTableIncId INT

  )

  AS

  BEGIN

  ---- Get static data

  -- Get the containers meta table key

  DECLARE @containerMetaTableSqlId SMALLINT

  DECLARE @containerMetaTableIncId INT

  SELECT@containerMetaTableSqlId = MT.metaTableSqlId,

  @containerMetaTableIncId = MT.metaTableIncId

  FROM [dbo].[MetaTables] MT

  WHERE MT.metaTableName = 'Containers'

  ---- Add direct items

  INSERT INTO @Results

  SELECTCC.contentSqlId,

  CC.contentIncId,

  CC.contentMetaTableSqlId,

  CC.contentMetaTableIncId

  FROM [dbo].[ContainersContents] CC

  WHERECC.containerSqlId = @containerSqlId AND

  CC.containerIncId = @containerIncId AND

  CC.isDeleted = 0

  ---- Add items from child containers

  -- Get child containers

  DECLARE @childContainersCursor CURSOR

  SET @childContainersCursor = CURSOR FOR

  SELECTCC.contentSqlId,

  CC.contentIncId

  FROM [dbo].[ContainersContents] CC

  WHERECC.containerSqlId = @containerSqlId AND

  CC.containerIncId = @containerIncId AND

  CC.contentMetaTableSqlId = @containerMetaTableSqlId AND

  CC.contentMetaTableIncId = @containerMetaTableIncId AND

  CC.isDeleted = 0

  -- Get child containers content

  DECLARE @childContainerSqlId SMALLINT

  DECLARE @childContainerIncId INT

  OPEN @childContainersCursor

  FETCH @childContainersCursor INTO @childContainerSqlId, @childContainerIncId

  WHILE(@@FETCH_STATUS = 0)

  BEGIN

  INSERT INTO @Results

  SELECT *

  FROM [dbo].[eufn_e5_eSM_SE_GetCurrentContentForContainer](@childContainerSqlId, @childContainerIncId)

  FETCH @childContainersCursor INTO @childContainerSqlId, @childContainerIncId

  END

  CLOSE @childContainersCursor

  RETURN

  END

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  October 16, 2015 at 7:11 am

  #1833796

  Junglee_George (10/16/2015)

  ---

  Hello

  I need help in removing cursor in my table valued function with alternate code. Thanks in advance.

  ALTER FUNCTION [dbo].[eufn_e5_eSM_SE_GetCurrentContentForContainer]

  (

  @containerSqlId SMALLINT,

  @containerIncId INT

  )

  RETURNS @Results TABLE

  (

  itemSqlId SMALLINT,

  itemIncId INT,

  itemMetaTableSqlId SMALLINT,

  itemMetaTableIncId INT

  )

  AS

  BEGIN

  ---- Get static data

  -- Get the containers meta table key

  DECLARE @containerMetaTableSqlId SMALLINT

  DECLARE @containerMetaTableIncId INT

  SELECT@containerMetaTableSqlId = MT.metaTableSqlId,

  @containerMetaTableIncId = MT.metaTableIncId

  FROM [dbo].[MetaTables] MT

  WHERE MT.metaTableName = 'Containers'

  ---- Add direct items

  INSERT INTO @Results

  SELECTCC.contentSqlId,

  CC.contentIncId,

  CC.contentMetaTableSqlId,

  CC.contentMetaTableIncId

  FROM [dbo].[ContainersContents] CC

  WHERECC.containerSqlId = @containerSqlId AND

  CC.containerIncId = @containerIncId AND

  CC.isDeleted = 0

  ---- Add items from child containers

  -- Get child containers

  DECLARE @childContainersCursor CURSOR

  SET @childContainersCursor = CURSOR FOR

  SELECTCC.contentSqlId,

  CC.contentIncId

  FROM [dbo].[ContainersContents] CC

  WHERECC.containerSqlId = @containerSqlId AND

  CC.containerIncId = @containerIncId AND

  CC.contentMetaTableSqlId = @containerMetaTableSqlId AND

  CC.contentMetaTableIncId = @containerMetaTableIncId AND

  CC.isDeleted = 0

  -- Get child containers content

  DECLARE @childContainerSqlId SMALLINT

  DECLARE @childContainerIncId INT

  OPEN @childContainersCursor

  FETCH @childContainersCursor INTO @childContainerSqlId, @childContainerIncId

  WHILE(@@FETCH_STATUS = 0)

  BEGIN

  INSERT INTO @Results

  SELECT *

  FROM [dbo].[eufn_e5_eSM_SE_GetCurrentContentForContainer](@childContainerSqlId, @childContainerIncId)

  FETCH @childContainersCursor INTO @childContainerSqlId, @childContainerIncId

  END

  CLOSE @childContainersCursor

  RETURN

  END

  Without sample data scripts and expected results? Your first post on ssc was on 31st July 2013, you know how it works.

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  October 16, 2015 at 9:09 am

  #1833851

  My wild guess would be something like this:

  CREATE FUNCTION [dbo].[eufn_e5_eSM_SE_GetCurrentContentForContainer]

  (

  @containerSqlId SMALLINT,

  @containerIncId INT

  )

  RETURNS TABLE

  AS

  RETURN

  SELECTCC.contentSqlId,

  CC.contentIncId,

  CC.contentMetaTableSqlId,

  CC.contentMetaTableIncId

  FROM [dbo].[ContainersContents] CC

  WHERECC.containerSqlId = @containerSqlId AND

  CC.containerIncId = @containerIncId AND

  CC.isDeleted = 0

  UNION ALL

  SELECT ccc.*

  FROM [dbo].[ContainersContents] CC

  CROSS APPLY [dbo].[eufn_e5_eSM_SE_GetCurrentContentForContainer](CC.contentSqlId, CC.contentIncId) ccc

  JOIN [dbo].[MetaTables] MT ON CC.contentMetaTableSqlId = MT.metaTableSqlId AND

  CC.contentMetaTableIncId = MT.metaTableIncId

  WHERECC.containerSqlId = @containerSqlId AND

  CC.containerIncId = @containerIncId AND

  CC.isDeleted = 0 AND

  MT.metaTableName = 'Containers'

  The not so wild guess would be like this:

  ALTER FUNCTION [dbo].[eufn_e5_eSM_SE_GetCurrentContentForContainer]

  (

  @containerSqlId SMALLINT,

  @containerIncId INT

  )

  RETURNS @Results TABLE

  (

  itemSqlId SMALLINT,

  itemIncId INT,

  itemMetaTableSqlId SMALLINT,

  itemMetaTableIncId INT

  )

  AS

  BEGIN

  ---- Get static data

  -- Get the containers meta table key

  DECLARE @containerMetaTableSqlId SMALLINT

  DECLARE @containerMetaTableIncId INT

  SELECT@containerMetaTableSqlId = MT.metaTableSqlId,

  @containerMetaTableIncId = MT.metaTableIncId

  FROM [dbo].[MetaTables] MT

  WHERE MT.metaTableName = 'Containers'

  ---- Add direct items

  INSERT INTO @Results

  SELECTCC.contentSqlId,

  CC.contentIncId,

  CC.contentMetaTableSqlId,

  CC.contentMetaTableIncId

  FROM [dbo].[ContainersContents] CC

  WHERECC.containerSqlId = @containerSqlId AND

  CC.containerIncId = @containerIncId AND

  CC.isDeleted = 0

  ---- Add items from child containers

  INSERT INTO @Results

  SELECT ccc.*

  FROM [dbo].[ContainersContents] CC

  CROSS APPLY [dbo].[eufn_e5_eSM_SE_GetCurrentContentForContainer](CC.contentSqlId, CC.contentIncId) ccc

  WHERECC.containerSqlId = @containerSqlId AND

  CC.containerIncId = @containerIncId AND

  CC.contentMetaTableSqlId = @containerMetaTableSqlId AND

  CC.contentMetaTableIncId = @containerMetaTableIncId AND

  CC.isDeleted = 0

  RETURN

  END

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2