{"id":2001,"date":"2016-03-22T20:00:42","date_gmt":"2016-03-22T19:00:42","guid":{"rendered":"http:\/\/www.unicoda.com\/?p=2001"},"modified":"2016-03-22T16:18:50","modified_gmt":"2016-03-22T15:18:50","slug":"sql-drop-create-pattern-mssql","status":"publish","type":"post","link":"https:\/\/www.unicoda.com\/?p=2001","title":{"rendered":"[SQL] DROP CREATE \u00ab\u00a0pattern\u00a0\u00bb (MSSQL)"},"content":{"rendered":"<p>Simple reminder for a working drop-create pattern in SQL (Successfully used with MSSQL).<\/p>\n<p>Log modification in table _chgLog first.<\/p>\n<pre>INSERT INTO _chgLog ([Date], [Version], [txt], [Type])\r\n\u00a0\u00a0 \u00a0VALUES (GETDATE(), '0.0.1', 'Update example', 0)\r\nGO<\/pre>\n<p>If object exists, we drop it before doing anything (But do not drop table unless you don&rsquo;t care about data).<\/p>\n<pre>IF OBJECTPROPERTY(object_id('example'), N'IsProcedure') = 1\r\n\u00a0\u00a0 \u00a0DROP PROCEDURE [dbo].[example]\r\nGO<\/pre>\n<p>Other interesting parameters for function objectproperty are the following (more to be found <a href=\"https:\/\/msdn.microsoft.com\/en-US\/library\/ms176105.aspx\" target=\"_blank\">here<\/a>) :<\/p>\n<ul>\n<li><span class=\"literal\">IsInlineFunction<\/span><\/li>\n<li><span class=\"literal\">IsProcedure<\/span><\/li>\n<li><span class=\"literal\">IsScalarFunction<\/span><\/li>\n<li><span class=\"literal\">IsTable<\/span><\/li>\n<li><span class=\"literal\">IsTableFunction<\/span><\/li>\n<li><span class=\"literal\">IsTrigger<\/span><\/li>\n<li><span class=\"literal\">IsView<\/span><\/li>\n<\/ul>\n<p>Then, we create the object.<\/p>\n<pre>-- =============================================\r\n-- Author: vvision\r\n-- Create date: 22\/03\/2016\r\n-- Description: &lt;Description&gt;\r\n-- =============================================\r\n\r\nCREATE PROCEDURE [dbo].[example]\r\n\u00a0 @id INT,\r\nAS\r\n  SELECT *\r\n  FROM [dbo].[example]\r\n  WHERE id = @id\r\nGO<\/pre>\n<p>Finally, we give permissions to users if needed.<\/p>\n<pre>GRANT EXECUTE ON [dbo].[example] TO [user] AS [dbo]\r\nGO<\/pre>\n<p>&nbsp;<\/p>\n<p>On another subject, to alter a table, we can use:<\/p>\n<pre>IF COL_LENGTH('table','newField') IS NULL\r\nBEGIN\r\n\u00a0 ALTER TABLE [dbo].[table] ADD newField VARCHAR(128) NULL\r\nEND<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Simple reminder for a working drop-create pattern in SQL (Successfully used with MSSQL). Log modification in table _chgLog first. INSERT INTO _chgLog ([Date], [Version], [txt], [Type]) \u00a0\u00a0 \u00a0VALUES (GETDATE(), &lsquo;0.0.1&rsquo;, &lsquo;Update example&rsquo;, 0) GO If object exists, we drop it before doing anything (But do not drop table unless you don&rsquo;t care about data). IF &hellip; <a href=\"https:\/\/www.unicoda.com\/?p=2001\" class=\"more-link\">Continuer la lecture<span class=\"screen-reader-text\"> de &laquo;&nbsp;[SQL] DROP CREATE \u00ab\u00a0pattern\u00a0\u00bb (MSSQL)&nbsp;&raquo;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3,40],"tags":[263,262,267,206],"class_list":["post-2001","post","type-post","status-publish","format-standard","hentry","category-code","category-info","tag-create","tag-drop","tag-mssql","tag-sql"],"_links":{"self":[{"href":"https:\/\/www.unicoda.com\/index.php?rest_route=\/wp\/v2\/posts\/2001","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.unicoda.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.unicoda.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.unicoda.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.unicoda.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2001"}],"version-history":[{"count":3,"href":"https:\/\/www.unicoda.com\/index.php?rest_route=\/wp\/v2\/posts\/2001\/revisions"}],"predecessor-version":[{"id":2055,"href":"https:\/\/www.unicoda.com\/index.php?rest_route=\/wp\/v2\/posts\/2001\/revisions\/2055"}],"wp:attachment":[{"href":"https:\/\/www.unicoda.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2001"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.unicoda.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2001"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.unicoda.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2001"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}