(window.webpackJsonp=window.webpackJsonp||[]).push([[3290],{3698:function(s,a,t){"use strict";t.r(a);var r=t(31),e=Object(r.a)({},(function(){var s=this,a=s.$createElement,t=s._self._c||a;return t("ContentSlotsDistributor",{attrs:{"slot-key":s.$parent.slotKey}},[t("h1",{attrs:{id:"sql-cursor"}},[t("a",{staticClass:"header-anchor",attrs:{href:"#sql-cursor"}},[s._v("#")]),s._v(" SQL CURSOR")]),s._v(" "),t("h2",{attrs:{id:"example-of-a-cursor-that-queries-all-rows-by-index-for-each-database"}},[t("a",{staticClass:"header-anchor",attrs:{href:"#example-of-a-cursor-that-queries-all-rows-by-index-for-each-database"}},[s._v("#")]),s._v(" Example of a cursor that queries all rows by index for each database")]),s._v(" "),t("p",[s._v("Here, a cursor is used to loop through all databases. "),t("br"),s._v("\nFuthermore, a cursor from dynamic sql is used to query each database returned by the first cursor. "),t("br"),s._v("\nThis is to demonstrate the connection-scope of a cursor.")]),s._v(" "),t("div",{staticClass:"language-sql extra-class"},[t("pre",{pre:!0,attrs:{class:"language-sql"}},[t("code",[t("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("DECLARE")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token variable"}},[s._v("@db_name")]),s._v(" nvarchar"),t("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v("(")]),t("span",{pre:!0,attrs:{class:"token number"}},[s._v("255")]),t("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(")")]),s._v("\n"),t("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("DECLARE")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token variable"}},[s._v("@sql")]),s._v(" nvarchar"),t("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v("(")]),s._v("MAX"),t("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(")")]),s._v("\n\n"),t("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("DECLARE")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token variable"}},[s._v("@schema")]),s._v(" nvarchar"),t("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v("(")]),t("span",{pre:!0,attrs:{class:"token number"}},[s._v("255")]),t("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(")")]),s._v(" \n"),t("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("DECLARE")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token variable"}},[s._v("@table")]),s._v(" nvarchar"),t("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v("(")]),t("span",{pre:!0,attrs:{class:"token number"}},[s._v("255")]),t("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(")")]),s._v("\n"),t("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("DECLARE")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token variable"}},[s._v("@column")]),s._v(" nvarchar"),t("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v("(")]),t("span",{pre:!0,attrs:{class:"token number"}},[s._v("255")]),t("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(")")]),s._v("\n\n\n\n\n"),t("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("DECLARE")]),s._v(" db_cursor "),t("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("CURSOR")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("FOR")]),s._v("  \n"),t("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("SELECT")]),s._v(" name "),t("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("FROM")]),s._v(" sys"),t("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(".")]),t("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("databases")]),s._v(" \n\n\n"),t("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("OPEN")]),s._v(" db_cursor   \n"),t("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("FETCH")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("NEXT")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("FROM")]),s._v(" db_cursor "),t("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("INTO")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token variable"}},[s._v("@db_name")]),s._v("    \n\n"),t("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("WHILE")]),s._v(" @"),t("span",{pre:!0,attrs:{class:"token variable"}},[s._v("@FETCH_STATUS")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("=")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token number"}},[s._v("0")]),s._v("   \n"),t("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("BEGIN")]),s._v("   \n    "),t("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("SET")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token variable"}},[s._v("@sql")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("=")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token string"}},[s._v("'SELECT * FROM '")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("+")]),s._v(" QUOTENAME"),t("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v("(")]),t("span",{pre:!0,attrs:{class:"token variable"}},[s._v("@db_name")]),t("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(")")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("+")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token string"}},[s._v("'.information_schema.columns'")]),s._v(" \n    "),t("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("PRINT")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token string"}},[s._v("''")]),s._v("\n    "),t("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("PRINT")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token string"}},[s._v("''")]),s._v("\n    "),t("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("PRINT")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token string"}},[s._v("''")]),s._v("\n    "),t("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("PRINT")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token variable"}},[s._v("@sql")]),s._v(" \n    "),t("span",{pre:!0,attrs:{class:"token comment"}},[s._v("-- EXECUTE(@sql) ")]),s._v("\n    \n    \n    \n    "),t("span",{pre:!0,attrs:{class:"token comment"}},[s._v("-- For each database ")]),s._v("\n\n    "),t("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("DECLARE")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token variable"}},[s._v("@sqlstatement")]),s._v(" nvarchar"),t("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v("(")]),t("span",{pre:!0,attrs:{class:"token number"}},[s._v("4000")]),t("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(")")]),s._v("\n    "),t("span",{pre:!0,attrs:{class:"token comment"}},[s._v("--move declare cursor into sql to be executed")]),s._v("\n    "),t("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("SET")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token variable"}},[s._v("@sqlstatement")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("=")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token string"}},[s._v("'DECLARE  columns_cursor CURSOR FOR SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME FROM '")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("+")]),s._v(" QUOTENAME"),t("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v("(")]),t("span",{pre:!0,attrs:{class:"token variable"}},[s._v("@db_name")]),t("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(")")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("+")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token string"}},[s._v("'.information_schema.columns ORDER BY TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION'")]),s._v("\n\n\n\n    "),t("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("EXEC")]),s._v(" sp_executesql "),t("span",{pre:!0,attrs:{class:"token variable"}},[s._v("@sqlstatement")]),s._v("\n\n\n    "),t("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("OPEN")]),s._v(" columns_cursor\n    "),t("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("FETCH")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("NEXT")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("FROM")]),s._v(" columns_cursor\n    "),t("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("INTO")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token variable"}},[s._v("@schema")]),t("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(",")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token variable"}},[s._v("@table")]),t("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(",")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token variable"}},[s._v("@column")]),s._v(" \n\n    "),t("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("WHILE")]),s._v(" @"),t("span",{pre:!0,attrs:{class:"token variable"}},[s._v("@FETCH_STATUS")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("=")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token number"}},[s._v("0")]),s._v("\n    "),t("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("BEGIN")]),s._v("\n        "),t("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("PRINT")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token variable"}},[s._v("@schema")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("+")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token string"}},[s._v("'.'")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("+")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token variable"}},[s._v("@table")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("+")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token string"}},[s._v("'.'")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token operator"}},[s._v("+")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token variable"}},[s._v("@column")]),s._v(" \n        "),t("span",{pre:!0,attrs:{class:"token comment"}},[s._v("--EXEC asp_DoSomethingStoredProc @UserId")]),s._v("\n\n    "),t("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("FETCH")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("NEXT")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("FROM")]),s._v(" columns_cursor "),t("span",{pre:!0,attrs:{class:"token comment"}},[s._v("--have to fetch again within loop")]),s._v("\n    "),t("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("INTO")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token variable"}},[s._v("@schema")]),t("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(",")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token variable"}},[s._v("@table")]),t("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(",")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token variable"}},[s._v("@column")]),s._v(" \n\n    "),t("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("END")]),s._v("\n    "),t("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("CLOSE")]),s._v(" columns_cursor\n    "),t("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("DEALLOCATE")]),s._v(" columns_cursor\n\n    "),t("span",{pre:!0,attrs:{class:"token comment"}},[s._v("-- End for each database ")]),s._v("\n    \n    \n    \n    \n    \n    "),t("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("FETCH")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("NEXT")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("FROM")]),s._v(" db_cursor "),t("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("INTO")]),s._v(" "),t("span",{pre:!0,attrs:{class:"token variable"}},[s._v("@db_name")]),s._v("   \n"),t("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("END")]),s._v("   \n\n"),t("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("CLOSE")]),s._v(" db_cursor   \n"),t("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("DEALLOCATE")]),s._v(" db_cursor\n\n")])])])])}),[],!1,null,null,null);a.default=e.exports}}]);