problem in automatically generating id when inserting values in database using stored procedure

Question

0.00/5 (No votes)

See more:

i have created a stored procedure for only inserting certain values . Everything is fine till now but i also want that a column name ID gets its values when something is entered in database i.e suppose i insert :

execute spuserdetails 'Rachit','01-15-1988',null,'1 x free zone',97545,null,null,null

than correspondingly a column that i dont want to fill by SP automatically fills itself like :
Rachit,1-15-1988,null,1 x free zone,97545,null,null,null,1 now i havenot inserted it but it gets generated .
What i have done so far is :
1. i have made my ID column as primary key.
2. rt. clicking on that ID column --> Column prop. --> Identity Specification --> isIdentity -->yes.

Butt on sending values via SP ID shows null.Why? what i m missing? Thanks in advance.

Posted 27-Jun-13 0:16am

Rambo_Raja

Updated 27-Jun-13 5:26am

RedDk

v2

Add a Solution

Comments

[no name] 27-Jun-13 6:34am

If you do not want it auto-generated then remove the IsIdentity->yes because that is what that does and that is what that is for.

Rambo_Raja 27-Jun-13 6:44am

no no ..i want column ID to auto generate .

gvprabu 27-Jun-13 7:30am

Hi You have to use @@Identity or SCOPE_IDENTITY() functions and assign the output variable

RedDk 27-Jun-13 11:27am

Let me ask about how that ID comes into being. Are you using IDENTITY(1,1)?

Rambo_Raja 28-Jun-13 1:49am

yes ...i m using it...and many of my programmer friends i think misinterpreted my question. again repeting i want a column to automatically take seed value (eg: 1) without passing that value from stored procedure. i have although created a column in my table named userdetails as 'ID' but from my ASP.NET web project i m not passing it to database it generates itself.

RedDk 28-Jun-13 12:37pm

Ok,

Do you see how long and tedious this conversation is going to become? Ten hours out-of-round. I didn't mean to shout yesterday. BUt that's IDENTITY(1,1). Did you happen to see my post down there under "Solution 3". What did you think?

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

gvprabu · Answer 1 · 2013-06-27T01:39:00

Hi,

Check the below Sample SP

SQL

CREATE PROCEDURE Test
@EmpID INT,
@Name VARCHAR(100),
@ID INT OUTPUT
AS
BEGIN
	-- Insert Statement
	-- Table Structure (ID,EmpID,Name) here Identity Column is "ID"
	INSERT INTO Table_Name (EmpID,Name) VALUES(@EmpID, @Name)
	-- Get last Inserted Identity Value
	SELECT @ID=SCOPE_IDENTITY()
END

DECLARE @ID INT
EXEC Test 12,'Test', @ID=@ID OUTPUT
SELECT @ID

Regards,
GVPRabu

KuttiSankar · Answer 2 · 2013-06-27T04:16:00

You can set the column Property by

Identification Specification -> (Expand it)
(Is Identity) = Yes

You can set the Increment Value here. By Default it is set to 1.

By changing this you need not to pass the Value for this column and it will be autogenerated.

RedDk · Answer 3 · 2013-06-27T05:47:00

I'll go ahead and do what I'd do given this as the only real information:

execute spuserdetails 'Rachit','01-15-1988',null,'1 x free zone',97545,null,null,null

Make the table for what's ready-to-hand.

USE [cpqaAnswers]
GO
CREATE TABLE [cpqa].[tbl_RS_UserDetails](
   [name][nvarchar](50),
      [date][datetime],
         [info_01][nvarchar](3),
             [location][nvarchar](13),
                [fivedigit][nvarchar](676),
                    [info_02][nvarchar](131),
                        [info_03][nvarchar](128),
                            [info_04][nvarchar](228)
    )

In the stored procedure, that I can't see, INSERT the data making sure that the target is this newly crreated table. Next make another table.

CREATE TABLE [cpqa].[tbl_RS_UserDetailsIdx](
  [idx][int]IDENTITY(1,1),
   [name][nvarchar](50),
      [date][datetime],
         [info_01][nvarchar](3),
             [location][nvarchar](13),
                [fivedigit][nvarchar](676),
                    [info_02][nvarchar](131),
                        [info_03][nvarchar](128),
                            [info_04][nvarchar](228)
    )

In an intermediate step somewhere, after the first table is filled with the records you want to INSERT, do the same thing with the second table. The indexing is automatic. If you try to put data in datatypes that mismatch you'll get an error.

problem in automatically generating id when inserting values in database using stored procedure

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0