| Author |
Topic |
|
a_r_satish
Yak Posting Veteran
84 Posts |
 Posted - 2006-03-24 : 04:22:00
|
| Hi Everyone,For my application, i am writing a generalized Sp for the Select Stmt.I have started with as shown below,Create procedure proSelect@TabName varchar(1000),@ColName varchar(1000),@ConName varchar(1000)AsBegin Declare @str Varchar(8000) If @ColName = '' Set @ColName = '* ' else Set @ColName = @ColName + ' ' Set @str = 'Select ' +@ColName+ 'From ' +@tabname If @ConName <> '' Set @str = @str + ' Where '+ @ConName exec (@str)End;I wan to more generalize it.. so that all the functionalities of select stmt can be accomplished with this sp...Can anyone help ???Thanks in advance...one more small doubt(personal): All these days i was a starting member of this forum,but today it has changed to 'Yak Veteran Posting'.. What does that mean.. If not to be disclosed in forum, Do mail me at satishr@kggroup.comRegards,satish.r"Known is a drop, Unknown is an Ocean" |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-03-24 : 04:35:24
|
sp_executesql is preferred over exec() as it allow passing of parameters and as well as encourage the reuse of query plan.What do you want to use a dynamically generated select statement ? I don't think there is much advantage of doing this."All these days i was a starting member of this forum,but today it has changed to 'Yak Veteran Posting'.. What does that mean"You have been promoted to Yak Veteran Posting. Congratulation !  . Also refer to this
KHChoice is an illusion, created between those with power, and those without.Concordantly, while your first question may be the most pertinent, you may or may not realize it is also the most irrelevant |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-03-24 : 04:47:24
|
| What is the need of generalising the Select?MadhivananFailing to plan is Planning to fail |
|
|
|
a_r_satish
Yak Posting Veteran
84 Posts |
Posted - 2006-03-24 : 05:08:14
|
| The think is, In my business layer we are using a lot of queries. Most of them are select stmt and displaying the result in front end.once i am able to create such a generalized Select Stmt Sp, then it would reduce a huge amount of code.. As i would be passing the TableName and columnName and Conditions from my Front End Itself.Ie ... fnRetrUser("tbMstUser","vchUserName","bitActive = 1") => Once i specify like this, then i can make use of this generalized Sp and fetch the result. Instead of creating the Sp for each and every select stmt seperately...I hope it makes some sense...Regards,satish.r"Known is a drop, Unknown is an Ocean" |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2006-03-24 : 08:09:41
|
| Your system is potentially open to abuse. You may be better off actually writing the code, even though it might take more effort.-------Moo. :) |
|
|
|
a_r_satish
Yak Posting Veteran
84 Posts |
Posted - 2006-03-25 : 04:32:37
|
quote:
Originally posted by mr_mist
Your system is potentially open to abuse. -------Moo. :)
May i know the reason. I thought it would be better option. Please don deprive me with the reasons..Regards,satish.r"Known is a drop, Unknown is an Ocean" |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2006-03-25 : 09:45:24
|
quote:
Originally posted by a_r_satish
quote:
Originally posted by mr_mist
Your system is potentially open to abuse. -------Moo. :)
May i know the reason. I thought it would be better option. Please don deprive me with the reasons..Regards,satish.r"Known is a drop, Unknown is an Ocean"
exec proSelect 'master.dbo.syslogins', 'name,*', '1=1; print ''drop database xxx'''You should never let arbitrary dynamic sql be sent to the database.rockmoose |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2006-03-25 : 12:25:47
|
http://www.4guysfromrolla.com/webtech/061902-1.shtmlGo with the flow & have fun! Else fight the flow  |
|
|
|
a_r_satish
Yak Posting Veteran
84 Posts |
Posted - 2006-03-27 : 01:37:53
|
| Thanks a lot friendz. i had limited my scope with the windows based application. But know after reading the Article(link by spirit1) i understood the problems.. Thanks a lotRegards,satish.r"Known is a drop, Unknown is an Ocean" |
|
|
|
|
generalized SP for Select Stmt : Better option