[OT] Insert Distinct Value into a SQLServer Table

  • Thread starter Thread starter Shadowboxer
  • Start date Start date
S

Shadowboxer

Here's what I need to do:

essentially I have a table in a SQL database that has two columns

int_Artist and str_Artist

int_Artist is a PK field

str_Artist Needs to have unique values

if the data in str_Artist exists, I need the int_Artist Value for that
record.

What I need sql to do in a stored procedure is this:

dim x as integer
x = select Artist_ID from Artists where artist_name = 'Test'
if x = nothing then
x = INSERT into Artists (Artist_ID) values ('test')
end if

insert into blah (blah) values (x,blah)



ANY IDEAS? or does someone have something similar?
 
Don´t really know what you want, but if you want this in a stored proc this
could work:

declare x int
set x = (select int_Artist from Artists where artist_name = 'Test'
if(x is null)
begin
INSERT into Artists (int_Artist, str_Artist) values
(yourNewIntArtistValue, 'test')
set x = scope_identity()
end

return x

/Marre
 
Well that generates this error:
Server: Msg 155, Level 15, State 2, Line 1
'integer' is not a recognized CURSOR option.
Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'if'.
Server: Msg 170, Level 15, State 1, Line 7
Line 7: Incorrect syntax near '='.
Server: Msg 178, Level 15, State 1, Line 10
A RETURN statement with a return value cannot be used in this context.


this is a SQLSERVER 2000sp3 machine
 
P.S. This code will work exactly if it works
Marre said:
Don´t really know what you want, but if you want this in a stored proc this
could work:

declare x int
set x = (select int_Artist from Artists where artist_name = 'Test'
if(x is null)
begin
INSERT into Artists (int_Artist, str_Artist) values
(yourNewIntArtistValue, 'test')
set x = scope_identity()
end

return x

/Marre
Click to expand...
 
In SQL server, local variables are prefixed thus:

CREATE PROCEDURE AddUnique

@Name VARCHAR(64),
@ExistsID INTEGER OUTPUT
AS

DECLARE @Error INTEGER

SELECT @ExistsID = int_Artist FROM Artists WHERE artist_name = @Name
SET @Error = @@ERROR

IF @Error = 0
BEGIN
IF @ExistsID IS NULL
BEGIN
INSERT INTO Artists ( str_Artist ) VALUES ( @Name )
SET @Error = @@ERROR
END
END

RETURN @Error
 
Change this line to:

INSERT INTO Artists ( artist_name ) VALUES ( @Name )

and then it should work.
 
Made two changes
ALTER PROCEDURE sp_AddUnique

@Name VARCHAR(8000)

AS

DECLARE @Error INTEGER

DECLARE @ExistsID INTEGER

SELECT @ExistsID = Artist_ID FROM Artists WHERE artist_name = @Name

SET @Error = @@ERROR

IF @Error = 0

BEGIN

IF @ExistsID IS NULL

BEGIN

INSERT INTO Artists ( artist_name ) VALUES ( @Name )

SELECT @ExistsID = Artist_ID FROM Artists WHERE artist_name = @Name

SET @Error = @@ERROR

END

END

select @ExistsID



this works great, I owe you a beer!
 
It works, in the query analyzer.
it works sometimes in the program
I sometimes, but not reliably get an error on differing records:

An unhandled exception of type 'System.Runtime.InteropServices.COMException'
occurred in adodb.dll

Additional information: Item cannot be found in the collection corresponding
to the requested name or ordinal.

Record: 000bf713 Imported Sucsessfully

The thread '0' (0xadc) has exited with code 0 (0x0).

Unhandled Exception: System.Runtime.InteropServices.COMException
(0x800A0CC1): Item cannot be found in the collection corresponding to the
requested name or ordinal.

at ADODB.Fields.get_Item(Object Index)

at ADODB.InternalFields.get_Item(Object index)

at braindonorsDB.clsDatabase.commitRecord(clsFileFormat inFileFormat) in
e:\My Documents\Visual Studio Projects\braindonorsDB\clsDatabase.vb:line 90

at cddbimporter.clsConverter.start_conversion() in e:\My Documents\Visual
Studio Projects\cddbimporter\clsConverter.vb:line 179The program '[4084]
cddbimporter.exe' has exited with code 0 (0x0).



Any Ideas?
 
Back
Top