imran February 2016

Creating a view in tempdb in SQL Server through a post request

Since I need to send the query statement through a http post request there are certain limitations. 1. It should be a one liner 2. it should be created in tempdb since i am going to drop it afterwards. since SQL server takes CREATE VIEW statement only in new line I am feeding new line characters to the statement. here is the statement:

DECLARE @NewLineChar AS CHAR(2) = CHAR(13) + CHAR(10);  ('USE tempdb;' +@NewLineChar + 'GO' +@NewLineChar +'CREATE VIEW temp_view AS select name from sys.databases') 

This query gives me following error:

Msg 102, Level 15, State 1. Incorrect syntax near 'USE tempdb;'. (Line 1)

what could be the problem ? Thanks

edit: The same query works like this

USE tempdb;
GO 
CREATE VIEW temp_view AS select name from sys.databases

where is the syntax error?

Answers


Gordon Linoff February 2016

This is a bit long for a comment.

You can create a view in the current database with a name like _temp_<something>. You can even include session information if you want to emulate temporary tables.

Or, create a temporary table with no rows:

select top 0 *
into #temp
from <whatever>;

You can access the structure of this table.

If you are using a very recent version of SQL Server, use sp_describe_first_result_set (see here).


TT. February 2016

Since SQL server takes CREATE VIEW statement only in new line I am feeding new line characters to the statement.

I have never heard of such a requirement. What the documentation does state is: The CREATE VIEW must be the first statement in a query batch.

The statement you have in your question doesn't make sense. You can't just drop a VARCHAR in SSMS and expect SQL Server to just execute it.

What you probably want is something like the following:

USE tempdb;
DECLARE @stmt NVARCHAR(MAX)=N'CREATE VIEW temp_view AS SELECT name FROM sys.databases;';
EXECUTE sp_executesql @stmt;

Or in one line:

USE tempdb;DECLARE @stmt NVARCHAR(MAX)=N'CREATE VIEW temp_view AS SELECT name FROM sys.databases;';EXECUTE sp_executesql @stmt;

Post Status

Asked in February 2016
Viewed 2,727 times
Voted 10
Answered 2 times

Search




Leave an answer