question

Katie 1 avatar image
Katie 1 asked

What is the issue here in the code?

Guys I am learning to write procedures and, i am kind of stuck, trying to execute this.. I am not sure why is the error CREATE PROCEDURE CreateIdentity ( @AliasName varchar(50), @id int output ) AS BEGIN SELECT @id = ISNULL(max(IdentifiedObjectId),0) + 1 from A.IdentifiedObject INSERT INTO A.IdentifiedObject VALUES (@AliasName, '', '', '', @AliasName, '', @id, NULL) END GO DECLARE @id int EXEC CreateIdentity ENTITY, @id output print @id > ERROR:: Msg 8114, Level 16, State 5, > Procedure CreateIdentity, Line 0 > Error converting data type nvarchar to int.
sql-server-2008stored-procedures
5 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@Katie you should use an identity field instead of rolling your own
2 Likes 2 ·
Hi. What's the schema of the IdentifiedObject table?
0 Likes 0 ·
dbo.identifiedobject
0 Likes 0 ·
Sorry to bother i got the answer to the issue.. the order of the insert is wrong.
0 Likes 0 ·
Scott, i dont have any oppurtunity to add any other column.
0 Likes 0 ·

1 Answer

· Write an Answer
TimothyAWiseman avatar image
TimothyAWiseman answered
I suspect your IdentifiedObjectId column in A.Identifiedobject is an nvarchar column and contains at least one value that is not an integer. Just to see, I created some test entities like such: create schema A Go create table A.IdentifiedObject (col1 nvarchar(128), col2 nvarchar(128), col3 nvarchar(128), col4 nvarchar(128), col5 nvarchar(128), col6 nvarchar(128), IdentifiedObjectId int, col8 nvarchar(128) ) GO Once I had created those, your script ran without errors.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thank you so much Timothy, but i got the answer i was doing the insert in a wrong order. I really appreciate your help.Thank you.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.