Sql-server – Create stored procedure that contains newlines with a oneliner sql statement

dynamic-sqlsql serverstored-procedurest-sql

I want to put code in a single line that will create a stored procedure that contains newlines.

Is that possible?
Do I need to use sp_executesql?
How do I escape newlines in a sql statement?
How do I escape newlines in a string?

Best Answer

Yes, you can do something like this with dynamic SQL:

DECLARE @SQL NVARCHAR(MAX) = N'';
DECLARE @NewLine NCHAR(1) = NCHAR(10);

SET @SQL = @SQL + N'SELECT * ' + @NewLine + N'FROM sys.databases AS d ' + @NewLine + N'WHERE d.database_id > 4;' + @NewLine;

PRINT @SQL;
EXEC sys.sp_executesql @SQL;

Related Solutions

Mysql – use of signal for insert statement in thesql stored procedure

This is done using a proper UNIQUE index on the table itself. MySQL will automatically take care of it.
This is done using proper data types for the fields in the table itself. MySQL will automatically take care of it. (BTW: your item_value is already smallint(6) so it can only be numeric anyway.)
Seeing the obove two points, you probably don't need to check this anymore.

Meaning you probably not need your stored procedure anymore. At least not for this reason.

Stored procedure with ROW instead of a list of parameters

Sure. You can even use the predefined %rowtype record.

CREATE OR REPLACE PROCEDURE insert_row( p_rec IN table_name%rowtype )
AS
BEGIN
  INSERT INTO table_name
    VALUES p_rec;
END;

Related Question