Stored procedure: can I pass a field name as parameter?

  • Thread starter Thread starter Klaus
  • Start date Start date
K

Klaus

Dear peoples
following problem kept me searching for a few hours, but still didn't find solution

Want to retrieve data with a stored procedure from a SQL server DB
Also want to be able to filter the data
But on top of supplying a value (@FilterValue), I also want to supply the field name (@FieldName) that should be used in the filter

Following is the sproc-code

ALTER PROCEDURE proc_Orders_Find_Filtered_Loa


@FieldName nvarchar(50)
@FilterValue nvarchar(100


A

SELECT ....
FROM tbl...
WHERE (@FieldName = @FilterValue

So what would be a correct syntax for @FieldName = @FilterValu

Obviously I do make sure that the @Fieldname parameter (= the field that I want to filter) exists in the query
 
Klaus said:
Dear peoples,
following problem kept me searching for a few hours, but still didn't find
solution:

Want to retrieve data with a stored procedure from a SQL server DB.
Also want to be able to filter the data.
But on top of supplying a value (@FilterValue), I also want to supply the
field name (@FieldName) that should be used in the filter.

Following is the sproc-code:

ALTER PROCEDURE proc_Orders_Find_Filtered_Load

(
@FieldName nvarchar(50),
@FilterValue nvarchar(100)
)

AS

SELECT .....
FROM tbl...
WHERE (@FieldName = @FilterValue)


So what would be a correct syntax for @FieldName = @FilterValue

Obviously I do make sure that the @Fieldname parameter (= the field that I
want to filter) exists in the query.
Click to expand...

You can't do that. T-SQL requires that the field name is mentioned in the
code.

FB
 
Yes you can

EXEC ('SELECT FIELD_X FROM TABLEX ORDER BY ' + @SortField )

note....this is leaving your code open to SQL Injection attaks and I
wouldn't personally recommend it unless you make sure to do the appropriate
escaping on @SortField


Klaus said:
Dear peoples,
following problem kept me searching for a few hours, but still didn't find solution:

Want to retrieve data with a stored procedure from a SQL server DB.
Also want to be able to filter the data.
But on top of supplying a value (@FilterValue), I also want to supply the
Click to expand...
field name (@FieldName) that should be used in the filter.
Following is the sproc-code:

ALTER PROCEDURE proc_Orders_Find_Filtered_Load

(
@FieldName nvarchar(50),
@FilterValue nvarchar(100)
)

AS

SELECT .....
FROM tbl...
WHERE (@FieldName = @FilterValue)


So what would be a correct syntax for @FieldName = @FilterValue

Obviously I do make sure that the @Fieldname parameter (= the field that I
Click to expand...
want to filter) exists in the query.
 
Back
Top