August 30, 2010 at 4:46 am
Hi
I have a table CurrencyAccount
CREATE TABLE CurrencyAccount
(
CurrencyAccountID int PK
CurrencyCode int,
CurrencyExponent varchar,
CurrencySign varchar
)
i need a SP for table CurrencyAmount
while passing the 3 fields CurrencyCode,CurrencyExponent,CurrencySign
it should check if the row exists and if not insert a row,
it should return the ID in both case..
thank u
August 30, 2010 at 5:47 am
this is fairly simple; are you familiar with the IF EXISTS / IF NOT EXISTS syntax?
I was going to post the whole exmaple proc, but the question struck me as a little too much like homework....so I'll give a hint and ask what have you tried so far?
IF NOT EXISTS(SELECT *
FROM CurrencyAccount
WHERE CurrencyCode = @CurrencyCode
AND CurrencyExponent = @CurrencyExponent
AND CurrencySign = @CurrencySign
)
BEGIN
--create it, it did not exist
--now select it back
END
ELSE
BEGIN
--it already exists, so select it back
END
Lowell
August 30, 2010 at 6:40 am
thanks for replying
but i solve the problem this way
CREATE PROCEDURE [dbo].[CurrencyAmountIns]
(
@CurrencyAmountID int OUTPUT ,
@CurrencyCode CurrencyCodeType,
@CurrencyExponent CurrencyExponentType,
@CurrencySign SignCodeType
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @CurrencyAmountID2 int
IF (@CurrencyCode IS NULL) OR (@CurrencyExponent IS NULL) OR (@CurrencySign IS NULL)
BEGIN
RAISERROR('Invalid Procedure arguments, procedure failed to execute',16,1)
Return -1
END
SELECT @CurrencyAmountID2 = CurrencyAmountID
FROM dbo.CurrencyAmount
WHERE (CurrencyCode = @CurrencyCode) AND (CurrencyExponent = @CurrencyExponent) AND (CurrencySign = @CurrencySign)
IF (@CurrencyAmountID2 IS NULL)
BEGIN
-- No matching records in the table
INSERT INTO dbo.CurrencyAmount
(
CurrencyCode,
CurrencyExponent,
CurrencySign
)
VALUES
(
@CurrencyCode,
@CurrencyExponent,
@CurrencySign
)
SET @CurrencyAmountID = @@IDENTITY
END
ELSE
BEGIN
SET @CurrencyAmountID = @CurrencyAmountID2
END
END
August 30, 2010 at 7:20 am
Don't use @@IDENTITY for this. It could cause the wrong number to be returned if certain things happen to the database in the future. Use SCOPE_IDENTITY() instead. Look it up in Books Online.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 30, 2010 at 7:23 am
At least use
SET @CurrencyAmountID = SCOPE_IDENTITY()
instead of
SET @CurrencyAmountID = @@IDENTITY
@@IDENTITY will give you the latest created identity value in the database, no matter which table or user created it. In most cases it will be you but it is not guaranteed.
Since you are using SQL Server 2008, why aren't you using MERGE command?
N 56°04'39.16"
E 12°55'05.25"
August 30, 2010 at 7:54 am
thanks 4 ur valuable input...
August 30, 2010 at 7:20 pm
SwePeso (8/30/2010)
At least useSET @CurrencyAmountID = SCOPE_IDENTITY()
instead of
SET @CurrencyAmountID = @@IDENTITY
@@IDENTITY will give you the latest created identity value in the database, no matter which table or user created it. In most cases it will be you but it is not guaranteed.
Since you are using SQL Server 2008, why aren't you using MERGE command?
Ummm... Not quite true. It will always give you the latest identity from a table that your connection inserted to... it just may not be the table you expected especially if triggers that write to other tables are present. It does NOT give you the latest created identity value in the database (or, at least BOL doesn't say so).
--Jeff Moden
Change is inevitable... Change for the better is not.
September 2, 2010 at 12:02 am
Hi,
With both solutions above, there is the possibility of interleaved access. If not run in serializable transactions, one process could run the first query, then a second process could also run the first query, then both try to run the INSERT. One of the two INSERTS will fail on a primary key violation. To avoid this without the concurrency penalty of serializable transactions, you need to do the check, the modification and the key value retrieval in a single, atomic instruction.
Also, the table does not have the identity property on the CurrencyAccountID column, so one needs to explicitly set the column.
By using the MERGE statement (this is a SQL Server 2008 forum), a single, atomic statement solution can be achieved. My MERGE statement is pretty much a straight copy out of SQL 2008 Books Online (April 2009). I just added the “[font="Courier New"](SELECT ISNULL(MAX(CurrencyAccountID), 0) + 1 FROM dbo.CurrencyAccount)[/font]” subquery as an INSERT VALUE to generate CurrencyAccountID values for new records (including the first record added, when “[font="Courier New"]MAX(CurrencyAccountID)[/font]” would return NULL).
My script’s output looks better when run in “Results to Text” mode in an SSMS query window.
- Dan
CREATE TABLE dbo.CurrencyAccount (
CurrencyAccountID int PRIMARY KEY,
CurrencyCode int,
CurrencyExponent varchar,
CurrencySign varchar
);
GO
CREATE PROCEDURE dbo.p_AddCurrencyAccount
@CurrencyAccountID int OUTPUT,
@CurrencyCode int,
@CurrencyExponent varchar,
@CurrencySign varchar
AS
declare @ca table (CurrencyAccountID int);
MERGE dbo.CurrencyAccount target
USING (SELECT @CurrencyCode, @CurrencyExponent, @CurrencySign)
AS source (CurrencyCode, CurrencyExponent, CurrencySign)
ON (
target.CurrencyCode = source.CurrencyCode
AND target.CurrencyExponent = source.CurrencyExponent
AND target.CurrencySign = source.CurrencySign
)
WHEN MATCHED THEN
-- dummy SET to existing value just to generate "inserted" data
UPDATE SET CurrencyCode = target.CurrencyCode
WHEN NOT MATCHED THEN
INSERT (
CurrencyAccountID,
CurrencyCode, CurrencyExponent, CurrencySign
) VALUES (
(SELECT ISNULL(MAX(CurrencyAccountID), 0) + 1 FROM dbo.CurrencyAccount),
@CurrencyCode, @CurrencyExponent, @CurrencySign
)
OUTPUT inserted.CurrencyAccountID INTO @ca
;
SELECT @CurrencyAccountID = CurrencyAccountID from @ca;
GO
DECLARE
@CurrencyAccountID int,
@CurrencyCode int,
@CurrencyExponent varchar,
@CurrencySign varchar
;
-- EXEC 1: Show first row ISNULL works
SELECT @CurrencyCode = 1, @CurrencyExponent = 'A', @CurrencySign = '$';
EXEC dbo.p_AddCurrencyAccount
@CurrencyAccountID = @CurrencyAccountID OUTPUT,
@CurrencyCode = @CurrencyCode,
@CurrencyExponent = @CurrencyExponent,
@CurrencySign = @CurrencySign
;
RAISERROR(N'
EXEC 1 @CurrencyAccountID result: %d', 10, 1, @CurrencyAccountID);
-- EXEC 2: Show same data gets same CurrencyAccountID
EXEC dbo.p_AddCurrencyAccount
@CurrencyAccountID = @CurrencyAccountID OUTPUT,
@CurrencyCode = @CurrencyCode,
@CurrencyExponent = @CurrencyExponent,
@CurrencySign = @CurrencySign
;
RAISERROR(N'
EXEC 2 @CurrencyAccountID result: %d', 10, 1, @CurrencyAccountID);
-- EXEC 3: Try new data
SELECT @CurrencyCode = 1, @CurrencyExponent = 'B', @CurrencySign = '$';
EXEC dbo.p_AddCurrencyAccount
@CurrencyAccountID = @CurrencyAccountID OUTPUT,
@CurrencyCode = @CurrencyCode,
@CurrencyExponent = @CurrencyExponent,
@CurrencySign = @CurrencySign
;
RAISERROR(N'
EXEC 3 @CurrencyAccountID result: %d', 10, 1, @CurrencyAccountID);
GO
-- See we have two rows, A & B
PRINT '' -- generate a blank line
SELECT * FROM dbo.CurrencyAccount;
GO
DROP TABLE dbo.CurrencyAccount;
DROP PROCEDURE dbo.p_AddCurrencyAccount
GO
Sincerely,
Daniel
September 2, 2010 at 12:35 am
Actually, after thinking a little more, I think this use of “[font="Courier New"]UNIQUE (CurrencyCode, CurrencyExponent, CurrencySign) WITH (IGNORE_DUP_KEY = ON)[/font]” is a perfect match for your business rules, and it leads to a simple stored procedure with no multi-threading issues. If your application requires consecutive CurrencyAccountID values, this will not work. Otherwise, it’s pretty darn solid.
- Dan
CREATE TABLE dbo.CurrencyAccount (
CurrencyAccountID int IDENTITY(1,1) PRIMARY KEY,
CurrencyCode int,
CurrencyExponent varchar,
CurrencySign varchar,
UNIQUE (CurrencyCode, CurrencyExponent, CurrencySign) WITH (IGNORE_DUP_KEY = ON)
);
GO
CREATE PROCEDURE dbo.p_AddCurrencyAccount
@CurrencyAccountID int OUTPUT,
@CurrencyCode int,
@CurrencyExponent varchar,
@CurrencySign varchar
AS
INSERT CurrencyAccount (
CurrencyCode, CurrencyExponent, CurrencySign
) VALUES (
@CurrencyCode, @CurrencyExponent, @CurrencySign
);
SELECT @CurrencyAccountID = CurrencyAccountID
FROM dbo.CurrencyAccount
WHERE CurrencyCode = @CurrencyCode
AND CurrencyExponent = @CurrencyExponent
AND CurrencySign = @CurrencySign
;
GO
DECLARE
@CurrencyAccountID int,
@CurrencyCode int,
@CurrencyExponent varchar,
@CurrencySign varchar
;
-- EXEC 1: Show first row works
SELECT @CurrencyCode = 1, @CurrencyExponent = 'A', @CurrencySign = '$';
EXEC dbo.p_AddCurrencyAccount
@CurrencyAccountID = @CurrencyAccountID OUTPUT,
@CurrencyCode = @CurrencyCode,
@CurrencyExponent = @CurrencyExponent,
@CurrencySign = @CurrencySign
;
RAISERROR(N'
EXEC 1 @CurrencyAccountID result: %d', 10, 1, @CurrencyAccountID);
-- EXEC 2: Show same data gets same CurrencyAccountID
EXEC dbo.p_AddCurrencyAccount
@CurrencyAccountID = @CurrencyAccountID OUTPUT,
@CurrencyCode = @CurrencyCode,
@CurrencyExponent = @CurrencyExponent,
@CurrencySign = @CurrencySign
;
RAISERROR(N'
EXEC 2 @CurrencyAccountID result: %d', 10, 1, @CurrencyAccountID);
-- EXEC 3: Try new data
SELECT @CurrencyCode = 1, @CurrencyExponent = 'B', @CurrencySign = '$';
EXEC dbo.p_AddCurrencyAccount
@CurrencyAccountID = @CurrencyAccountID OUTPUT,
@CurrencyCode = @CurrencyCode,
@CurrencyExponent = @CurrencyExponent,
@CurrencySign = @CurrencySign
;
RAISERROR(N'
EXEC 3 @CurrencyAccountID result: %d', 10, 1, @CurrencyAccountID);
GO
-- See we have two rows, A & B
PRINT ''; -- generate a blank line
SELECT * FROM dbo.CurrencyAccount;
GO
DROP TABLE dbo.CurrencyAccount;
DROP PROCEDURE dbo.p_AddCurrencyAccount;
GO
Sincerely,
Daniel
September 2, 2010 at 2:52 am
thanks dan..i like the 2nd option
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply