Conversion from NVarchar To Int

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)

Conversion from NVarchar To Int

Author

Topic

ravininave
Posting Yak Master

111 Posts

Posted - 2012-07-22 : 13:36:33

In my procedure value comes in character.
like - '251064,251065,251066'
type of my field is bigint

now when i try like this

ALTER PROCEDURE [dbo].[DelScratch]
(@ScratchNo Nvarchar (100))
AS
BEGIN
--@ScratchNo = '251064,251065,251066'
Delete from scratchcard Where ScratchNo in (@ScratchNo)
Delete from ScratchMast Where ScratchNo in (@ScratchNo)
END

it gives error like
Error converting data type varchar to bigint.

How can i convert it in sql server

VB6/ASP.NET
------------------------
http://www.nehasoftec.com

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-22 : 14:11:17

SQL Server does not let you use a variable in the manner you are trying to use in the query. Your choices are:

a) To split the comma-separated string into individual tokens using a string splitter function into a (virtual) table and then join with that table. There are string splitter functions available on the web and in SQL Team archives - for example Fig 21 in here: http://www.sqlservercentral.com/articles/Tally+Table/72993/

b) Change your where clause to this:

WHERE ','+@ScratchNo+',' LIKE '%,'+CAST(ScratchNo AS NVARCHAR(32))+',%'

The second approach is simpler, but it may be less efficient because of the non-sargable predicate.

ravininave
Posting Yak Master

111 Posts

Posted - 2012-07-22 : 14:16:32

quote:
Originally posted by sunitabeck

SQL Server does not let you use a variable in the manner you are trying to use in the query. Your choices are:

a) To split the comma-separated string into individual tokens using a string splitter function into a (virtual) table and then join with that table. There are string splitter functions available on the web and in SQL Team archives - for example Fig 21 in here: http://www.sqlservercentral.com/articles/Tally+Table/72993/

b) Change your where clause to this:
WHERE ','+@ScratchNo+',' LIKE '%,'+CAST(ScratchNo AS NVARCHAR(32))+',%'
The second approach is simpler, but it may be less efficient because of the non-sargable predicate.

Thanx

VB6/ASP.NET
------------------------
http://www.nehasoftec.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources