December 6, 2002 at 3:07 am
I have a script that references a table on a 2nd database and uses a stored procedure on the primary database that it also uploads data to. What it is suppose to do is read the values from 2 columns in a table called dbo.parts and uploads records to a different table with other data.
the code is below. In its current state it will upload data fine it will read the parts field and upload records for each record in that table.
and it will display a record in the format:
CODE = FWAR52
DESCRIPTION = car
which is what it has to do, but there is a problem in the code field thats generated, they currently all have the same code (52 refers to the last entry in that column. In the dbo.parts table each part (such as car) has a code assigned to it which is an int that automatically increments by 1 from the previous entry. What i need to do is ensure the code is matched to the part that its with in the dbo.parts table. I used a cursor function to ensure it reads all the parts but i've had trouble get it to read both fields.
DECLARE @PREFIX VARCHAR(6)
DECLARE @CODE VARCHAR(8)
DECLARE @GUID UNIQUEIDENTIFIER
DECLARE @PARENTGUID UNIQUEIDENTIFIER
DECLARE @PART VARCHAR(50)
DECLARE @DIGIT VARCHAR(4)
SELECT @PART = PART FROM PROACTIS_CUSTOM.DBO.PARTS
SELECT @DIGIT = DIGIT FROM PROACTIS_CUSTOM.DBO.PARTS
SET @PREFIX = 'FWAR'
SELECT @PARENTGUID = GUID FROM DSDBA.iTEMGROUPS
WHERE CODE = @PREFIX
DECLARE TABLCURSOR CURSOR FOR
SELECT PART FROM Proactis_Custom.dbo.parts
SELECT DIGIT FROM proactis_custom.dbo.parts
OPEN TABLECURSOR
FETCH NEXT FROM TABLECURSOR INTO @PART
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @PART
FETCH NEXT FROM TABLECURSOR INTO @PART
SET @CODE = @PREFIX + @DIGIT
SELECT @GUID = NEWID()
EXEC DSDBA.usp_ItemsDB_InsertGroup @GUID, @CODE, @PART, @PARENTGUID, 1
END
CLOSE TABLECURSOR
DEALLOCATE TABLECURSOR
December 6, 2002 at 3:52 am
Hi, if i understandcorrectly you have to change the following lines:
DECLARE @PREFIX VARCHAR(6)
DECLARE @CODE VARCHAR(8)
DECLARE @GUID UNIQUEIDENTIFIER
DECLARE @PARENTGUID UNIQUEIDENTIFIER
DECLARE @PART VARCHAR(50)
DECLARE @DIGIT VARCHAR(4)
--SELECT @PART = PART FROM PROACTIS_CUSTOM.DBO.PARTS
--SELECT @DIGIT = DIGIT FROM PROACTIS_CUSTOM.DBO.PARTS
SET @PREFIX = 'FWAR'
SELECT @PARENTGUID = GUID FROM DSDBA.iTEMGROUPS
WHERE CODE = @PREFIX
DECLARE TABLCURSOR CURSOR FOR
SELECT PART,DIGIT FROM Proactis_Custom.dbo.parts
--SELECT DIGIT FROM proactis_custom.dbo.parts
OPEN TABLECURSOR
FETCH NEXT FROM TABLECURSOR INTO @PART, @digit
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @PART
print @digit
FETCH NEXT FROM TABLECURSOR INTO @PART,@digit
SET @CODE = @PREFIX + @DIGIT
SELECT @GUID = NEWID()
EXEC DSDBA.usp_ItemsDB_InsertGroup @GUID, @CODE, @PART, @PARENTGUID, 1
END
CLOSE TABLECURSOR
DEALLOCATE TABLECURSOR
December 6, 2002 at 4:16 am
I have tried the code like you suggested where i changed the fetch command to:
FETCH NEXT FROM TABLECURSOR INTO @PART,@DIGIT
.....
.....
BEGIN
PRINT @PART
PRINT @DIGIT
FETCH NEXT FROM TABLECURSOR INTO @PART,@DIGIT
but i get an error:
line 20
cursorfetch: the number of variables declared in the INTO list must match that of selected column.
And it doesn't update the database at all.
December 6, 2002 at 4:20 am
Hi see also the cursor select definition i changed
SELECT PART,DIGIT FROM Proactis_Custom.dbo.parts
December 6, 2002 at 4:24 am
thats great, thankyou
December 6, 2002 at 7:01 am
Why are you using a cursor. Depending on what is happening in DSDBA.usp_ItemsDB_InsertGroup you should be able to do your insert much more effectivly like this.
DECLARE @PREFIX VARCHAR(6)
DECLARE @PARENTGUID UNIQUEIDENTIFIER
SET @PREFIX = 'FWAR'
SELECT @PARENTGUID = GUID FROM DSDBA.iTEMGROUPS
WHERE CODE = @PREFIX
INSERT INTO tblinsertedintoinsp(GUID, CODE, PART, PARENTGUID, SOMECOL) SELECT NEWID() AS GUID, (@PREFIX + DIGIT) AS CODE, PART, @PARENTGUID AS PARENTGUID, 1 AS SOMECOL FROM Proactis_Custom.dbo.parts
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply