stored procedure - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

stored procedure

Author

Topic

programer
Posting Yak Master

221 Posts

Posted - 2012-01-03 : 05:59:06

ALTER PROCEDURE dbo.PaymentTypeInsert

@UserId uniqueidentifier OUTPUT,
@PaymentType nvarchar(50),
@IsDeleted bit = false,
@IsBlocked bit = false,
@PaymentTypeID int,
@AttributeName nvarchar(50),
@Value nvarchar(150)

AS

INSERT INTO dbo.tbl_PaymentType
(UserId,
PaymentType,
IsDeleted,
IsBlocked)
VALUES (@UserId,
@PaymentType,
@IsDeleted,
@IsBlocked)

INSERT INTO dbo.tbl_PaymentDetails
(PaymentTypeID,
AttributeName,
Value)
VALUES (@PaymentTypeID,
@AttributeName,
@Value)

How to insert tbl_PaymentType.PaymentType to the table tbl_PaymentDetails.PaymentTypeID with stored procedure?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-03 : 06:27:21

[code]
ALTER PROCEDURE dbo.PaymentTypeInsert

@UserId uniqueidentifier OUTPUT,
@PaymentType nvarchar(50),
@IsDeleted bit = false,
@IsBlocked bit = false,
@PaymentTypeID int,
@AttributeName nvarchar(50),
@Value nvarchar(150)

AS

DECLARE @PaymentTypeId int

INSERT INTO dbo.tbl_PaymentType
(UserId,
PaymentType,
IsDeleted,
IsBlocked)
VALUES (@UserId,
@PaymentType,
@IsDeleted,
@IsBlocked)

SET @PaymentTypeId = @@SCOPE_IDENTITY()

INSERT INTO dbo.tbl_PaymentDetails
(PaymentTypeID,
AttributeName,
Value)
VALUES (@PaymentTypeID,
@AttributeName,
@Value)

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

programer
Posting Yak Master

221 Posts

Posted - 2012-01-03 : 06:33:14

quote:

Originally posted by visakh16


ALTER PROCEDURE dbo.PaymentTypeInsert

@UserId uniqueidentifier OUTPUT,
@PaymentType nvarchar(50),
@IsDeleted bit = false,
@IsBlocked bit = false,
@PaymentTypeID int,
@AttributeName nvarchar(50),
@Value nvarchar(150)

AS

DECLARE @PaymentTypeId int

INSERT INTO dbo.tbl_PaymentType
(UserId,
PaymentType,
IsDeleted,
IsBlocked)
VALUES (@UserId,
@PaymentType,
@IsDeleted,
@IsBlocked)

SET @PaymentTypeId = @@SCOPE_IDENTITY()

INSERT INTO dbo.tbl_PaymentDetails
(PaymentTypeID,
AttributeName,
Value)
VALUES (@PaymentTypeID,
@AttributeName,
@Value)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Returns: Must declare the scalar variable "@@SCOPE_IDENTITY"

How to declare SCOPE_IDENTITY from the table tbl_PaymentType?

programer
Posting Yak Master

221 Posts

Posted - 2012-01-03 : 06:42:22

quote:
Originally posted by programer

quote:
Originally posted by visakh16

ALTER PROCEDURE dbo.PaymentTypeInsert

@UserId uniqueidentifier OUTPUT,
@PaymentType nvarchar(50),
@IsDeleted bit = false,
@IsBlocked bit = false,
@PaymentTypeID int,
@AttributeName nvarchar(50),
@Value nvarchar(150)

AS

DECLARE @PaymentTypeId int

INSERT INTO dbo.tbl_PaymentType
(UserId,
PaymentType,
IsDeleted,
IsBlocked)
VALUES (@UserId,
@PaymentType,
@IsDeleted,
@IsBlocked)

SET @PaymentTypeId = @@SCOPE_IDENTITY()

INSERT INTO dbo.tbl_PaymentDetails
(PaymentTypeID,
AttributeName,
Value)
VALUES (@PaymentTypeID,
@AttributeName,
@Value)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Returns: Must declare the scalar variable "@@SCOPE_IDENTITY"

How to declare SCOPE_IDENTITY from the table tbl_PaymentType?

BEGIN
INSERT INTO dbo.tbl_PaymentType
(UserId,
PaymentType,
IsDeleted,
IsBlocked)
VALUES (@UserId,
@PaymentType,
@IsDeleted,
@IsBlocked)

SET @PaymentTypeID = CAST(SCOPE_IDENTITY() AS INT)
END

Maybe is this ok?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-03 : 07:34:38

ah sorry you dont need @@...just SCOPE_IDENTITY() is enough

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources