how do I assign a string to a parameter Im passing to a select statement?

Hello,

I'm needing to pass a variable length number of values to a select statement so I can populate a result list with items related to all the checkboxlist items that were selected by the user. for example, the user checks products x, y and z, then hits submit, and then they see a list of all the tests they need to run for each product.

I found a UDF that parses a comma delimited string and puts the values into a table. I learned how to do this here:

http://codebetter.com/blogs/darrell.norton/archive/2003/07/01/361.aspx

I have a checkboxlist that I'm generating the string from, so the string could look like this: "1,3,4,5,7" etc.

I added the function mentioned in the URL above to my database, and if I understand right, I should be able to pass the table it creates into the select statement like so:

WHERE (OrderStatus IN ((select value from dbo.fn_Split(@.StatusList,','))) OR @.StatusList IS NULL)

but now I don't know how to assign the string value to the parameter, say to '@.solution_id'.

my current select statement which was generated by Visual Studio 2005 looks like this:

SELECT [test], [owner], [date] FROM [test_table] WHERE ([solution_ID] = @.solution_ID)

...but this only pulls results for the first item checked in the checkboxlist.

Does anyone know how this is done? I'm sure it's simple, but I'm new to ASP .NET so any help would be greatly appreciated.

hi

First make sure you have createddbo.fn_Split .

SELECT [test], [owner], [date]FROM [test_table]WHERE ([solution_ID]IN ((select valuefrom dbo.fn_Split(@.solution_ID,',')))OR @.solution_IDISNULL)

I am not sure "OR @.solution_IDISNULL" should be added,you have to decide it according to your logic.

You are required to pass @.solution_ID to the statement(1,3,4,6 etc) then you can get corresponding test.

Hope this helps.

|||

Thanks for your response. If I'm following you, I do understand that I need to pass @.solution_ID to the select statement like you showed. I have a string of values that I created from iterating through CheckBoxList to find selected boxes. My question is, how do I assign the value of this string to @.solution_ID?

Regards,

Daniel

|||

Assume you have checkboxlist Check1, using following code to get @.solution_ID :

for (int i = 0; i < Check1.Items.Count; i++)
{
if (Check1.Items[i].Selected)
{
// List the selected items
solution_ID = solution_ID + Check1.Items[i].Text;
solution_ID = solution_ID +",";
}
}

Then connect with DB:

SqlCommand sqlcmd = new SqlCommand("SELECT [test], [owner], [date]FROM [test_table]WHERE
([solution_ID]IN ((select valuefrom dbo.fn_Split(@.solution_ID,',')))OR @.solution_IDISNULL)", sqlconn);
sqlcmd.Parameters.AddWithValue("@.solution_ID",solution_ID);
sqlconn.Open();
SqlDataReader sdr = sqlcmd.ExecuteReader();

.............

You 'd bette put bold sql script into a stored procedure.

hope this helps.

How do I access sqldatasource data values in code behind?

How do I accesssqldatasource data values in code behind and bind them to strings or texboxes etc. as oposed to using Eval in the markup?

I can create a new database connection, but I would like to use the data values from the autogenerated sqldatasource control

Many thanks,

Here is one way. It simply creates a table and adds the rows and columns from the data source

<%@.PageLanguage="C#" %>

<!DOCTYPEhtmlPUBLIC"-//W3C//DTD XHTML 1.0 Transitional//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<scriptrunat="server">

protectedvoid Page_Load(object sender,EventArgs e)

{

Table t =newTable();

PanelData.Controls.Add(t);

System.Data.DataView dv = (System.Data.DataView)ds.Select(newDataSourceSelectArguments());

for (int rowIndex = 0; rowIndex < dv.Count; rowIndex++)

{

TableRow tr =newTableRow();

t.Rows.Add(tr);

System.Data.DataRow dr = dv[rowIndex].Row;

for (int colIndex = 0; colIndex < dr.Table.Columns.Count; colIndex++)

{

TableCell tc =newTableCell();

tr.Cells.Add(tc);

tc.Text = dr[colIndex].ToString();

}

}

}

</script>

<htmlxmlns="http://www.w3.org/1999/xhtml">

<headrunat="server">

<title>Untitled Page</title>

</head>

<body>

<formid="form1"runat="server">

<asp:PanelID="PanelData"runat="server"/>

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

SelectCommand="SELECT ProductID, ProductName FROM Products"></asp:SqlDataSource>

</form>

</body>

</html>

How do a pass a 'Where' string clause to a stored procedure

I need to be able to construct a SELECT statement in a stored procedure and
concatenate a CSV list of values passed as an input parm to the stored
procedure. How can I do this without declaring a variable for the Select,
then executing it. I need to use this with Reporting Services so unless the
Select fields are available in the query, RS won't work.See
http://www.sommarskog.se/dyn-search.html
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Kitty" <Kitty@.discussions.microsoft.com> wrote in message
news:B0D87B12-BDCB-488E-89C2-F5D713DFE80E@.microsoft.com...
>I need to be able to construct a SELECT statement in a stored procedure and
> concatenate a CSV list of values passed as an input parm to the stored
> procedure. How can I do this without declaring a variable for the Select,
> then executing it. I need to use this with Reporting Services so unless
> the
> Select fields are available in the query, RS won't work.|||See
http://www.sommarskog.se/arrays-in-sql.html
http://www.users.drew.edu/skass/SQL...unction.sql.txt
The outline is like this:
select stuff
from T
join ListToTableFunction(@.myList) as Items
on Items.Item = T.Item
or with the logic of ListToTableFunction directly
in the non-dynamic query. <>
Steve Kass
Drew University
Kitty wrote:

>I need to be able to construct a SELECT statement in a stored procedure and
>concatenate a CSV list of values passed as an input parm to the stored
>procedure. How can I do this without declaring a variable for the Select,
>then executing it. I need to use this with Reporting Services so unless th
e
>Select fields are available in the query, RS won't work.
>|||>> I need to be able to construct a SELECT statement in a stored
procedure and concatenate a CSV list of values passed as an input parm
to the stored procedure. <<
No, you need to learn about coupling, cohesion and the basics of
software engineering. You are supposed to know what you are doing
before you do it. The idea of doing code on the fly is a violation of
everything you should have been taught in a freshman CS course.

How could I use row as columns?

create table t2
(
umc varchar(20),
outdate datetime,
outnumber int
)
insert t2 values (1,'2005-2-5',1)
insert t2 values (2,'2005-2-5',1)
insert t2 values (2,'2005-2-6',1)
insert t2 values (3,'2005-2-5',2)
insert t2 values (3,'2005-2-6',1)
insert t2 values (4,'2005-2-7',1)
I hope the result to be
(2005-2-5,2005-2-6,2005-2-7 is column name now)
2005-2-5 2005-2-6 2005-2-7
1 1 0 0
2 1 1 0
3 2 1 0
4 0 0 1
Can I just compose it with SELECT statement?First you have to select distinct dates into a cursor,
than select from the table left outer join each date where date from the
table = date of the column.
"XXY" <xxy02021@.NOSPAM.163.com> wrote in message
news:eUkwCXNEFHA.2508@.TK2MSFTNGP09.phx.gbl...
> create table t2
> (
> umc varchar(20),
> outdate datetime,
> outnumber int
> )
> insert t2 values (1,'2005-2-5',1)
> insert t2 values (2,'2005-2-5',1)
> insert t2 values (2,'2005-2-6',1)
> insert t2 values (3,'2005-2-5',2)
> insert t2 values (3,'2005-2-6',1)
> insert t2 values (4,'2005-2-7',1)
> I hope the result to be
> (2005-2-5,2005-2-6,2005-2-7 is column name now)
> 2005-2-5 2005-2-6 2005-2-7
> 1 1 0 0
> 2 1 1 0
> 3 2 1 0
> 4 0 0 1
> Can I just compose it with SELECT statement?
>|||http://aspfaq.com/show.asp?id=2462
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"XXY" <xxy02021@.NOSPAM.163.com> wrote in message
news:eUkwCXNEFHA.2508@.TK2MSFTNGP09.phx.gbl...
> create table t2
> (
> umc varchar(20),
> outdate datetime,
> outnumber int
> )
> insert t2 values (1,'2005-2-5',1)
> insert t2 values (2,'2005-2-5',1)
> insert t2 values (2,'2005-2-6',1)
> insert t2 values (3,'2005-2-5',2)
> insert t2 values (3,'2005-2-6',1)
> insert t2 values (4,'2005-2-7',1)
> I hope the result to be
> (2005-2-5,2005-2-6,2005-2-7 is column name now)
> 2005-2-5 2005-2-6 2005-2-7
> 1 1 0 0
> 2 1 1 0
> 3 2 1 0
> 4 0 0 1
> Can I just compose it with SELECT statement?
>|||SELECT umc,
SUM(CASE WHEN DATEDIFF(DAY,@.dt,outdate)=0 THEN outnumber ELSE 0 END),
SUM(CASE WHEN DATEDIFF(DAY,@.dt,outdate)=1 THEN outnumber ELSE 0 END),
SUM(CASE WHEN DATEDIFF(DAY,@.dt,outdate)=2 THEN outnumber ELSE 0 END)
FROM T2
WHERE outdate >= @.dt
AND outdate < DATEADD(DAY,3,@.dt)
GROUP BY umc
Column names in a query are fixed so dynamic SQL would be required to
change the names based on the data. That shouldn't really be a problem
though. It should be easy enough to display different column names in
your client application.
David Portas
SQL Server MVP
--|||Nadim,
Thanks so much and that's what I want, however, is it possible for you to
show me some sample codes based on my DDL?
yours, XXY
"Nadim Wakim" <nadimlb@.cyberia.net.lb>
:uOjnD2NEFHA.1392@.tk2msftngp13.phx.gbl...
> First you have to select distinct dates into a cursor,
> than select from the table left outer join each date where date from the
> table = date of the column.
>
> "XXY" <xxy02021@.NOSPAM.163.com> wrote in message
> news:eUkwCXNEFHA.2508@.TK2MSFTNGP09.phx.gbl...
>|||Hi David and Roji,
I do appreciated your articles and sample codes, however when the outdate
ranges much(it might be any day in a year in my table), I am afraid it is
not a good idea using datediff. Don't you think so ?
yours, XXY
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org>
:1108198808.232843.84220@.g14g2000cwa.googlegroups.com...
> SELECT umc,
> SUM(CASE WHEN DATEDIFF(DAY,@.dt,outdate)=0 THEN outnumber ELSE 0 END),
> SUM(CASE WHEN DATEDIFF(DAY,@.dt,outdate)=1 THEN outnumber ELSE 0 END),
> SUM(CASE WHEN DATEDIFF(DAY,@.dt,outdate)=2 THEN outnumber ELSE 0 END)
> FROM T2
> WHERE outdate >= @.dt
> AND outdate < DATEADD(DAY,3,@.dt)
> GROUP BY umc
> Column names in a query are fixed so dynamic SQL would be required to
> change the names based on the data. That shouldn't really be a problem
> though. It should be easy enough to display different column names in
> your client application.
> --
> David Portas
> SQL Server MVP
> --
>|||Roji, Thanks so much!!!
I read http://www.sqlteam.com/item.asp?ItemID=2955 and got the right answer
from
exec crosstab 'select umc from t2 group by
umc','sum(outnumber)','outdate','t2'
You are the MAN!!
"XXY" <xxy02021@.NOSPAM.163.com> д?
:eUkwCXNEFHA.2508@.TK2MSFTNGP09.phx.gbl...
> create table t2
> (
> umc varchar(20),
> outdate datetime,
> outnumber int
> )
> insert t2 values (1,'2005-2-5',1)
> insert t2 values (2,'2005-2-5',1)
> insert t2 values (2,'2005-2-6',1)
> insert t2 values (3,'2005-2-5',2)
> insert t2 values (3,'2005-2-6',1)
> insert t2 values (4,'2005-2-7',1)
> I hope the result to be
> (2005-2-5,2005-2-6,2005-2-7 is column name now)
> 2005-2-5 2005-2-6 2005-2-7
> 1 1 0 0
> 2 1 1 0
> 3 2 1 0
> 4 0 0 1
> Can I just compose it with SELECT statement?
>|||I'm reminded of the Di-Tech commercials:( :)
www.rac4sql.net
"XXY" <xxy02021@.NOSPAM.163.com> wrote in message
news:%23kdz4APEFHA.2608@.TK2MSFTNGP10.phx.gbl...
> Roji, Thanks so much!!!
> I read http://www.sqlteam.com/item.asp?ItemID=2955 and got the right
> answer
> from
> exec crosstab 'select umc from t2 group by
> umc','sum(outnumber)','outdate','t2'
> You are the MAN!!
>
> "XXY" <xxy02021@.NOSPAM.163.com> д?
> :eUkwCXNEFHA.2508@.TK2MSFTNGP09.phx.gbl...
>|||I don't see a problem. The date range selection is in the WHERE clause
and is sargable. The cost of DATEDIFF should be relatively light but if
performance is a concern then you should test it out with your typical
data-set.
David Portas
SQL Server MVP
--sql

How can we format the percentage value format?

Hi, experts,

Thanks for your kind attention.

I wanna know how can we format percentage values with 2 decimals (e.g. 98.88%)?

I am looking forward to hearing from you shortly and thanks a lot in advance.

With kindest regards,

Yours sincerely,

If it is a measure try the format property in the Properties tab. Or do you need something else?

Cheers

|||

Hi,

Thank you for your reply.

Yes, I cant find the format for defining the decimal places for percentage values in the format property tab?

I am looking forward to hearing from you.

With kindest regards,

Yours sincerely,

|||

Actually there isn't one :-) . The building option for percentage always uses 2 decimal places. Else I think that you would have to specify your own format if you need percentage with more than 2 decimal places.

cheers

|||

Hi,

Thanks for that.

Kind regards,

|||

Hello Helen! Try "###.##%" or "000.00%" for the calculated member!

You will figure out how they work!

HTH

Thomas Ivarsson

|||

Hi, Thomas,

Thanks very much for your advices.

With kindest regards,

Yours sincerely,

How can we format the percentage value format?

Hi, experts,

Thanks for your kind attention.

I wanna know how can we format percentage values with 2 decimals (e.g. 98.88%)?

I am looking forward to hearing from you shortly and thanks a lot in advance.

With kindest regards,

Yours sincerely,

If it is a measure try the format property in the Properties tab. Or do you need something else?

Cheers

|||

Hi,

Thank you for your reply.

Yes, I cant find the format for defining the decimal places for percentage values in the format property tab?

I am looking forward to hearing from you.

With kindest regards,

Yours sincerely,

|||

Actually there isn't one :-) . The building option for percentage always uses 2 decimal places. Else I think that you would have to specify your own format if you need percentage with more than 2 decimal places.

cheers

|||

Hi,

Thanks for that.

Kind regards,

|||

Hello Helen! Try "###.##%" or "000.00%" for the calculated member!

You will figure out how they work!

HTH

Thomas Ivarsson

|||

Hi, Thomas,

Thanks very much for your advices.

With kindest regards,

Yours sincerely,

How can we format the percentage value format?

Hi, experts,

Thanks for your kind attention.

I wanna know how can we format percentage values with 2 decimals (e.g. 98.88%)?

I am looking forward to hearing from you shortly and thanks a lot in advance.

With kindest regards,

Yours sincerely,

If it is a measure try the format property in the Properties tab. Or do you need something else?

Cheers

|||

Hi,

Thank you for your reply.

Yes, I cant find the format for defining the decimal places for percentage values in the format property tab?

I am looking forward to hearing from you.

With kindest regards,

Yours sincerely,

|||

Actually there isn't one :-) . The building option for percentage always uses 2 decimal places. Else I think that you would have to specify your own format if you need percentage with more than 2 decimal places.

cheers

|||

Hi,

Thanks for that.

Kind regards,

|||

Hello Helen! Try "###.##%" or "000.00%" for the calculated member!

You will figure out how they work!

HTH

Thomas Ivarsson

|||

Hi, Thomas,

Thanks very much for your advices.

With kindest regards,

Yours sincerely,

How can i write a sproc for dynamic columns

Hi...

Is it possible to eliminate values from a select statements if they are NULL or Blank..

the reason i have use cast as decimals is because that value is a varchar in my database... and i want to eliminate those values from my Final select statement that dont is blank so that i will have a data set which may be only 1 - 10 long...

ALTER PROCEDURE [dbo].[rpt_ParticipantPlanPeriodInvActivity]@.PlanIdint,@.ParticipantIdint,@.PeriodIdintASDECLARE @.tbl table (tblId smallint IDENTITY(1,1),ParticipantIdint,LoanIdint, Name1char(2), NDesc1char(30),TotAct1decimal(19,4),Name2char(2), NDesc2char(30), TotAct2decimal(19,4),Name3char(2), NDesc3char(30),TotAct3decimal(19,4),Name4 Char(2), NDesc4char(30),TotAct4decimal(19,4),Name5char(2),NDesc5char(30),TotAct5decimal(19,4),Name6char(2),NDesc6char(30), TotAct6decimal(19,4),Name7char(2),NDesc7char(30),TotAct7decimal(19,4), Name8char(2),NDesc8char(30),TotAct8decimal(19,4),Name9char(2),NDesc9char(30),TotAct9decimal(19,4), Name10char(2),NDesc10char(30),TotAct10decimal(19,4),Name11char(2),NDesc11char(30),TotAct11decimal(19,4),Name12char(2),NDesc12char(30),TotAct12decimal(19,4),Name13char(2),NDesc13char(30),TotAct13decimal(19,4), Name14char(2),NDesc14char(30), TotAct14decimal(19,4),Name15char(2),NDesc15char(30),TotAct15decimal(19,4),Name16char(2),NDesc16char(30),TotAct16decimal(19,4),Name17char(2),NDesc17char(30),TotAct17decimal(19,4),Name18char(2),NDesc18char(30),TotAct18decimal(19,4), Name19char(2),NDesc19char(30),TotAct19decimal(19,4),Name20char(2),NDesc20char(30),TotAct20decimal(19,4) )Insert Into @.tbl SELECTpf.ParticipantId,pf.FundIdas LoanId, --CASE When FundName Is Null Then ShortName ELSE FundName ENDas FundNames, --pf.PortfolioId,--PortfolioName, Act1as Name1, a.Descriptionas NDesc1, cast(TotAct1as decimal(19,4)) ,Act2as Name2, b.Descriptionas NDesc2,Cast(TotAct2as decimal(19,4)),Act3as Name3, c.Descriptionas NDesc3,Cast(TotAct3as decimal(19,4)),Act4as Name4, d.Descriptionas NDesc4,Cast(TotAct4as decimal(19,4)),Act5as Name5,e.Descriptionas NDesc5,Cast(TotAct5as decimal(19,4)),Act6as Name6, fi.Descriptionas NDesc6,Cast(TotAct6as decimal(19,4)),Act7as Name7,g.Descriptionas NDesc7,Cast(TotAct7as decimal(19,4)),Act8as Name8,h.Descriptionas NDesc8,Cast(TotAct8as decimal(19,4)),Act9as Name9, i.Descriptionas NDesc9,Cast(TotAct9as decimal(19,4)),Act10as Name10, j.Descriptionas NDesc10,Cast(TotAct10as decimal(19,4)),Act11as Name11,k.Descriptionas NDesc11,Cast(TotAct11as decimal(19,4)),Act12as Name12,l.Descriptionas NDesc12,Cast(TotAct12as decimal(19,4)),Act13as Name13, m.Descriptionas NDesc13,Cast(TotAct13as decimal(19,4)),Act14as Name14,n.Descriptionas NDesc14,Cast(TotAct14as decimal(19,4)),Act15as Name15,o.Descriptionas NDesc15,Cast(TotAct15as decimal(19,4)),Act16as Name16,p1.Descriptionas NDesc16,Cast(TotAct16as decimal(19,4)),Act17as Name17,q.Descriptionas NDesc17,Cast(TotAct17as decimal(19,4)),Act18as Name18,r.Descriptionas NDesc18,Cast(TotAct18as decimal(19,4)),Act19as Name19, s.Descriptionas NDesc19,Cast(TotAct19as decimal(19,4)),Act20as Name20,t.Descriptionas NDesc20,Cast(TotAct20as decimal(19,4))FROM ParticipantPlanFundBalances1 pfLeft Outer JOIN Fund f On f.FundId = pf.FundIdLEFT Join PlanPortfolio pOn pf.PortfolioId = p.PortfolioId Left outer Join AscActCodes a on pf.Act1 = a.Name left outer Join AscActCodes b on pf.Act2 = b.Name left outer Join AscActCodes c on pf.Act3 = c.Name left outer Join AscActCodes d on pf.Act4 = d.Nameleft outer Join AscActCodes e on pf.Act5 = e.Nameleft outer Join AscActCodes fi on pf.Act6 = fi.Nameleft outer Join AscActCodes g on pf.Act7 = g.Nameleft outer Join AscActCodes h on pf.Act8 = h.Nameleft Outer Join AscActCodes i on pf.Act9 = i.Nameleft Outer Join AscActCodes j on pf.Act10 = j.Name left outer Join AscActCodes k on pf.Act11 = k.Nameleft outer Join AscActCodes l on pf.Act12 = l.Nameleft outer Join AscActCodes m on pf.Act13 = m.Nameleft outer Join AscActCodes n on pf.Act14 = n.Nameleft outer Join AscActCodes o on pf.Act15 = o.Nameleft outer Join AscActCodes p1 on pf.Act16 = p1.Nameleft outer Join AscActCodes q on pf.Act17 = q.Nameleft outer Join AscActCodes r on pf.Act18 = r.Nameleft outer Join AscActCodes s on pf.Act19 = s.Nameleft outer Join AscActCodes t on pf.Act20 = t.Name WHERE pf.FundId = 0 ANDPeriodId = @.PeriodIdANDpf.PlanId = @.PlanIdANDpf.ParticipantId = @.ParticipantId--Get the Fund informationfor the report and combine it with the Loan information--in the table variable...SELECTpf.ParticipantId,pf.PortfolioId,PortfolioName,pf.FundId LoanFundId, CASE When FundName Is Null Then ShortName ELSE FundName ENDas FundNames,Act1as Name1, a.Descriptionas NDesc1, Cast(TotAct1as decimal(19,4)),Act2as Name2, b.Descriptionas NDesc2,Cast(TotAct2as decimal(19,4)),Act3as Name3, c.Descriptionas NDesc3,Cast(TotAct3as decimal(19,4)),Act4as Name4, d.Descriptionas NDesc4,Cast(TotAct4as decimal(19,4)),Act5as Name5,e.Descriptionas NDesc5,Cast(TotAct5as decimal(19,4)),Act6as Name6, fi.Descriptionas NDesc6,Cast(TotAct6as decimal(19,4)),Act7as Name7,g.Descriptionas NDesc7,Cast(TotAct7as decimal(19,4)),Act8as Name8,h.Descriptionas NDesc8,Cast(TotAct8as decimal(19,4)),Act9as Name9, i.Descriptionas NDesc9,Cast(TotAct9as decimal(19,4)),Act10as Name10, j.Descriptionas NDesc10,Cast(TotAct10as decimal(19,4)),Act11as Name11,k.Descriptionas NDesc11,Cast(TotAct11as decimal(19,4)),Act12as Name12,l.Descriptionas NDesc12,Cast(TotAct12as decimal(19,4)),Act13as Name13, m.Descriptionas NDesc13,Cast(TotAct13as decimal(19,4)),Act14as Name14,n.Descriptionas NDesc14,Cast(TotAct14as decimal(19,4)),Act15as Name15,o.Descriptionas NDesc15,Cast(TotAct15as decimal(19,4)),Act16as Name16,p1.Descriptionas NDesc16,Cast(TotAct16as decimal(19,4)),Act17as Name17,q.Descriptionas NDesc17,Cast(TotAct17as decimal(19,4)),Act18as Name18,r.Descriptionas NDesc18,Cast(TotAct18as decimal(19,4)),Act19as Name19, s.Descriptionas NDesc19,Cast(TotAct19as decimal(19,4)),Act20as Name20,t.Descriptionas NDesc20,Cast(TotAct20as decimal(19,4))FROM ParticipantPlanFundBalances1 pfLeft Outer JOIN Fund f On f.FundId = pf.FundIdLEFT Join PlanPortfolio pOn pf.PortfolioId = p.PortfolioId Left outer Join AscActCodes a on pf.Act1 = a.Name left outer Join AscActCodes b on pf.Act2 = b.Name left outer Join AscActCodes c on pf.Act3 = c.Name left outer Join AscActCodes d on pf.Act4 = d.Nameleft outer Join AscActCodes e on pf.Act5 = e.Nameleft outer Join AscActCodes fi on pf.Act6 = fi.Nameleft outer Join AscActCodes g on pf.Act7 = g.Nameleft outer Join AscActCodes h on pf.Act8 = h.Nameleft Outer Join AscActCodes i on pf.Act9 = i.Nameleft Outer Join AscActCodes j on pf.Act10 = j.Name left outer Join AscActCodes k on pf.Act11 = k.Nameleft outer Join AscActCodes l on pf.Act12 = l.Nameleft outer Join AscActCodes m on pf.Act13 = m.Nameleft outer Join AscActCodes n on pf.Act14 = n.Nameleft outer Join AscActCodes o on pf.Act15 = o.Nameleft outer Join AscActCodes p1 on pf.Act16 = p1.Nameleft outer Join AscActCodes q on pf.Act17 = q.Nameleft outer Join AscActCodes r on pf.Act18 = r.Nameleft outer Join AscActCodes s on pf.Act19 = s.Nameleft outer Join AscActCodes t on pf.Act20 = t.Name WHEREpf.FundId <> 0 ANDPeriodId = @.PeriodIdANDpf.PlanId = @.PlanIdANDParticipantId = @.ParticipantIdUnionSELECTParticipantId,0,'NA',LoanId,'Loan ' + cast(tblIdas char(1)),Name1,NDesc1,Cast(TotAct1as decimal(19,4)),Name2,NDesc2,Cast(TotAct2as decimal(19,4)),Name3,NDesc3,Cast(TotAct3as decimal(19,4)), Name4,NDesc4,Cast(TotAct4as decimal(19,4)),Name5,NDesc5,Cast(TotAct5as decimal(19,4)),Name6,NDesc6,Cast(TotAct6as decimal(19,4)),Name7,NDesc7,Cast(TotAct7as decimal(19,4)),Name8,NDesc8,Cast(TotAct8as decimal(19,4)),Name9,NDesc9,Cast(TotAct9as decimal(19,4)),Name10,NDesc10,Cast(TotAct10as decimal(19,4)),Name11,NDesc11,Cast(TotAct11as decimal(19,4)),Name12,NDesc12,Cast(TotAct12as decimal(19,4)),Name13,NDesc13,Cast(TotAct13as decimal(19,4)),Name14,NDesc14,Cast(TotAct14as decimal(19,4)),Name15,NDesc15,Cast(TotAct15as decimal(19,4)),Name16,NDesc16,Cast(TotAct16as decimal(19,4)),Name17,NDesc17,Cast(TotAct17as decimal(19,4)),Name18,NDesc18,Cast(TotAct18as decimal(19,4)),Name19,NDesc19,Cast(TotAct19as decimal(19,4)),Name20,NDesc20,Cast(TotAct20as decimal(19,4)) FROM @.tbl

Any help will be appreciated.

Regards

Karen

You can either limit rows in the WHERE clause by specifying the NOT NULL option or you can cast at the front end...

|||

huh?

Please post the minimum amount of code necessary to make your question clear. We don't want to read a book! :)

Your queries are bringing back values from many different columns in one resulting row.

Do you want to skip an entire row because one of the columns has a null or blank value? Or something else? I really don't understand.

|||

I want to skip an entire column because everything is NULL

|||

Never ask a query you don't want the answer to! :)

The short answer is no. If you query a value as a distinct column in the result set, you get that column in the result set.

The longer answer is maybe. If you don't mind concatenating multiple values together into one column in the result set, you can effectively skip the column. Just be sure to use isnull(columnname,'') to replace null values with an empty string, otherwise the whole string will end up null. That's a useful technique for writing lines of text that need to be printed out (as in queries that write sql code), but useless if you need the other column values back as discrete values.

|||

Thanks for your answer... can u tell a good place where i paste a screen shot of my report and show it to others..

Regards

Karen

|||

Karenros:

Thanks for your answer... can u tell a good place where i paste a screen shot of my report and show it to others..

Sorry, no. I'm sure you can get a free website with yahoo or geocities.

|||

David and Dinakar

Thanks for your answers..

Suppose if i have declared a table in my sproc.. like

Declare @.tbl table

(

Column1 - Column N

)

Is it possible to add columns dynamically to it.. like for eg..

my select statement has around 10 columns and in that 4 columns are returning no value... so can i adjust the number of columns in that table.. based on the select statement results...

Regards

Karen

|||

Karenros:

Declare @.tbl table

(

Column1 - Column N

)

I don't understand the syntax you are using in your example.

You could do this as a two step process.

Issue the query with all the columns.

Construct a query statement in a string that only includes the columns that got results and exec that query string.

Honestly, why not just set the Visible or Hidden property of the UI component to false for those columns that don't have values. I think that would be a lot simpler. :)

|||

The reason i am not doing it in the UI compenent,, cause in SSRS 2005 if i hide a column the width of the table would shrink.

Declare @.Tbl Table

(

tbld int identity,

col 1,

Col2,

.

.

Col N

)

|||

To add columns you need to ALTER TABLE. I dont think you can ALTER a table variable. you might either need to use an actual table or a temporary table... I havent looked at your entire post.. I've been quite busy last week and will be so next week too.. am just posting based on your most recent post...

how can i use "order by" with empty values

hi all.
how can i use "order by" with empty values
In SQL server, if record have empty value wills display firts, i need display empty value at last
please help me, thanks

use following query...

Select Value1, Value2,Value3 From table

Order By Case When Value1 is Null or Value1 = '' Then 1 Else 0 End, Value1