How to write a procedure that creates a view depending on its parameter in SQL Server 2005

Question

I would like to write a procedure that creates a view depending on its parameter in SQL Server 2005.

Grossly, this should look like this :

CREATE PROCEDURE testprocedure
@clientid float
as
create view testview as 
select * from deliveries where clientid=@clientid

(but this does not work, it gives the error : Incorrect syntax near the keyword 'view'.)

Is it possible and how ?

Answer 1

try:

create procedure testprocedure
@clientid float
as
declare @query  varchar(8000)
set @query='create view testview as select * from deliveries where clientid='+CONVERT(varchar(20),@clientid)
IF  EXISTS (SELECT object_id FROM sys.views WHERE object_id = OBJECT_ID('testview'))
BEGIN
    DROP VIEW testview
END
exec (@Query)

go

Answer 2

Not sure that i would recommend this as a solution to any kind of problem but you could try something like this: (dynamic sql)

CREATE PROCEDURE testprocedure

@clientid float

as

DECLARE @sql varchar(8000) set @sql = ''

SET @sql = @sql + 'create view testview as '

SET @sql = @sql + 'select * from deliveries where clientid=@clientid '

SET @sql = @sql + cast(@clientid as varchar(50))

EXEC(@sql)