Using IN clause for and double type field in table

• gyanendra.pundhir

  Grasshopper

  Points: 19

  More actions

  March 16, 2009 at 3:00 pm

  #75434

  I am writing SP in SQL-Server select some rows from a table. Like

  =======

  Create Proc Test

  @strIDs NVARCHAR(500)

  as

  SELECT * FROM [dbo].[test_table]

  where Serial_No in (@strIDs )

  =======

  Column is of type bigint.

  when i run thid proc like:

  exec Test '1,3,4,6,7,8'..

  I get this errir : 'Error converting data type nvarchar to bigint.'

  but it runs fine with exec Test '1',

  Note:Only one item in the list..

  Any solution for the same ?

• Jeff Moden

  SSC Guru

  Points: 1004433

  More actions

  March 16, 2009 at 10:06 pm

  #960776

  "Teach a man to fish and..."

  All of your questions are answered in the following two articles...

  http://www.sqlservercentral.com/articles/TSQL/62867/

  http://www.sqlservercentral.com/articles/T-SQL/63003/

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• RP_DBA

  SSCertifiable

  Points: 5116

  More actions

  March 17, 2009 at 6:47 am

  #960965

  Not nearly as elegant but might serve your purpose.

  drop proc test

  go

  drop table test_table

  go

  create table dbo.test_table (Serial_No int)

  go

  insert into dbo.test_table values (1)

  insert into dbo.test_table values (3)

  insert into dbo.test_table values (5)

  insert into dbo.test_table values (7)

  insert into dbo.test_table values (9)

  go

  Create Proc Test

  @strIDs NVARCHAR(500)

  as

  declare @sql varchar(255)

  set @sql = 'SELECT * FROM [dbo].[test_table] where Serial_No in (' + @strIDs + ')'

  exec (@sql)

  go

  exec Test '1,3,4,6,7,8'

  go

  _____________________________________________________________________
  - Nate

  @nate_hughes