Dynamically selected columns with column switch option

Hi,
my requirement is to dynamically select certain columns from a table
and depending on a flag some columns must be swapped. This is easier
explained with the following statements one can paste into Query
Analyzer:
declare @.sql nvarchar(4000), @.cols nvarchar (500)
set @.cols = '
v1 = case when sel=1 then
v1a else v1b end,
v2 = case when sel=1 then v2a
else v2b end
'
create table ##temp_test (
v1a int,
v1b int,
v2a varchar(15),
v2b varchar(15),
sel bit
)
insert into ##temp_test (v1a, v1b, v2a, v2b, sel)
values (1, 2, 'a1', 'b1', 0)
insert into ##temp_test (v1a, v1b, v2a, v2b, sel)
values (3, 4, 'a2', 'b2', 1)
set @.sql = 'select ' + @.cols + 'from
##temp_test'
exec sp_executesql @.sql
drop table ##temp_test
Result:
v1 v2
-- --
2 b1
3 a2
This fits bots requirements: @.cols (which is stored in a table in the
real world application) holds the column names to be selected, and it
holds it in a way which also enables to switch between the a and b
version of the columns via the CASE statements. The performance is OK.
My problem: the real world query is a lot more complex, ##temp_test is
actually a table with about 80 columns and there are a lot of other
tables joined in.
Since I am doomed to not use more than 4000 characters for the dynamic
sql part (not using sp_executesql results in a huge performance
penalty in my scenario) this approach in the end works find without
all the CASE-ELSE-END stuff, but when I insert the CASE stuff, the
@.cols variable length will grow by factor 2-3, typically from around
1000 - 2000 chars to about 2000 - 6000 chars.
That's the end of the sp_executesql approach.
Maybe our design is wrong in the first place. We have tried to
alternate between the columns by using UNION to a View that contains
the alternate column, but ended up in quiet a performance hit with
more complex queries plus sometimes the SORT function would not work
anymore and the like.
Maybe I can shorten the
v1 = case when sel=1 then
v1a else v1b end
part somehow? Or in the best case someone knows a similar approach...
just better.
TIA for any comments!
Regards
DChttp://www.sommarskog.se/dynamic_sql.html
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"DC" <dc@.upsize.de> wrote in message
news:5b7bac12.0502250228.4d94de6c@.posting.google.com...
> Hi,
> my requirement is to dynamically select certain columns from a table
> and depending on a flag some columns must be swapped. This is easier
> explained with the following statements one can paste into Query
> Analyzer:
>
> declare @.sql nvarchar(4000), @.cols nvarchar (500)
> set @.cols = '
> v1 = case when sel=1 then
> v1a else v1b end,
> v2 = case when sel=1 then v2a
> else v2b end
> '
> create table ##temp_test (
> v1a int,
> v1b int,
> v2a varchar(15),
> v2b varchar(15),
> sel bit
> )
> insert into ##temp_test (v1a, v1b, v2a, v2b, sel)
> values (1, 2, 'a1', 'b1', 0)
> insert into ##temp_test (v1a, v1b, v2a, v2b, sel)
> values (3, 4, 'a2', 'b2', 1)
> set @.sql = 'select ' + @.cols + 'from
> ##temp_test'
> exec sp_executesql @.sql
> drop table ##temp_test
>
> Result:
> v1 v2
> -- --
> 2 b1
> 3 a2
> This fits bots requirements: @.cols (which is stored in a table in the
> real world application) holds the column names to be selected, and it
> holds it in a way which also enables to switch between the a and b
> version of the columns via the CASE statements. The performance is OK.
> My problem: the real world query is a lot more complex, ##temp_test is
> actually a table with about 80 columns and there are a lot of other
> tables joined in.
> Since I am doomed to not use more than 4000 characters for the dynamic
> sql part (not using sp_executesql results in a huge performance
> penalty in my scenario) this approach in the end works find without
> all the CASE-ELSE-END stuff, but when I insert the CASE stuff, the
> @.cols variable length will grow by factor 2-3, typically from around
> 1000 - 2000 chars to about 2000 - 6000 chars.
> That's the end of the sp_executesql approach.
> Maybe our design is wrong in the first place. We have tried to
> alternate between the columns by using UNION to a View that contains
> the alternate column, but ended up in quiet a performance hit with
> more complex queries plus sometimes the SORT function would not work
> anymore and the like.
> Maybe I can shorten the
> v1 = case when sel=1 then
> v1a else v1b end
> part somehow? Or in the best case someone knows a similar approach...
> just better.
> TIA for any comments!
> Regards
> DC|||I don't understand why you want to use dynamic SQL for this. Where is
the string in @.cols generated from? You say it comes from a table but
why put it in a table at all? Why can't you define views for these
different views of the data?
David Portas
SQL Server MVP
--|||>> my requirement is to dynamically select certain columns from a table
and depending on a flag some columns must be swapped. <<
You might want to get any book on **basic software engineering** and
look up the concept of cohesion in a code module.|||Thank you! Can you recommend a good book on this topic? I probably
focussed too much on the loose coupling aspects? I support your claim
for the art of programming, but then again: it's only T-SQL. Hacky
stuff! Wouldn't a real programmer avoid messing around with SQL anyway?|||Thanks David, views or pre-generated stored procs are an option. We are
in the process of upgrading a life application though, and wanted to
integrate this additional requirement (the CASE req.) with the least
effort possible.|||Thank you Vinod, great article with a wealth of dynamic sql info. Does
not provide an exact solution, but pointed out that using EXEC shoud
not make as much of a difference as I experienced.|||> Wouldn't a real programmer avoid messing around with SQL anyway?
Sure. Why go looking for a new peg when you can just keep bashing that
square one into a round hole? After all, tables are only arrays aren't
they?
David Portas
SQL Server MVP
--|||On 25 Feb 2005 05:52:23 -0800, David Portas wrote:

>Sure. Why go looking for a new peg when you can just keep bashing that
>square one into a round hole? After all, tables are only arrays aren't
>they?
Hi David,
A "real" programmer - isn't that a guy who needs nothing but 8 switches
and 8 LED's to program the 'puter? Who needs all that modern assembler
language mnemonics anyway, huh?
(Hmmm - am I showing my age now <g> )
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||>> Can you recommend a good book on this topic? <<
Any of the classic by Yourdon, Constantine, DeMarco or Gane & Sarson
are a good place to start.
They would avoid dynamic SQL and do the order of presentation of the
columns in the front end. I have become a fan of the idea that one
team in the shop handles the database and writes all the SQL for
everyone, and the application developers make requests to that team.

Dynamically define the height of a chart

Current Situation:
I have a "Stacked Bar" chart and it has a certain ammount of lines depending on its data source. Sometimes if there are too many lines, only every other label shows up for that line on the left hand side (See image).

What I need:
Basically I need a solution that fixes my current situation. My first thouts were to dynamically size the height of the chart, but I haven't had much luck doing that. Also, I have tried to find properties for the char item that might let it grow.

If anyone has any suggestions, or know where I can get more information on this, I would appreciate it very much. Thanks in advance.

It looks like you have just a list of items to show in the chart.

Here is one approach:
* Add a list to the report, put the chart inside the list
* Add a (detail) group to the list with the following grouping expression:
=Int((RowNumber(Nothing)-1)/15)
This should result in the desired grouping of 15 items per (repeating) list instance.

Another approach is to define multiple charts of different sizes and use the Visibility.Hidden property on the chart to dynamically hide all charts but one. Note: you can use =CountRows("DatasetName") to determine the number of rows in a particular dataset and hide one chart e.g. if the total number of dataset rows is greater than 20: Visibility.Hidden property setting: =CountRows("DatasetName") > 20

-- Robert

|||Thanks for the reply. This seems like a good solution, however other problems were present that I didn't notice before. If you look at the x-axis of my graph it too does not display the labels correctly. My boss would like me to now explore using image creation on the fly to possibly fix this problem. Do you think that using a list would fix my problem with the x-axis?|||

You didn't explain why you think the x-axis labels are displayed incorrectly - but if the problem is that lots of data is shown in one chart and the x-axis looks crowded, splitting the underlying dataset into multiple charts will help.

-- Robert

Dynamically change the SelectCommand property

Hello

I have a gridview that I use on a products page, my problems is certain products have different attributes that I would like to display.

Therefore what I would like to do is change the SelectCommand property to my SQLDatasource depending on the querystring that is passed.

For instance in my page load event I would have a CASE statement with numerous SQLString Variables.

Here is the current coding for my datasource

<asp:SqlDataSourceID="SqlDataSource2"runat="server"ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

SelectCommand="SELECT PS.ProductSizeMM AS [Coupling Size], PS.ProductWallThickness AS [To Suit], PS.Cost AS [Price], PS.Sold_By AS [Sold by] FROM tblProduct AS P INNER JOIN tblProductSize AS PS ON P.ProductCode = PS.ProductCode WHERE (P.ProductDescription = @.ProductDescription) ORDER BY PS.Sorter">

<SelectParameters>

<asp:QueryStringParameterName="ProductDescription"QueryStringField="ProductDescription"/>

</SelectParameters>

</asp:SqlDataSource>

I have tried declaring a string variable in my page load event (SQLString) then setting the

SelectCommand="SQLString" but this causes a syntax error

Exception Details:System.Data.SqlClient.SqlException: Incorrect syntax near 'SQLString'.

Any help would be greatly appreicated!!

Hi,

a cleaner way to use would be by doing it in the SQLDataSource's Selecting event:http://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.sqldatasource.selecting.aspx.

Grz, Kris.

|||

You can't do that declaratively within your SqlDataSource. Instead, you'll need to set it within the code-behind like so:

SqlDataSource2.SelectCommand = SQLString

|||

Thanks it worked a treat

Dynamic SQL issue

While I realize there are certain inherent pitfalls with Dynamic SQL, I'm
curious what is causing this so I can recognize it in the future. Anyway,
we have a stored procedure. The stored procedure concatenates a string that
is ultimately executed via EXEC(). The stored procedure is called from PHP
and is returning different results than what I'm seeing when I run it in
query analyzer. For some reason I'm getting a leading comma in PHP that I'm
not seeing in Query Analyzer in the "City_State" field. Now, if I change
City_State to be hardcoded to something for sake of example, like City_State
= ''something'', the comma goes away. But it doesn't make any sense that a
UDF nested in a Stored Proc would somehow return a different value when it's
called via PHP as opposed to Query Analyzer. ...does it? Thanks a billion.
set @.x = 'SELECT DISTINCT TOP 100 PERCENT
List.List_ID,
City_State = dbo.udf_AddressCityStateZip('''', List.List_City,
List.List_State, SUBSTRING(List.List_Zip, 1, 5)),
List.List_Latitude, List.List_Longitude
WHERE List_ID = 5'
EXEC (@.x)It could be how you are pulling your results out of the resultset, rather
than the stored procedure. That is, unless PHP is passing in a value for
'address' that is a single space, or something other than what you expect.
Also, where is the "FROM" clause, or is this not all the code we would need
to help out?
> While I realize there are certain inherent pitfalls with Dynamic SQL,
> I'm
> curious what is causing this so I can recognize it in the future.
> Anyway,
> we have a stored procedure. The stored procedure concatenates a
> string that
> is ultimately executed via EXEC(). The stored procedure is called
> from PHP
> and is returning different results than what I'm seeing when I run it
> in
> query analyzer. For some reason I'm getting a leading comma in PHP
> that I'm
> not seeing in Query Analyzer in the "City_State" field. Now, if I
> change
> City_State to be hardcoded to something for sake of example, like
> City_State
> = ''something'', the comma goes away. But it doesn't make any sense
> that a
> UDF nested in a Stored Proc would somehow return a different value
> when it's
> called via PHP as opposed to Query Analyzer. ...does it? Thanks a
> billion.
> set @.x = 'SELECT DISTINCT TOP 100 PERCENT
> List.List_ID,
> City_State = dbo.udf_AddressCityStateZip('''', List.List_City,
> List.List_State, SUBSTRING(List.List_Zip, 1, 5)),
> List.List_Latitude, List.List_Longitude
> WHERE List_ID = 5'
> EXEC (@.x)
>|||I had considered that, but they use the same process pretty much everywhere
and haven't had this problem before. That isn't to say it's not the
problem. Sorry for missing the FROM clause, I had just trimmed it to make
it more readable (parts that were 100% not the issue). Standard FROM before
the WHERE...FROM Tbl_List AS List I believe. When I run it in .NET and use
the results to populate a DataSet, I don't get the leading comma either.
"Andrew Backer" <awbacker@.gmail.com> wrote in message
news:1e2aa300c5b48c9bd137b610158@.news.microsoft.com...
> It could be how you are pulling your results out of the resultset, rather
> than the stored procedure. That is, unless PHP is passing in a value for
> 'address' that is a single space, or something other than what you expect.
> Also, where is the "FROM" clause, or is this not all the code we would
> need to help out?
>
>> While I realize there are certain inherent pitfalls with Dynamic SQL,
>> I'm
>> curious what is causing this so I can recognize it in the future.
>> Anyway,
>> we have a stored procedure. The stored procedure concatenates a
>> string that
>> is ultimately executed via EXEC(). The stored procedure is called
>> from PHP
>> and is returning different results than what I'm seeing when I run it
>> in
>> query analyzer. For some reason I'm getting a leading comma in PHP
>> that I'm
>> not seeing in Query Analyzer in the "City_State" field. Now, if I
>> change
>> City_State to be hardcoded to something for sake of example, like
>> City_State
>> = ''something'', the comma goes away. But it doesn't make any sense
>> that a
>> UDF nested in a Stored Proc would somehow return a different value
>> when it's
>> called via PHP as opposed to Query Analyzer. ...does it? Thanks a
>> billion.
>> set @.x = 'SELECT DISTINCT TOP 100 PERCENT
>> List.List_ID,
>> City_State = dbo.udf_AddressCityStateZip('''', List.List_City,
>> List.List_State, SUBSTRING(List.List_Zip, 1, 5)),
>> List.List_Latitude, List.List_Longitude
>> WHERE List_ID = 5'
>> EXEC (@.x)
>

Dynamic SQL Cells

How can I make the values for certain cells in a table in a database show the day a folder was created?

On the same note, How can I make other values in other cells change (say the values 101 through 200 had to change to 1-100)?

For the first part, you would have to explicily 'store' the "day a folder was created" in the field.

On the second part, if you wanted to subtract 100 from a current field value, and replace the current field value with that subtracted value, you would use a query similar to this one.

Code Snippet

UPDATE MyTable

SET MyColumn = ( MyColumn - 100 )

WHERE MyColumn BETWEEN 101 and 200

|||How am I supposed to "store" the day a folder was created in a field?|||

Well, somewhere, somehow, the folder was created. It was not created in SQL Server. SQL Server does not know anything about file folders UNLESS we tell give it a value and tell it to store the data.

So, unless your 'process' that creates the folders is smart enough to add that data to SQL Server, it will be left up to you to determine the creation date, and to add that creation date to a field in a table.

You haven't provided enough information to get the type of help that you are seeking. Perhaps if you were to provide a better description of what is happening and what you desire to accomplish, someone here 'may' be able to assist you.