create views from parameterized stored procedures

[evilmousse]

I'm looking to do as the topic says, I currently
use temporary real tables (not #temp tables) that
i keep in existence for just long enough for
an access adp frontend to issue an output to excel
command, and then it's dropped. The parameters are
usually 2 dates to form a range or a few char fields.
The cry for me to use views instead has been issued,
and i'm investigating changing my select into queries
into create view statements.
My first problem is:
"Server: Msg 111, Level 15, State 1, Line 4
'CREATE VIEW' must be the first statement in a query batch."
when i try a simple create view statement after
defining a few variables. If i'm to issue go statements
to get that working, i'll lose the definition of my
parameters.
So what are my options then, to use global variables?
Further, does anyone know if the performance impact is worthwhile? The main reason for the change to views is
to avoid the confusion of these tables with the permenant
ones, but i'm curious about performance.

thx ahead of time

-g

 

[swampBoogie]

I can not see any benefit of creating views instead of using ordinary queries.

As to the actual problem, as you don't show any code it's hard to say what's wrong. If you wish to use parameters in the create statement, you need to use dynamic sql.

 

[evilmousse]

ok, restated more succinctly:
I make tables right now for excel output
routines to use, then drop them.
I'm considering changing them to views,
continuing the behavior of creation and
deletion.

tsql doesn't like create view statements
unless they're the first statement of the block
of code, disallowing my using variables.

I've gotten further in another forum, now
my problem is how to get quotename to format
the date correctly enough to include it in
a sql statement.

declare @from datetime, @to datetime, @sql varchar(1000)
select @from = '1/1/1', @to = '2/2/2'
select convert(datetime, @from, 101)
set @sql = 'create view vw1 as select * from t1 where dt1
= '+quotename(@from,']')
print @sql
exec(@sql)