DECLARE @IDENT INT

INSERT INTO BLEK.EstatesTable(City,Phone,Furn,Heating,TypeEstate,TypeOffr,TypeConstr)
VALUES(@CITY,@PHONE,@FURN,@HEATING,@TYPEESTATE,@TYPEOFFR,@TYPECONSTR)
SELECT @IDENT = SCOPE_IDENTITY()

-- You can insert all rows into Offertable at once like this:

INSERT INTO BLEK.OFFERTABLE(EstateID,UserID,Value,ParamID)
	SELECT @IDENT,@USERID,@ValuePrice,'price' UNION ALL
	SELECT @IDENT,@USERID,@ValueFloor,'floor' UNION ALL
	SELECT @IDENT,@USERID,@ValueArea,'area' UNION ALL
	SELECT @IDENT,@USERID,@ValueRooms,'room'

PRINT 'Create procedure prSVOFFRDATA'
GO
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[prSVOFFRDATA]') 
    AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.prSVOFFRDATA
END
GO
CREATE PROCEDURE dbo.prSVOFFRDATA
	@CITY		VARCHAR(20),
	@PHONE		CHAR(1),	-- Insert 'Y' or 'N'
	@FURN		CHAR(1),	-- Insert 'Y' or 'N'
	@HEATING	CHAR(1),	-- Insert 'Y' or 'N'
	@TYPEESTATE	CHAR(3),
	@TYPEOFFR	INT,
	@TYPECONSTR	CHAR(1),
	@USERID		INT,
	@ValuePrice	BIGINT,
	@ValueFloor	BIGINT,
	@ValueArea	BIGINT,
	@ValueRooms	BIGINT,
	@PICTURE	VARCHAR(50),
	@DESCRIPTION	VARCHAR(255)

AS
SET NOCOUNT ON
SET XACT_ABORT ON	-- Force this SProc to abort if runtime errors occur
DECLARE @IDENT		INT,
	@intErrNo	INT,
	@intRowCount	INT,
	@intFailPoint	INT	-- Indicates "where" any error occurred

SELECT	@intErrNo = 0	-- Assume no error

-- Tidy up data
SELECT	@PHONE = UPPER(@PHONE),
	@FURN = UPPER(@FURN),
	@HEATING = UPPER(@HEATING)

-- Validate data
IF @PHONE NOT IN ('Y', 'N')
BEGIN
	SELECT	@intFailPoint = -1,
		@intErrNo = -999
	GOTO prSVOFFRDATA_Exit
END

... other validation here ...

BEGIN TRANSACTION

INSERT INTO BLEK.EstatesTable(City, Phone, Furn, Heating, TypeEstate, 
	TypeOffr, TypeConstr)
	VALUES(@CITY, @PHONE, @FURN, @HEATING, @TYPEESTATE, @TYPEOFFR, @TYPECONSTR)
SELECT	@IDENT = SCOPE_IDENTITY(), @intErrNo = @@ERROR, @intRowCount = @@ROWCOUNT,
	@intFailPoint=1
IF @intErrNo <> 0 OR @intRowCount <> 1 GOTO prSVOFFRDATA_Abort

-- You can insert all rows into Offertable at once like this:

INSERT INTO BLEK.OFFERTABLE(EstateID, UserID, Value, ParamID)
		  SELECT @IDENT, @USERID, @ValuePrice, 'price' 
	UNION ALL SELECT @IDENT, @USERID, @ValueFloor, 'floor' 
	UNION ALL SELECT @IDENT, @USERID, @ValueArea, 'area' 
	UNION ALL SELECT @IDENT, @USERID, @ValueRooms, 'room'
SELECT @intErrNo = @@ERROR, @intRowCount = @@ROWCOUNT, @intFailPoint=2
IF @intErrNo <> 0 OR @intRowCount <> 1 GOTO prSVOFFRDATA_Abort

IF (@PICTURE IS NOT NULL)
BEGIN
INSERT INTO BLEK.PICTUREESTATETABLE(EstateID, PICTUREFILEPATH) 
	VALUES(@IDENT, @PICTURE)
SELECT @intErrNo = @@ERROR, @intRowCount = @@ROWCOUNT, @intFailPoint=3
IF @intErrNo <> 0 OR @intRowCount <> 1 GOTO prSVOFFRDATA_Abort
END
IF (@DESCRIPTION IS NOT NULL)
BEGIN
INSERT INTO BLEK.DSCRIPTIONESTATETABLE(EstateID, TextDescription) 
	VALUES(@IDENT, @DESCRIPTION)
SELECT @intErrNo = @@ERROR, @intRowCount = @@ROWCOUNT, @intFailPoint=4
IF @intErrNo <> 0 OR @intRowCount <> 1 GOTO prSVOFFRDATA_Abort
END

prSVOFFRDATA_Abort:
IF @intErrNo = 0
BEGIN
	COMMIT TRANSACTION
END
ELSE
BEGIN
	ROLLBACK TRANSACTION
END

prSVOFFRDATA_Exit:
IF @intErrNo = 0
BEGIN

--	Report error to User/Application some how 
--		[output variable / select list / LOG table / RAISERROR() ...]
	SELECT	'prSVOFFRDATA failed with error ['
		+ CONVERT(varchar(20), @intErrNo)
		+'] processing ['
		+ CONVERT(varchar(20), @intRowCount)
		+'] at FailPoint ['
		+ CONVERT(varchar(20), @intFailPoint)
		+']' AS ErrorMessage,
		@CITY		AS [@CITY],
		@PHONE		AS [@PHONE],
		@FURN		AS [@FURN],
		@HEATING	AS [@HEATING],
		@TYPEESTATE	AS [@TYPEESTATE],
		@TYPEOFFR	AS [@TYPEOFFR],
		@TYPECONSTR	AS [@TYPECONSTR],
		@USERID		AS [@USERID],
		@ValuePrice	AS [@ValuePrice],
		@ValueFloor	AS [@ValueFloor],
		@ValueArea	AS [@ValueArea],
		@ValueRooms	AS [@ValueRooms],
		@PICTURE	AS [@PICTURE],
		@DESCRIPTION	AS [@DESCRIPTION]
END
SET NOCOUNT OFF
RETURN @intErrNo	-- 0=No error
/* TEST RIG
BEGIN TRANSACTION
EXEC dbo.prSVOFFRDATA
	@CITY		= 'MyCity',
	@PHONE		'Y',
	@FURN		'Y',
	@HEATING	'N',
	@TYPEESTATE	'FOO',
	@TYPEOFFR	1,
	@TYPECONSTR	'X',
	@USERID		1234,
	@ValuePrice	11234567890,
	@ValueFloor	21234567890,
	@ValueArea	31234567890,
	@ValueRooms	41234567890,
	@PICTURE	'FooBar.JPG',
	@DESCRIPTION	'Des Res'
ROLLBACK

 */
--================== rk_SP_XX_ModuleDetail_Get ==================--
GO
IF exists (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[prSVOFFRDATA]') 
    AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	GRANT EXECUTE ON dbo.prSVOFFRDATA TO MyPermissionsRole
END
GO
PRINT 'Create procedure prSVOFFRDATA DONE'
GO