Create stored procedure that contains newlines with a oneliner sql statement
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty{ margin-bottom:0;
}
up vote
1
down vote
favorite
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?
sql-server
add a comment |
up vote
1
down vote
favorite
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?
sql-server
So you need to include carriage return and new line characters in your stored procedure, but not in your CREATE statement?
– George.Palacios
13 hours ago
Yes, that is true.
– Anders Lindén
13 hours ago
2
May we ask why?
– user1008646
12 hours ago
add a comment |
up vote
1
down vote
favorite
up vote
1
down vote
favorite
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?
sql-server
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?
sql-server
sql-server
asked 13 hours ago
Anders Lindén
2171312
2171312
So you need to include carriage return and new line characters in your stored procedure, but not in your CREATE statement?
– George.Palacios
13 hours ago
Yes, that is true.
– Anders Lindén
13 hours ago
2
May we ask why?
– user1008646
12 hours ago
add a comment |
So you need to include carriage return and new line characters in your stored procedure, but not in your CREATE statement?
– George.Palacios
13 hours ago
Yes, that is true.
– Anders Lindén
13 hours ago
2
May we ask why?
– user1008646
12 hours ago
So you need to include carriage return and new line characters in your stored procedure, but not in your CREATE statement?
– George.Palacios
13 hours ago
So you need to include carriage return and new line characters in your stored procedure, but not in your CREATE statement?
– George.Palacios
13 hours ago
Yes, that is true.
– Anders Lindén
13 hours ago
Yes, that is true.
– Anders Lindén
13 hours ago
2
2
May we ask why?
– user1008646
12 hours ago
May we ask why?
– user1008646
12 hours ago
add a comment |
1 Answer
1
active
oldest
votes
up vote
6
down vote
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;
@AndersLindén yep, don't put a + before EXEC.
– sp_BlitzErik
13 hours ago
I was trying to put the + in the argument to exec, building the sql string there
– Anders Lindén
13 hours ago
1
@Anders um, why?
– Aaron Bertrand♦
13 hours ago
The parameter of sp_executesql must be either a variable or a constant. No expressions allowed.
– user1008646
12 hours ago
Is there no way of escaping characters in a t-sql string?
– Anders Lindén
11 hours ago
|
show 1 more comment
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
6
down vote
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;
@AndersLindén yep, don't put a + before EXEC.
– sp_BlitzErik
13 hours ago
I was trying to put the + in the argument to exec, building the sql string there
– Anders Lindén
13 hours ago
1
@Anders um, why?
– Aaron Bertrand♦
13 hours ago
The parameter of sp_executesql must be either a variable or a constant. No expressions allowed.
– user1008646
12 hours ago
Is there no way of escaping characters in a t-sql string?
– Anders Lindén
11 hours ago
|
show 1 more comment
up vote
6
down vote
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;
@AndersLindén yep, don't put a + before EXEC.
– sp_BlitzErik
13 hours ago
I was trying to put the + in the argument to exec, building the sql string there
– Anders Lindén
13 hours ago
1
@Anders um, why?
– Aaron Bertrand♦
13 hours ago
The parameter of sp_executesql must be either a variable or a constant. No expressions allowed.
– user1008646
12 hours ago
Is there no way of escaping characters in a t-sql string?
– Anders Lindén
11 hours ago
|
show 1 more comment
up vote
6
down vote
up vote
6
down vote
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;
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;
answered 13 hours ago
sp_BlitzErik
20.6k1262102
20.6k1262102
@AndersLindén yep, don't put a + before EXEC.
– sp_BlitzErik
13 hours ago
I was trying to put the + in the argument to exec, building the sql string there
– Anders Lindén
13 hours ago
1
@Anders um, why?
– Aaron Bertrand♦
13 hours ago
The parameter of sp_executesql must be either a variable or a constant. No expressions allowed.
– user1008646
12 hours ago
Is there no way of escaping characters in a t-sql string?
– Anders Lindén
11 hours ago
|
show 1 more comment
@AndersLindén yep, don't put a + before EXEC.
– sp_BlitzErik
13 hours ago
I was trying to put the + in the argument to exec, building the sql string there
– Anders Lindén
13 hours ago
1
@Anders um, why?
– Aaron Bertrand♦
13 hours ago
The parameter of sp_executesql must be either a variable or a constant. No expressions allowed.
– user1008646
12 hours ago
Is there no way of escaping characters in a t-sql string?
– Anders Lindén
11 hours ago
@AndersLindén yep, don't put a + before EXEC.
– sp_BlitzErik
13 hours ago
@AndersLindén yep, don't put a + before EXEC.
– sp_BlitzErik
13 hours ago
I was trying to put the + in the argument to exec, building the sql string there
– Anders Lindén
13 hours ago
I was trying to put the + in the argument to exec, building the sql string there
– Anders Lindén
13 hours ago
1
1
@Anders um, why?
– Aaron Bertrand♦
13 hours ago
@Anders um, why?
– Aaron Bertrand♦
13 hours ago
The parameter of sp_executesql must be either a variable or a constant. No expressions allowed.
– user1008646
12 hours ago
The parameter of sp_executesql must be either a variable or a constant. No expressions allowed.
– user1008646
12 hours ago
Is there no way of escaping characters in a t-sql string?
– Anders Lindén
11 hours ago
Is there no way of escaping characters in a t-sql string?
– Anders Lindén
11 hours ago
|
show 1 more comment
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f223095%2fcreate-stored-procedure-that-contains-newlines-with-a-oneliner-sql-statement%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
So you need to include carriage return and new line characters in your stored procedure, but not in your CREATE statement?
– George.Palacios
13 hours ago
Yes, that is true.
– Anders Lindén
13 hours ago
2
May we ask why?
– user1008646
12 hours ago