SCOPE_IDENTITY not erring, but not working either.

[travisbrown]

Trying to figure out what I'm doing wrong here. About half way down I'm trying to get the SCOPE_IDENTITY() from the last insert. No error, but won't run any line referencing the SCOPE_IDENTITY var.

This is simply a one-off process, so don't look at as an eventual sproc.

DECLARE @standard_name NVARCHAR(12);
DECLARE @standard_id NVARCHAR(3);
DECLARE @sSQL NVARCHAR(MAX);
DECLARE @counter AS INT;
DECLARE @totalrows AS INT;
DECLARE @last_parameter_id AS NVARCHAR(10);
SET @counter = 1;
SELECT @totalrows = COUNT(*) FROM [dbo].[standards];


BEGIN TRAN A;
PRINT 'Create Temp table for counting rows';
CREATE TABLE #tempstandards(standard_id INT, standard_group_id INT, standard_name NVARCHAR(12), medium INT, RowID INT);

INSERT INTO #tempstandards
SELECT standard_id, standard_group_id, standard_name, medium, ROW_NUMBER() OVER(ORDER BY standard_id) FROM [dbo].[standards];

WHILE (@counter <= @totalrows)
BEGIN

SELECT @standard_name = standard_name FROM #tempstandards WHERE RowID = @counter;
SELECT @standard_id = standard_id FROM #tempstandards WHERE RowID = @counter;

SET @sSQL = 'INSERT INTO [dbo].[parameter_limits]
SELECT  b.parameter_id, [' + @standard_name + ']
FROM [keystone_standards].[dbo].[CSR4510soil] A 
INNER JOIN dbo.parameters B ON B.parameter_name = A.parameter_id WHERE [' + @standard_name + '] IS NOT NULL';

PRINT 'Inserting parameter_limits for ' + @standard_name ;
EXEC(@sSQL);

-- BEGIN FAIL but no error message.

SELECT @last_parameter_id = SCOPE_IDENTITY();
PRINT 'Last parameter_limit_id:' +  @last_parameter_id
-- won't PRINT, won't err.

SET @sSQL = 'INSERT INTO [dbo].[standard_parameter_limits] 
SELECT ' + @standard_id + ', parameter_limit_id FROM [dbo].[parameter_limits] WHERE last_parameter_id > ' + @last_parameter_id;

-- If I comment out ' + @last_parameter_id; the dynamic query will run below. If not, no error, no PRINT. 

-- END FAIL

PRINT 'Inserting parameter limit index for ' + @standard_id;
EXEC(@sSQL);

--SELECT * FROM [dbo].[standard_parameter_limits]

SET @counter = @counter + 1  
END

DROP TABLE #tempstandards

ROLLBACK TRAN A

 

[travisbrown]

Curiously @@IDENTITY works. Probably safe in this case, but not sure why SCOPE_IDENTITY fails.

 

[SQLBill]

I'm not positive, but I think the issue is you aren't doing the INSERT within the scope of the stored procedure. You are creating a statement that is then run by EXEC(@sSQL). I think that EXEC(@sSQL) runs outside the scope of the stored procedure.

In other words, when you run this:

SET @sSQL = 'INSERT INTO [dbo].[parameter_limits]
SELECT b.parameter_id, [' + @standard_name + ']
FROM [keystone_standards].[dbo].[CSR4510soil] A
INNER JOIN dbo.parameters B ON B.parameter_name = A.parameter_id WHERE [' + @standard_name + '] IS NOT NULL';

PRINT 'Inserting parameter_limits for ' + @standard_name ;
EXEC(@sSQL);

The SET @sSQL is happening in one scope, but the EXEC(@sSQL) is happening in another scope. The check for SCOPE_IDENTITY is only good for the SET statement.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[travisbrown]

I suspected it might have something to do with running dynamic SQL and EXEC closing the scope. Your explanation seems to fit. And would explain why @@IDENTITY returns something.

I guess I could use MAX reasonably safely since it's not a transactional DB.

 

[gmmastros]

It probably didn't work because you ran it in an EXEC command.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gk53]

Why you usinf EXEC(@sSQL); why not strait insert statement like

if @standard_name IS NOT NULL;
INSERT INTO [dbo].[parameter_limits]
SELECT  b.parameter_id, @standard_name 
FROM [keystone_standards].[dbo].[CSR4510soil] A 
INNER JOIN dbo.parameters B 
[indent][/indent]ON B.parameter_name = A.parameter_id

 

[travisbrown]

Because @standard_name in the SELECT is a column name, not a value. Can't run dynamic SQL like that, can you?

 

[gk53]

something like that will give what you need

declare @maxNum nvarchar(50), @val int
declare @text varchar(50), @sql nvarchar(4000)

set @text = 'sometext'
SET @maxNum = '@val int output'


set @sql = 'INSERT INTO parameter_limits '
	+ 'SELECT  1, ''' + @text + '''' + '; select @val = @@IDENTITY;'


EXEC sp_executesql @sql , @maxNum ,@val OUT

select @val

 

[travisbrown]

Here was the winning/working solution using MAX. Looks like you can't use TOP in declaring a SELECT variable.

USE [keystone_standards]

DECLARE @standard_name NVARCHAR(12);
DECLARE @standard_id NVARCHAR(3);
DECLARE @sSQL NVARCHAR(MAX);
DECLARE @counter AS INT;
DECLARE @totalrows AS INT;
DECLARE @last_parameter_limit_id AS NVARCHAR(10);
SET @counter = 1;
SELECT @totalrows = COUNT(*) FROM [dbo].[standards];

SET NOCOUNT ON
BEGIN TRAN A;

CREATE TABLE #tempstandards(standard_id INT, standard_group_id INT, standard_name NVARCHAR(12), medium INT, RowID INT);

INSERT INTO #tempstandards
SELECT standard_id, standard_group_id, standard_name, medium, ROW_NUMBER() OVER(ORDER BY standard_id) FROM [dbo].[standards];

WHILE (@counter <= @totalrows)
BEGIN

SELECT @standard_name = standard_name FROM #tempstandards WHERE RowID = @counter;
SELECT @standard_id = standard_id FROM #tempstandards WHERE RowID = @counter;
SELECT @last_parameter_limit_id = COALESCE(MAX(parameter_limit_id),0) FROM [dbo].[parameter_limits];
PRINT 'MAX parameter_limit_id:' +  @last_parameter_limit_id

SET @sSQL = 'INSERT INTO [dbo].[parameter_limits]
SELECT  b.parameter_id, [' + @standard_name + '] FROM [dbo].[CSR4510soil] A 
INNER JOIN dbo.parameters B ON B.parameter_name = A.parameter_id WHERE [' + @standard_name + '] IS NOT NULL';
--PRINT @sSQL;
EXEC(@sSQL);

SET @sSQL = 'INSERT INTO [dbo].[standard_parameter_limits] 
SELECT ' + @standard_id + ', parameter_limit_id FROM [dbo].[parameter_limits] WHERE parameter_id > ' + @last_parameter_limit_id;
EXEC(@sSQL);

SET @counter = @counter + 1 
SELECT * FROM [dbo].[parameter_limits]
END


DROP TABLE #tempstandards
ROLLBACK TRAN A

SET NOCOUNT OFF

 

[travisbrown]

Hmm. Not sure what's going on here.

The second query here it printing out the correct queries, but seems like only the first iteration is being executed.

SET @sSQL = 'INSERT INTO [dbo].[standard_parameter_limits] 
SELECT ' + @standard_id + ', parameter_limit_id FROM [dbo].[parameter_limits] WHERE parameter_id > ' + @last_parameter_limit_id + ';';
PRINT @sSQL;
EXEC(@sSQL);

PRINT OUTPUT

INSERT INTO [dbo].[standard_parameter_limits] 
SELECT 43, parameter_limit_id FROM [dbo].[parameter_limits] WHERE parameter_id > 0;
INSERT INTO [dbo].[standard_parameter_limits] 
SELECT 44, parameter_limit_id FROM [dbo].[parameter_limits] WHERE parameter_id > 458777;
INSERT INTO [dbo].[standard_parameter_limits] 
SELECT 45, parameter_limit_id FROM [dbo].[parameter_limits] WHERE parameter_id > 459348;
INSERT INTO [dbo].[standard_parameter_limits] 
SELECT 46, parameter_limit_id FROM [dbo].[parameter_limits] WHERE parameter_id > 459919;
INSERT INTO [dbo].[standard_parameter_limits] 
SELECT 47, parameter_limit_id FROM [dbo].[parameter_limits] WHERE parameter_id > 460489;

But the final select to confirm just shows results for 43. The > filters line up properly with the insert IDs if the preceding. Am I missing something obvious in the logic?

USE [keystone_standards]

DECLARE @standard_name NVARCHAR(12);
DECLARE @standard_id NVARCHAR(3);
DECLARE @sSQL NVARCHAR(MAX);
DECLARE @counter AS INT;
DECLARE @totalrows AS INT;
DECLARE @last_parameter_limit_id AS NVARCHAR(10);
SET @counter = 1;
SELECT @totalrows = COUNT(*) FROM [dbo].[standards];

SET NOCOUNT ON
BEGIN TRAN A;

CREATE TABLE #tempstandards(standard_id INT, standard_group_id INT, standard_name NVARCHAR(12), medium INT, RowID INT);

INSERT INTO #tempstandards
SELECT standard_id, standard_group_id, standard_name, medium, ROW_NUMBER() OVER(ORDER BY standard_id) FROM [dbo].[standards];

WHILE (@counter <= @totalrows)
BEGIN

SELECT @standard_name = standard_name FROM #tempstandards WHERE RowID = @counter;
SELECT @standard_id = standard_id FROM #tempstandards WHERE RowID = @counter;
SELECT @last_parameter_limit_id = COALESCE(MAX(parameter_limit_id),0) FROM [dbo].[parameter_limits];

SET @sSQL = 'INSERT INTO [dbo].[parameter_limits]
SELECT  b.parameter_id, [' + @standard_name + '] FROM [dbo].[CSR4510soil] A 
INNER JOIN dbo.parameters B ON B.parameter_name = A.parameter_id WHERE [' + @standard_name + '] IS NOT NULL';
--PRINT @sSQL;
EXEC(@sSQL);

SET @sSQL = 'INSERT INTO [dbo].[standard_parameter_limits] 
SELECT ' + @standard_id + ', parameter_limit_id FROM [dbo].[parameter_limits] WHERE parameter_id > ' + @last_parameter_limit_id + ';';
PRINT @sSQL;
EXEC(@sSQL);

SET @counter = @counter + 1 

END

SELECT COUNT(*) FROM  [dbo].[parameter_limits];
SELECT DISTINCT standard_id FROM [dbo].[standard_parameter_limits];

DROP TABLE #tempstandards
ROLLBACK TRAN A

SET NOCOUNT OFF

 

[travisbrown]

And running a trace shows the sequential queries executing.

 

[gk53]

It looks like
SELECT 44, parameter_limit_id FROM [dbo].[parameter_limits] WHERE parameter_id > 458777;
return nothing....

 

[travisbrown]

Indeed. I wonder how this works. MAX returns the same value before and after the INSERT. The second MAX should be 571 higher. parameter_limit_id is an ID column.

SELECT COALESCE(MAX(parameter_limit_id),0) FROM [dbo].[parameter_limits];
SET @sSQL = 'INSERT INTO [dbo].[parameter_limits]
SELECT  b.parameter_id, [' + @standard_name + '] FROM [dbo].[CSR4510soil] A 
INNER JOIN dbo.parameters B ON B.parameter_name = A.parameter_id WHERE [' + @standard_name + '] IS NOT NULL;';
EXEC(@sSQL);
SELECT COALESCE(MAX(parameter_limit_id),0) FROM [dbo].[parameter_limits];