How to create view??

How can i create a view in a stored procedure?

Here's my sample code:

---------------------------------------
PROCEDURE sp_CreateView (@Model char(3)) AS

declare @ViewName char(6)

select @ViewName = 'vw_' + @Model

create view @ViewName AS
select * from Serial where substring(SerialNo,1,3) = @Model
---------------------------------------
it always prompt me a message 'CREATE VIEW must be the first command in a query batch.'
yyjulieAsked:
Who is Participating?
 
chigrikConnect With a Mentor Commented:
You can use dynamic SQL statement.
Read about EXEC statement from Books Online.
This is the example:

CREATE PROCEDURE sp_CreateView (@Model char(3)) AS
DECLARE @ViewName char(6),
        @exec_str varchar (255)
SELECT @ViewName = 'vw_' + @Model
SELECT @exec_str = 'CREATE VIEW ' + @ViewName + ' AS
       SELECT * FROM Serial WHERE substring(SerialNo,1,3) = ' + @Model
EXEC (@exec_str)
GO
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.