Help for SQL Query - 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

Help for SQL Query

Author

Topic

abul_mohsin
Starting Member

21 Posts

Posted - 2011-03-03 : 01:40:34

the below query is give me some error plz help me

Note:: here i cannot change the @In_TransferID to int

Declare @In_TransferID varchar(8000)
Declare @VehicleID int
set @In_TransferID ='2140609,2140610,2140611'
Set @VehicleID = (Select VehicleId from TTRANSFER where TRANSFERID in (@In_TransferID))

ERROR ### Conversion failed when converting the varchar value '2140609,2140610,2140611' to data type int.

Your Help in this regard will be appriciated.

Thanks & Best Regard's
Abul Mohsin

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-03-03 : 01:46:37

use the function in the link to parse the string to integer
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=25830&SearchTerms=CSVTable

KH
[spoiler]Time is always against us[/spoiler]

abul_mohsin
Starting Member

21 Posts

Posted - 2011-03-03 : 03:05:40

Thanks for your reply i got u r solution by creating the function

Declare @In_TransferID varchar(8000)
set @In_TransferID ='2140609,2140610,2140611'
Select VehicleId from TTRANSFER where TRANSFERID in (SELECT * FROM dbo.fnGetTable(@In_TransferID,','))

this will give me below result

1821862
1821892
1889059

Note## but here i need to assign it to this variable (@VehicleID), i think i need to put some loop logic here. Plz Suggest

Declare @VehicleID int
Set @VehicleID = (Select VehicleId from TTRANSFER where TRANSFERID in (SELECT * FROM dbo.fnGetTable(@In_TransferID,',')))

now it is giving me this error
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Thanks & Best Regard's
Abul Mohsin

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-03-03 : 03:19:53

you can't assigned multiple value to a variable

KH
[spoiler]Time is always against us[/spoiler]

abul_mohsin
Starting Member

21 Posts

Posted - 2011-03-03 : 03:22:01

Yes i Agree but i can put some loop logic so that i can read the values one by one.

Thanks & Best Regard's
Abul Mohsin

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-03-03 : 03:27:54

why do you want to use a loop to do that ? and why one by one ?

You shouldn't process the data one by one but as a set. Doing it record by record is not efficient at all .

KH
[spoiler]Time is always against us[/spoiler]

abul_mohsin
Starting Member

21 Posts

Posted - 2011-03-03 : 03:39:59

Dear

This is the requirment, i need to get the values one by one and send it as parameter to another StoredProcedure.

Hope it's clear for u now.

Thanks & Best Regard's
Abul Mohsin

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-03-03 : 03:48:23

then why don't you just pass the csv string into the stored procedure and let the stored procedure handle it. Rather than you called the stored procedure several times ?

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources