Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-server-2005 SQL , +
Hi All
I have to send the bank Id with IN SQL Quarry.
I write the below Quarry
 
declare @Bank_id varchar(4000)
set @Bank_id = '5,28'
SELECT CONVERT(int, CONVERT(int, @Bank_id));
select Bank_Id,Bank_Name from  Tbl_bank where bank_Id in (@Bank_id)
 
but i get the Error
"Conversion failed when converting the varchar value '5,28' to data type int."
 
Please give me quarry for the sending ID with In Quarry in to Stored procedure.
Thanks In Advanced
 
Regards,
Ravi Sharma
Posted 14-Apr-12 2:41am
Edited 14-Apr-12 17:50pm
v3
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 4

Hello,
Use The Following Function To split such values
Execute this and u need to use
Example:
declare @Bank_id varchar(4000)
set @Bank_id = '5,28'
SELECT CONVERT(int, CONVERT(int, @Bank_id));
select Bank_Id,Bank_Name from  Tbl_bank where bank_Id in (select data from split(@Bank_id,','))
 
where split takes two arguements
1st:the value to be split(@Bank_id)
2nd:The symbol used to split(,)
 
you will get wht you want
 
Create FUNCTION [dbo].[Split]
(
	@RowData nvarchar(4000),
	@SplitOn nvarchar(5)
)  
RETURNS @RtnValue table 
(
	Id int identity(1,1),
	Data nvarchar(400)
) 
AS  
BEGIN 
	Declare @Cnt int
	Set @Cnt = 1
 
	While (Charindex(@SplitOn,@RowData)>0)
	Begin
		Insert Into @RtnValue (data)
		Select 
			Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))
 
		Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
		Set @Cnt = @Cnt + 1
	End
	
	Insert Into @RtnValue (data)
	Select Data = ltrim(rtrim(@RowData))
 
	Return
end
 
Thanks & Regards
  Permalink  
v2
Comments
Ravi Sharma 2 at 16-Apr-12 2:04am
   
Thanks A Lot Kishore....my issue is resolve from above Solution
You are Rock.
kishore sharma at 16-Apr-12 2:17am
   
you are welcome dear,Thanks for your response
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Have You tried set @Bank_id = '5.28'?
  Permalink  
Comments
Ravi Sharma 2 at 14-Apr-12 23:49pm
   
Yes , I want to Send ID to the Stored procedure through In Quarry.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

If I look in your question,
it seems you need to fetch the data of banks whose id's are
5 and 28 these two banks's data you need to fetch.
but you got confused, you are trying '5,28' is you bank id for one bank.
 
don't convert just try
 
declare @Bank_id varchar(4000)
set @Bank_id = '5,28'
select Bank_Id,Bank_Name from Tbl_bank where bank_Id in (@Bank_id)
  Permalink  
Comments
Ravi Sharma 2 at 14-Apr-12 23:47pm
   
Yes i try for the same but got the Same Error
Conversion failed when converting the varchar value '5,28' to data type int.
Please provide me proper Quarry to send the ID in Stored Procedure through SQL IN Quarry.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 288
1 _Amy 270
2 Maciej Los 220
3 Manfred R. Bihy 200
4 CHill60 180
0 OriginalGriff 7,445
1 Sergey Alexandrovich Kryukov 6,347
2 Maciej Los 3,799
3 Peter Leow 3,558
4 CHill60 2,702


Advertise | Privacy | Mobile
Web02 | 2.8.140721.1 | Last Updated 16 Apr 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100