Another basic question - 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

General SQL Server Forums

New to SQL Server Programming

Another basic question

Author

Topic

mavershang
Posting Yak Master

111 Posts

Posted - 2009-05-20 : 16:13:06

Hi all. I am new to SQL and really confused about the conversion of data type. Here is my question.

Declare @aln int
Declare @see int
Declare @SQL nvarchar(4000)
set @aln=1
set @see=100

set @SQL='
select *
from table1 t1 join table2 t2
on t1.col1=' + cast(@aln as nvarchar(10)) + ' and t2.col1=' + cast(@see as nvarchar(10))

exec (@SQL)

But the code did not work, and keep saying "Error converting data type varchar to numeric"

Anyone could help me out?

Thanks

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-05-20 : 16:25:40

try PRINTing @SQL..

The issue us that @see in unassigned, and therefore NULL. When you contacenate any value with NULL the result is NULL.

mavershang
Posting Yak Master

111 Posts

Posted - 2009-05-20 : 16:29:49

my bad. I missed one line. the @see is assigned actually.

when i print @sql, I could see the value of @aln and @see.

quote:
Originally posted by Lamprey

try PRINTing @SQL..

The issue us that @see in unassigned, and therefore NULL. When you contacenate any value with NULL the result is NULL.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-05-20 : 16:47:12

AHh, no worries.

Are both Col1 and Col2 both Numeric types?

mavershang
Posting Yak Master

111 Posts

Posted - 2009-05-20 : 16:49:58

Yes, the col1 of table1 and table2 are int type

quote:
Originally posted by Lamprey

AHh, no worries.

Are both Col1 and Col2 both Numeric types?

kira
Starting Member

17 Posts

Posted - 2009-05-20 : 17:09:28

the code runs without any error in my machine. I use SQL Server2008.

mavershang
Posting Yak Master

111 Posts

Posted - 2009-05-20 : 17:21:03

That is weird. I gotta check the table. Thanks.

quote:
Originally posted by kira

the code runs without any error in my machine. I use SQL Server2008.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-05-20 : 17:44:01

For the example you posted, you are better off using sp_executesql to substitute parameters in a SQL string. In fact, there's no reason to use dynamic SQL for this example.

mavershang
Posting Yak Master

111 Posts

Posted - 2009-05-20 : 18:00:08

All right. Thanks a lot

quote:
Originally posted by robvolk

For the example you posted, you are better off using sp_executesql to substitute parameters in a SQL string. In fact, there's no reason to use dynamic SQL for this example.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources