Field name as a parameter to a stored procedure?

Hi,
Is it possible to pass a field name as a parameter to a stored procedure?
The user wants to select either Dollar or Euro in the UI. The field I want
to be variable is a float & is used in a simple calculation...
(a.A_Tot_Rev_For / e.USDollar)
So I want e.USDollar to be able to change to e.Euro for example when my
report needs euro values.
thanks in advance,
jpi think a UDF is a better option

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

FEATURE REQUEST: Jump to Report with rs:, rc: parameters

FEATURE REQUEST (already submitted to sqlwish@.microsoft.com): Allow rs: and
rc: parameters as an exception to the "CLR-compliant parameter name" rule
within Navigation Parameter properties.
Background: I have created a custom report manager that displays reports
within an IFRAME. With URL access, the rc:LinkTarget points to the IFRAME
name.
Problem: Using the "Jump to report" navigation does not carry the
rc:LinkTarget=iframename parameter forward to the linked report. Subsequent
clicks break the report out of the IFRAME. The workaround, dynamically
creating the URL, is cumbersome and limits report portability. In the
Navigation tab (Parameters button), I tried to set rc:LinkTarget as a
parameter with iframename as the value, which produced a compilation error,
that parameter names must be CLR-compliant. This is true except for rs: and
rc: parameters. Please consider allowing a report to compile and publish
with rs: and rc: parameters specified in the Jump to... links.
Thanks,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---I should have mentioned an alternative (also sent to sqlwish):
FEATURE REQUEST ALTERNATIVE: Provide an option to automatically carry
rc:LinkTarget paramters forward in linked (Jump to) reports.
Thanks,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
news:usQBm8DzEHA.2624@.TK2MSFTNGP11.phx.gbl...
> FEATURE REQUEST (already submitted to sqlwish@.microsoft.com): Allow rs:
> and rc: parameters as an exception to the "CLR-compliant parameter name"
> rule within Navigation Parameter properties.
> Background: I have created a custom report manager that displays reports
> within an IFRAME. With URL access, the rc:LinkTarget points to the IFRAME
> name.
> Problem: Using the "Jump to report" navigation does not carry the
> rc:LinkTarget=iframename parameter forward to the linked report.
> Subsequent clicks break the report out of the IFRAME. The workaround,
> dynamically creating the URL, is cumbersome and limits report portability.
> In the Navigation tab (Parameters button), I tried to set rc:LinkTarget as
> a parameter with iframename as the value, which produced a compilation
> error, that parameter names must be CLR-compliant. This is true except for
> rs: and rc: parameters. Please consider allowing a report to compile and
> publish with rs: and rc: parameters specified in the Jump to... links.
> Thanks,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
>