Support for ALTER not just CREATE

[franzfrank]

Hey Maintainer i like your project very much and it's already helping me a lot. i am working with t-sql and all my .sql files start with / ... / ALTER

[jackscodemonkey]

Hi Frank, What type of objects in t-sql use the create or alter syntax? Could you provide a mock example of each type of object and I'll have a look.

[franzfrank]

Hi Marcus

thanks for your mail. i didn't expect a reply this fast :-) procedures and functions can use the syntax, but not tables:

procedures:

CREATE [ OR ALTER ] { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ] [ { @parameter_name [ type_schema_name. ] data_type } [ VARYING ] [ NULL ] [ = default ] [ OUT | OUTPUT | [READONLY] ] [ ,...n ] [ WITH [ ,...n ] ] [ FOR REPLICATION ]AS { [ BEGIN ] sql_statement [;] [ ...n ] [ END ] } [;]

::= [ ENCRYPTION ] [ RECOMPILE ] [ EXECUTE AS Clause ] so the procedure declarations is one of: Create procedure myschema.myproc @i int = 0 as begin select 'Hello', @i end or alter procedure myschema.myproc @i int = 0 as begin select 'Hello', @i end or create or alter procedure myschema.myproc @i int = 0 as begin select 'Hello', @i end (personally, i am just using the ALTER or CREATE OR ALTER syntax) Regarding functions (scalar and table-valued) and views, its basically the same syntax: -- Transact-SQL Scalar Function SyntaxCREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name ( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type [ NULL ] [ = default ] [ READONLY ] } [ ,...n ] ] )RETURNS return_data_type [ WITH [ ,...n ] ] [ AS ] BEGIN function_body RETURN scalar_expression END [ ; ] alter function dbo.vMyFunction ***@***.*** int = 0, @b nvarchar(10) = 'test') returns int begin return @i end table valued function: CREATE FUNCTION udfProductInYear ( @model_year INT )RETURNS TABLEASRETURN SELECT product_name, model_year, list_price FROM production.products WHERE model_year = @model_year; CREATE [ OR ALTER ] VIEW [ schema_name . ] view_name [ (column [ ,...n ] ) ] [ WITH [ ,...n ] ]AS select_statement Hope this helps and thanks again for this project. Frank Am Do., 13. Juli 2023 um 19:11 Uhr schrieb Marcus Robb < ***@***.***>: > Hi Frank, > What type of objects in t-sql use the create or alter syntax? > Could you provide a mock example of each type of object and I'll have a > look. > > — > Reply to this email directly, view it on GitHub > , > or unsubscribe > > . > You are receiving this because you authored the thread.Message ID: > ***@***.***> > -- Frank Schmidt Birkenstrasse 13 6003 Luzern / Schweiz +41 76 427 75 88