Using In Statement in Stored Procedure?

Question

Post reply

Using In Statement in Stored Procedure?

CMitesh

Say Hey Kid

Points: 674
More actions
May 5, 2005 at 6:13 am

#65817

this is my stored procedure
CREATE PROCEDURE sp_Test_FOR_InStatement
@sStr as char(50)
AS
set @sStr = rtrim(@sStr)
select * from a2zOrderdata where [Order No] in (@sStr)
GO
i pass in this values to stored procedure '146767,142359,146743' and i get Error converting data type varchar to int. as error messge.
[Order No] is a numeric Field.
Any idea, suggestion would be really appreicated.
Mits

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

CMitesh Say Hey Kid Points: 674 More actions · Answer 1

Hi Guys n gals

I have found the solution to my above problem

follow this link http://www.sqlteam.com/item.asp?ItemID=11499

Mits

Prasad Bhogadi SSCrazy Eights Points: 9235 More actions · Answer 2

TRY THIS...

CREATE PROCEDURE sp_Test_FOR_InStatement

@sStr as char(50)

AS

set @sStr = rtrim(@sStr)

DECLARE @strQuery VARCHAR(500)

SET @strQuery='select * from a2zOrderdata where [Order No] in (' + @sStr +')'

EXEC (@strQuery)

GO

Ofcourse usage of dynamic sql is not advised.

Regards

Prasad Bhogadi
www.inforaise.com

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

I swear I'm gonna kill the next guy who proposes a dynimaic sql solution for this problem. Guys learn how to code pls b4 answering questions.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

Needlessly harsh words, Remi.

It will certainly be more helpful for anyone here if you post a better solution than just exercise rhetorical platitudes, don't you think?

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

CMitesh Say Hey Kid Points: 674 More actions · Answer 5

Thank you all guys for your response. This is the first time i have asked anything in this forums and i will definitely come back.

Mits

Jonathan SSC-Insane Points: 20427 More actions · Answer 6

Rather than putting your list in a variable, put it into a temporary table as one row per value. Then just use a join to that temporary table in your stored procedure.

CREATE PROC sp_Test_FOR_InStatement AS

SET NOCOUNT ON

SELECT d.*

FROM a2zOrderdata d JOIN #t t ON d.[Order No] = t.OrdNo

CREATE TABLE #t(OrdNo int PRIMARY KEY)

INSERT #t

SELECT 146767

UNION ALL SELECT 142359

UNION ALL SELECT 146743

--Jonathan