setting/declaring variables plus concatenation

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

General SQL Server Forums

New to SQL Server Programming

setting/declaring variables plus concatenation

Author

Topic

crugerenator
Posting Yak Master

126 Posts

Posted - 2008-03-12 : 19:39:18

So I know my code is pretty far off on the variables, but I couldn't find info on how to do this anywhere. Do I need to use sub selects to set the variables? Or something other than variables?

DECLARE @Prefix VARCHAR(10),
@First_Name VARCHAR(50),
@Middle_Name VARCHAR(50),
@Last_Name VARCHAR(50),
@Suffix VARCHAR (10),
@Title VARCHAR (50);

SET @Prefix = ind_prf_code;
SET @First_Name = ind_first_name;
SET @Middle_Name = ind_mid_name;
SET @Last_Name = ind_last_name;
SET @Suffix = ind_sfx_code;
SET @Title = cst_title_dn;

select coalesce((@Prefix + ' '),'') +
ltrim(rtrim(@First_Name)) +
case when ltrim(rtrim(@Middle_Name)) = '' then ' ' else (' ' +
ltrim(rtrim(@Middle_Name)) + ' ') end +
ltrim(rtrim(@Last_Name)) +
coalesce(' ' + @Suffix, '') +
char(13) +
ltrim(rtrim(@Title)) +
char(13) as label

from co_individual (nolock)
join dbo.co_customer (nolock)
on cst_key = ind_cst_key

Thanks!

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-12 : 21:12:59

You would do it more like this:


Declare @somekey varchar(10)
SET @somekey = 'somevalue'


select coalesce((ind_prf_code + ' '),'') +
ltrim(rtrim(ind_first_name )) + ' ' + 
ltrim(Rtrim(' ' + ind_mid_name + ' ' + ltrim(rtrim(ind_last_name)) +
coalesce(' ' + ind_sfx_code, '') +
char(13) +
ltrim(rtrim(cst_title_dn)) +
char(13) as label

from co_individual (nolock)
join dbo.co_customer (nolock)
on cst_key = ind_cst_key

where cst_key =@somekey

You can't set the variable to column names before you Select from the table, but if the intent is to produce the results based on your select statement, try mine. Substitute the @somekey variable for one that filters for the intended "cst_key"

Note this slight change:
[code]
ltrim(Rtrim(' ' + ind_mid_name + ' ' + ltrim(rtrim(ind_last_name)) +
[code]

This would trim the concation of mid_name space last name with the leading space already there from the preceding line. the space between mid name and last name would only be need if mid_name has a string that isn't empty.

Poor planning on your part does not constitute an emergency on my part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources