How to put in a sp a parameter with IN sentence

Question

Post reply

How to put in a sp a parameter with IN sentence

marcelo salgado

SSC Veteran

Points: 287
More actions
April 15, 2008 at 9:09 am

#71148

Fella's :
I need in a sp, thru my code in -Visual Basic, send a parameter with a set of data like (2,3,4,7,100) to put in a sentence like 'table.field in (2,3,4,7,100)' , but the field is a numeric field (int).
Example:
sp:
CREATE PROCEDURE [dbo].[sp_XXXXX]
@Crias varchar(50),
AS
SELECT xxxx
FROM xxxxx
where
Sumario.criasumario in (@Crias)
ORDER BY xxxx
GO
Obviously, Sumario.criasumario is numeric and @Crias is varchar an error is produced,so, how Can I to Adapt this sentence becouse I send a set of data like (2,1,4,5,6,5) ?
Thanks, and sorry for my english.-

Viewing 5 posts - 1 through 4 (of 4 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

There are some solutions for this at:

http://www.sqlservercentral.com/Forums/Topic457395-338-1.aspx

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

souLTower SSCarpal Tunnel Points: 4026 More actions · Answer 2

Here's a way that I've done this. Never used it in production but I've played with it and it works. You would have to look up the max number of arguments. You could also create a single procedure which returns a table from the list of parameters passed:

create procedure proc_foo (@v1 int = null,

@v2 int = null,

@v3 int = null,

@v4 int = null,

@v5 int = null,

@v6 int = null,

@v7 int = null,

@v8 int = null,

@v9 int = null,

@v10 int = null,

@v11 int = null,

@v12 int = null,

@v13 int = null,

@v14 int = null )

AS begin

-- Create a temp table to search against

create table #vals (ii int)

insert into #vals(ii)

select v from

(

select @v1 as v union all

select @v2 as v union all

select @v3 as v union all

select @v4 as v union all

select @v5 union all

select @v6 union all

select @v7 union all

select @v8 union all

select @v9 union all

select @v10 union all

select @v11 union all

select @v12 union all

select @v13 union all

select @v14

)D WHERE v is not null

select * FROM myTable where myValue in (select ii from #vals)

end

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

Assuming you check the input for SQL Injection, you could use dynamic SQL, or use a LIKE syntax to simulate this.

Example - assuming you passed in a list looking like this :

@list = '*1*2*3*12*15*'

you could do:

select *

from mytable

where @list like '%*'+cast(Sumario.criasumario as varchar(10))+'*%'

Dynamic SQL would likely be faster, if your Sumario.criasumario is indexed

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 4

you may want to have a look at fn_split kind of functions .

Maybe they may provide what you look for without using dynamic sql.

have a look at :

http://www.sqlservercentral.com/scripts/Miscellaneous/30225/

http://www.sqlservercentral.com/scripts/String+Manipulation/61509/

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me