Stored Procedure Script

22 Dec 2005 1:04 AM

Bil Click

I would like to run a script that takes some variables and creates a view and
stored procedure. I keep getting an error saying "'create view' must be the
first statement in a query batch". What is the correct way to do this?

DECLARE @custview varchar(50),
@custID varchar(10)
SET @custview = 'testview'
SET @custID = 'TEST1234'
CREATE VIEW @custview AS
SELECT * from sometable
WHERE (sometable.cust = '@custID')

Happy Holidays!

22 Dec 2005 1:10 AM

ML

You'll have to use dynamic SQL for that. IMHO there rarely comes an occasion
when SQL objects need to be created on-the-fly like this.

Look up sp_executesql in Books Online.

ML

---
http://milambda.blogspot.com/

22 Dec 2005 1:20 AM

Bil Click

Thanks for the reply, I will check BOL. I am using this to automate the setup
of required sprocs, views & tables for new customers.

BC

Show quote

"ML" wrote:

> You'll have to use dynamic SQL for that. IMHO there rarely comes an occasion
> when SQL objects need to be created on-the-fly like this.
>
> Look up sp_executesql in Books Online.
>
>
> ML
>
> ---
> http://milambda.blogspot.com/

22 Dec 2005 3:14 PM

Aaron Bertrand [SQL Server MVP]

> Thanks for the reply, I will check BOL. I am using this to automate the
> setup
> of required sprocs, views & tables for new customers.

If these are the only databases you are creating on this server going
forward, put your objects in model. Then they will be included in any new
databases automatically.

Or, store your scripts in a .sql file, and run it whenever you create a new
database.

Putting these object scripts in stored procedures will make it very tough to
read, will complicate any queries that have strings (you'll have to double
any instance of '), and I'm sure you will eventually want to use source
control for your code.

A

22 Dec 2005 4:27 PM

Bil Click

This is an ongoing process for each new client. Database is the same, but
each new client requires a number of views, tables & sprocs, all of which are
identical except for the custID and object name.

Many thanks to all responders - BC

Show quote

"Aaron Bertrand [SQL Server MVP]" wrote:

> > Thanks for the reply, I will check BOL. I am using this to automate the
> > setup
> > of required sprocs, views & tables for new customers.
>
> If these are the only databases you are creating on this server going
> forward, put your objects in model. Then they will be included in any new
> databases automatically.
>
> Or, store your scripts in a .sql file, and run it whenever you create a new
> database.
>
> Putting these object scripts in stored procedures will make it very tough to
> read, will complicate any queries that have strings (you'll have to double
> any instance of '), and I'm sure you will eventually want to use source
> control for your code.
>
> A
>
>
>

22 Dec 2005 1:16 AM

Stijn Verrept

Bil Click wrote:

> I would like to run a script that takes some variables and creates a
> view and stored procedure. I keep getting an error saying "'create
> view' must be the first statement in a query batch". What is the
> correct way to do this?

This procedure will do it:

CREATE PROCEDURE dbo.CreateView(@custview nvarchar(40), @custID
nvarchar(40)) AS
declare @sql nvarchar(400)
set @sql = 'CREATE VIEW ' + @custview + ' AS SELECT * from sometable
WHERE (sometable.cust = ' + @custID + ')'

exec (@sql)
GO

--
HTH,

Stijn Verrept.

26 Dec 2005 4:56 PM

Joe from WI

If this is an established system, it may be too late for this but you can
have identical objects with different owners in a single database and have
each user or customer sign on with a different SQL Server login name.

custA.Customers
custB.Customers
custC.Customers
dbo.Customers <--your template customers table

Code would access Customers. SQL Server security would automatically
resolve the name to custA.Customers when customer A was logged in.

Or, you could have a single Customers table with a customer id column to
logically split the data between the different customers.

Just a thought,
Joe

Show quote

"Bil Click" wrote:

> I would like to run a script that takes some variables and creates a view and
> stored procedure. I keep getting an error saying "'create view' must be the
> first statement in a query batch". What is the correct way to do this?
>
> DECLARE @custview varchar(50),
> @custID varchar(10)
> SET @custview = 'testview'
> SET @custID = 'TEST1234'
> CREATE VIEW @custview AS
> SELECT * from sometable
> WHERE (sometable.cust = '@custID')
>
> Happy Holidays!
>