December 15, 2006 at 8:02 am
I'm trying to pass a variable (Column name) to a stored procedure to retrieve that column name's values. I have never used case statements before and I'm having problems
So far I have
Alter PROCEDURE [dbo].[Get_TableValues]
@Value as varchar (100)
AS
Case
When 'Model_No'
Then Select Distinct Model_No FROM [IT].[dbo].[test]
When 'Proj_Engineer'
Then Select Distinct Proj_Engineer FROM [IT].[dbo].[test]
end
ideas?
CY
December 15, 2006 at 8:08 am
Alter PROCEDURE [dbo].[Get_TableValues]
@Value as varchar (100)
AS
begin
set nocount on
if @Value = 'Model_No'
Select Distinct Model_No FROM [IT].[dbo].[test]
if @Value = 'Proj_Engineer'
Select Distinct Proj_Engineer FROM [IT].[dbo].[test]
end
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/code to get the best help[/url]
- 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
December 15, 2006 at 8:12 am
Works like a champ - Thanks - CY
December 15, 2006 at 3:37 pm
What if you have more columns? Will you be Altering the procedure each time you want to pass another column name?
Try this..
Alter PROCEDURE [dbo].[Get_TableValues]
@Value as varchar (100)
AS
EXEC ('Select Distinct ' + @Value + ' FROM [IT].[dbo].[test]')
December 15, 2006 at 5:51 pm
Both suggestions are valid approachs and both would solve the problem outlined.
I would be weary with a solution like Ronald's, simply because it introduces risk of injection attack. The previous soluion is inflexiable, but has very rigidly defined regarding what is allowed.
You could do a simple test for legality by parsing @value and joining the results to syscolumns (and thus stripping out anything this is invalid).
Regarding the first solution, it's worth mentioning that when you have PROCs structured like that, it is very difficult for the query optimizer to cache a decent query plan. A plan that performs great for case 1 may perform miserably for case 2.
I don't mean to sound critical, both these options will work, but I thought I should bring up these points.
Out of interest, why are you passing column names as parameters anyway?
SQL guy and Houston Magician
December 15, 2006 at 10:54 pm
December 16, 2006 at 12:17 pm
I'm not saying that the design is right nor wrong. But this seems to be a case where the tool used to get the data out is not the right one. Why not simply create 2 distinct procedure and have the application call the correct proc (you could also have a master proc that calls the correct child procedure but I wouldn't use that in this case).
December 16, 2006 at 12:25 pm
If you remind yourself normalization rules you'll see the design is wrong.
No tools can help here.
_____________
Code for TallyGenerator
December 16, 2006 at 3:10 pm
I know there's something fishy here but again I never said the design was right. So what I'd like to see now is what solution mr helpfull can come up with more details than just redo the damn thing!
December 17, 2006 at 12:57 pm
How to fix the house not having proper basement?
There could be many suggestions about quick fixes and patches.
But none of them would make that house through the next spring season, unless you have done the basement right.
_____________
Code for TallyGenerator
December 18, 2006 at 4:52 am
Thanks for all the input. I wish I had control over the design however I do not. I was tasked to build a report writer that would show all the column names of a particular table than let the user pick which clolumns they would like to query against. The database in question is a legacy DB which was built before my time and I do not have access it.
Thnaks
CY
December 18, 2006 at 6:21 am
I C. I never had to do this but why not keep all the queries on the application side? You just have to make sure that the user can only select the list of columns (no manual input). And make sure no injection can occur with the where conditions. That way you will have all the control you need and still be safe.
December 18, 2006 at 6:36 am
Also you might find this read very helpfull!
http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=236&messageid=215144&p=1
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply