Monday, March 19, 2012

Need an example of failed CREATE PROC...

This is an example of a working CREATE PROCEDURE.
USE Northwind
GO
CREATE PROC dbo.OverdueOrders
AS
SELECT *
FROM dbo.Orders
WHERE RequiredDate < GETDATE() AND ShippedDate IS Null
What does it mean that "a CREATE PROCEDURE statement cannot be combined with
other TSQL statements in a single batch?" I see a TSQL stmt in this working
one.
Would someone provide me an example, please?
Thanks.
USE Northwind
GO
SELECT *
FROM dbo.Orders
CREATE PROC dbo.OverdueOrders
AS
SELECT *
FROM dbo.Orders
WHERE RequiredDate < GETDATE() AND ShippedDate IS Null
GO
"light_wt" <lightwt@.discussions.microsoft.com> wrote in message
news:8E891A71-BE7D-495A-B134-CC3C1D1E2367@.microsoft.com...
> This is an example of a working CREATE PROCEDURE.
> USE Northwind
> GO
> CREATE PROC dbo.OverdueOrders
> AS
> SELECT *
> FROM dbo.Orders
> WHERE RequiredDate < GETDATE() AND ShippedDate IS Null
> What does it mean that "a CREATE PROCEDURE statement cannot be combined
with
> other TSQL statements in a single batch?" I see a TSQL stmt in this
working
> one.
> Would someone provide me an example, please?
> Thanks.
|||Here you go:
USE Northwind
GO
SELECT * FROM Orders
CREATE PROC dbo.OverdueOrders
AS
SELECT *
FROM dbo.Orders
WHERE RequiredDate < GETDATE()
AND ShippedDate IS NULL
GO
Anith
|||A batch is a set of TSQL statements that are submitted together for
processing. You designate the end of a batch of commands in SQL Server
using the GO statement.
There are only two CREATE statements that can be combined in a single batch.
CREATE DATBASE and CREATE TABLE.
All of the other CREATE statements must be submitted as separate batches.
So:
CREATE DATABASE Frog (....)
CREATE TABLE LilyPad (...)
CREATE TABLE Swim(...)
GO
Would work, but
CREATE TABLE LilyPad(...)
CREATE PROC Croak ...
Would not.
You would have to submit them as:
CREATE TABLE LilyPad...
GO
CREATE PROC Croak...
GO
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"light_wt" <lightwt@.discussions.microsoft.com> wrote in message
news:8E891A71-BE7D-495A-B134-CC3C1D1E2367@.microsoft.com...
> This is an example of a working CREATE PROCEDURE.
> USE Northwind
> GO
> CREATE PROC dbo.OverdueOrders
> AS
> SELECT *
> FROM dbo.Orders
> WHERE RequiredDate < GETDATE() AND ShippedDate IS Null
> What does it mean that "a CREATE PROCEDURE statement cannot be combined
with
> other TSQL statements in a single batch?" I see a TSQL stmt in this
working
> one.
> Would someone provide me an example, please?
> Thanks.
|||Thank you, all.
I appreciate your detailed and good explaination, Rick.
"Rick Sawtell" wrote:
<snip>

No comments:

Post a Comment