Problem with table variables and identity column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Problem with table variables and identity column

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-03-10 : 08:49:58

Michele writes "I'm trying to create a table variable with an identity column that populates upon insert. For what I have read SET IDENTITY_INSERT @myTable ON is required but, when included, this line causes errors in my stored procedure. When I take it out of the stored procedure, it reports that that line is required. I've supplied my code below. Any help would be SO MUCH appreciated!

CREATE PROCEDURE ListUsers
@userid int,
@domainID varchar(200)

AS
SET NOCOUNT ON

DECLARE @myTable TABLE (
table_id int IDENTITY(1,1) NOT NULL, ,profileID int,
displayName nvarchar(50), firstname varchar(50),
lastname varchar(50),email varchar(255), admin bit,
moderator bit
)

SET IDENTITY_INSERT @myTable ON

INSERT INTO @myTable
SELECT
p.profileid
, p.DisplayName
, p.firstname
, p.lastname
, p.email
, pr.admin
, pr.moderator
FROM UP_Profiles p WITH (nolock)
LEFT JOIN profile_relationships pr WITH (nolock) ON pr.profileID = p.profileID
WHERE
pr.userid= @userid
AND pr.owner <> 1
AND p.profileID NOT IN
(SELECT profileID FROM up_banned WITH (nolock) WHERE domainid = @domainID)
ORDER BY DisplayName ASC

GO"

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2006-03-10 : 08:54:14

An identity automatically populates by default. SET IDENTITY_INSERT ON allows you to manually populate the identity field.

Take SET IDENTITY_INSERT @myTable ON out of your procedure.

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2006-03-10 : 08:55:13

Why not try it by specifying the columns that you want to insert into in a column list. That way you just ignore the identity column and let it take care of itself.

eg.
CREATE PROCEDURE ListUsers
@userid int,
@domainID varchar(200)

AS
SET NOCOUNT ON

DECLARE @myTable TABLE (
table_id int IDENTITY(1,1) NOT NULL, profileID int,
displayName nvarchar(50), firstname varchar(50),
lastname varchar(50),email varchar(255), admin bit,
moderator bit
)

INSERT INTO @myTable
(
profileid
, DisplayName
, firstname
, lastname
, email
, admin
, moderator

)
SELECT
p.profileid
, p.DisplayName
, p.firstname
, p.lastname
, p.email
, pr.admin
, pr.moderator
FROM UP_Profiles p WITH (nolock)
LEFT JOIN profile_relationships pr WITH (nolock) ON pr.profileID = p.profileID
WHERE
pr.userid= @userid
AND pr.owner <> 1
AND p.profileID NOT IN
(SELECT profileID FROM up_banned WITH (nolock) WHERE domainid = @domainID)
ORDER BY DisplayName ASC

GO"

Duane.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources