{"id":6858,"date":"2022-03-16T06:27:00","date_gmt":"2022-03-16T05:27:00","guid":{"rendered":"https:\/\/expert-only.com\/?p=6858"},"modified":"2022-04-18T14:30:40","modified_gmt":"2022-04-18T12:30:40","slug":"modify-sql-server-function","status":"publish","type":"post","link":"https:\/\/expert-only.com\/en\/t-sql\/modify-sql-server-function\/","title":{"rendered":"Modify a SQL Server function with alter function"},"content":{"rendered":"\n<p><strong>How to modify a SQL Server function with Alter Function ? How to update the code of an SQL Server function without deleting it? The solution is to update its definition using the SQL Server Alter Function command. The query updates the function already created in the database. This new code returns not only the customer name, but also the customer city, separated with a dash, the result is stored in the @NameCityCustomer variable.<\/strong><br><strong>\u00a0<\/strong><\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"pid03\"><strong>How to modify a SQL Server function with the Alter Function command ?<\/strong><\/h2>\n\n\n\n<p>First of all, it is pretty easy to do, if the original code of the function is available. Indeed, just replace the <strong>CREATE FUNCTION<\/strong> keywords by the&nbsp;<strong>ALTER FUNCTION <\/strong>ones. To get the actual code of the function, just <strong>navigate to the function<\/strong> using SQL Server Management Studio, <strong>right click<\/strong> and select <strong>Modify<\/strong>. Then the window will show you the script of the function, just edit it and run it.<\/p>\n\n\n\n<p>Use the script below to modify this <a href=\"https:\/\/expert-only.com\/en\/t-sql\/how-to-create-a-sql-server-function\/\"><strong>SQL Function<\/strong><\/a> created in a previous article, in order to display the customer name and the customer city, separated by a dash sign.<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">ALTER FUNCTION dbo.ufnDisplayCustomers(@CustomerNumber INTEGER)\nRETURNS NVARCHAR(40)\nAS \nBEGIN \n  DECLARE\t@NameCityCustomer\t NVARCHAR(20);\n  SELECT\t@NameCityCustomer = [Name] + '-' + [City] \n  FROM\t\t[dbo].[CUSTOMERS]\n  WHERE\t\t[CustomerNumber] = @CustomerNumber;\n  RETURN\t@NameCityCustomer;\nEND\nGO\n<\/pre>\n\n\n\n<p>This SQL Server query updates the code of an existing function. An easy way to verify that the code works is to select all the code. Start between the first <strong>BEGIN<\/strong> and the last <strong>END<\/strong>, and then run it, using <strong>F5<\/strong>.<\/p>\n\n\n\n<p>For example, to test the first query, declare the @CustomerNumber variable. Like in the next example:<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">DECLARE  @CustomerNumber  INTEGER = 1;\nDECLARE  @CustomerName   NVARCHAR(20);\nSELECT  @CustomerName = [Name]\nFROM    [dbo].[CUSTOMERS]\nWHERE    [CustomerNumber] = @CustomerNumber;\nRETURN  @CustomerName;\n<\/pre>\n\n\n\n<p>User-defined functions are, along with <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/relational-databases\/stored-procedures\/create-a-stored-procedure?view=sql-server-ver15\" target=\"_blank\" rel=\"noreferrer noopener\">stored procedures<\/a>, the most commonly used programs in T-SQL.<\/p>\n\n\n\n<p>Note that the code of the updated function must also be correct. Otherwise, the system displays an error of this type:<\/p>\n\n\n\n<p>The message in an English version of SSMS : <strong>Incorrect syntax near the keyword &#8216;RETURN&#8217;.<\/strong><\/p>\n\n\n\n<p>All you have to do is correct the syntax and restart the script via the <strong>SSMS<\/strong> graphical user interface or with the <strong>F5 shortcut<\/strong> which runs the script in the current window.<\/p>\n","protected":false},"excerpt":{"rendered":"<div class=\"mh-excerpt\"><p>How to modify a SQL Server function with Alter Function ? How to update the code of an SQL Server function without deleting it? The solution is to update its definition using the SQL Server Alter Function command. The <a class=\"mh-excerpt-more\" href=\"https:\/\/expert-only.com\/en\/t-sql\/modify-sql-server-function\/\" title=\"Modify a SQL Server function with alter function\">&#8230;<\/a><\/p>\n<\/div>","protected":false},"author":1,"featured_media":6400,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[454],"tags":[626],"class_list":{"0":"post-6858","1":"post","2":"type-post","3":"status-publish","4":"format-standard","5":"has-post-thumbnail","7":"category-t-sql","8":"tag-alter"},"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v21.7 (Yoast SEO v26.2) - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Modify a SQL Server function with alter function - T-SQL<\/title>\n<meta name=\"description\" content=\"How to modify a SQL Server function with alter function ? Use the Alter Function statement to update the T-SQL code in the database.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/expert-only.com\/en\/t-sql\/modify-sql-server-function\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Modify a SQL Server function with alter function\" \/>\n<meta property=\"og:description\" content=\"How to modify a SQL Server function with alter function ? Use the Alter Function statement to update the T-SQL code in the database.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/expert-only.com\/en\/t-sql\/modify-sql-server-function\/\" \/>\n<meta property=\"og:site_name\" content=\"SQL and IT Tutorials\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/ExpertOnlyCom\/\" \/>\n<meta property=\"article:published_time\" content=\"2022-03-16T05:27:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2022-04-18T12:30:40+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/expert-only.com\/wp-content\/uploads\/2020\/07\/fresh-glass-coffee-cup-2D256C64A99_1920x1080.jpeg\" \/>\n\t<meta property=\"og:image:width\" content=\"1920\" \/>\n\t<meta property=\"og:image:height\" content=\"1080\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"Expert-Only\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@expert_only\" \/>\n<meta name=\"twitter:site\" content=\"@expert_only\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Expert-Only\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/expert-only.com\/en\/t-sql\/modify-sql-server-function\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/expert-only.com\/en\/t-sql\/modify-sql-server-function\/\"},\"author\":{\"name\":\"Expert-Only\",\"@id\":\"https:\/\/expert-only.com\/en\/#\/schema\/person\/406a9576b52944f018739a42046873ef\"},\"headline\":\"Modify a SQL Server function with alter function\",\"datePublished\":\"2022-03-16T05:27:00+00:00\",\"dateModified\":\"2022-04-18T12:30:40+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/expert-only.com\/en\/t-sql\/modify-sql-server-function\/\"},\"wordCount\":332,\"publisher\":{\"@id\":\"https:\/\/expert-only.com\/en\/#organization\"},\"image\":{\"@id\":\"https:\/\/expert-only.com\/en\/t-sql\/modify-sql-server-function\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2020\/07\/fresh-glass-coffee-cup-2D256C64A99_1920x1080.jpeg\",\"keywords\":[\"Alter\"],\"articleSection\":[\"T-SQL\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/expert-only.com\/en\/t-sql\/modify-sql-server-function\/\",\"url\":\"https:\/\/expert-only.com\/en\/t-sql\/modify-sql-server-function\/\",\"name\":\"Modify a SQL Server function with alter function - T-SQL\",\"isPartOf\":{\"@id\":\"https:\/\/expert-only.com\/en\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/expert-only.com\/en\/t-sql\/modify-sql-server-function\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/expert-only.com\/en\/t-sql\/modify-sql-server-function\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2020\/07\/fresh-glass-coffee-cup-2D256C64A99_1920x1080.jpeg\",\"datePublished\":\"2022-03-16T05:27:00+00:00\",\"dateModified\":\"2022-04-18T12:30:40+00:00\",\"description\":\"How to modify a SQL Server function with alter function ? Use the Alter Function statement to update the T-SQL code in the database.\",\"breadcrumb\":{\"@id\":\"https:\/\/expert-only.com\/en\/t-sql\/modify-sql-server-function\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/expert-only.com\/en\/t-sql\/modify-sql-server-function\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/expert-only.com\/en\/t-sql\/modify-sql-server-function\/#primaryimage\",\"url\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2020\/07\/fresh-glass-coffee-cup-2D256C64A99_1920x1080.jpeg\",\"contentUrl\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2020\/07\/fresh-glass-coffee-cup-2D256C64A99_1920x1080.jpeg\",\"width\":1920,\"height\":1080},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/expert-only.com\/en\/t-sql\/modify-sql-server-function\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"en\",\"item\":\"https:\/\/expert-only.com\/en\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Modify a SQL Server function with alter function\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/expert-only.com\/en\/#website\",\"url\":\"https:\/\/expert-only.com\/en\/\",\"name\":\"SQL and IT Tutorials\",\"description\":\"\",\"publisher\":{\"@id\":\"https:\/\/expert-only.com\/en\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/expert-only.com\/en\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/expert-only.com\/en\/#organization\",\"name\":\"Expert-Only\",\"url\":\"https:\/\/expert-only.com\/en\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/expert-only.com\/en\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2017\/09\/cropped-logo_Expert-Only.jpg\",\"contentUrl\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2017\/09\/cropped-logo_Expert-Only.jpg\",\"width\":381,\"height\":174,\"caption\":\"Expert-Only\"},\"image\":{\"@id\":\"https:\/\/expert-only.com\/en\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/www.facebook.com\/ExpertOnlyCom\/\",\"https:\/\/x.com\/expert_only\",\"https:\/\/www.youtube.com\/channel\/UCMS5sR_FwAetB0FmciNvUaA\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/expert-only.com\/en\/#\/schema\/person\/406a9576b52944f018739a42046873ef\",\"name\":\"Expert-Only\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/expert-only.com\/en\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/084b15660763ff5b13bb60b2f52f97bb?s=96&d=identicon&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/084b15660763ff5b13bb60b2f52f97bb?s=96&d=identicon&r=g\",\"caption\":\"Expert-Only\"}}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Modify a SQL Server function with alter function - T-SQL","description":"How to modify a SQL Server function with alter function ? Use the Alter Function statement to update the T-SQL code in the database.","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:\/\/expert-only.com\/en\/t-sql\/modify-sql-server-function\/","og_locale":"en_US","og_type":"article","og_title":"Modify a SQL Server function with alter function","og_description":"How to modify a SQL Server function with alter function ? Use the Alter Function statement to update the T-SQL code in the database.","og_url":"https:\/\/expert-only.com\/en\/t-sql\/modify-sql-server-function\/","og_site_name":"SQL and IT Tutorials","article_publisher":"https:\/\/www.facebook.com\/ExpertOnlyCom\/","article_published_time":"2022-03-16T05:27:00+00:00","article_modified_time":"2022-04-18T12:30:40+00:00","og_image":[{"width":1920,"height":1080,"url":"https:\/\/expert-only.com\/wp-content\/uploads\/2020\/07\/fresh-glass-coffee-cup-2D256C64A99_1920x1080.jpeg","type":"image\/jpeg"}],"author":"Expert-Only","twitter_card":"summary_large_image","twitter_creator":"@expert_only","twitter_site":"@expert_only","twitter_misc":{"Written by":"Expert-Only","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/expert-only.com\/en\/t-sql\/modify-sql-server-function\/#article","isPartOf":{"@id":"https:\/\/expert-only.com\/en\/t-sql\/modify-sql-server-function\/"},"author":{"name":"Expert-Only","@id":"https:\/\/expert-only.com\/en\/#\/schema\/person\/406a9576b52944f018739a42046873ef"},"headline":"Modify a SQL Server function with alter function","datePublished":"2022-03-16T05:27:00+00:00","dateModified":"2022-04-18T12:30:40+00:00","mainEntityOfPage":{"@id":"https:\/\/expert-only.com\/en\/t-sql\/modify-sql-server-function\/"},"wordCount":332,"publisher":{"@id":"https:\/\/expert-only.com\/en\/#organization"},"image":{"@id":"https:\/\/expert-only.com\/en\/t-sql\/modify-sql-server-function\/#primaryimage"},"thumbnailUrl":"https:\/\/expert-only.com\/wp-content\/uploads\/2020\/07\/fresh-glass-coffee-cup-2D256C64A99_1920x1080.jpeg","keywords":["Alter"],"articleSection":["T-SQL"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/expert-only.com\/en\/t-sql\/modify-sql-server-function\/","url":"https:\/\/expert-only.com\/en\/t-sql\/modify-sql-server-function\/","name":"Modify a SQL Server function with alter function - T-SQL","isPartOf":{"@id":"https:\/\/expert-only.com\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/expert-only.com\/en\/t-sql\/modify-sql-server-function\/#primaryimage"},"image":{"@id":"https:\/\/expert-only.com\/en\/t-sql\/modify-sql-server-function\/#primaryimage"},"thumbnailUrl":"https:\/\/expert-only.com\/wp-content\/uploads\/2020\/07\/fresh-glass-coffee-cup-2D256C64A99_1920x1080.jpeg","datePublished":"2022-03-16T05:27:00+00:00","dateModified":"2022-04-18T12:30:40+00:00","description":"How to modify a SQL Server function with alter function ? Use the Alter Function statement to update the T-SQL code in the database.","breadcrumb":{"@id":"https:\/\/expert-only.com\/en\/t-sql\/modify-sql-server-function\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/expert-only.com\/en\/t-sql\/modify-sql-server-function\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/expert-only.com\/en\/t-sql\/modify-sql-server-function\/#primaryimage","url":"https:\/\/expert-only.com\/wp-content\/uploads\/2020\/07\/fresh-glass-coffee-cup-2D256C64A99_1920x1080.jpeg","contentUrl":"https:\/\/expert-only.com\/wp-content\/uploads\/2020\/07\/fresh-glass-coffee-cup-2D256C64A99_1920x1080.jpeg","width":1920,"height":1080},{"@type":"BreadcrumbList","@id":"https:\/\/expert-only.com\/en\/t-sql\/modify-sql-server-function\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"en","item":"https:\/\/expert-only.com\/en\/"},{"@type":"ListItem","position":2,"name":"Modify a SQL Server function with alter function"}]},{"@type":"WebSite","@id":"https:\/\/expert-only.com\/en\/#website","url":"https:\/\/expert-only.com\/en\/","name":"SQL and IT Tutorials","description":"","publisher":{"@id":"https:\/\/expert-only.com\/en\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/expert-only.com\/en\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/expert-only.com\/en\/#organization","name":"Expert-Only","url":"https:\/\/expert-only.com\/en\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/expert-only.com\/en\/#\/schema\/logo\/image\/","url":"https:\/\/expert-only.com\/wp-content\/uploads\/2017\/09\/cropped-logo_Expert-Only.jpg","contentUrl":"https:\/\/expert-only.com\/wp-content\/uploads\/2017\/09\/cropped-logo_Expert-Only.jpg","width":381,"height":174,"caption":"Expert-Only"},"image":{"@id":"https:\/\/expert-only.com\/en\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/ExpertOnlyCom\/","https:\/\/x.com\/expert_only","https:\/\/www.youtube.com\/channel\/UCMS5sR_FwAetB0FmciNvUaA"]},{"@type":"Person","@id":"https:\/\/expert-only.com\/en\/#\/schema\/person\/406a9576b52944f018739a42046873ef","name":"Expert-Only","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/expert-only.com\/en\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/084b15660763ff5b13bb60b2f52f97bb?s=96&d=identicon&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/084b15660763ff5b13bb60b2f52f97bb?s=96&d=identicon&r=g","caption":"Expert-Only"}}]}},"_links":{"self":[{"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/posts\/6858","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/comments?post=6858"}],"version-history":[{"count":0,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/posts\/6858\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/media\/6400"}],"wp:attachment":[{"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/media?parent=6858"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/categories?post=6858"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/tags?post=6858"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}