Only one character of string getting in @variable

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

SQL Server Development (2000)

Only one character of string getting in @variable

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-07-07 : 08:01:13

Gary Pool writes "I just looked at your article about parsing and I am trying to do something similar. I've had many years of programming but am a novice with SQL. In the code below, I am trying to take a row of fields and make each field its own record (in this case, the group field -- there are extra variables as I was just going to change the SELECT for each field).

But by golly, even though the serial number I'm reading can be 8 to 25 characters long, as soon as it gets into the @sn variable, only the first character is populating the variable. Come to find out, another program that I could have sworn was working properly is now doing the same thing.

THIS IS DRIVING ME CRAZY! Can you make any sense of it? I'm desperate!

DECLARE @term int, @group int, @status int, @sid int, @sac int
DECLARE @sn varchar, @ta varchar, @gid varchar, @cstat varchar, @svid varchar, @svac varchar

SET @term=33
SET @group=6
SET @status=7
SET @sid=9
SET @sac=17

DECLARE Asset_cursor CURSOR for
SELECT
assettest.SERIAL_NUMBER, assettest.GROUP_ID
FROM
csupport.dbo.assettest assettest
WHERE assettest.GROUP_ID <> ''

open Asset_cursor

WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM Asset_cursor into @sn, @gid

print @sn

/*
INSERT INTO CFIELDS
(SERIAL_NUMBER, FIELD_VALUE, FIELD_ID)
VALUES
(@sn, @gid, @group)
*/
END
CLOSE Asset_cursor
DEALLOCATE Asset_Cursor "

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-07-07 : 08:06:10

You MUST specify a length value for char, varchar, nvarchar, binary, and varbinary datatypes:

DECLARE @sn varchar(20), @ta varchar(10), @gid varchar(15), @cstat varchar(20), @svid varchar(20), @svac varchar(10)

Change the lengths to match the maximum number of characters you need to store in each variable, you can put up to 8000 characters in a varchar.

Also, you absolutely do not need a cursor to perform the task at hand:

INSERT INTO CFIELDS (SERIAL_NUMBER, FIELD_VALUE, FIELD_ID)
SELECT assettest.SERIAL_NUMBER, assettest.GROUP_ID, 6 AS Field_ID
FROM csupport.dbo.assettest assettest WHERE assettest.GROUP_ID <> ''

poolmwv
Starting Member

12 Posts

Posted - 2003-07-07 : 08:56:26

First off -- THANK YOU, THANK YOU, THANK YOU!

quote:

You MUST specify a length value for char, varchar, nvarchar, binary, and varbinary datatypes:

I could have sworn I tried that with no success, but I'll test that further.

quote:

INSERT INTO CFIELDS (SERIAL_NUMBER, FIELD_VALUE, FIELD_ID)
SELECT assettest.SERIAL_NUMBER, assettest.GROUP_ID, 6 AS Field_ID
FROM csupport.dbo.assettest assettest WHERE assettest.GROUP_ID <> ''

Very nice! I like this a whole lot better! This will do it the elementary way that I was going to do it and run a script for each field. Is there a good way to do all five fields at once? See, the data is stored:

SERIAL NUMBER, (bunch o' fields), TERMID, STATUS, SERVICE_ID, SERVICE_AUTHORIZATION_CODE

This information is taken from our "Golden Inventory Database". I need to keep the Asset database updated in our problem tracking software (That's my bailiwick. I'm not an SQL programmer by trade but the Help Desk Supervisor).

The Asset database is very flexible. These are custom fields that I want to eventually add (the CFIELDS table is a temporary layover). I'm moving them all from the single row in assettest to giving each field its own record in CFIELDS (in our problem tracking software, each custom field is its own record with an index as to which of the five fields it is:

SET @term=33
SET @group=6
SET @status=7
SET @sid=9
SET @sac=17

As well as an index as to which asset is the "owner" of the custom field).

In the next step, I'll populate the owner field by using the serial number to get the owner's index number.

I was then planning to use IF EXIST and IF NOT EXIST to split CFIELDS into two more temporary tables UPDATE_CFIELDS and INSERT_CFIELDS because it could be either on an updated record. Then I'd finally update the REAL table, ASSET_CUSTOM_FIELDS.

Now that I've jabbered enough, I'll re-ask the question:

Is there a relatively easy way to take all 5 fields from a row and write 5 records to CFIELDS?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources