IN Clause problem 1

[HardcoreTechnoHead]

I have a Database which requires all access to be made through stored procedures for security/audit purposes.

What I need to do is pass the list of id's to search in an IN clause as a parameter of a stored procedure

i.e.
Create Procedure [GetOrgUserList]
@OrganisationList varchar(2048)
AS
SELECT UserId
FROM User
WHERE OrganisationId in (@OrganisationList)

@OrganisationList will take the form of
2,6,5,9,26

This causes an error when executed
Syntax error converting the varchar value '2,6' to a column of data type int.

I can wrap the select up into an execute statement such as
EXECUTE ('SELECT UserId FROM User WHERE OrganisationId IN (' + @OrganisationList + ')')

But this then requires select permission on the User table which is expressly forbidden

Any Ideas?

Andie Harper
"If you can keep your head when all around you have lost theirs, you probably haven't understood the seriousness of the situation"

 

[MeanGreen]

You will probably have to use dynamic sql. Try this:

Create Procedure [GetOrgUserList]
@OrganisationList varchar(2048)
AS
declare @sql varchar(3000)
select @sql = 'SELECT UserId
FROM User
WHERE OrganisationId in (' + @OrganisationList + ')'
exec (@sql)

Hope this helps.

 

[tlbroadbent]

See thread183-450813. I recommend downloading and using fn_split if you are running SQL Server 2000. Otherwise, use the 1st solution in the referenced thread. If you want to get the best answer for your question read faq183-874 and thread183-468158.


Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[HardcoreTechnoHead]

More info

I'm using SQL Server 7

I have already stated tha the use of dynamic SQL is not allowed as this requires SELECT pemrissions on the USER table. As this is for a web applicaiton using a single SQL login, it is not possible to isolate permissions for specific users

Any other ides?

Andie Harper
"If you can keep your head when all around you have lost theirs, you probably haven't understood the seriousness of the situation"

 

[tlbroadbent]

Write your own parser. Parse the delimited string and insert the values into a temporary table. Then join the main table to the temp table. This solution is the SQL 7 version of fn_split. Check at SWYNK.COM for some parsing scripts.

http://www.swynk.com/sqlhome/misc7.asp

If you want to get the best answer for your question read faq183-874 and thread183-468158.


Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[HardcoreTechnoHead]

The parser was a good idea, sometimes you need to step away from the problem to see the solutions.

Basically I parse the input paramter into a temporary table and use a select to produce the "IN" list

Thanks for the help Terry

Andie Harper
"If you can keep your head when all around you have lost theirs, you probably haven't understood the seriousness of the situation"