Field name as a Parameter

Hi,

I'm trying to figure out how to pass a field name into a procedure so the procedure can selectively find out the value of different fields. The DB table I'm interested in has multiple bool fields in it and the field name is the parameter I want to pass into the procedure. With the example code I list below, there is only one row that exists in the table so the end result should be dependent upon the value in the field. The calling procedure is "attempting" to pass in the name of the field, and the called procedure should use the paramter passed in as the field to return in the table. If the field returned in the called procedure is true, the called procedure returns "0" to the calling procedure, otherwise it returns 1. The syntax I have doesn't seem to work in that the called procedure always returns true from the field and the calling procedure always gets 0 back from the called procedure.

In the calling procedure, I'm doing a select just to find out if a 0 or 1 was returned, but this will not be the final version of the code, its just for test purposes.

Any help on what I'm doing wrong greatly appriciated.

- Bruce

ALTER PROCEDURE [dbo].[caller]

AS

BEGIN

SET NOCOUNT ON;

DECLARE @.include int,

@.Committee bit

execute @.include = dbo.callee @.Committee

IF @.include = 0

BEGIN

select * from ProvCompensation

END

ELSE

BEGIN

select * from ProvCommittee

END

END

ALTER PROCEDURE [dbo].[callee]

(@.fName bit)

AS

BEGIN

DECLARE @.aBool bit

SET NOCOUNT ON;

select @.aBool = @.fName from ProvisionsPlanSum where PlanId = -99

IF @.aBool = 'true'

BEGIN

RETURN (0)

END

ELSE

BEGIN

RETURN (1)

END

END

Generally speaking, parameterizing a column (or table for that matter) is not considered the "right" way to build SQL Server objects. But, if you really need to, you can use dynamic sql, and just execute it like:

declare @.aBool bit
declare @.query nvarchar(max)
declare @.fName varchar(10)
set @.fName = 'someCol'

set @.query = 'select @.aBool = ' + @.fName + ' from ProvisionsPlanSum where PlanId = -99'

exec sp_executeSQL
@.query, N'@.aBool bit output', @.aBool= @.aBool output

select @.aBool

Not 100% sure if this is perfect. I do know that this will work, if you need an example:

declare @.objectId int

exec sp_executeSQL
N'select @.objectId = max(object_id) from sys.objects',
N'@.objectId int output', @.objectId=@.objectId output
select @.objectId

|||

This will also work :)

declare @.aBool bit
declare @.query nvarchar(1000)
declare @.fName varchar(30)
set @.fName = '1; DROP TABLE Test12; --'

set @.query = 'select @.aBool = ' + @.fName + ' from ProvisionsPlanSum where PlanId = -99'

EXEC sp_executeSQL
@.query, N'@.aBool bit output', @.aBool= @.aBool OUTPUT

SELECT @.aBool

|||

Hey guys,

Thanks for the help. I'll give it a go sometime today.

... worked like poop through a goose, thanks guys!

- Bruce