Search code examples
sqlsql-serversqlcmd

Run create function code without SQLCMD

I have DDL code to create a function that runs flawlessly from sqlcmd and other specific SQL Server clients:

/*
 * Émulo de la función LPAD() de Oracle
 */
CREATE FUNCTION LPAD
(
    @cadena VARCHAR(MAX), -- Texto de partida
    @tamano INT,          -- Tamaño de la cadena resultante
    @relleno CHAR         -- Carácter de relleno
)
RETURNS VARCHAR(MAX)
AS
BEGIN
    return REPLICATE(@relleno, @tamano - LEN(@cadena)) + @cadena;
END
GO

However, it won't typically run from cross-DBMS clients (SQL Fiddle, DBeaver, HeidiSQL...):

[102] Incorrect syntax near '@cadena'.

I suspect it's got to do with the GO batch separator not being implemented. If that's the case, is there an alternative syntax?

Solution

  • To sum up my comments:

    The standard delimiter in SQL is the ;.

    However, when running a code block that contains embedded semicolons (e.g. when creating a stored procedure) the standard delimiter cannot be applied as in that case the block wouldn't be sent as a single statement.

    For those cases the SQL client usually has some way of defining an alternate delimiter that marks the end of a block and usually has a higher precedence than the ;

    Microsoft calls this "alternate delimiter" the "batch separated" and decided to use GO for this and all Microsoft tools stick to that. In SSMS this batch separator can actually be configured to something else.

    Oracle's SQL*Plus (and SQL Developer) use / for this.

    Other SQL clients allow a dynamic definition inside a SQL script typically using delimiter (or something similar) to change the delimiter for subsequent statements.

    As I have never used DBeaver or HeidiSQL I cannot tell how this is done in those SQL clients.