Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Resender
Starting Member
3 Posts |
 Posted - 2010-12-10 : 09:39:44
|
| Hello I got a stored procedure called DynamicSQL2This procedure has the following parameters:*SelectedColumns: The columns the user wants, this is passed along as a varchar(max) with ',' as delimiter*Database: The database the user selected*Table: The name of the table in the form schema.table_name*Rows: number of rows to see*Filter Fields: The columns the user wants to filter upon, this is passed along as a varchar(max) with ',' as delimiter*Filtervalues: The are the values needed to be applied to the filterfields, this is passed along as a varchar(max) with ';' as delimiter.The filterfields and filtervalues are delimeted by a user defined function, which returns me the delimited values back in following table form: ID FilterField FilterFieldDataType FilterValueA udf called DynamicFilter uses the FilterFieldDataType, FilterField and FilterValue to build a string that will become the where clause at the end of the stored procedure DynamicSQL2.(We get FilterFieldDataType by joining the delimited resultset with the INFORMATION_SCHEMA.COLUMNS)The delimiter function can also be applied on the SelectedColumns.Now I need A)To get the size in bytes that the result set will takeB)Limit the result set that will be returned, so that the resultset will never be larger then a certain amount of kb. |
|
|
BruceT
Yak Posting Veteran
78 Posts |
Posted - 2010-12-10 : 11:14:03
|
| An approach. Use the system tables to get the sizes of the columns in your query (you will need to parse the passed in parameters to get that info of course), from which you should be able to calculate the row size, from which you should be able to calculate how many rows will fit within your kb limit and from that make your query to something like "select top x from....where....."where x is the number of rows that will fit in your kb limit. |
 |
|
|
|
|
|
possible to put a (default) limit (kb)on a dataset