How can we CREATE a VIEW with variable which stores a select stat?

20 Jan 2006 1:32 PM

savvy

The code shown below is working perfectly in the analyzer. I want to
create a view with the results
Is it possible ?
Thanks in Advance

DECLARE @searchstring varchar(22)
SET @searchstring = 'aspnet'
declare @strSQL varchar(244)
select @strSQL='select FileName,Path from scope(''''
"c:\inetpub\wwwroot\sap-resources\Uploads" '''') where contains ('
select @strSQL=@strSQL +char(39)+ char(39)+ @searchstring +char(39)+
char(39)+')'
select @strSQL='select * from openquery(FileSystem,'+ char(39)+
@strSQL+ char(39)+ ')'
exec (@strSQL)

Something like
CREATE VIEW FileSearchResults AS (@strSQL)
which is not working

20 Jan 2006 2:11 PM

Jack Vamvas

Hi Savvy
You can't create views that way. A view is a SELECT statement against a
base table.
Either set up a stored procedure or have you considered using an "Inline
User-Defined Functions"

--
Jack Vamvas
__________________________________________________________________
Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
New article by Jack Vamvas - SQL and Markov Chains -
www.ciquery.com/articles/art_04.asp

Show quote

"savvy" <johng***@gmail.com> wrote in message
news:1137763947.141472.182100@g49g2000cwa.googlegroups.com...
> The code shown below is working perfectly in the analyzer. I want to
> create a view with the results
> Is it possible ?
> Thanks in Advance
>
> DECLARE @searchstring varchar(22)
> SET @searchstring = 'aspnet'
> declare @strSQL varchar(244)
> select @strSQL='select FileName,Path from scope(''''
> "c:\inetpub\wwwroot\sap-resources\Uploads" '''') where contains ('
> select @strSQL=@strSQL +char(39)+ char(39)+ @searchstring +char(39)+
> char(39)+')'
> select @strSQL='select * from openquery(FileSystem,'+ char(39)+
> @strSQL+ char(39)+ ')'
> exec (@strSQL)
>
> Something like
> CREATE VIEW FileSearchResults AS (@strSQL)
> which is not working
>

20 Jan 2006 3:03 PM

Jim Underwood

I don't know if this will work, if DDL is permitted in dynamic SQL, but...

declare @strSQLView varchar(2000)
set @strSQLView = 'CREATE VIEW FileSearchResults AS (' + @strSQL + ')'
exec (@strSQLView )

Why are you doing this though? Creating objects on the fly has never seemed
like a good idea to me. Isn't there another way to do it?

Show quote

"savvy" <johng***@gmail.com> wrote in message
news:1137763947.141472.182100@g49g2000cwa.googlegroups.com...
> The code shown below is working perfectly in the analyzer. I want to
> create a view with the results
> Is it possible ?
> Thanks in Advance
>
> DECLARE @searchstring varchar(22)
> SET @searchstring = 'aspnet'
> declare @strSQL varchar(244)
> select @strSQL='select FileName,Path from scope(''''
> "c:\inetpub\wwwroot\sap-resources\Uploads" '''') where contains ('
> select @strSQL=@strSQL +char(39)+ char(39)+ @searchstring +char(39)+
> char(39)+')'
> select @strSQL='select * from openquery(FileSystem,'+ char(39)+
> @strSQL+ char(39)+ ')'
> exec (@strSQL)
>
> Something like
> CREATE VIEW FileSearchResults AS (@strSQL)
> which is not working
>