{"id":486,"date":"2008-01-26T16:07:08","date_gmt":"2008-01-26T21:07:08","guid":{"rendered":"http:\/\/blogs.devhorizon.com\/reza\/?p=486"},"modified":"2008-01-27T01:50:46","modified_gmt":"2008-01-27T06:50:46","slug":"fba-combining-stored-procedures-and-power-of-ctes","status":"publish","type":"post","link":"https:\/\/blogs.devhorizon.com\/reza\/2008\/01\/26\/fba-combining-stored-procedures-and-power-of-ctes\/","title":{"rendered":"FBA: Combining Stored Procedures and Power of CTEs"},"content":{"rendered":"

Since I am a lazy developer I normally don’t like to author multiple stored procedures if I can afford to aggregate all the logic in one place without causing too much complexity. For example when working with custom authentication providers you are often required to write separate stored procedures for GetAllUsers, FindUsersByName, FindUsersByEmail that can be called from their counterpart overridden methods in your custom authentication provider’s code. Following example demonstrates how you can have one stored procedure that can handle GetAllUsers, FindUsersByName, FindUsersByEmail logic in one stored procedure. As shown below, @email and @username parameters can be null which means you can ignore these two filters and get all the users instead. One important thing to note is the table that holds all the user information in this example. It is called Contacts with ContactID as the primary key, but yours might be different! <\/p>\n

\n\n\t\t
\n\n\t\t
\n\t\t\t\t\t\t< View plain text<\/span> ><\/a>\n\t\t\t\t\t<\/div>\n\n\t\t
sql<\/div>\n\n\t\t\n\t\t
\n\n\t<\/div>\n\t\n\t
\n\t\t
  1. CREATE<\/span> PROCEDURE<\/span> [<\/span>dbo]<\/span>.<\/span>[<\/span>GetUsers]<\/span><\/div><\/li>\n
  2. (<\/span><\/div><\/li>\n
  3. @email AS<\/span> VARCHAR<\/span>(<\/span>50<\/span>)<\/span> =<\/span> NULL<\/span>,<\/span><\/div><\/li>\n
  4. @username AS<\/span> VARCHAR<\/span>(<\/span>50<\/span>)<\/span> =<\/span> NULL<\/span>,<\/span><\/div><\/li>\n
  5. @pageIndex AS<\/span> INTEGER<\/span>,<\/span><\/div><\/li>\n
  6. @pageSize AS<\/span> INTEGER<\/span><\/div><\/li>\n
  7. )<\/span><\/div><\/li>\n
  8. AS<\/span><\/div><\/li>\n
  9.  <\/div><\/li>\n
  10. SET<\/span> NOCOUNT ON<\/span><\/div><\/li>\n
  11. --Set the page bounds<\/span><\/div><\/li>\n
  12. DECLARE<\/span> @PageLowerBound int<\/span><\/div><\/li>\n
  13. DECLARE<\/span> @PageUpperBound int<\/span><\/div><\/li>\n
  14. DECLARE<\/span> @TotalRecords   int<\/span><\/div><\/li>\n
  15. SET<\/span> @PageLowerBound =<\/span> @PageSize *<\/span> @PageIndex<\/div><\/li>\n
  16. SET<\/span> @PageUpperBound =<\/span> @PageSize -<\/span> 1<\/span> +<\/span> @PageLowerBound<\/div><\/li>\n
  17.  <\/div><\/li>\n
  18. --Create a temp table for pager logic<\/span><\/div><\/li>\n
  19. CREATE<\/span> TABLE<\/span> #PageIndexForContacts<\/div><\/li>\n
  20. (<\/span><\/div><\/li>\n
  21. IndexId INT<\/span> IDENTITY<\/span> (<\/span>0<\/span>,<\/span> 1<\/span>)<\/span> NOT<\/span> NULL<\/span>,<\/span><\/div><\/li>\n
  22. ContactID UNIQUEIDENTIFIER<\/div><\/li>\n
  23. )<\/span><\/div><\/li>\n
  24.  <\/div><\/li>\n
  25. --Insert into temp table<\/span><\/div><\/li>\n
  26. IF<\/span>(<\/span> @email IS<\/span> NOT<\/span> NULL<\/span> )<\/span><\/div><\/li>\n
  27.  <\/div><\/li>\n
  28. INSERT<\/span> INTO<\/span> #PageIndexForContacts(<\/span>ContactID)<\/span> SELECT<\/span> ContactID FROM<\/span> Contacts<\/div><\/li>\n
  29. WHERE<\/span>  LOWER<\/span>(<\/span>Email)<\/span> LIKE<\/span> LOWER<\/span>(<\/span>@email)<\/span><\/div><\/li>\n
  30. ORDER<\/span> BY<\/span> Email<\/div><\/li>\n
  31.  <\/div><\/li>\n
  32. ELSE<\/span> IF<\/span>(<\/span>@username IS<\/span> NOT<\/span> NULL<\/span>)<\/span><\/div><\/li>\n
  33.  <\/div><\/li>\n
  34. INSERT<\/span> INTO<\/span> #PageIndexForContacts(<\/span>ContactID)<\/span> SELECT<\/span> ContactID FROM<\/span> Contacts<\/div><\/li>\n
  35. WHERE<\/span>  LOWER<\/span>(<\/span>UserName)<\/span> LIKE<\/span> LOWER<\/span>(<\/span>UserName)<\/span><\/div><\/li>\n
  36. ORDER<\/span> BY<\/span> UserName<\/div><\/li>\n
  37.  <\/div><\/li>\n
  38. ELSE<\/span><\/div><\/li>\n
  39. INSERT<\/span> INTO<\/span> #PageIndexForContacts(<\/span>ContactID)<\/span> SELECT<\/span> ContactID FROM<\/span> Contacts<\/div><\/li>\n
  40. ORDER<\/span> BY<\/span> UserName<\/div><\/li>\n
  41.  <\/div><\/li>\n
  42. SELECT<\/span> @TotalRecords =<\/span> @@ROWCOUNT<\/div><\/li>\n
  43.  <\/div><\/li>\n
  44. SELECT<\/span><\/div><\/li>\n
  45. Comment,<\/span><\/div><\/li>\n
  46. CreationDate,<\/span><\/div><\/li>\n
  47. Email,<\/span><\/div><\/li>\n
  48. IsApproved,<\/span><\/div><\/li>\n
  49. IsLockedOut,<\/span><\/div><\/li>\n
  50. IsOnline,<\/span><\/div><\/li>\n
  51. LastActivityDate,<\/span><\/div><\/li>\n
  52. LastLockoutDate,<\/span><\/div><\/li>\n
  53. LastLoginDate,<\/span><\/div><\/li>\n
  54. LastPasswordChangedDate,<\/span><\/div><\/li>\n
  55. PasswordQuestion,<\/span><\/div><\/li>\n
  56. ProviderName,<\/span><\/div><\/li>\n
  57. ProviderUserKey,<\/span><\/div><\/li>\n
  58. UserName<\/div><\/li>\n
  59.  <\/div><\/li>\n
  60. FROM<\/span><\/div><\/li>\n
  61. Contacts con,<\/span>#PageIndexForContacts conp<\/div><\/li>\n
  62. WHERE<\/span><\/div><\/li>\n
  63. con.<\/span>ContactID =<\/span> conp.<\/span>ContactID AND<\/span><\/div><\/li>\n
  64. conp.<\/span>IndexId &lt;=<\/span> @PageLowerBound AND<\/span> conp.<\/span>IndexId &gt;=<\/span> @PageUpperBound<\/div><\/li>\n
  65.  <\/div><\/li>\n
  66. RETURN<\/span> @TotalRecords<\/div><\/li>\n
  67.  <\/div><\/li>\n
  68. -- Drop the temp table<\/span><\/div><\/li>\n
  69. DROP<\/span> TABLE<\/span> #PageIndexForContacts<\/div><\/li>\n
  70.  <\/div><\/li>\n
  71. SET<\/span> NOCOUNT OFF<\/div><\/li>\n<\/ol>\t<\/div>\n\n<\/div>\n\n

    One new feature in SQL Server 2005 that comes handy a lot when dealing with paging and recursions is Common Table Expressions<\/a> (a.k.a CTEs) . Common Table Expressions are much nicer way to work with derived tables and specially when you need recursion functionality. Let’s say , you want to retrieve all your users and show them on <wssawc<\/span>:<\/span>SPGridView> <\/span><\/span>control with pagination<\/span> and row numbers. It’s ideal if you deal with records in chunks (specified by @pageIndex parameter) to save bandwidth and time<\/span> instead of retrieving all users at once. Then in your code (MemberGrid_ClickNext<\/span>), what you need to do is to send the right @pageIndex parameter to the stored procedure which in turn takes care of paging the requested records and return the right chunk to the client .Here is how CTEs can be your friend:<\/p>\n

    \n\n\t\t
    \n\n\t\t
    \n\t\t\t\t\t\t< View plain text<\/span> ><\/a>\n\t\t\t\t\t<\/div>\n\n\t\t
    sql<\/div>\n\n\t\t\n\t\t
    \n\n\t<\/div>\n\t\n\t
    \n\t\t
    1. with<\/span> UsersCTE as<\/span><\/div><\/li>\n
    2. (<\/span><\/div><\/li>\n
    3. SELECT<\/span><\/div><\/li>\n
    4.  <\/div><\/li>\n
    5. ROW_NUMBER<\/span>(<\/span>)<\/span> over<\/span> (<\/span>order<\/span> by<\/span> con.<\/span>UserName,<\/span> con.<\/span>CreatedOn,<\/span> con.<\/span> Email)<\/span> as<\/span> RowNum,<\/span><\/div><\/li>\n
    6. con.<\/span>UserName,<\/span><\/div><\/li>\n
    7. con.<\/span>CreatedOn,<\/span><\/div><\/li>\n
    8. con.<\/span>Email<\/div><\/li>\n
    9. FROM<\/span><\/div><\/li>\n
    10. dbo.<\/span>Contacts con<\/div><\/li>\n
    11. )<\/span><\/div><\/li>\n
    12. select<\/span><\/div><\/li>\n
    13. UsersCTE.*<\/span><\/div><\/li>\n
    14. from<\/span><\/div><\/li>\n
    15. UsersCTE<\/div><\/li>\n
    16. where<\/span><\/div><\/li>\n
    17. RowNum between<\/span> @PageLowerBound and<\/span> @PageUpperBound<\/div><\/li>\n<\/ol>\t<\/div>\n\n<\/div>\n\n\n\n\n
      \n

      <\/p>\n<\/td>\n<\/tr>\n

      \n

      Retrieving Users in 10 records chunks<\/p>\n<\/td>\n<\/tr>\n<\/table>\n

      It is not uncommon that you find yourself in a situation that your identity store (for which you are writing your custom authentication provider) allows duplicate usernames, WOW that’s scary isn’t it? For example in the Users table structure I provided above , I am extracting user information from a table that is indexed on ContactID and not on username. Picture a system (Like a CRM instance) that allows a non-member becomes a member and upon this transformation users can keep their username. Reza (username= rezaa) becomes a member but Reza is still the same person with the same username, billing address, credit card information and bluh bluh . From CRM standpoint Reza is no longer old Reza but a new identity, therefore a new ContactID is issued for him and some of his associated information is copied to the new identity including his username. This leads us to a situation in which we have to deal with duplicate usernames ! Your stored procedure returns two records (with different ContactIDs but same username) and in your code when you construct MembershipUserCollection<\/a> you get an exception that two keys with the same value cannot be added to a hashtable (which makes sense!). You need to delete the duplicate rows right in your stored procedure before returning them to your custom authentication provider. Alternatively, you can handle avoiding duplications in your code by putting your code in a try-catch block and move on to the next record should adding the current record throws an exception , but question is why do you need to take this approach when you can easily handle it in your stored procedure? So how exactly does this all work? I am so glad you asked! Here you go:<\/p>\n

      \n\n\t\t
      \n\n\t\t
      \n\t\t\t\t\t\t< View plain text<\/span> ><\/a>\n\t\t\t\t\t<\/div>\n\n\t\t
      sql<\/div>\n\n\t\t\n\t\t
      \n\n\t<\/div>\n\t\n\t
      \n\t\t
      1. with<\/span> UsersCTE as<\/span><\/div><\/li>\n
      2. (<\/span><\/div><\/li>\n
      3. SELECT<\/span><\/div><\/li>\n
      4. ROW_NUMBER<\/span>(<\/span>)<\/span> over<\/span> (<\/span>order<\/span> by<\/span> con.<\/span>ContactID,<\/span>con.<\/span>UserName,<\/span>con.<\/span>CreatedOn,<\/span>con.<\/span>Email)<\/span> as<\/span> RowNum,<\/span><\/div><\/li>\n
      5. con.<\/span>ContactID,<\/span><\/div><\/li>\n
      6. con.<\/span>UserName,<\/span><\/div><\/li>\n
      7. con.<\/span>CreatedOn,<\/span><\/div><\/li>\n
      8. con.<\/span>Email<\/div><\/li>\n
      9. FROM<\/span><\/div><\/li>\n
      10. dbo.<\/span>Contacts con<\/div><\/li>\n
      11. )<\/span><\/div><\/li>\n
      12.  <\/div><\/li>\n
      13. SELECT<\/span><\/div><\/li>\n
      14. UsersCTE.<\/span>RowNum,<\/span><\/div><\/li>\n
      15. conm.<\/span>ContactID,<\/span><\/div><\/li>\n
      16. conm.<\/span>CreatedOn,<\/span><\/div><\/li>\n
      17. conm.<\/span>Email,<\/span><\/div><\/li>\n
      18. conm.<\/span>UserName<\/div><\/li>\n
      19. FROM<\/span><\/div><\/li>\n
      20. dbo.<\/span>Contacts conm INNER<\/span> JOIN<\/span> UsersCTE ON<\/span> conm.<\/span>ContactID =<\/span> UsersCTE.<\/span>ContactID<\/div><\/li>\n
      21.  <\/div><\/li>\n
      22. WHERE<\/span>        UsersCTE.<\/span>RowNum IN<\/span> (<\/span>SELECT<\/span> MIN<\/span> (<\/span>UsersCTE.<\/span>RowNum)<\/span><\/div><\/li>\n
      23. FROM<\/span> UsersCTE<\/div><\/li>\n
      24. GROUP<\/span> BY<\/span> UserName<\/div><\/li>\n
      25. HAVING<\/span> Count<\/span>(<\/span>*<\/span>)<\/span> =<\/span> 1<\/span>)<\/span><\/div><\/li>\n<\/ol>\t<\/div>\n\n<\/div>\n\n","protected":false},"excerpt":{"rendered":"

        Since I am a lazy developer I normally don’t like to author multiple stored procedures if I can afford to aggregate all the logic in one place without causing too much complexity. For example when working with custom authentication providers you are often required to write separate stored procedures for GetAllUsers, FindUsersByName, FindUsersByEmail that can […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[43],"tags":[],"class_list":["post-486","post","type-post","status-publish","format-standard","hentry","category-moss-2007"],"yoast_head":"\nFBA: Combining Stored Procedures and Power of CTEs - Reza Alirezaei's Blog %<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/blogs.devhorizon.com\/reza\/2008\/01\/26\/fba-combining-stored-procedures-and-power-of-ctes\/\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Reza Alirezaei\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/blogs.devhorizon.com\\\/reza\\\/2008\\\/01\\\/26\\\/fba-combining-stored-procedures-and-power-of-ctes\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/blogs.devhorizon.com\\\/reza\\\/2008\\\/01\\\/26\\\/fba-combining-stored-procedures-and-power-of-ctes\\\/\"},\"author\":{\"name\":\"Reza Alirezaei\",\"@id\":\"https:\\\/\\\/blogs.devhorizon.com\\\/reza\\\/#\\\/schema\\\/person\\\/cdbb24d283697a65951cb4a14e474938\"},\"headline\":\"FBA: Combining Stored Procedures and Power of CTEs\",\"datePublished\":\"2008-01-26T21:07:08+00:00\",\"dateModified\":\"2008-01-27T06:50:46+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/blogs.devhorizon.com\\\/reza\\\/2008\\\/01\\\/26\\\/fba-combining-stored-procedures-and-power-of-ctes\\\/\"},\"wordCount\":876,\"commentCount\":1,\"image\":{\"@id\":\"https:\\\/\\\/blogs.devhorizon.com\\\/reza\\\/2008\\\/01\\\/26\\\/fba-combining-stored-procedures-and-power-of-ctes\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/blogs.devhorizon.com\\\/reza\\\/wp-content\\\/uploads\\\/2008\\\/01\\\/012608-2107-fbacombinin13.png\",\"articleSection\":[\"MOSS 2007\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/blogs.devhorizon.com\\\/reza\\\/2008\\\/01\\\/26\\\/fba-combining-stored-procedures-and-power-of-ctes\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/blogs.devhorizon.com\\\/reza\\\/2008\\\/01\\\/26\\\/fba-combining-stored-procedures-and-power-of-ctes\\\/\",\"url\":\"https:\\\/\\\/blogs.devhorizon.com\\\/reza\\\/2008\\\/01\\\/26\\\/fba-combining-stored-procedures-and-power-of-ctes\\\/\",\"name\":\"FBA: Combining Stored Procedures and Power of CTEs - Reza Alirezaei's Blog %\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/blogs.devhorizon.com\\\/reza\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/blogs.devhorizon.com\\\/reza\\\/2008\\\/01\\\/26\\\/fba-combining-stored-procedures-and-power-of-ctes\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/blogs.devhorizon.com\\\/reza\\\/2008\\\/01\\\/26\\\/fba-combining-stored-procedures-and-power-of-ctes\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/blogs.devhorizon.com\\\/reza\\\/wp-content\\\/uploads\\\/2008\\\/01\\\/012608-2107-fbacombinin13.png\",\"datePublished\":\"2008-01-26T21:07:08+00:00\",\"dateModified\":\"2008-01-27T06:50:46+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/blogs.devhorizon.com\\\/reza\\\/#\\\/schema\\\/person\\\/cdbb24d283697a65951cb4a14e474938\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/blogs.devhorizon.com\\\/reza\\\/2008\\\/01\\\/26\\\/fba-combining-stored-procedures-and-power-of-ctes\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/blogs.devhorizon.com\\\/reza\\\/2008\\\/01\\\/26\\\/fba-combining-stored-procedures-and-power-of-ctes\\\/#primaryimage\",\"url\":\"https:\\\/\\\/blogs.devhorizon.com\\\/reza\\\/wp-content\\\/uploads\\\/2008\\\/01\\\/012608-2107-fbacombinin13.png\",\"contentUrl\":\"https:\\\/\\\/blogs.devhorizon.com\\\/reza\\\/wp-content\\\/uploads\\\/2008\\\/01\\\/012608-2107-fbacombinin13.png\",\"width\":574,\"height\":376},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/blogs.devhorizon.com\\\/reza\\\/#website\",\"url\":\"https:\\\/\\\/blogs.devhorizon.com\\\/reza\\\/\",\"name\":\"Reza Alirezaei's Blog\",\"description\":\"Blogging from the field!\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/blogs.devhorizon.com\\\/reza\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/blogs.devhorizon.com\\\/reza\\\/#\\\/schema\\\/person\\\/cdbb24d283697a65951cb4a14e474938\",\"name\":\"Reza Alirezaei\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/3ba940d84e0ecb909e62e93df4c56daf0395c7e53c914467ab2ee73124a7d7b6?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/3ba940d84e0ecb909e62e93df4c56daf0395c7e53c914467ab2ee73124a7d7b6?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/3ba940d84e0ecb909e62e93df4c56daf0395c7e53c914467ab2ee73124a7d7b6?s=96&d=mm&r=g\",\"caption\":\"Reza Alirezaei\"},\"url\":\"https:\\\/\\\/blogs.devhorizon.com\\\/reza\\\/author\\\/rezaa\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"FBA: Combining Stored Procedures and Power of CTEs - Reza Alirezaei's Blog %","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/blogs.devhorizon.com\/reza\/2008\/01\/26\/fba-combining-stored-procedures-and-power-of-ctes\/","twitter_misc":{"Written by":"Reza Alirezaei","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/blogs.devhorizon.com\/reza\/2008\/01\/26\/fba-combining-stored-procedures-and-power-of-ctes\/#article","isPartOf":{"@id":"https:\/\/blogs.devhorizon.com\/reza\/2008\/01\/26\/fba-combining-stored-procedures-and-power-of-ctes\/"},"author":{"name":"Reza Alirezaei","@id":"https:\/\/blogs.devhorizon.com\/reza\/#\/schema\/person\/cdbb24d283697a65951cb4a14e474938"},"headline":"FBA: Combining Stored Procedures and Power of CTEs","datePublished":"2008-01-26T21:07:08+00:00","dateModified":"2008-01-27T06:50:46+00:00","mainEntityOfPage":{"@id":"https:\/\/blogs.devhorizon.com\/reza\/2008\/01\/26\/fba-combining-stored-procedures-and-power-of-ctes\/"},"wordCount":876,"commentCount":1,"image":{"@id":"https:\/\/blogs.devhorizon.com\/reza\/2008\/01\/26\/fba-combining-stored-procedures-and-power-of-ctes\/#primaryimage"},"thumbnailUrl":"https:\/\/blogs.devhorizon.com\/reza\/wp-content\/uploads\/2008\/01\/012608-2107-fbacombinin13.png","articleSection":["MOSS 2007"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/blogs.devhorizon.com\/reza\/2008\/01\/26\/fba-combining-stored-procedures-and-power-of-ctes\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/blogs.devhorizon.com\/reza\/2008\/01\/26\/fba-combining-stored-procedures-and-power-of-ctes\/","url":"https:\/\/blogs.devhorizon.com\/reza\/2008\/01\/26\/fba-combining-stored-procedures-and-power-of-ctes\/","name":"FBA: Combining Stored Procedures and Power of CTEs - Reza Alirezaei's Blog %","isPartOf":{"@id":"https:\/\/blogs.devhorizon.com\/reza\/#website"},"primaryImageOfPage":{"@id":"https:\/\/blogs.devhorizon.com\/reza\/2008\/01\/26\/fba-combining-stored-procedures-and-power-of-ctes\/#primaryimage"},"image":{"@id":"https:\/\/blogs.devhorizon.com\/reza\/2008\/01\/26\/fba-combining-stored-procedures-and-power-of-ctes\/#primaryimage"},"thumbnailUrl":"https:\/\/blogs.devhorizon.com\/reza\/wp-content\/uploads\/2008\/01\/012608-2107-fbacombinin13.png","datePublished":"2008-01-26T21:07:08+00:00","dateModified":"2008-01-27T06:50:46+00:00","author":{"@id":"https:\/\/blogs.devhorizon.com\/reza\/#\/schema\/person\/cdbb24d283697a65951cb4a14e474938"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/blogs.devhorizon.com\/reza\/2008\/01\/26\/fba-combining-stored-procedures-and-power-of-ctes\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/blogs.devhorizon.com\/reza\/2008\/01\/26\/fba-combining-stored-procedures-and-power-of-ctes\/#primaryimage","url":"https:\/\/blogs.devhorizon.com\/reza\/wp-content\/uploads\/2008\/01\/012608-2107-fbacombinin13.png","contentUrl":"https:\/\/blogs.devhorizon.com\/reza\/wp-content\/uploads\/2008\/01\/012608-2107-fbacombinin13.png","width":574,"height":376},{"@type":"WebSite","@id":"https:\/\/blogs.devhorizon.com\/reza\/#website","url":"https:\/\/blogs.devhorizon.com\/reza\/","name":"Reza Alirezaei's Blog","description":"Blogging from the field!","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/blogs.devhorizon.com\/reza\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/blogs.devhorizon.com\/reza\/#\/schema\/person\/cdbb24d283697a65951cb4a14e474938","name":"Reza Alirezaei","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/3ba940d84e0ecb909e62e93df4c56daf0395c7e53c914467ab2ee73124a7d7b6?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/3ba940d84e0ecb909e62e93df4c56daf0395c7e53c914467ab2ee73124a7d7b6?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/3ba940d84e0ecb909e62e93df4c56daf0395c7e53c914467ab2ee73124a7d7b6?s=96&d=mm&r=g","caption":"Reza Alirezaei"},"url":"https:\/\/blogs.devhorizon.com\/reza\/author\/rezaa\/"}]}},"_links":{"self":[{"href":"https:\/\/blogs.devhorizon.com\/reza\/wp-json\/wp\/v2\/posts\/486","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blogs.devhorizon.com\/reza\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blogs.devhorizon.com\/reza\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blogs.devhorizon.com\/reza\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/blogs.devhorizon.com\/reza\/wp-json\/wp\/v2\/comments?post=486"}],"version-history":[{"count":0,"href":"https:\/\/blogs.devhorizon.com\/reza\/wp-json\/wp\/v2\/posts\/486\/revisions"}],"wp:attachment":[{"href":"https:\/\/blogs.devhorizon.com\/reza\/wp-json\/wp\/v2\/media?parent=486"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blogs.devhorizon.com\/reza\/wp-json\/wp\/v2\/categories?post=486"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blogs.devhorizon.com\/reza\/wp-json\/wp\/v2\/tags?post=486"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}