using IN and an integer list

[unseth]

I have a list of states by number. If I do this, it works fine:

select * from states where state_id IN ('15,25,30')

(state_id is an int)
however i want to pass it into a spo. so I can call:

exec statelist '15,25,30'

however it doesn't work this way:

create procedure statelist as
@input varchar(100)
AS

select * from states where state_id IN (@input)

GO

because that is a varchar. If I do it this way it works, but that's not what I want

sql = 'select * from states where state_id IN (' + @input + ')'
exec (sql)

How can i pass in an ordered list of INTS?

 

[SQLBill]

Consider me confused, but if you are passing an integer for the state_id, why does your create procedure use a varchar as a variable?

Wouldn't this work?

create procedure statelist as
@input int
AS

select * from states where state_id IN (@input)
go

then you run:

exec statelist ('15,25,30')

-SQLBill

 

[unseth]

That doesn't work because you are passing the state list in a string '15,25,30' and it errors trying to convert it to an int.

I actually listed it wrong above, this is what works fine (in the first line)

select * from states where state_id IN (15,25,30)

 

[bperry]

If we do this:
exec statelist (15,25,30)

Then the SP will need to be coded to look for (a fixed number of) separate incoming parameters. Perhaps like this:

create procedure statelist as
@input1 int,
@input2 int,
...
@inputn int
AS
select blah blah

And if we do this:
exec statelist ('15,25,30')

Then the SP can look for and receive one incoming (varchar)parameter, but we will then have to write code to 'unstring' the various parameters into separate values.

Either way, you are caught in an unhappy sitation. First option is easier to code, but limits you to n variables. Second way is do-able but requires more code.

If we insist on passing in n parameters, then I think what could be done is:
- pass to the SP the parameters as one comma-delim string

and inside the SP:
- build a temporary table,
- unstring the parameters into the temp table,
- in our main select statement, use the temp table to include records we want i.e.
Select * from states where state in (select State from temp)


was this any help?
bp

 

[unseth]

thanks, that does help. I didn't think there was a good way to do it. I'm trying to tune my spos to get the best performance out of them. I think i'll just keep it the way i have it by

create procedure statelist as
@input varchar(100)
AS

sql = 'select * from states where state_id IN (' +@input +')'
exec(sql)
go

 

[tlbroadbent]

You can pass the parameters in a tring but you'll either need to parse the string or use dynamic SQL. Here is a simple example of the latter.

Create Procedure Statelist
@input varchar(100)
AS

Declare @sql varchar(200)

Select @sql=
'select * from states ' +
'where state_id IN (' + @input + ')"

Exec(@sql)

GO Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[unseth]

That's the way i have it set up now. But I heard that dynamic sql in that way is not as fast and efficient because it will recompile everytime it is run.

Is this true?

thanks for all your help everyone

 

[bperry]

At least one issue to be aware of when runnning dynamic sql (i.e. via the exec command) inside a stored procedure, is that the security context changes from the SP itself, to the user actually running the SP.

Suppose a SP does 'Select * From aTable'. A user running that SP will not need any access rights to aTable itself; he will just need execute permission on the SP.

Now suppose instead the SP does these 2 commands:
set @Sql='Select * from aTable'
exec(@Sql)

Now, the user needs not only execute permission on the SP, BUT ALSO read permission on the table itself.

It is a common (and well-accepted) practice, when setting up security for an application, to give users no permissions at all to the tables, and instead give them execute permission on the SPs. This insures that users can only get at the tables through the SPs provided for them. But if the SPs have exec code in them, then that approach will no longer be possible.

Just something to be aware of.

 

[tlbroadbent]

1) The recompilation time is minimal. Unless your system is processesing thousands of transactions per minute, I don't think that you'll notice. You can use the stored procedure sp_executesql to mitigate some of the recompilation issues. I've read that using that SP requires less recompilations than execute. However, I haven't done any testing myself.

An alternative method involving a temporary table may eliminate the need for recompilation but would likely be slower because of the overhead of temp tables. If I can find the sample code that I created for that method I'll post it here.

2) I agree with bperry that dynamic SQL can breach security. But by carefully choosing when to use dynamic SQL and accounting for the security you can overcome the problem. For example, you don't need to grant permissions on the underlying table to execute the select statement dynamically. You can create a view that has only Select permissions and still keep the table locked down. The dynamic query could select from the view.

Brian,

I appreciate your pointing out this security issue. I haven't given it enough thought. May I recommend that you create a FAQ about this and point people to the FAQ rather than posting the same message repeatedly. Perhaps you could include some resources such as Robert Marda's excellent article at SQL ServerCentral - "Dynamic SQL vs. Static SQL Part 1 - Security."

http://www.sqlservercentral.com/columnists/rmarda/dynamicsqlversusstaticsqlp1.asp

Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[bperry]

>>May I recommend that you create a FAQ....
Thank you, a good suggestion. Unfortunately, I tried to create one but found it beyond my abilities: too dumb. But I can compromise by agreeing to not mention it anymore.

My only (final) observation is that many contributors to this forum (yourself eXcluded) sometimes seem to routinely recommend a dynamic approach either when it is not necessary, or without any reference to the underlying issues. (I suspect that perhaps many come from an ASP environment where sql statements are routinely constructed and executed as a string.)

I actually liked your last suggestion to <unseth>. It's an approach I often overlook myself because I am in a very security-conscious environment. But I would mildly disagree with using views to compensate for using dynamic sql. I would tend to think that creating/managing a new set of database objects (views on n+ tables) would not be a scalable approach to dealing with a security problem that we created by using dyn sql. (Actually, I'm sure you'd agree with that too.)

That said, dynamic sql can certainly be a useful tool. (And I myself was getting tired of my little security rant.)

 

[tlbroadbent]

Brian,

I disagree with your assesment of your own ability to write a FAQ. The quality of your contributions to this forum indicates otherwise.

I probably recommend dynamic SQL too frequently and need to consider security more often. You've helped me to get a better handle on that concept. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.

 

[VogonPoet]

Brian,

Please keep bringing up the security issue. I recommended the dynamic SQL approach to someone a few days ago and you raised the security issue.

For many of us the security issue may not be important and we get someone else in trouble by overlooking it in our responses.

Michael