problem with cast and varchar - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

problem with cast and varchar

Author

Topic

scottstown1
Starting Member

15 Posts

Posted - 2012-03-22 : 09:11:56

Hello,

I am having a small problem with my simple SQL statement.

DECLARE
@ICDs VARCHAR(1000),
@n INT
SELECT @n = 1
SELECT @ICDs = COALESCE(@ICDs + ',', '') + cast(diagnosis_code as varchar(10)) + 'code' +cast(@n as varchar(20)), @n = @n + 1
FROM diagnosis
WHERE (encounter_id = 34371)
exec('SELECT '+@ICDs)

It works find if the data in the db is something like 123.12 but not if it is v123.12, Anyone have any suggestions for me?

Thank you,
Scott

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-03-22 : 09:36:09

Change VARCHAR(10) to VARCHAR(20)?

N 56°04'39.26"
E 12°55'05.63"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-03-22 : 09:43:50

[code]
SELECT @ICDs = COALESCE(@ICDs + ',', '')
+ case when isnumeric(diagnosis_code) = 0 then '''' else '' end
+ cast(diagnosis_code as varchar(10))
+ case when isnumeric(diagnosis_code) = 0 then '''' else '' end
+ 'code' +cast(@n as varchar(20)), @n = @n + 1
[/code]

KH
[spoiler]Time is always against us[/spoiler]

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-22 : 09:43:56

are you thinking that @n will increment?

what data is like v123.12?

instead of exec, do a PRINT @ICDs first

In any case...not sure what you are doing..trying to get a delimited list I guess...

Post some sample data in DML form, expected results and the table DDL

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

scottstown1
Starting Member

15 Posts

Posted - 2012-03-22 : 10:23:46

@khtan,

Works perfect!!! Thank you so much!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-03-22 : 10:42:19

welcome

KH
[spoiler]Time is always against us[/spoiler]

scottstown1
Starting Member

15 Posts

Posted - 2012-03-23 : 09:49:30

@khtan

How would I go about selecting another column other then diagnosis_code.

Ex:

DECLARE
@ICDs VARCHAR(8000),
@n INT

SELECT @n = 1

SELECT @ICDs =

COALESCE(@ICDs + ',', '')
+ case when isnumeric(diagnosis_code) = 0 then '''' else '' end
+ case when isnumeric(short_description) = 0 then '''' else '' end
+ cast(diagnosis_code as varchar(max))
+ cast(short_description as varchar(max))
+ case when isnumeric(diagnosis_code) = 0 then '''' else '' end
+ case when isnumeric(short_description) = 0 then '''' else '' end
+ 'icd9_' +cast(@n as varchar(1))
, @n = @n + 1

FROM diagnosis JOIN icd9cm_ref ON icd9cm_ref.icd9cm_code = diagnosis.diagnosis_code

WHERE (diagnosis.encounter_id = 34350)

print @ICDs

exec('SELECT '+@ICDs)

Doesn't seem to work.. any ideas? Thanks again for any help.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-03-23 : 10:27:22

how do you want to show the diagnostic_code and description ?

please post some samples

KH
[spoiler]Time is always against us[/spoiler]

scottstown1
Starting Member

15 Posts

Posted - 2012-03-23 : 13:45:11

scottstown1
Starting Member

15 Posts

Posted - 2012-03-23 : 15:21:06

Alright I almost have it... but I am not sure how to combine the variables in the end, as it is currently giving an error Incorrect syntax near '.01'.

DECLARE
@abcd VARCHAR(1000),
@abc VARCHAR(1000),
@ab VARCHAR(1000),
@n INT
SELECT @n = 1

SELECT @abc = COALESCE(@abc + ',', '')
+ case when isnumeric(short_description) = 0 then '''' else '' end
+ cast(short_description as varchar(10))
+ case when isnumeric(short_description) = 0 then '''' else '' end
+ 'Code' +cast(@n as varchar(20)), @n = @n + 1
FROM diagnosis
JOIN icd9cm_ref ON icd9cm_ref.icd9cm_code = diagnosis.diagnosis_code
WHERE (encounter_id = 34371)

SELECT @abcd = COALESCE(@abcd + ',', '')
+ case when isnumeric(diagnosis_code) = 0 then '''' else '' end
+ cast(diagnosis_code as varchar(10))
+ case when isnumeric(diagnosis_code) = 0 then '''' else '' end
+ 'Code' +cast(@n as varchar(20)), @n = @n + 1
FROM diagnosis
JOIN icd9cm_ref ON icd9cm_ref.icd9cm_code = diagnosis.diagnosis_code
WHERE (encounter_id = 34371)

SET @ab = @abc + @abcd

exec('SELECT '+@ab)

scottstown1
Starting Member

15 Posts

Posted - 2012-03-23 : 15:45:23

figured it out!!!

SET @ab = @a + @abc + @abcd

exec('SELECT '+@a + ',' +@abc + ',' + @abcd)

thank you all very much for your help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources