Return Key (identity) on INSERT operation (SQL/ASP)

[Tibor Karaszi]

Re: Return Key (identity) on INSERT operation (SQL/ASP)

> No matter what I do, the NOCOUNT lines disappear, and the[color=blue]
> QUOTED_IDENTIFI ER/ANSI_NULLS lines appear.[/color]

It is no surprise that the QUOTED_IDENTIFI ER and ANSI_NULLS commands
disappears as you set them *outside* the procedure code. These two are
"sticky parameters" meaning that the proc will have the setting you had in
the connection from where you *created* the proc.

All other options you need to set *inside* the procedure code.

--
Tibor Karaszi, SQL Server MVP

http://www.karaszi.com/sqlserver/default.asp

"CJM" <cjmwork@yahoo. co.uk> wrote in message
news:OVv3ASYEEH A.3336@TK2MSFTN GP12.phx.gbl...[color=blue]
> Tibor,
>
> Here's the full SP code:
>
> SET QUOTED_IDENTIFI ER ON
> GO
> SET ANSI_NULLS ON
> GO
>
> /****** Object: Stored Procedure dbo.Orders_Crea teOrder Script Date:
> 24/03/2004 09:41:39 ******/
>
> ALTER PROCEDURE Orders_CreateOr der
> @CustomerID int,
> @LocationID int,
> @CustOrderNo varchar(20),
> @OrderDate smalldatetime
> AS
>
> Insert into Orders (CustomerID, LocationID, CustOrderNo, OrderDate)
> Values (@CustomerID, @LocationID, @CustOrderNo, @OrderDate)
>
> Select Scope_Identity( ) as OrderID
>
> GO
>
> SET QUOTED_IDENTIFI ER OFF
> GO
> SET ANSI_NULLS ON
> GO
>
>
> No matter what I do, the NOCOUNT lines disappear, and the
> QUOTED_IDENTIFI ER/ANSI_NULLS lines appear. I went so far as deleting the[/color]
SP,[color=blue]
> and recreating it with SET NOCOUNT ON, but not including the other[/color]
settings,[color=blue]
> but when I look back they, everything is back to as it was...
>
> Help!
>
> Chris
>
>[/color]

[CJM]

Re: Return Key (identity) on INSERT operation (SQL/ASP)

Tibor,

Thanks...

After a few guesses, I've finally figured the correct syntax...!

All appears to be working now, though it is no thanks to Books Online, which
was as much use as a chocolate teapot!

Thanks again for you efforts and you prompt response...

Chris

[Bob Barrows [MVP]]

Re: Return Key (identity) on INSERT operation (SQL/ASP)

[Tom Moreau]

Re: Return Key (identity) on INSERT operation (SQL/ASP)

[Bob Barrows [MVP]]

Re: Return Key (identity) on INSERT operation (SQL/ASP)

[Tom Moreau]

Re: Return Key (identity) on INSERT operation (SQL/ASP)

[Bob Barrows [MVP]]

Re: Return Key (identity) on INSERT operation (SQL/ASP)

[Tom Moreau]

Re: Return Key (identity) on INSERT operation (SQL/ASP)

[Bob Barrows [MVP]]

Re: Return Key (identity) on INSERT operation (SQL/ASP)

[Tom Moreau]

Re: Return Key (identity) on INSERT operation (SQL/ASP)