Prev: ALTER DATABASE [...] SET COMPATIBILITY_LEVEL = 100 ---- giving err
Next: UPDATE in UDF
From: jonpb on 2 Jun 2010 13:09
Hi,

Taken from the example here:
http://msdn.microsoft.com/en-us/library/ks9f57t0(VS.71).aspx

I have this procedure defined:
----------------------------------
create procedure sp_insert_get_id
@sql varchar(3000),
@identity int output
as
execute (@sql);
set @identity = scope_identity();
go
----------------------------------

then, if I run this script:
----------------------------------
declare @sql varchar(3000);
declare @identity int;

set @sql = N'insert into [dwOptions2D] ([DoBevels]) values (1)';
set @identity = -1;

exec dbo.sp_insert_get_id @sql, @identity;

print @identity;
go
----------------------------------

-1 is printed to the screen, even though the insert was successful

I can't see what is wrong with the code, thanks.
From: Jeroen Mostert on 2 Jun 2010 15:40
On 2010-06-02 19:09, jonpb wrote:
> Hi,
>
> Taken from the example here:
> http://msdn.microsoft.com/en-us/library/ks9f57t0(VS.71).aspx
>
> I have this procedure defined:
> ----------------------------------
> create procedure sp_insert_get_id
> @sql varchar(3000),
> @identity int output
> as
> execute (@sql);
> set @identity = scope_identity();
> go
> ----------------------------------
>
> then, if I run this script:
> ----------------------------------
> declare @sql varchar(3000);
> declare @identity int;
>
> set @sql = N'insert into [dwOptions2D] ([DoBevels]) values (1)';
> set @identity = -1;
>
> exec dbo.sp_insert_get_id @sql, @identity;
>
First of all, you need to specify OUTPUT in the call to the stored procedure
as well, otherwise it's only passed as input. So

exec dbo.sp_insert_get_id @sql = @sql, @identity = @identity OUTPUT;

This may be slightly confusing initially; just remember that the left-hand
side is the stored procedure formal argument and the right-hand side is your
actual value (which may be a variable that may have the same name as the
argument).

If you now execute your script, you will find that @identity is NULL on
returning from the stored procedure. This is because the EXECUTE statement
begins a new scope, so there is no SCOPE_IDENTITY() value in the stored
procedure. If you need to get an identity from a dynamically executed
statement, the SCOPE_IDENTITY() select has to be part of that statement:

create procedure #sp_insert_get_id
@sql nvarchar(3000),
@identity int output
as
set @sql = @sql + N';select @identity = scope_identity()';
exec sp_executesql @sql, @parameters = N'@identity int output',
@identity = @identity output;
go

Of course, this use of dynamic SQL is horrible and convoluted and not what
you should be doing in production code, it's just to illustrate the point.

--
J.
From: jonpb on 2 Jun 2010 18:49
On 02/06/2010 12:40 PM, Jeroen Mostert wrote:
> If you now execute your script, you will find that @identity is NULL on
> returning from the stored procedure. This is because the EXECUTE
> statement begins a new scope, so there is no SCOPE_IDENTITY() value in
> the stored procedure.

Thanks, that makes sense, so I changed the procedure definition to:

create procedure sp_insert_get_id
@table varchar(50),
@fields varchar(1000),
@values varchar(5000),
@identity int output
as
insert into @table (@fields) values (@values);
set @identity = scope_identity();
go

Now I get the error:
Incorrect syntax near '@fields'

And again, I can't tell from looking at the code why is doesn't work.

From: Jeroen Mostert on 3 Jun 2010 00:42
On 2010-06-03 0:49, jonpb wrote:
> On 02/06/2010 12:40 PM, Jeroen Mostert wrote:
>> If you now execute your script, you will find that @identity is NULL on
>> returning from the stored procedure. This is because the EXECUTE
>> statement begins a new scope, so there is no SCOPE_IDENTITY() value in
>> the stored procedure.
>
> Thanks, that makes sense, so I changed the procedure definition to:
>
> create procedure sp_insert_get_id
> @table varchar(50),
> @fields varchar(1000),
> @values varchar(5000),
> @identity int output
> as
> insert into @table (@fields) values (@values);
> set @identity = scope_identity();
> go
>
> Now I get the error:
> Incorrect syntax near '@fields'
>
> And again, I can't tell from looking at the code why is doesn't work.
>
Because @table, @fields and @values all cannot be variables. T-SQL can't
construct dynamic statements that way.

If I understand you correctly, you want to be able to execute any statement
and get the identity value it produced. Why? Why can't you simply write the
SCOPE_IDENTITY() assignment after the statement itself? There seems to be
little value in a stored procedure for this.

You *can* do this, but not without dynamic SQL of the form I demonstrated
earlier. If you are going to dynamic SQL route, see
http://www.sommarskog.se/dynamic_sql.html for a thorough treatise.

In this particular case, if replication and triggers are not an issue, you
may want to use @@IDENTITY instead. Although it's recommended to use
SCOPE_IDENTITY(), @@IDENTITY is the last identity value generated by any
statement, regardless of scope, so it can be used if you can't or don't want
to modify the original statement executed in its original scope.

--
J.
From: jonpb on 3 Jun 2010 14:53
On 02/06/2010 9:42 PM, Jeroen Mostert wrote:
> If I understand you correctly, you want to be able to execute any
> statement and get the identity value it produced. Why? Why can't you
> simply write the SCOPE_IDENTITY() assignment after the statement itself?
> There seems to be little value in a stored procedure for this.

Because I want a generic way of doing this and it was my understanding
that it needed to be in a stored procedure to make the "transaction" atomic.

> You *can* do this, but not without dynamic SQL of the form I
> demonstrated earlier. If you are going to dynamic SQL route, see
> http://www.sommarskog.se/dynamic_sql.html for a thorough treatise.

Thanks very much for the link, I obviously have fair bit to learn about
how dynamic sql works, why it works they it does.

I may actually use what you call "horrible and convoluted and not suited
for production code" because my concern is not so much an ugly stored
procedure but a simple client interface.

Thanks again Jeroen
 |  Next  |  Last
Pages: 1 2
Prev: ALTER DATABASE [...] SET COMPATIBILITY_LEVEL = 100 ---- giving err
Next: UPDATE in UDF