i want to have my primary key as out put , when i insert a record!

Question

Post reply

i want to have my primary key as out put , when i insert a record!

nazaninahmady_sh

Default port

Points: 1465
More actions
December 28, 2013 at 12:16 am

#286676

Hi
I have a store procedure, I want when I insert a record, I can have its id (Poll_ID, it is my primary key) as a output , would you please help me? here is my store procedure :
ALTER PROCEDURE dbo.InsertNewPoll
@varquestion nvarchar(500),
@Poll_I int
AS
begin
INSERT INTO Polls(Question, Active)
VALUES (@varquestion, 1)
select @Poll_ID=(Poll_ID) from Polls where Question=@varquestion
end
I always receive this message:
Procedure or function 'InsertNewPoll' expects parameter '@Poll_ID', which was not supplied.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

sql crazy kid Ten Centuries Points: 1388 More actions · Answer 1

You should define your input parameter as "@Poll_I int out".

Also the called application should have variable to set your output as like below

declare @Poll int

InsertNewPoll @varquestion ='TestQst',@Poll OUT

More details are here available

http://technet.microsoft.com/en-us/library/ms187926.aspx

LutzM SSC Guru Points: 107049 More actions · Answer 2

You might also want to have a look into the OUTPUT clause.

This would help to avoid the additional SELECT to get the related Poll_ID.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Christian Graus SSC Eights! Points: 963 More actions · Answer 3

You also don't need to look up the identity value that was inserted, you can get it using SCOPE_IDENTITY

http://technet.microsoft.com/en-us/library/ms190315.aspx covers it.

nazaninahmady_sh Default port Points: 1465 More actions · Answer 4

I really wish that our friends in this forum , FIRST READ CAREFULLY THE QUESTIONS AND THEN ANSWER.

LutzM SSC Guru Points: 107049 More actions · Answer 5

nazaninahmady_sh (12/28/2013)
I really wish that our friends in this forum , FIRST READ CAREFULLY THE QUESTIONS AND THEN ANSWER.

I herewith apologize to provide an additional information on how to improve the code you have beyond your question. It won't happen again.

Please do hesitate to ask again.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

sql crazy kid Ten Centuries Points: 1388 More actions · Answer 6

Structured Query Language has it's own coding standards, your question is beyond that.You should follow that,it would help you to solve your problem.

I suggest start with mssql library.

http://technet.microsoft.com/en-us/library

matak SSCrazy Points: 2674 More actions · Answer 7

You have a typo

@Poll_I int

Im pretty sure this should be @Poll_ID

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 8

nazaninahmady_sh (12/28/2013)
Hi
I have a store procedure, I want when I insert a record, I can have its id (Poll_ID, it is my primary key) as a output , would you please help me? here is my store procedure :
ALTER PROCEDURE dbo.InsertNewPoll
@varquestion nvarchar(500),
@Poll_I int
AS
begin
INSERT INTO Polls(Question, Active)
VALUES (@varquestion, 1)
select @Poll_ID=(Poll_ID) from Polls where Question=@varquestion
end
I always receive this message:
Procedure or function 'InsertNewPoll' expects parameter '@Poll_ID', which was not supplied.

nazaninahmady_sh (12/28/2013)
I really wish that our friends in this forum , FIRST READ CAREFULLY THE QUESTIONS AND THEN ANSWER.

I don't care how much of a pinch you may be in, you don't need to be rude. The folks that have replied to your post have done so in good faith and with excellent answers to your problem. SQL Crazy Kid correctly nailed your specific problem on the very first response post. You have to define the parameter as an OUT parameter to stop getting the error that you're receiving. The others have offered alternatives to the problem, some of which are actually MUCH better than your original attempt.

Considering the typo that you have in your original post and that people have kindly helped you on a question that a rank amateur could easily find the answer to in Books Online and the fact that the very first response to your post was actually correct and that you've apparently missed that fact, you'd be wise to take that nasty chip off of your shoulder and say "Thank you for the help."

Please drive through.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)