November 14, 2025 at 1:21 pm
I have a need where I need to pass a field name to a Stored Procedure then lookup information on it. Any insight would be greatly appreciated!
IE:
Declare @Field char(50)
Set @field = 'fpartno'
Select @field from m2mdata01..inmastx
November 14, 2025 at 4:50 pm
altough not a best practice, dynamic sql will be about the only way to help you out
ref: dos-and-donts-of-dynamic-sql
btw: Where is the where clause of your query ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data and code to get the best help
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 14, 2025 at 7:47 pm
Thanks. I know that way but thought there might be an easier way. Not to say this is hard.
November 14, 2025 at 10:21 pm
another approach, although will become messy if there are a lot of columns, would be to have 1 "master" stored procedure that takes the parameter (such as fpartno in your example), then a bunch of IF statements for if input = fpartno, then run stored procedure GetFPARTNO. If input = gpartno, then run stored procedure GetGPARTNO and so on.
If it is only a handful of potential columns, it'd work and no dynamic SQL. If it's hundreds of columns OR if the columns change frequently, then this will be too much admin overhead of an approach.
But just wanted to throw out another option on how to handle this.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
November 17, 2025 at 9:38 pm
You could also use Brian Gale's approach and use a proc to generate all the code for you. Personally I would stick with dynamic SQL, but the other is an option if you prefer that.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 20, 2025 at 9:33 am
I have a need where I need to pass a field name to a Stored Procedure then lookup information on it. Any insight would be greatly appreciated!
IE:
Declare @Field char(50)
Set @field = 'fpartno'
Select @field from m2mdata01..inmastx
You can’t directly use a variable as a column name in a SELECT statement in SQL Server because variables are resolved at runtime, while column names are resolved at compile time. So when you do something like:
SELECT @Field FROM m2mdata01..inmastx;
SQL Server treats @Field as a value, not as a column reference. To make the column name dynamic, you need to use dynamic SQL, which builds the query as a text string and then executes it. A safe way to do this is:
DECLARE @Field sysname = 'fpartno';
DECLARE @sql NVARCHAR(MAX);
SET @sql = 'SELECT ' + QUOTENAME(@Field) + ' FROM m2mdata01..inmastx;';
EXEC sp_executesql @sql;
The important part here is QUOTENAME(), which protects against SQL injection and ensures the value is treated as a valid column name. If you only have a small set of known columns, a CASE expression could work as an alternative, but for true dynamic column selection, dynamic SQL is the right approach.
November 20, 2025 at 7:12 pm
I personally prefer the dynamic SQL IF you are working with changing table structure as then if you add or remove a column you don't need to add or remove a stored procedure and update the "master" stored procedure to handle the new/removed column.
BUT if things are going to be static and never changing, I like the stored procedure approach as it is a bit easier to tune plus you can reuse the plan cache. Dynamic SQL doesn't always reuse the plan cache. It depends on if the dynamic SQL is simple enough or not. Plus if the query is being run a lot of times, you want to avoid recompiles. If it is run infrequently, the recompile may not be a big deal. Stored procedures are pre-compiled and as long as the parameter sniffing picked good defaults, you will be fine.
Also, if you plan on potentially selecting multiple columns, the stored procedure approach is going to be a pain. If you have n columns, you have 2^n stored procedures to make. 1 column means 2 stored procedures, 2 columns means 4 stored procedures, 3 columns means 8 stored procedures, and so on.
Mind you with dynamic SQL and multiple columns in the parameter, you have the fun with quotename not working in that case. You'll need to do your own version of that function (if you plan to reuse it, or you can just have it in a SET/SELECT statement) which does a bunch of REPLACE's. It isn't too hard to do AND it will scale better than the stored procedure approach, but it's still work and something to maintain.
In general, I try to avoid dynamic SQL as well as situations like this by designing the database tables and application so that the application shouldn't need special cases like this.
As a third option - do this at the application layer instead of the database layer. Have the application pull in all columns from the table (via stored procedure) and then have the application decide which columns to present based on what the user selects. Advantage to this is that changing the column the user is looking at will be a LOT faster as you are pulling from cache (likely memory, but may have paged to disk) and not from network/SQL for each change you are doing. Plus, adding/removing columns being presented at the application side is then trivial to do. Downside is that the user will not have "realtime" data - it'll be cached data unless you do background refreshes of the data which is entirely possible to do - put the data refresh on it's own thread and store the value in a secondary "data" variable and when the user has an "idle" moment in the app, change the pointer from the display variable to the same as the secondary data variable then have the thread pull data into the first "data" variable and repeat as frequently as you need without causing interruptions to the user. If the "idle" moment doesn't happen until after the next requested refresh cycle, refresh the data from the DB and wait again for an idle moment. If the data refresh to screen is quick, you can even pause the drawing, swap things, and resume drawing and the user will see a slight flicker as the values change. I recommend pausing the redraw only if the data set is large as it can cause GUI interruptions which can upset users. Just make sure to read the sort status, selected row, visible rows, column/row sizes, and visible columns prior to swapping things so you can be sure the user isn't losing their place or having things jump around while they are trying to work.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply