HardcoreTechnoHead
Technical User
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"
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"