Pls help with this simple query

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

Transact-SQL (2000)

Pls help with this simple query

Author

Topic

superhero
Yak Posting Veteran

52 Posts

Posted - 2007-06-04 : 04:09:22

I have this query:

INSERT INTO Containers
(containercode)
SELECT '01021018' AS TheContainerCode
WHERE (NOT EXISTS
(SELECT containercode
FROM containers
WHERE containercode = '01021018'))

It basically checks if a container code exists, and if not, it inserts it into the containers table. How will I know, in VB.net whether the container code entered was infact a unique number or a duplicate already in the database? When I execute the query in SQL it just tells me "1 row(s) affected" for a successful insert (if it's a unique container code) or "query executed ok" (if it's a duplicate container code and nothing was inserted into the database). How can I get something physical, like a code or something from the database that I can "see" in VB and interpret?

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2007-06-04 : 04:15:05

Put this in a stored procedure and use a return code to indicate to your application whether or not the ContainerCode already exists. Since you'd have to test for this following the insert anyway, I'd be tempted to split it into two statements, testing for existence of the ContainerCode upfront, and conditionally executing the insert statement if required.

Mark

superhero
Yak Posting Veteran

52 Posts

Posted - 2007-06-04 : 05:00:08

Sorry man, I'm still busy learning, but is there any way you could show me how to put this into a stored procedure? I know how to use the value returned from the stored procedure in VB, just donno how to word the actual procedure. Thanks.

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2007-06-04 : 05:17:43

Something like:

CREATE PROCEDURE dbo.usp_AddContainer 
( @ContainerCode CHAR(8))
AS
BEGIN
IF NOT EXISTS (SELECT ContainerCode FROM dbo.Containers WHERE ContainerCode = @ContainerCode)
  BEGIN
    -- Insert statement here
    -- Return appropriate value
  END
ELSE
  BEGIN
    -- Return appropriate value
  END
END

Mark

superhero
Yak Posting Veteran

52 Posts

Posted - 2007-06-04 : 07:30:39

Thanks bud, it worked like a dream.

Thought I'd post the code so that someone else might learn from it as well:

[this is the stored procedure]
CREATE PROCEDURE CheckInsertContainer
(@rev int output,
@ContainerCode nvarchar(10))
AS
BEGIN
IF NOT EXISTS (SELECT ContainerCode FROM dbo.Containers WHERE ContainerCode = @ContainerCode)
BEGIN
Insert Into Containers (containercode) values(@ContainerCode)
-- Return appropriate value
set @rev = 1
END
ELSE
BEGIN
-- Return appropriate value
set @rev = 2
END
END
GO

And this is the function I used in my webservice to get the return value:

<WebMethod(Description:="Checks container code before insert", EnableSession:=False)> _
Public Function CheckInsert(ByVal containercode As String) As String

Dim cmd As New SqlCommand("CheckInsertContainer", connection)
cmd.CommandType = CommandType.StoredProcedure
connection.Open()

Dim param As SqlParameter
param = cmd.Parameters.AddWithValue("@containercode", containercode)

Dim pID As System.Data.SqlClient.SqlParameter = cmd.Parameters.Add("@rev", System.Data.SqlDbType.Int)
pID.Direction = System.Data.ParameterDirection.Output

cmd.ExecuteNonQuery()

Dim results As String = cmd.Parameters("@rev").Value.ToString()

Dim containerid As Integer
containerid = 0

If IsNumeric(results) = True Then
containerid = results
End If
connection.Close()

Return containerid

End Function

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-06-04 : 07:34:06

Or, you could create a unique index over containercode column and check the "ignore dup value" box.
Then you will get a specific error message if trying to insert a duplicate value.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources