| Author |
Topic |
|
khalik
Constraint Violating Yak Guru
443 Posts |
 Posted - 2009-01-05 : 10:16:39
|
| Hi guysits been long time, i am back. i have case where i have queries are stored in table. i need to execute these qry and return the data. the max row which need to processed can be 100 which mean 100 set of data has to be return.simple way..(i dont want to)open a cursor for the rows in the tableget the query in variable exe variablecursor are bad. and i am not sure if i execute max 100 qry from one sp is it a good thing.i am using .net and SQL 2005.please feel free to give me tips or suggestion.ThanksKhalik======================================Ask to your self before u ask someone |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-05 : 10:22:00
|
| you mean queries are stored as column values? why is reason for that? |
 |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2009-01-05 : 10:22:27
|
| Why the queries are stored in the table, in the first place? Wouldn't it be good if related queries are stored in individual SPs and execute those SPs instead of queries?Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
|
khalik
Constraint Violating Yak Guru
443 Posts |
Posted - 2009-01-05 : 10:25:24
|
| hi HarshIts a role based system. based on the acess lel i populated the table with the simple query.sample Qry will be like 'Select * from table 1 where condition1='Val1' and condition2='Val2'i have many rows .. no point in creating those many sp.======================================Ask to your self before u ask someone |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-05 : 10:27:41
|
quote:
Originally posted by khalik
hi HarshIts a role based system. based on the acess lel i populated the table with the simple query.sample Qry will be like 'Select * from table 1 where condition1='Val1' and condition2='Val2'i have many rows .. no point in creating those many sp.======================================Ask to your self before u ask someone
shouldnt it be better to keep queries in sps as harsh pointed out and give execute rights of sps to only reqd people? |
|
|
|
khalik
Constraint Violating Yak Guru
443 Posts |
Posted - 2009-01-05 : 10:34:26
|
| when i say role i mean business roles. guys i have a system full in place please suggest some thign which can make my life easy. i cannot change the over model of the system..Let me put it with different example... example is not valid one. so please suggest why shd it not be like this.let assume that i have a huge form which have many data entry item with many master fill in dropdown. depending on the user roles i need to fill in the dropdown from master. i hope i have a simple example and kind request dont come up with suggestion why you have a single big form like soo...======================================Ask to your self before u ask someone |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-05 : 10:40:36
|
quote:
Originally posted by khalik
when i say role i mean business roles. guys i have a system full in place please suggest some thign which can make my life easy. i cannot change the over model of the system..Let me put it with different example... example is not valid one. so please suggest why shd it not be like this.let assume that i have a huge form which have many data entry item with many master fill in dropdown. depending on the user roles i need to fill in the dropdown from master. i hope i have a simple example and kind request dont come up with suggestion why you have a single big form like soo...======================================Ask to your self before u ask someone
cant that be controlled by means of access right mechanism. just associate the dropdown items to access right and also users to access right. so that based on who logs in, you can check his access right and list one those items which that access right is assigned to |
|
|
|
khalik
Constraint Violating Yak Guru
443 Posts |
Posted - 2009-01-05 : 10:45:25
|
| again we are going by the example.. forget abt the example understand the case if possible you can give a solution or else please dont kill the thread....======================================Ask to your self before u ask someone |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-05 : 10:50:44
|
quote:
Originally posted by khalik
again we are going by the example.. forget abt the example understand the case if possible you can give a solution or else please dont kill the thread....======================================Ask to your self before u ask someone
how do you think we can understand your case if you yourself are not able to explain us what it is, please understand that we don't know your system neither can read your mind. based on your explanation, i feel it can be done in a better approach. if you still believe you cant use it, at least try to explain your scenario so that we can understand. |
|
|
|
khalik
Constraint Violating Yak Guru
443 Posts |
Posted - 2009-01-05 : 11:01:09
|
| Let me put it simple no example..i have to execute simple qry's (1-100) by some mean i can find out which qry need to be execut. my .net code need to take all this in one dataset.what is the way to execute set of qry which may be in simple table or array. not using cursor======================================Ask to your self before u ask someone |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2009-01-05 : 12:47:53
|
| If I understood the question, you could conceivably do something like this:declare @sql nvarchar(max); set @sql = N'';select @sql = @sql + cast(YourQueryColumn + N';' as nvarchar(max)) from YourTable;exec sp_executesql @sql;YourQueryColumn is the column in YourTable where the queries are stored.However, this has problems - for example, it is open to SQL injection; the select query shown above does not guarantee the order in which the rows are concatenated even if you specified an order by clause (which would mean that in your .net application if you are using something like SqlDataReader.NextResult you may not get what you expect).So, if at all possible, I would do it differently, like visakh suggested. |
|
|
|
khalik
Constraint Violating Yak Guru
443 Posts |
Posted - 2009-01-06 : 05:50:14
|
| agreed sunita, but it has 2 problems 1. varchar max len 2. such qry are terribly slow. Comparatively cursors are much faster. I have tried both.======================================Ask to your self before u ask someone |
|
|
|
|
Execute Query stored in a table