String SQL datatype converting issue

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)

String SQL datatype converting issue

Author

Topic

micnie_2020
Posting Yak Master

232 Posts

Posted - 2011-10-06 : 01:35:41

Just simple scenario for my issue current facing.

SET @sSQL='select * from fn_testVal_Yr('+@AID+','
SET @sSQL=@sSQL +''''+@DT+''')

@AID is Int.

But when i run it. It's raised me error on @AID

Conversion failed when converting the varchar value 'select * from fn_TestVal_Yr(' to data type int.

Please help me. I need to used string. If i tried to used without string it's work fine for me.

Please Advise.

TQVM

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-06 : 01:38:17

[code]SET @sSQL='select * from fn_testVal_Yr('+CAST(@AID AS varchar(15))+','
SET @sSQL=@sSQL +''''+@DT+''')[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

m_imran18
Starting Member

14 Posts

Posted - 2011-10-06 : 02:05:18

try this,

SET @sSQL='select * from fn_testVal_Yr('+ Convert(nvarchar(15),@AID)+','
SET @sSQL=@sSQL +''''+@DT+''')

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-06 : 02:09:19

quote:
Originally posted by m_imran18

try this,

SET @sSQL='select * from fn_testVal_Yr('+ Convert(nvarchar(15),@AID)+','
SET @sSQL=@sSQL +''''+@DT+''')

why cast it to nvarchar?

you know difference between nvarchar and varchar?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

m_imran18
Starting Member

14 Posts

Posted - 2011-10-06 : 03:22:27

Practicing on multilingual database that’s why made it like this.
There is no need for nvarchar (to make an extra overhead)

make it like this.

SET @sSQL='select * from fn_testVal_Yr('+ Convert(varchar(15),@AID)+','
SET @sSQL=@sSQL +''''+@DT+''')

Thank you.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-06 : 04:58:40

quote:
Originally posted by m_imran18

Practicing on multilingual database that’s why made it like this.
There is no need for nvarchar (to make an extra overhead)

make it like this.

SET @sSQL='select * from fn_testVal_Yr('+ Convert(varchar(15),@AID)+','
SET @sSQL=@sSQL +''''+@DT+''')

Thank you.

isnt this same as what i suggested?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources