August 26, 2009 at 5:52 am
I've written few stored procedures for retrieving data but how to write an insert query.
CREATE PROCEDURE [dbo].[SP_INSERT_Status]
@StIDPK INT,
@status varchar(10),
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO lk_Status(St_ID_PK,St_Name)
VALUES(@StIDPK, @status)
END
with this I can successfully insert a new record but my query is "when I run this Stored Proc I should be able to retrieve max(StIDPK) before execution and a new record should be added after execution"
some thing like...
Select Max(StIDPK) From lk_Status results in 5 entries.
my new record should be 6th entry after execution
August 26, 2009 at 9:55 am
I'm not sure I understand what you are asking. Do you want to retrieve the MAX(StIDPK) and then increment it before doing an insert? So if you have this structure and data:
DECLARE @test-2 TABLE(pk INT PRIMARY KEY, data VARCHAR(10))
INSERT INTO @test-2 (
pk,
data
)
SELECT
1,
'Test1'
UNION all
SELECT
2,
'Test2'
UNION all
SELECT
3,
'Test3'
UNION all
SELECT
4,
'Test4'
Your stored procedure will insert 5, 'Test5' into the table next?
If this is what you want then you should not need to pass in the @StIDPK parameter since you would be getting it in the procedure. Also if you have an incrementing key value, why not make it an IDENTITY column?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply