Joining multiple variables into one? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Joining multiple variables into one?

Author

Topic

sjmorgan
Starting Member

9 Posts

Posted - 2003-04-14 : 10:13:22

Hi,

I have a table where the work_id is created by joining together 3 variables:
1) The sector type
2) The unique number
3) A status abbreviation

In my SP to insert a new record into the "work" table I need to join the 3 variables above to make one variable. At the moment I am using:

SET @new_work_id = (SELECT @job_number + @new_work_increment + @status_abbreviation)

However, this does not work - the @new_work_id is just blank.

The variables are all the same datatype (varchar).

Am I doing something completely wrong here?

Thanks,

Stuart

Bambola
Posting Yak Master

103 Posts

Posted - 2003-04-14 : 10:53:42

probably one of your variables is NULL.
try to wrap them with ISNULL(@var, something) and see where the problem is.

Bambola.

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-14 : 10:55:58

OK, I don't pretend to understand the syntax or logic of you approach (for example whya raen't you just using a composite key?, and do you mean blank or Null, 2 very different things).

Anyway, hope this helps:

Declare @new_work_id varchar(255), @job_number varchar(255), @new_work_increment varchar(255), @status_abbreviation varchar(255)

SELECT @job_number = '12345', @New_Work_Increment = 'ABC', @Status_abbreviation = 'XYZ'

SELECT @new_work_id = @job_number + @new_work_increment + @status_abbreviation

SELECT @new_Work_Id

-- But you'll have a problem if one of the values is null

SELECT @Status_abbreviation = null

SELECT @new_work_id = @job_number + @new_work_increment + @status_abbreviation

SELECT @new_Work_Id

-- But that can be corrected by:

SELECT @new_work_id = IsNull(@job_number,'') + IsNull(@new_work_increment,'') + IsNull(@status_abbreviation,'')

SELECT @new_Work_Id

Brett

8-)

sjmorgan
Starting Member

9 Posts

Posted - 2003-04-14 : 11:16:16

Thanks - the Isnull showed that one of my previous values was not being retieved properly.

The work_id gets updated depending on status etc. which is why I can't use a composite key

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-14 : 12:40:25

quote:

The work_id gets updated depending on status etc. which is why I can't use a composite key

And why not?

Status and New increment seem to attributes of Job_Number. Is Job_Number an Identity coulmn? If not, is it Unique?

Is any combination of the columns unique?

Brett

8-)

sjmorgan
Starting Member

9 Posts

Posted - 2003-04-14 : 13:05:46

Ahh... well maybe I am misunderstanding.

- job_number is unique from the sector table
- status is unique from the status table

work_id is formed using as shown above:
1) Sector type
2) Unique number
3) Status

the status may change on the work_id which means it would have to updated from, say, FV9823A to FV9823B

Stuart

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources