Iteration in a stored procedure

I have a storde procedure which compares one column of a table against each
of the other columns in the same table. If it finds a 'one' in each column,
it counts the number of 'paired data' . As there can be up to 60 columns and
60 rows in any table , can anyone suggest a shorter , more efficient way of
writing the following code? Any assistanc ewould be appreciated. Thank you.
ALTER PROCEDURE GetXXXXXXAll
/*
(
@.IdNumber = 1
)
*/
AS
Begin
/* SET NOCOUNT ON */
SELECT COUNT ([1]) As Expr1,
COUNT([1] + [2]) AS Expr2, COUNT([1] + [3]) AS Expr3, COUNT([1]
+ [4]) AS Expr4, COUNT([1] + [5]) AS Expr5,
COUNT([1] + [6]) AS Expr6,COUNT([1] + [7]) AS Expr7, COUNT([1] +
[8]) AS Expr8, COUNT([1] + [9]) AS Expr9,
COUNT([1] + [10]) AS Expr10, COUNT([1] + [11]) AS Expr11,
COUNT([1] + [12]) AS Expr12,COUNT([1] + [13]) AS Expr13,
COUNT([1] + [14]) AS Expr14, COUNT([1] + [15]) AS Expr15,
COUNT([1] + [16]) AS Expr16,COUNT([1] + [17]) AS Expr17,
COUNT([1] + [18]) AS Expr18, COUNT([1] + [19]) AS Expr19,
COUNT([1] + [20]) AS Expr20,COUNT([1] + [21]) AS Expr21,
COUNT([1] + [22]) AS Expr22, COUNT([1] + [23]) AS Expr23,
COUNT([1] + [24]) AS Expr24,COUNT([1] + [25]) AS Expr25,
COUNT([1] + [26]) AS Expr26, COUNT([1] + [27]) AS Expr27,
COUNT([1] + [28]) AS Expr28,COUNT([1] + [29]) AS Expr29,
COUNT([1] + [30]) AS Expr30,
COUNT ([2]) As Expr31,
COUNT([2] + [3]) AS Expr32, COUNT([2] + [4]) AS Expr33,
COUNT([2] + [5]) AS Expr34,
COUNT([2] + [6]) AS Expr35,COUNT([2] + [7]) AS Expr36, COUNT([2]
+ [8]) AS Expr37, COUNT([2] + [9]) AS Expr38,
COUNT([2] + [10]) AS Expr39, COUNT([2] + [11]) AS Expr40,
COUNT([2] + [12]) AS Expr41,COUNT([2] + [13]) AS Expr42,
COUNT([2] + [14]) AS Expr43, COUNT([2] + [15]) AS Expr44,
COUNT([2] + [16]) AS Expr45,COUNT([2] + [17]) AS Expr46,
COUNT([2] + [18]) AS Expr47, COUNT([2] + [19]) AS Expr48,
COUNT([2] + [20]) AS Expr49,COUNT([2] + [21]) AS Expr50,
COUNT([2] + [22]) AS Expr51, COUNT([2] + [23]) AS Expr52,
COUNT([2] + [24]) AS Expr53,COUNT([2] + [25]) AS Expr54,
COUNT([2] + [26]) AS Expr55, COUNT([2] + [27]) AS Expr56,
COUNT([2] + [28]) AS Expr57,COUNT([2] + [29]) AS Expr58,
COUNT([2] + [30]) AS Expr59,
COUNT ([3]) As Expr60,
COUNT([3] + [4]) AS Expr61, COUNT([3] + [5]) AS Expr62,
COUNT([3] + [6]) AS Expr63,COUNT([3] + [7]) AS Expr64, COUNT([3]
+ [8]) AS Expr65, COUNT([3] + [9]) AS Expr66,
COUNT([3] + [10]) AS Expr67, COUNT([3] + [11]) AS Expr68,
COUNT([3] + [12]) AS Expr69,COUNT([3] + [13]) AS Expr70,
COUNT([3] + [14]) AS Expr71, COUNT([3] + [15]) AS Expr72,
COUNT([3] + [16]) AS Expr73,COUNT([3] + [17]) AS Expr74,
COUNT([3] + [18]) AS Expr75, COUNT([3] + [19]) AS Expr76,
COUNT([3] + [20]) AS Expr77,COUNT([3] + [21]) AS Expr78,
COUNT([3] + [22]) AS Expr79, COUNT([3] + [23]) AS Expr80,
COUNT([3] + [24]) AS Expr81,COUNT([3] + [25]) AS Expr82,
COUNT([3] + [26]) AS Expr83, COUNT([3] + [27]) AS Expr84,
COUNT([3] + [28]) AS Expr85,COUNT([3] + [29]) AS Expr86,
COUNT([3] + [30]) AS Expr87,
COUNT ([4]) As Expr88,
COUNT([4] + [5]) AS Expr89,
COUNT([4] + [6]) AS Expr90,COUNT([4] + [7]) AS Expr91, COUNT([4]
+ [8]) AS Expr92, COUNT([4] + [9]) AS Expr93,
COUNT([4] + [10]) AS Expr94, COUNT([4] + [11]) AS Expr95,
COUNT([4] + [12]) AS Expr96,COUNT([4] + [13]) AS Expr97,
COUNT([4] + [14]) AS Expr98, COUNT([4] + [15]) AS Expr99,
COUNT([4] + [16]) AS Expr100,COUNT([4] + [17]) AS Expr101,
COUNT([4] + [18]) AS Expr102, COUNT([4] + [19]) AS Expr103,
COUNT([4] + [20]) AS Expr104,COUNT([4] + [21]) AS Expr105,
COUNT([4] + [22]) AS Expr106, COUNT([4] + [23]) AS Expr107,
COUNT([4] + [24]) AS Expr108,COUNT([4] + [25]) AS Expr109,
COUNT([4] + [26]) AS Expr110, COUNT([4] + [27]) AS Expr111,
COUNT([4] + [28]) AS Expr112,COUNT([4] + [29]) AS Expr113,
COUNT([4] + [30]) AS Expr114,
COUNT ([5]) As Expr115,
COUNT([5] + [6]) AS Expr116,COUNT([5] + [7]) AS Expr117,
COUNT([5] + [8]) AS Expr118, COUNT([5] + [9]) AS Expr119,
COUNT([5] + [10]) AS Expr120, COUNT([5] + [11]) AS Expr121,
COUNT([5] + [12]) AS Expr122,COUNT([5] + [13]) AS Expr123,
COUNT([5] + [14]) AS Expr124, COUNT([5] + [15]) AS Expr125,
COUNT([5] + [16]) AS Expr126,COUNT([5] + [17]) AS Expr127,
COUNT([5] + [18]) AS Expr128, COUNT([5] + [19]) AS Expr129,
COUNT([5] + [20]) AS Expr130,COUNT([5] + [21]) AS Expr131,
COUNT([5] + [22]) AS Expr132, COUNT([5] + [23]) AS Expr133,
COUNT([5] + [24]) AS Expr134,COUNT([5] + [25]) AS Expr135,
COUNT([5] + [26]) AS Expr136, COUNT([5] + [27]) AS Expr137,
COUNT([5] + [28]) AS Expr138,COUNT([5] + [29]) AS Expr139,
COUNT([5] + [30]) AS Expr140
.
.
. etc
.
.
COUNT([60] + [60]) AS Expr1859
FROM XXXXXXFrequencyTable
WHERE ([1] = 1) OR
([1] = 1) AND ([2] = 1) OR
([1] = 1) AND ([3] = 1) OR
([1] = 1) AND ([4] = 1) OR
([1] = 1) AND ([5] = 1) OR
([1] = 1) AND ([6] = 1) OR
([1] = 1) AND ([7] = 1) OR
([1] = 1) AND ([8] = 1) OR
([1] = 1) AND ([9] = 1) OR
([1] = 1) AND ([10] = 1) OR
([1] = 1) AND ([11] = 1) OR
([1] = 1) AND ([12] = 1) OR
([1] = 1) AND ([13] = 1) OR
([1] = 1) AND ([14] = 1) OR
([1] = 1) AND ([15] = 1) OR
([1] = 1) AND ([16] = 1) OR
([1] = 1) AND ([17] = 1) OR
([1] = 1) AND ([18] = 1) OR
([1] = 1) AND ([19] = 1) OR
([1] = 1) AND ([20] = 1) OR
([1] = 1) AND ([21] = 1) OR
([1] = 1) AND ([22] = 1) OR
([1] = 1) AND ([23] = 1) OR
([1] = 1) AND ([24] = 1) OR
([1] = 1) AND ([25] = 1) OR
([1] = 1) AND ([26] = 1) OR
([1] = 1) AND ([27] = 1) OR
([1] = 1) AND ([28] = 1) OR
([1] = 1) AND ([29] = 1) OR
([1] = 1) AND ([30] = 1) OR
([2] = 1) OR
([2] = 1) AND ([3] = 1) OR
([2] = 1) AND ([4] = 1) OR
([2] = 1) AND ([5] = 1) OR
([2] = 1) AND ([6] = 1) OR
([2] = 1) AND ([7] = 1) OR
([2] = 1) AND ([8] = 1) OR
([2] = 1) AND ([9] = 1) OR
([2] = 1) AND ([10] = 1) OR
([2] = 1) AND ([11] = 1) OR
([2] = 1) AND ([12] = 1) OR
([2] = 1) AND ([13] = 1) OR
([2] = 1) AND ([14] = 1) OR
([2] = 1) AND ([15] = 1) OR
([2] = 1) AND ([16] = 1) OR
([2] = 1) AND ([17] = 1) OR
([2] = 1) AND ([18] = 1) OR
([2] = 1) AND ([19] = 1) OR
([2] = 1) AND ([20] = 1) OR
([2] = 1) AND ([21] = 1) OR
([2] = 1) AND ([22] = 1) OR
([2] = 1) AND ([23] = 1) OR
([2] = 1) AND ([24] = 1) OR
([2] = 1) AND ([25] = 1) OR
([2] = 1) AND ([26] = 1) OR
([2] = 1) AND ([27] = 1) OR
([2] = 1) AND ([28] = 1) OR
([2] = 1) AND ([29] = 1) OR
([2] = 1) AND ([30] = 1) OR
([3] = 1) OR
([3] = 1) AND ([4] = 1) OR
([3] = 1) AND ([5] = 1) OR
([3] = 1) AND ([6] = 1) OR
([3] = 1) AND ([7] = 1) OR
([3] = 1) AND ([8] = 1) OR
([3] = 1) AND ([9] = 1) OR
([3] = 1) AND ([10] = 1) OR
([3] = 1) AND ([11] = 1) OR
([3] = 1) AND ([12] = 1) OR
([3] = 1) AND ([13] = 1) OR
([3] = 1) AND ([14] = 1) OR
([3] = 1) AND ([15] = 1) OR
([3] = 1) AND ([16] = 1) OR
([3] = 1) AND ([17] = 1) OR
([3] = 1) AND ([18] = 1) OR
([3] = 1) AND ([19] = 1) OR
([3] = 1) AND ([20] = 1) OR
([3] = 1) AND ([21] = 1) OR
([3] = 1) AND ([22] = 1) OR
([3] = 1) AND ([23] = 1) OR
([3] = 1) AND ([24] = 1) OR
([3] = 1) AND ([25] = 1) OR
([3] = 1) AND ([26] = 1) OR
([3] = 1) AND ([27] = 1) OR
([3] = 1) AND ([28] = 1) OR
([3] = 1) AND ([29] = 1) OR
([3] = 1) AND ([30] = 1) OR
([4] = 1) OR
([4] = 1) AND ([5] = 1) OR
([4] = 1) AND ([6] = 1) OR
([4] = 1) AND ([7] = 1) OR
([4] = 1) AND ([8] = 1) OR
([4] = 1) AND ([9] = 1) OR
([4] = 1) AND ([10] = 1) OR
([4] = 1) AND ([11] = 1) OR
([4] = 1) AND ([12] = 1) OR
([4] = 1) AND ([13] = 1) OR
([4] = 1) AND ([14] = 1) OR
([4] = 1) AND ([15] = 1) OR
([4] = 1) AND ([16] = 1) OR
([4] = 1) AND ([17] = 1) OR
([4] = 1) AND ([18] = 1) OR
([4] = 1) AND ([19] = 1) OR
([4] = 1) AND ([20] = 1) OR
([4] = 1) AND ([21] = 1) OR
([4] = 1) AND ([22] = 1) OR
([4] = 1) AND ([23] = 1) OR
([4] = 1) AND ([24] = 1) OR
([4] = 1) AND ([25] = 1) OR
([4] = 1) AND ([26] = 1) OR
([4] = 1) AND ([27] = 1) OR
([4] = 1) AND ([28] = 1) OR
([4] = 1) AND ([29] = 1) OR
([4] = 1) AND ([30] = 1) OR
([5] = 1) OR
([5] = 1) AND ([6] = 1) OR
([5] = 1) AND ([7] = 1) OR
([5] = 1) AND ([8] = 1) OR
([5] = 1) AND ([9] = 1) OR
([5] = 1) AND ([10] = 1) OR
([5] = 1) AND ([11] = 1) OR
([5] = 1) AND ([12] = 1) OR
([5] = 1) AND ([13] = 1) OR
([5] = 1) AND ([14] = 1) OR
([5] = 1) AND ([15] = 1) OR
([5] = 1) AND ([16] = 1) OR
([5] = 1) AND ([17] = 1) OR
([5] = 1) AND ([18] = 1) OR
([5] = 1) AND ([19] = 1) OR
([5] = 1) AND ([20] = 1) OR
([5] = 1) AND ([21] = 1) OR
([5] = 1) AND ([22] = 1) OR
([5] = 1) AND ([23] = 1) OR
([5] = 1) AND ([24] = 1) OR
([5] = 1) AND ([25] = 1) OR
([5] = 1) AND ([26] = 1) OR
([5] = 1) AND ([27] = 1) OR
([5] = 1) AND ([28] = 1) OR
([5] = 1) AND ([29] = 1) OR
([5] = 1) AND ([30] = 1)
.
.
. etc
.
([60] = 1) AND ([60] = 1)
ENDonecorp wrote:
> I have a storde procedure which compares one column of a table against eac
h
> of the other columns in the same table. If it finds a 'one' in each column
,
> it counts the number of 'paired data' . As there can be up to 60 columns a
nd
> 60 rows in any table , can anyone suggest a shorter , more efficient way o
f
> writing the following code? Any assistanc ewould be appreciated. Thank you
.
>
Holy moley!!! What in the world is this for? If the values of these
fields are truly limited to 1 or 0, you could probably do something like
SUM([1] * [2]) AS Expr2 instead of COUNT([1] + [2]) AS Expr2, and
eliminate the WHERE clause entirely. If [1] = 1 and [2] = 0, the SUM is
going to increase by zero (1 * 0), essentially the same effect you're
getting by counting WHERE ([1] = 1 AND [2] = 1).
Does that make sense?|||Hi There,
I hope this help
Select 1 R ,1 val1,1 val2,1 val3 , 1 val4 into dummy
insert into dummy Select 2 R ,1 val1,1 val2,1 val3 , 1 val4
insert into dummy Select 3 R ,1 val1,1 val2,1 val3 , 4 val4
Select * from dummy
Select *,
(
case when val1=1 then 1 else 0 end +
case when val2=1 then 1 else 0 end +
case when val3=1 then 1 else 0 end +
case when val4=1 then 1 else 0 end
)/2 As Pairs
From dummy
With Warm regards
Jatinder Singh
http://jatindersingh.blogspot.com
Tracy McKibben wrote:
> onecorp wrote:
> Holy moley!!! What in the world is this for? If the values of these
> fields are truly limited to 1 or 0, you could probably do something like
> SUM([1] * [2]) AS Expr2 instead of COUNT([1] + [2]) AS Expr2, and
> eliminate the WHERE clause entirely. If [1] = 1 and [2] = 0, the SUM is
> going to increase by zero (1 * 0), essentially the same effect you're
> getting by counting WHERE ([1] = 1 AND [2] = 1).
> Does that make sense?|||Hi,
Thanks for your post!
From your description, I understand that:
A table of your database includes 60 columns and 60 rows.
You wanted to get a 60*60 matrix, in which any item ([x,y]) value
represents the count when column x equals column y and both of their values
are 1.
If I have misunderstood, please feel free to let me know.
If the column type is bit, Tracy's idea is wonderful. I also recommend you
use SUM expression to retrieve the count value.
Otherwise, in this case, the where clause can be written as "WHERE
[1]+[2]+[3]+...+[60]>0".
However if the column type is not bit and the value may be out of 0 and 1,
the where clause should be separated.
I recommend you write a function which can count by accepting the two
parameters of each coordinates.
And then you can get all items count by this way:
SELECT proc_selcount(1,1) as [1_1],proc_selcount(1,2) as
[1_2],proc_selcount(1,3) as [1_3],...,proc_selcount(60,60) as [60_60] into
MATRIX;
SELECT * FROM MATRIX;
If you have any other concerns, please feel free to let me know. It's my
pleasure to be of assistance.
+++++++++++++++++++++++++++
Charles Wang
Microsoft Online Partner Support
+++++++++++++++++++++++++++
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a w to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/te...erview/40010469
Others:
https://partner.microsoft.com/US/te...upportoverview/
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/defaul...rnational.aspx.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi,
Can you give the table definition and the insert script for the data? I
have got the result you had mailed.
Moreover, is it always 60 columns or will it change?
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||Hi,
Since I didn't get the data, I created with a temp table which has 6
columns.
It can be extended to 60 columns or how many ever you want, just add those
columns in the pivot and unpivot expression.
Let me know if this was what you expected.
-- Insert script
Select 1 [1],0 [2],1 [3] , 1 [4] , 0 [5], 1 [6] into #temp
insert into #temp Select 1 ,1 ,0 , 1,0,0
insert into #temp Select 1 ,1 ,1 , 1 ,1,1
insert into #temp Select 1 ,0 ,0 , 1,1,0
insert into #temp Select 1 ,1 ,1 , 0,0,1
insert into #temp Select 0 ,1 ,1 , 0,1,0
--Query
with cte as(
select row_id, loc, val from
(select row_number() over (order by [1]) as row_id, * from #temp) as a
unpivot (val for loc in ([1],[2],[3],[4],[5],[6])) as U
), cte1 as(
select a.loc as loc,b.loc as loc2,sum(a.val*b.val) as cnt from cte a, cte b
where a.row_id = b.row_id
and a.loc <= b.loc
group by a.loc,b.loc
)
select * from cte1
pivot ( sum(cnt) for loc2 in ([1],[2],[3],[4],[5],[6])) as P
Hope this helps.
-Omni|||Thank you to all for their assistance.
To Omnibuzz
....my apologies for the lack of a reply, but it was probably dut to time
differences etc.
I originally derived the table by using in-line SQL. I was then advised that
that was not a good idea, and hence came up with the stored procedure (one
example of my attempts being the one I posted ).
Regarding the table definition:
At the first block of code referring to NUmber [1] I used create table.
At each block thereafter, I used 'insert into table' thereby building up
each row.
To display the results, I used a gridview control to obtain the data from
the tbale created in the stored procedure and a 'sort' to get the rows in th
e
right order.
In your reply you wrote:
-- Insert script
Select 1 [1],0 [2],1 [3] , 1 [4] , 0 [5], 1 [6] into #temp
insert into #temp Select 1 ,1 ,0 , 1,0,0
insert into #temp Select 1 ,1 ,1 , 1 ,1,1
insert into #temp Select 1 ,0 ,0 , 1,1,0
insert into #temp Select 1 ,1 ,1 , 0,0,1
insert into #temp Select 0 ,1 ,1 , 0,1,0
Why in the select stmt is it 1 [1],0 [2],1 [3] , 1 [4] , 0 [5], 1 [6] and
not
1 [1],1 [2],1 [3] , 1 [4] , 1 [5], 1 [6] ?
What do the 'ones' and 'zeroes' mean in this exp ? insert into #temp Select
1 ,1 ,0 , 1,0,0
Dear Mr WANG,
Can you please tell me what proc_selcount means?
Thank you
"onecorp" wrote:

> I have a stored procedure which compares one column of a table against eac
h
> of the other columns in the same table. If it finds a 'one' in each column
,
> it counts the number of 'paired data' . As there can be up to 60 columns a
nd
> 60 rows in any table , can anyone suggest a shorter , more efficient way o
f
> writing the following code? Any assistance would be appreciated. Thank you
.
> ALTER PROCEDURE GetXXXXXXAll
> /*
> (
> @.IdNumber = 1
> )
> */
> AS
> Begin
> /* SET NOCOUNT ON */
> SELECT COUNT ([1]) As Expr1,
> COUNT([1] + [2]) AS Expr2, COUNT([1] + [3]) AS Expr3, COUNT([1
]
> + [4]) AS Expr4, COUNT([1] + [5]) AS Expr5,
> COUNT([1] + [6]) AS Expr6,COUNT([1] + [7]) AS Expr7, COUNT([1]
+
> [8]) AS Expr8, COUNT([1] + [9]) AS Expr9,
> COUNT([1] + [10]) AS Expr10, COUNT([1] + [11]) AS Expr11,
> COUNT([1] + [12]) AS Expr12,COUNT([1] + [13]) AS Expr13,
> COUNT([1] + [14]) AS Expr14, COUNT([1] + [15]) AS Expr15,
> COUNT([1] + [16]) AS Expr16,COUNT([1] + [17]) AS Expr17,
> COUNT([1] + [18]) AS Expr18, COUNT([1] + [19]) AS Expr19,
> COUNT([1] + [20]) AS Expr20,COUNT([1] + [21]) AS Expr21,
> COUNT([1] + [22]) AS Expr22, COUNT([1] + [23]) AS Expr23,
> COUNT([1] + [24]) AS Expr24,COUNT([1] + [25]) AS Expr25,
> COUNT([1] + [26]) AS Expr26, COUNT([1] + [27]) AS Expr27,
> COUNT([1] + [28]) AS Expr28,COUNT([1] + [29]) AS Expr29,
> COUNT([1] + [30]) AS Expr30,
> COUNT ([2]) As Expr31,
> COUNT([2] + [3]) AS Expr32, COUNT([2] + [4]) AS Expr33,
> COUNT([2] + [5]) AS Expr34,
> COUNT([2] + [6]) AS Expr35,COUNT([2] + [7]) AS Expr36, COUNT([
2]
> + [8]) AS Expr37, COUNT([2] + [9]) AS Expr38,
> COUNT([2] + [10]) AS Expr39, COUNT([2] + [11]) AS Expr40,
> COUNT([2] + [12]) AS Expr41,COUNT([2] + [13]) AS Expr42,
> COUNT([2] + [14]) AS Expr43, COUNT([2] + [15]) AS Expr44,
> COUNT([2] + [16]) AS Expr45,COUNT([2] + [17]) AS Expr46,
> COUNT([2] + [18]) AS Expr47, COUNT([2] + [19]) AS Expr48,
> COUNT([2] + [20]) AS Expr49,COUNT([2] + [21]) AS Expr50,
> COUNT([2] + [22]) AS Expr51, COUNT([2] + [23]) AS Expr52,
> COUNT([2] + [24]) AS Expr53,COUNT([2] + [25]) AS Expr54,
> COUNT([2] + [26]) AS Expr55, COUNT([2] + [27]) AS Expr56,
> COUNT([2] + [28]) AS Expr57,COUNT([2] + [29]) AS Expr58,
> COUNT([2] + [30]) AS Expr59,
> COUNT ([3]) As Expr60,
> COUNT([3] + [4]) AS Expr61, COUNT([3] + [5]) AS Expr62,
> COUNT([3] + [6]) AS Expr63,COUNT([3] + [7]) AS Expr64, COUNT([
3]
> + [8]) AS Expr65, COUNT([3] + [9]) AS Expr66,
> COUNT([3] + [10]) AS Expr67, COUNT([3] + [11]) AS Expr68,
> COUNT([3] + [12]) AS Expr69,COUNT([3] + [13]) AS Expr70,
> COUNT([3] + [14]) AS Expr71, COUNT([3] + [15]) AS Expr72,
> COUNT([3] + [16]) AS Expr73,COUNT([3] + [17]) AS Expr74,
> COUNT([3] + [18]) AS Expr75, COUNT([3] + [19]) AS Expr76,
> COUNT([3] + [20]) AS Expr77,COUNT([3] + [21]) AS Expr78,
> COUNT([3] + [22]) AS Expr79, COUNT([3] + [23]) AS Expr80,
> COUNT([3] + [24]) AS Expr81,COUNT([3] + [25]) AS Expr82,
> COUNT([3] + [26]) AS Expr83, COUNT([3] + [27]) AS Expr84,
> COUNT([3] + [28]) AS Expr85,COUNT([3] + [29]) AS Expr86,
> COUNT([3] + [30]) AS Expr87,
> COUNT ([4]) As Expr88,
> COUNT([4] + [5]) AS Expr89,
> COUNT([4] + [6]) AS Expr90,COUNT([4] + [7]) AS Expr91, COUNT([
4]
> + [8]) AS Expr92, COUNT([4] + [9]) AS Expr93,
> COUNT([4] + [10]) AS Expr94, COUNT([4] + [11]) AS Expr95,
> COUNT([4] + [12]) AS Expr96,COUNT([4] + [13]) AS Expr97,
> COUNT([4] + [14]) AS Expr98, COUNT([4] + [15]) AS Expr99,
> COUNT([4] + [16]) AS Expr100,COUNT([4] + [17]) AS Expr101,
> COUNT([4] + [18]) AS Expr102, COUNT([4] + [19]) AS Expr103,
> COUNT([4] + [20]) AS Expr104,COUNT([4] + [21]) AS Expr105,
> COUNT([4] + [22]) AS Expr106, COUNT([4] + [23]) AS Expr107,
> COUNT([4] + [24]) AS Expr108,COUNT([4] + [25]) AS Expr109,
> COUNT([4] + [26]) AS Expr110, COUNT([4] + [27]) AS Expr111,
> COUNT([4] + [28]) AS Expr112,COUNT([4] + [29]) AS Expr113,
> COUNT([4] + [30]) AS Expr114,
> COUNT ([5]) As Expr115,
> COUNT([5] + [6]) AS Expr116,COUNT([5] + [7]) AS Expr117,
> COUNT([5] + [8]) AS Expr118, COUNT([5] + [9]) AS Expr119,
> COUNT([5] + [10]) AS Expr120, COUNT([5] + [11]) AS Expr121,
> COUNT([5] + [12]) AS Expr122,COUNT([5] + [13]) AS Expr123,
> COUNT([5] + [14]) AS Expr124, COUNT([5] + [15]) AS Expr125,
> COUNT([5] + [16]) AS Expr126,COUNT([5] + [17]) AS Expr127,
> COUNT([5] + [18]) AS Expr128, COUNT([5] + [19]) AS Expr129,
> COUNT([5] + [20]) AS Expr130,COUNT([5] + [21]) AS Expr131,
> COUNT([5] + [22]) AS Expr132, COUNT([5] + [23]) AS Expr133,
> COUNT([5] + [24]) AS Expr134,COUNT([5] + [25]) AS Expr135,
> COUNT([5] + [26]) AS Expr136, COUNT([5] + [27]) AS Expr137,
> COUNT([5] + [28]) AS Expr138,COUNT([5] + [29]) AS Expr139,
> COUNT([5] + [30]) AS Expr140
> .
> .
> . etc
> .
> .
> COUNT([60] + [60]) AS Expr1859
>
> FROM XXXXXXFrequencyTable
> WHERE ([1] = 1) OR
> ([1] = 1) AND ([2] = 1) OR
> ([1] = 1) AND ([3] = 1) OR
> ([1] = 1) AND ([4] = 1) OR
> ([1] = 1) AND ([5] = 1) OR
> ([1] = 1) AND ([6] = 1) OR
> ([1] = 1) AND ([7] = 1) OR
> ([1] = 1) AND ([8] = 1) OR
> ([1] = 1) AND ([9] = 1) OR
> ([1] = 1) AND ([10] = 1) OR
> ([1] = 1) AND ([11] = 1) OR
> ([1] = 1) AND ([12] = 1) OR
> ([1] = 1) AND ([13] = 1) OR
> ([1] = 1) AND ([14] = 1) OR
> ([1] = 1) AND ([15] = 1) OR
> ([1] = 1) AND ([16] = 1) OR
> ([1] = 1) AND ([17] = 1) OR
> ([1] = 1) AND ([18] = 1) OR
> ([1] = 1) AND ([19] = 1) OR
> ([1] = 1) AND ([20] = 1) OR
> ([1] = 1) AND ([21] = 1) OR
> ([1] = 1) AND ([22] = 1) OR
> ([1] = 1) AND ([23] = 1) OR
> ([1] = 1) AND ([24] = 1) OR
> ([1] = 1) AND ([25] = 1) OR
> ([1] = 1) AND ([26] = 1) OR
> ([1] = 1) AND ([27] = 1) OR
> ([1] = 1) AND ([28] = 1) OR
> ([1] = 1) AND ([29] = 1) OR
> ([1] = 1) AND ([30] = 1) OR
> ([2] = 1) OR
> ([2] = 1) AND ([3] = 1) OR
> ([2] = 1) AND ([4] = 1) OR
> ([2] = 1) AND ([5] = 1) OR
> ([2] = 1) AND ([6] = 1) OR
> ([2] = 1) AND ([7] = 1) OR
> ([2] = 1) AND ([8] = 1) OR
> ([2] = 1) AND ([9] = 1) OR
> ([2] = 1) AND ([10] = 1) OR
> ([2] = 1) AND ([11] = 1) OR
> ([2] = 1) AND ([12] = 1) OR
> ([2] = 1) AND ([13] = 1) OR
> ([2] = 1) AND ([14] = 1) OR
> ([2] = 1) AND ([15] = 1) OR
> ([2] = 1) AND ([16] = 1) OR
> ([2] = 1) AND ([17] = 1) OR
> ([2] = 1) AND ([18] = 1) OR
> ([2] = 1) AND ([19] = 1) OR
> ([2] = 1) AND ([20] = 1) OR
> ([2] = 1) AND ([21] = 1) OR
> ([2] = 1) AND ([22] = 1) OR
> ([2] = 1) AND ([23] = 1) OR
> ([2] = 1) AND ([24] = 1) OR
> ([2] = 1) AND ([25] = 1) OR
> ([2] = 1) AND ([26] = 1) OR
> ([2] = 1) AND ([27] = 1) OR
> ([2] = 1) AND ([28] = 1) OR
> ([2] = 1) AND ([29] = 1) OR
> ([2] = 1) AND ([30] = 1) OR
> ([3] = 1) OR
> ([3] = 1) AND ([4] = 1) OR
> ([3] = 1) AND ([5] = 1) OR
> ([3] = 1) AND ([6] = 1) OR
> ([3] = 1) AND ([7] = 1) OR
> ([3] = 1) AND ([8] = 1) OR
> ([3] = 1) AND ([9] = 1) OR
> ([3] = 1) AND ([10] = 1) OR
> ([3] = 1) AND ([11] = 1) OR
> ([3] = 1) AND ([12] = 1) OR
> ([3] = 1) AND ([13] = 1) OR
> ([3] = 1) AND ([14] = 1) OR
> ([3] = 1) AND ([15] = 1) OR
> ([3] = 1) AND ([16] = 1) OR
> ([3] = 1) AND ([17] = 1) OR
> ([3] = 1) AND ([18] = 1) OR
> ([3] = 1) AND ([19] = 1) OR
> ([3] = 1) AND ([20] = 1) OR
> ([3] = 1) AND ([21] = 1) OR
> ([3] = 1) AND ([22] = 1) OR
> ([3] = 1) AND ([23] = 1) OR
> ([3] = 1) AND ([24] = 1) OR
> ([3] = 1) AND ([25] = 1) OR
> ([3] = 1) AND ([26] = 1) OR
> ([3] = 1) AND ([27] = 1) OR
> ([3] = 1) AND ([28] = 1) OR
> ([3] = 1) AND ([29] = 1) OR
> ([3] = 1) AND ([30] = 1) OR
> ([4] = 1) OR
> ([4] = 1) AND ([5] = 1) OR
> ([4] = 1) AND ([6] = 1) OR
> ([4] = 1) AND ([7] = 1) OR
> ([4] = 1) AND ([8] = 1) OR
> ([4] = 1) AND ([9] = 1) OR
> ([4] = 1) AND ([10] = 1) OR
> ([4] = 1) AND ([11] = 1) OR
> ([4] = 1) AND ([12] = 1) OR
> ([4] = 1) AND ([13] = 1) OR
> ([4] = 1) AND ([14] = 1) OR
> ([4] = 1) AND ([15] = 1) OR
> ([4] = 1) AND ([16] = 1) OR
> ([4] = 1) AND ([17] = 1) OR
> ([4] = 1) AND ([18] = 1) OR
> ([4] = 1) AND ([19] = 1) OR
> ([4] = 1) AND ([20] = 1) OR
> ([4] = 1) AND ([21] = 1) OR
> ([4] = 1) AND ([22] = 1) OR
> ([4] = 1) AND ([23] = 1) OR
> ([4] = 1) AND ([24] = 1) OR
> ([4] = 1) AND ([25] = 1) OR
> ([4] = 1) AND ([26] = 1) OR
> ([4] = 1) AND ([27] = 1) OR
> ([4] = 1) AND ([28] = 1) OR
> ([4] = 1) AND ([29] = 1) OR
> ([4] = 1) AND ([30] = 1) OR
> ([5] = 1) OR
> ([5] = 1) AND ([6] = 1) OR
> ([5] = 1) AND ([7] = 1) OR
> ([5] = 1) AND ([8] = 1) OR
> ([5] = 1) AND ([9] = 1) OR
> ([5] = 1) AND ([10] = 1) OR
> ([5] = 1) AND ([11] = 1) OR
> ([5] = 1) AND ([12] = 1) OR
> ([5] = 1) AND ([13] = 1) OR
> ([5] = 1) AND ([14] = 1) OR
> ([5] = 1) AND ([15] = 1) OR
> ([5] = 1) AND ([16] = 1) OR
> ([5] = 1) AND ([17] = 1) OR
> ([5] = 1) AND ([18] = 1) OR
> ([5] = 1) AND ([19] = 1) OR
> ([5] = 1) AND ([20] = 1) OR
> ([5] = 1) AND ([21] = 1) OR
> ([5] = 1) AND ([22] = 1) OR
> ([5] = 1) AND ([23] = 1) OR
> ([5] = 1) AND ([24] = 1) OR
> ([5] = 1) AND ([25] = 1) OR
> ([5] = 1) AND ([26] = 1) OR
> ([5] = 1) AND ([27] = 1) OR
> ([5] = 1) AND ([28] = 1) OR
> ([5] = 1) AND ([29] = 1) OR
> ([5] = 1) AND ([30] = 1)
> .
> .
> . etc
> .
> ([60] = 1) AND ([60] = 1)
> END|||The way I understood...
The values in the columns 1 through 6 can have 0s or 1s.
And you want to find the count of all possible pair of columns where both
have the value 1. Its just a sample data. You can have it as all 1s or all 0
s.
Did you run the code and check?
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||Hi,
Thanks for your response.
The proc_selcount is a custom function that can count any one or two
columns equaling 1.
However this name is not canonical, you can specify the name according to
your standard.
+++++++++++++++++++++++++++
Charles Wang
Microsoft Online Partner Support
+++++++++++++++++++++++++++
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

It's a computed column processing bug?

Hello everybody!

I have question about indexed and not indexed Persisted columns on sql server 2005. It's a bug?

First?, my version of SQL Server is

Microsoft SQL Server 2005 - 9.00.3186.00 (Intel X86) Aug 11 2007 03:13:58 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

Now I create two tables and try four select queries:

Code Snippet

SET ANSI_NULLS ON

SET ANSI_PADDING ON

SET ANSI_WARNINGS ON

SET ARITHABORT ON

SET CONCAT_NULL_YIELDS_NULL ON

SET NUMERIC_ROUNDABORT OFF

SET QUOTED_IDENTIFIER ON

GO

create table t1 (id int primary key, id_bigint as cast(id as bigint))

GO

create table t2 (id int primary key, id_bigint as cast(id as bigint) persisted)

GO

select * from t1 -- (1)

-- Clustered index scan with two times Compute Scalar

GO

select * from t2 -- (2)

-- Clustered index scan with one times Compute Scalar

GO

create index IX_t2 on t2 (id_bigint)

GO

select * from t2 -- (3)

-- Index Scan with one times Compute Scalar

GO

select * from t2 where id_bigint = 0 -- (4)

-- Index Seek with one times Compute Scalar

GO

drop table t1

GO

drop table t2

GO

SET ANSI_PADDING OFF

1. I don't understand why access to computed column raise scalar computation wto times?

2. I don't understand why access to persisted computed column raise any scalar computation?

3. I don't understand why access to persisted computed column over index required any scalar computations?

Can anyone from Microsoft SQL Server Team told me about this mistake?

It's a BUG or I incorrect understand value of the "PERSISTED" word?

--

Thanks with avanced.

WBR, Roman S. Golubin

grominc[at]gmail.com

Bug request on SQL Server 2005 Feedback for this icident.

To solve this problem while Microsoft SQL Server Team not fix this bug, use update with triggers instead persisted computed columns.

--

WBR, Roman S. Golubin

73! GL! RA1OGE/3

|||

It works.

Use 'WITH SCHEMABINDING'

Courtesy of Kent Tegels!

It's a computed column processing bug?

Hello everybody!

I have question about indexed and not indexed Persisted columns on sql server 2005. It's a bug?

First?, my version of SQL Server is

Microsoft SQL Server 2005 - 9.00.3186.00 (Intel X86) Aug 11 2007 03:13:58 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

Now I create two tables and try four select queries:

Code Snippet

SET ANSI_NULLS ON

SET ANSI_PADDING ON

SET ANSI_WARNINGS ON

SET ARITHABORT ON

SET CONCAT_NULL_YIELDS_NULL ON

SET NUMERIC_ROUNDABORT OFF

SET QUOTED_IDENTIFIER ON

GO

create table t1 (id int primary key, id_bigint as cast(id as bigint))

GO

create table t2 (id int primary key, id_bigint as cast(id as bigint) persisted)

GO

select * from t1 -- (1)

-- Clustered index scan with two times Compute Scalar

GO

select * from t2 -- (2)

-- Clustered index scan with one times Compute Scalar

GO

create index IX_t2 on t2 (id_bigint)

GO

select * from t2 -- (3)

-- Index Scan with one times Compute Scalar

GO

select * from t2 where id_bigint = 0 -- (4)

-- Index Seek with one times Compute Scalar

GO

drop table t1

GO

drop table t2

GO

SET ANSI_PADDING OFF

1. I don't understand why access to computed column raise scalar computation wto times?

2. I don't understand why access to persisted computed column raise any scalar computation?

3. I don't understand why access to persisted computed column over index required any scalar computations?

Can anyone from Microsoft SQL Server Team told me about this mistake?

It's a BUG or I incorrect understand value of the "PERSISTED" word?

--

Thanks with avanced.

WBR, Roman S. Golubin

grominc[at]gmail.com

Bug request on SQL Server 2005 Feedback for this icident.

To solve this problem while Microsoft SQL Server Team not fix this bug, use update with triggers instead persisted computed columns.

--

WBR, Roman S. Golubin

73! GL! RA1OGE/3

|||

It works.

Use 'WITH SCHEMABINDING'

Courtesy of Kent Tegels!

It is possible to hide/show a particular row or column?

Say, an original report displays
---
Supplier
Product 1
name
Price
...
Product 1
name
Price
...
---
Then our admin might want to hide price column so it will look like
---
Supplier
Product 1
name
...
Product 1
name
...
---
and then convert it into excel that doesn't show price.
or likewise the admin might not want name as well, so remove it from that
report.
Can it be done?
Thanks a lotHiddie,
hiding or displaying data can be controlled by the visibility property; at
the row or column level.
"Hiddie" wrote:
> Say, an original report displays
> ---
> Supplier
> Product 1
> name
> Price
> ...
> Product 1
> name
> Price
> ...
> ---
> Then our admin might want to hide price column so it will look like
> ---
> Supplier
> Product 1
> name
> ...
> Product 1
> name
> ...
> ---
> and then convert it into excel that doesn't show price.
> or likewise the admin might not want name as well, so remove it from that
> report.
> Can it be done?
> Thanks a lot
>
>|||The attached report demonstrates what you need to accomplish. Note that the
toggle is placed on the rows and not on the table cells.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hiddie" <Hiddie@.discussions.microsoft.com> wrote in message
news:B83ACC6E-C1CB-42FC-93C5-A85FEFC81051@.microsoft.com...
> Say, an original report displays
> ---
> Supplier
> Product 1
> name
> Price
> ...
> Product 1
> name
> Price
> ...
> ---
> Then our admin might want to hide price column so it will look like
> ---
> Supplier
> Product 1
> name
> ...
> Product 1
> name
> ...
> ---
> and then convert it into excel that doesn't show price.
> or likewise the admin might not want name as well, so remove it from that
> report.
> Can it be done?
> Thanks a lot
>
>
BasicShowHide.rdl
================================================================================
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Table Name="table1">
<Style />
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>23</ZIndex>
<rd:DefaultName>textbox1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Supplier Name</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>22</ZIndex>
<rd:DefaultName>textbox2</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Category Name</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox3">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>21</ZIndex>
<rd:DefaultName>textbox3</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Product Name</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Details>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox4">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>14</ZIndex>
<rd:DefaultName>textbox4</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox5">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>13</ZIndex>
<rd:DefaultName>textbox5</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="ProductName">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>12</ZIndex>
<rd:DefaultName>ProductName</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!ProductName.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Visibility>
<ToggleItem>CategoryName</ToggleItem>
<Hidden>true</Hidden>
</Visibility>
</TableRow>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox6">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>textbox6</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox10">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>textbox10</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="UnitPrice_1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>UnitPrice_1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!UnitPrice.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Visibility>
<ToggleItem>CategoryName</ToggleItem>
<Hidden>true</Hidden>
</Visibility>
</TableRow>
</TableRows>
</Details>
<DataSetName>DataSet1</DataSetName>
<TableGroups>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="SupplierName">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>20</ZIndex>
<rd:DefaultName>SupplierName</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!SupplierName.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox11">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>19</ZIndex>
<rd:DefaultName>textbox11</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox12">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>18</ZIndex>
<rd:DefaultName>textbox12</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
<RepeatOnNewPage>true</RepeatOnNewPage>
</Header>
<Grouping Name="table1_Group1">
<GroupExpressions>
<GroupExpression>=Fields!SupplierName.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Footer>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox13">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>8</ZIndex>
<rd:DefaultName>textbox13</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox14">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>7</ZIndex>
<rd:DefaultName>textbox14</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox15">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>6</ZIndex>
<rd:DefaultName>textbox15</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Footer>
</TableGroup>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox16">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>17</ZIndex>
<rd:DefaultName>textbox16</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="CategoryName">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>16</ZIndex>
<rd:DefaultName>CategoryName</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!CategoryName.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox18">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>15</ZIndex>
<rd:DefaultName>textbox18</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Visibility>
<ToggleItem>SupplierName</ToggleItem>
<Hidden>true</Hidden>
</Visibility>
</TableRow>
</TableRows>
<RepeatOnNewPage>true</RepeatOnNewPage>
</Header>
<Grouping Name="table1_Group2">
<GroupExpressions>
<GroupExpression>=Fields!CategoryName.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Footer>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox19">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>5</ZIndex>
<rd:DefaultName>textbox19</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox20">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>4</ZIndex>
<rd:DefaultName>textbox20</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox17">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>textbox17</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Footer>
</TableGroup>
</TableGroups>
<Footer>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox7">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>11</ZIndex>
<rd:DefaultName>textbox7</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox8">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>10</ZIndex>
<rd:DefaultName>textbox8</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox9">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>9</ZIndex>
<rd:DefaultName>textbox9</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Footer>
<TableColumns>
<TableColumn>
<Width>2.16667in</Width>
</TableColumn>
<TableColumn>
<Width>2.16667in</Width>
</TableColumn>
<TableColumn>
<Width>2.16667in</Width>
</TableColumn>
</TableColumns>
</Table>
</ReportItems>
<Style />
<Height>2in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="Northwind">
<rd:DataSourceID>cdeea17b-1207-41bf-922f-56809605e672</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>data source=localhost;initial
catalog=Northwind</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Width>6.5in</Width>
<DataSets>
<DataSet Name="DataSet1">
<Fields>
<Field Name="SupplierName">
<DataField>SupplierName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="CategoryName">
<DataField>CategoryName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="ProductName">
<DataField>ProductName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="UnitPrice">
<DataField>UnitPrice</DataField>
<rd:TypeName>System.Decimal</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>SELECT Suppliers.CompanyName AS SupplierName,
Categories.CategoryName, Products.ProductName, Products.UnitPrice
FROM Products INNER JOIN
Suppliers ON Products.SupplierID =Suppliers.SupplierID INNER JOIN
Categories ON Products.CategoryID =Categories.CategoryID</CommandText>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>f63998f3-88b8-4c34-a16e-364595d1d7a8</rd:ReportID>
<BottomMargin>1in</BottomMargin>
<Language>en-US</Language>
</Report>|||If I export your sample report to excel, the toggling does not work properly.
On the lowest level the unit prices are not shown if this category level was
collapsed in web before export. Furthermore if the category level was elapsed
before export, the toggling is per product name and not per category and the
unit prices don't become hidden.
Is this a bug?
Thanks for any help!
M. Peyer
"Bruce Johnson [MSFT]" wrote:
> The attached report demonstrates what you need to accomplish. Note that the
> toggle is placed on the rows and not on the table cells.
> --
> Bruce Johnson [MSFT]
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Hiddie" <Hiddie@.discussions.microsoft.com> wrote in message
> news:B83ACC6E-C1CB-42FC-93C5-A85FEFC81051@.microsoft.com...
> > Say, an original report displays
> >
> > ---
> > Supplier
> > Product 1
> > name
> > Price
> > ...
> > Product 1
> > name
> > Price
> > ...
> > ---
> >
> > Then our admin might want to hide price column so it will look like
> >
> > ---
> > Supplier
> > Product 1
> > name
> > ...
> > Product 1
> > name
> > ...
> > ---
> >
> > and then convert it into excel that doesn't show price.
> > or likewise the admin might not want name as well, so remove it from that
> > report.
> > Can it be done?
> >
> > Thanks a lot
> >
> >
> >
> >
>
> BasicShowHide.rdl
> ================================================================================> <?xml version="1.0" encoding="utf-8"?>
> <Report
> xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition"
> xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
> <RightMargin>1in</RightMargin>
> <Body>
> <ReportItems>
> <Table Name="table1">
> <Style />
> <Header>
> <TableRows>
> <TableRow>
> <Height>0.25in</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox1">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>23</ZIndex>
> <rd:DefaultName>textbox1</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>Supplier Name</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox2">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>22</ZIndex>
> <rd:DefaultName>textbox2</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>Category Name</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox3">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>21</ZIndex>
> <rd:DefaultName>textbox3</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>Product Name</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> </TableRow>
> </TableRows>
> </Header>
> <Details>
> <TableRows>
> <TableRow>
> <Height>0.25in</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox4">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>14</ZIndex>
> <rd:DefaultName>textbox4</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox5">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>13</ZIndex>
> <rd:DefaultName>textbox5</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="ProductName">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>12</ZIndex>
> <rd:DefaultName>ProductName</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=Fields!ProductName.Value</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> <Visibility>
> <ToggleItem>CategoryName</ToggleItem>
> <Hidden>true</Hidden>
> </Visibility>
> </TableRow>
> <TableRow>
> <Height>0.25in</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox6">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>2</ZIndex>
> <rd:DefaultName>textbox6</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox10">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>1</ZIndex>
> <rd:DefaultName>textbox10</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="UnitPrice_1">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <TextAlign>Right</TextAlign>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <rd:DefaultName>UnitPrice_1</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=Fields!UnitPrice.Value</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> <Visibility>
> <ToggleItem>CategoryName</ToggleItem>
> <Hidden>true</Hidden>
> </Visibility>
> </TableRow>
> </TableRows>
> </Details>
> <DataSetName>DataSet1</DataSetName>
> <TableGroups>
> <TableGroup>
> <Header>
> <TableRows>
> <TableRow>
> <Height>0.25in</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="SupplierName">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>20</ZIndex>
> <rd:DefaultName>SupplierName</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=Fields!SupplierName.Value</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox11">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>19</ZIndex>
> <rd:DefaultName>textbox11</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox12">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>18</ZIndex>
> <rd:DefaultName>textbox12</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> </TableRow>
> </TableRows>
> <RepeatOnNewPage>true</RepeatOnNewPage>
> </Header>
> <Grouping Name="table1_Group1">
> <GroupExpressions>
> <GroupExpression>=Fields!SupplierName.Value</GroupExpression>
> </GroupExpressions>
> </Grouping>
> <Footer>
> <TableRows>
> <TableRow>
> <Height>0.25in</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox13">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>8</ZIndex>
> <rd:DefaultName>textbox13</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox14">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>7</ZIndex>
> <rd:DefaultName>textbox14</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox15">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>6</ZIndex>
> <rd:DefaultName>textbox15</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> </TableRow>
> </TableRows>
> </Footer>
> </TableGroup>
> <TableGroup>
> <Header>
> <TableRows>
> <TableRow>
> <Height>0.25in</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox16">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>17</ZIndex>
> <rd:DefaultName>textbox16</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="CategoryName">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>16</ZIndex>
> <rd:DefaultName>CategoryName</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=Fields!CategoryName.Value</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox18">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>15</ZIndex>
> <rd:DefaultName>textbox18</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> <Visibility>
> <ToggleItem>SupplierName</ToggleItem>
> <Hidden>true</Hidden>
> </Visibility>
> </TableRow>
> </TableRows>
> <RepeatOnNewPage>true</RepeatOnNewPage>
> </Header>
> <Grouping Name="table1_Group2">
> <GroupExpressions>
> <GroupExpression>=Fields!CategoryName.Value</GroupExpression>
> </GroupExpressions>
> </Grouping>
> <Footer>
> <TableRows>
> <TableRow>
> <Height>0.25in</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox19">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>5</ZIndex>
> <rd:DefaultName>textbox19</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox20">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>4</ZIndex>
> <rd:DefaultName>textbox20</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox17">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>3</ZIndex>
> <rd:DefaultName>textbox17</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> </TableRow>
> </TableRows>
> </Footer>
> </TableGroup>
> </TableGroups>
> <Footer>
> <TableRows>
> <TableRow>
> <Height>0.25in</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox7">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>11</ZIndex>
> <rd:DefaultName>textbox7</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox8">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>10</ZIndex>
> <rd:DefaultName>textbox8</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox9">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>9</ZIndex>
> <rd:DefaultName>textbox9</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> </TableRow>
> </TableRows>
> </Footer>
> <TableColumns>
> <TableColumn>
> <Width>2.16667in</Width>
> </TableColumn>
> <TableColumn>
> <Width>2.16667in</Width>
> </TableColumn>
> <TableColumn>
> <Width>2.16667in</Width>
> </TableColumn>
> </TableColumns>
> </Table>
> </ReportItems>
> <Style />
> <Height>2in</Height>
> </Body>
> <TopMargin>1in</TopMargin>
> <DataSources>
> <DataSource Name="Northwind">
> <rd:DataSourceID>cdeea17b-1207-41bf-922f-56809605e672</rd:DataSourceID>
> <ConnectionProperties>
> <DataProvider>SQL</DataProvider>
> <ConnectString>data source=localhost;initial
> catalog=Northwind</ConnectString>
> <IntegratedSecurity>true</IntegratedSecurity>
> </ConnectionProperties>
> </DataSource>
> </DataSources>
> <Width>6.5in</Width>
> <DataSets>
> <DataSet Name="DataSet1">
> <Fields>
> <Field Name="SupplierName">
> <DataField>SupplierName</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="CategoryName">
> <DataField>CategoryName</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="ProductName">
> <DataField>ProductName</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="UnitPrice">
> <DataField>UnitPrice</DataField>
> <rd:TypeName>System.Decimal</rd:TypeName>
> </Field>
> </Fields>
> <Query>
> <DataSourceName>Northwind</DataSourceName>
> <CommandText>SELECT Suppliers.CompanyName AS SupplierName,
> Categories.CategoryName, Products.ProductName, Products.UnitPrice
> FROM Products INNER JOIN
> Suppliers ON Products.SupplierID => Suppliers.SupplierID INNER JOIN
> Categories ON Products.CategoryID => Categories.CategoryID</CommandText>
> </Query>
> </DataSet>
> </DataSets>
> <LeftMargin>1in</LeftMargin>
> <rd:SnapToGrid>true</rd:SnapToGrid>
> <rd:DrawGrid>true</rd:DrawGrid>
> <rd:ReportID>f63998f3-88b8-4c34-a16e-364595d1d7a8</rd:ReportID>
> <BottomMargin>1in</BottomMargin>
> <Language>en-US</Language>
> </Report>
>
>

Issuing multiple calls to SET IDENTITY_INSERT ON

My table's primary key is an identity column.
When I delete a row, I first copy it to another table and afterwards delete
it
from the original table.
When I want to restore the row, I use the SET IDENTITY_INSERT statement, in
order to avoid getting a new value for the identity column.
The only problem is when there are two clients trying to restore rows at the
same time - which causes an error, since the SET IDENTITY_INERT ON statement
can only be issued on one table at a time.
What can I do to fix this problem?Amir Shitrit wrote:
> My table's primary key is an identity column.
> When I delete a row, I first copy it to another table and afterwards
> delete it from the original table.
> When I want to restore the row, I use the SET IDENTITY_INSERT
> statement, in order to avoid getting a new value for the identity
> column.
> The only problem is when there are two clients trying to restore rows
> at the same time - which causes an error, since the SET
> IDENTITY_INERT ON statement can only be issued on one table at a time.
> What can I do to fix this problem?
SET IDENTITY_INSERT can be used by multiple sessions, even on the same
table, without a problem. Are you possibly running a web application
that is using pooled connections and both clients are getting the same
connection? If you're sure each client is using a different connection,
then there should be no problem.
If you are using the same connection, you may want to spawn temporary,
new connections when a restore operation occurs and close them once the
restore is complete.
David Gugick
Imceda Software
www.imceda.com|||Get rid of the IDENTITY Column and come up with a better way of assigning
keys to your rows.
"Amir Shitrit" <AmirShitrit@.discussions.microsoft.com> wrote in message
news:C400EE3C-E209-45F6-809F-CBB33DBC5FAE@.microsoft.com...
> My table's primary key is an identity column.
> When I delete a row, I first copy it to another table and afterwards
> delete it
> from the original table.
> When I want to restore the row, I use the SET IDENTITY_INSERT statement,
> in
> order to avoid getting a new value for the identity column.
> The only problem is when there are two clients trying to restore rows at
> the
> same time - which causes an error, since the SET IDENTITY_INERT ON
> statement
> can only be issued on one table at a time.
> What can I do to fix this problem?|||Hi Amir
Use Row level locks before inserting a row into the Table.
best regards
Chandra
"Amir Shitrit" <AmirShitrit@.discussions.microsoft.com> wrote in message
news:C400EE3C-E209-45F6-809F-CBB33DBC5FAE@.microsoft.com...
> My table's primary key is an identity column.
> When I delete a row, I first copy it to another table and afterwards
delete it
> from the original table.
> When I want to restore the row, I use the SET IDENTITY_INSERT statement,
in
> order to avoid getting a new value for the identity column.
> The only problem is when there are two clients trying to restore rows at
the
> same time - which causes an error, since the SET IDENTITY_INERT ON
statement
> can only be issued on one table at a time.
> What can I do to fix this problem?|||> When I delete a row, I first copy it to another table and afterwards
> delete it
> from the original table.
> When I want to restore the row, I use the SET IDENTITY_INSERT statement,
> in
> order to avoid getting a new value for the identity column.
This makes no sense to me. Why not just assign a new IDENTITY value, since
you apparently don't have dependent rows referencing the data in the
original table?
IDENTITY should be used only as an artificial key - if you care about what
the value is then don't use IDENTITY.
David Portas
SQL Server MVP
--|||"David Portas" wrote:

> This makes no sense to me. Why not just assign a new IDENTITY value, since
> you apparently don't have dependent rows referencing the data in the
> original table?
> IDENTITY should be used only as an artificial key - if you care about what
> the value is then don't use IDENTITY.
> --
> David Portas
> SQL Server MVP
> --
Well, I do have related records, thus I can't insert the row with a new
IDENTITY if I want to keep the relations.
It goes like this: I have a table full of Customers rows and another table
with CustomersReports rows (which is a child table of the Customers table).
When I delete a customer, I don't really delete it, but rather move it to an
archive table along with it's related CustomersReports child rows.
In another scenario, I might want to restore the Customer row to it's
original table, and restore it's related CustomersReports rows as well.
If I will restore the customer by assigning it a new ID, I will be compelled
to modify the foreign key in the child table as well.
I prefer to avoid it if possible.|||Add a CHAR(1) column called "Archive" and set it to 'Y' or 'N'. Adjust your
queries to include only Archive = 'Y'. The way you're doing it now, you're
leaving a lot of orphaned rows in related tables. From what you've
explained you don't even have Foreign Key constraints set up on these
tables, and won't be able to apply them at any point because of the manner
in which you've set this up.
"Amir Shitrit" <AmirShitrit@.discussions.microsoft.com> wrote in message
news:8578B5F3-08BF-4AB1-A950-8F5BE57AD138@.microsoft.com...
>
> "David Portas" wrote:
>
> Well, I do have related records, thus I can't insert the row with a new
> IDENTITY if I want to keep the relations.
> It goes like this: I have a table full of Customers rows and another table
> with CustomersReports rows (which is a child table of the Customers
> table).
> When I delete a customer, I don't really delete it, but rather move it to
> an
> archive table along with it's related CustomersReports child rows.
> In another scenario, I might want to restore the Customer row to it's
> original table, and restore it's related CustomersReports rows as well.
> If I will restore the customer by assigning it a new ID, I will be
> compelled
> to modify the foreign key in the child table as well.
> I prefer to avoid it if possible.|||"Michael C#" wrote:

> Add a CHAR(1) column called "Archive" and set it to 'Y' or 'N'. Adjust yo
ur
> queries to include only Archive = 'Y'. The way you're doing it now, you'r
e
> leaving a lot of orphaned rows in related tables. From what you've
> explained you don't even have Foreign Key constraints set up on these
> tables, and won't be able to apply them at any point because of the manner
> in which you've set this up.
> "Amir Shitrit" <AmirShitrit@.discussions.microsoft.com> wrote in message
> news:8578B5F3-08BF-4AB1-A950-8F5BE57AD138@.microsoft.com...
>
Hi.
I actually do have foreign key constrains, and when I'm moving a row to the
archive, I move all of it's related child rows as well (as I explaind before
).
Besides, Adding a column to the original table costs alot more than moving
rows to the archive - both in memory resources and performence.
Managing the table this way is also eazyer.
Thanks anyway.|||"Amir Shitrit" <AmirShitrit@.discussions.microsoft.com> wrote in message
news:2EC17FAD-43E6-41E8-9F6B-F5CACF4912FC@.microsoft.com...
> Hi.
> I actually do have foreign key constrains, and when I'm moving a row to
> the
> archive, I move all of it's related child rows as well (as I explaind
> before).
> Besides, Adding a column to the original table costs alot more than moving
> rows to the archive - both in memory resources and performence.
> Managing the table this way is also eazyer.
> Thanks anyway.
I missed your second post that explains how you're also keeping duplicates
of all your other tables as well to hold copies of your records.
I'm interested in learning more about how adding a CHAR(1) column to a
single table is much less efficient than maintaining and administering a
complete duplicate schema and writing additional code to move rows from one
schema to the other each time you want to eliminate them from your reports.
I'm a little surprised you find it "easier" to implement code that does
this:
INSERT INTO copy_of_schema_table1
SELECT * FROM real_schema_table1
WHERE MainID = 100
INSERT INTO copy_of_schema_table2
SELECT * FROM real_schema_table2
WHERE MainID = 100
--repeat for each table...
DELETE FROM real_schema_table2
WHERE MainID = 100
DELETE FROM real_schema_table1
WHERE MainID = 100
--repeat for each table...
All this to archive One set of related rows. Ahhh, probably better wrap all
of these INSERTs and DELETEs into a single transaction, so you don't end up
with out-of-sync schemas. Oh yeah, can't forget the IDENTITY_INSERT
statements. And it's a 'simple' matter of doing the reverse to "un-archive"
it. Yet something like this is 'inefficient'?
UPDATE schema_table1
SET Archive = 'Y'
WHERE MainID = 100
To "archive" a record, and
UPDATE schema_table1
SET Archive = 'N'
WHERE MainID = 100
To "un-archive" it.
Wow. As they say, to each his own, and whatever you find most clever.|||> Well, I do have related records, thus I can't insert the row with a new
> IDENTITY if I want to keep the relations.
In fact it should be easy to do this. See the example below. However, I
entirely agree with Michael. It's unnecessary and inefficient to move data
around in this way. Copying data from one table to another is a lot more
expensive than adding a one-byte column by any measure that I can think of.
CREATE TABLE Customers (cust_id INTEGER IDENTITY PRIMARY KEY, cust_name
VARCHAR(50) NOT NULL UNIQUE /* Note the alternate key */ )
CREATE TABLE CustomerReports (..., cust_id INTEGER REFERENCES Customrers
(cust_id), ...)
INSERT INTO Customers (cust_name, ...)
SELECT cust_name,
FROM CustomersArchive
WHERE ...
INSERT INTO CustomerReports (cust_id, ... /* other columns */)
SELECT C.cust_id, R. ... /* other columns */
FROM CustomerReportsArchive AS R
JOIN CustomersArchive AS A
ON R.cust_id = A.cust_id
JOIN Customers AS C
ON A.cust_name = C.cust_name
David Portas
SQL Server MVP
--

Issuing multiple calls to SET IDENTITY_INSERT ON

My table's primary key is an identity column.
When I delete a row, I first copy it to another table and afterwards delete it
from the original table.
When I want to restore the row, I use the SET IDENTITY_INSERT statement, in
order to avoid getting a new value for the identity column.
The only problem is when there are two clients trying to restore rows at the
same time - which causes an error, since the SET IDENTITY_INERT ON statement
can only be issued on one table at a time.
What can I do to fix this problem?Amir Shitrit wrote:
> My table's primary key is an identity column.
> When I delete a row, I first copy it to another table and afterwards
> delete it from the original table.
> When I want to restore the row, I use the SET IDENTITY_INSERT
> statement, in order to avoid getting a new value for the identity
> column.
> The only problem is when there are two clients trying to restore rows
> at the same time - which causes an error, since the SET
> IDENTITY_INERT ON statement can only be issued on one table at a time.
> What can I do to fix this problem?
SET IDENTITY_INSERT can be used by multiple sessions, even on the same
table, without a problem. Are you possibly running a web application
that is using pooled connections and both clients are getting the same
connection? If you're sure each client is using a different connection,
then there should be no problem.
If you are using the same connection, you may want to spawn temporary,
new connections when a restore operation occurs and close them once the
restore is complete.
David Gugick
Imceda Software
www.imceda.com|||Get rid of the IDENTITY Column and come up with a better way of assigning
keys to your rows.
"Amir Shitrit" <AmirShitrit@.discussions.microsoft.com> wrote in message
news:C400EE3C-E209-45F6-809F-CBB33DBC5FAE@.microsoft.com...
> My table's primary key is an identity column.
> When I delete a row, I first copy it to another table and afterwards
> delete it
> from the original table.
> When I want to restore the row, I use the SET IDENTITY_INSERT statement,
> in
> order to avoid getting a new value for the identity column.
> The only problem is when there are two clients trying to restore rows at
> the
> same time - which causes an error, since the SET IDENTITY_INERT ON
> statement
> can only be issued on one table at a time.
> What can I do to fix this problem?|||> When I delete a row, I first copy it to another table and afterwards
> delete it
> from the original table.
> When I want to restore the row, I use the SET IDENTITY_INSERT statement,
> in
> order to avoid getting a new value for the identity column.
This makes no sense to me. Why not just assign a new IDENTITY value, since
you apparently don't have dependent rows referencing the data in the
original table?
IDENTITY should be used only as an artificial key - if you care about what
the value is then don't use IDENTITY.
--
David Portas
SQL Server MVP
--|||"David Portas" wrote:
> > When I delete a row, I first copy it to another table and afterwards
> > delete it
> > from the original table.
> > When I want to restore the row, I use the SET IDENTITY_INSERT statement,
> > in
> > order to avoid getting a new value for the identity column.
> This makes no sense to me. Why not just assign a new IDENTITY value, since
> you apparently don't have dependent rows referencing the data in the
> original table?
> IDENTITY should be used only as an artificial key - if you care about what
> the value is then don't use IDENTITY.
> --
> David Portas
> SQL Server MVP
> --
Well, I do have related records, thus I can't insert the row with a new
IDENTITY if I want to keep the relations.
It goes like this: I have a table full of Customers rows and another table
with CustomersReports rows (which is a child table of the Customers table).
When I delete a customer, I don't really delete it, but rather move it to an
archive table along with it's related CustomersReports child rows.
In another scenario, I might want to restore the Customer row to it's
original table, and restore it's related CustomersReports rows as well.
If I will restore the customer by assigning it a new ID, I will be compelled
to modify the foreign key in the child table as well.
I prefer to avoid it if possible.|||Add a CHAR(1) column called "Archive" and set it to 'Y' or 'N'. Adjust your
queries to include only Archive = 'Y'. The way you're doing it now, you're
leaving a lot of orphaned rows in related tables. From what you've
explained you don't even have Foreign Key constraints set up on these
tables, and won't be able to apply them at any point because of the manner
in which you've set this up.
"Amir Shitrit" <AmirShitrit@.discussions.microsoft.com> wrote in message
news:8578B5F3-08BF-4AB1-A950-8F5BE57AD138@.microsoft.com...
>
> "David Portas" wrote:
>> > When I delete a row, I first copy it to another table and afterwards
>> > delete it
>> > from the original table.
>> > When I want to restore the row, I use the SET IDENTITY_INSERT
>> > statement,
>> > in
>> > order to avoid getting a new value for the identity column.
>> This makes no sense to me. Why not just assign a new IDENTITY value,
>> since
>> you apparently don't have dependent rows referencing the data in the
> > original table?
>> IDENTITY should be used only as an artificial key - if you care about
>> what
>> the value is then don't use IDENTITY.
>> --
>> David Portas
>> SQL Server MVP
>> --
> Well, I do have related records, thus I can't insert the row with a new
> IDENTITY if I want to keep the relations.
> It goes like this: I have a table full of Customers rows and another table
> with CustomersReports rows (which is a child table of the Customers
> table).
> When I delete a customer, I don't really delete it, but rather move it to
> an
> archive table along with it's related CustomersReports child rows.
> In another scenario, I might want to restore the Customer row to it's
> original table, and restore it's related CustomersReports rows as well.
> If I will restore the customer by assigning it a new ID, I will be
> compelled
> to modify the foreign key in the child table as well.
> I prefer to avoid it if possible.|||"Michael C#" wrote:
> Add a CHAR(1) column called "Archive" and set it to 'Y' or 'N'. Adjust your
> queries to include only Archive = 'Y'. The way you're doing it now, you're
> leaving a lot of orphaned rows in related tables. From what you've
> explained you don't even have Foreign Key constraints set up on these
> tables, and won't be able to apply them at any point because of the manner
> in which you've set this up.
> "Amir Shitrit" <AmirShitrit@.discussions.microsoft.com> wrote in message
> news:8578B5F3-08BF-4AB1-A950-8F5BE57AD138@.microsoft.com...
> >
> >
> > "David Portas" wrote:
> >
> >> > When I delete a row, I first copy it to another table and afterwards
> >> > delete it
> >> > from the original table.
> >> > When I want to restore the row, I use the SET IDENTITY_INSERT
> >> > statement,
> >> > in
> >> > order to avoid getting a new value for the identity column.
> >>
> >> This makes no sense to me. Why not just assign a new IDENTITY value,
> >> since
> >> you apparently don't have dependent rows referencing the data in the
> > > original table?
> >>
> >> IDENTITY should be used only as an artificial key - if you care about
> >> what
> >> the value is then don't use IDENTITY.
> >>
> >> --
> >> David Portas
> >> SQL Server MVP
> >> --
> >
> > Well, I do have related records, thus I can't insert the row with a new
> > IDENTITY if I want to keep the relations.
> > It goes like this: I have a table full of Customers rows and another table
> > with CustomersReports rows (which is a child table of the Customers
> > table).
> > When I delete a customer, I don't really delete it, but rather move it to
> > an
> > archive table along with it's related CustomersReports child rows.
> > In another scenario, I might want to restore the Customer row to it's
> > original table, and restore it's related CustomersReports rows as well.
> > If I will restore the customer by assigning it a new ID, I will be
> > compelled
> > to modify the foreign key in the child table as well.
> > I prefer to avoid it if possible.
>
Hi.
I actually do have foreign key constrains, and when I'm moving a row to the
archive, I move all of it's related child rows as well (as I explaind before).
Besides, Adding a column to the original table costs alot more than moving
rows to the archive - both in memory resources and performence.
Managing the table this way is also eazyer.
Thanks anyway.|||"Amir Shitrit" <AmirShitrit@.discussions.microsoft.com> wrote in message
news:2EC17FAD-43E6-41E8-9F6B-F5CACF4912FC@.microsoft.com...
> Hi.
> I actually do have foreign key constrains, and when I'm moving a row to
> the
> archive, I move all of it's related child rows as well (as I explaind
> before).
> Besides, Adding a column to the original table costs alot more than moving
> rows to the archive - both in memory resources and performence.
> Managing the table this way is also eazyer.
> Thanks anyway.
I missed your second post that explains how you're also keeping duplicates
of all your other tables as well to hold copies of your records.
I'm interested in learning more about how adding a CHAR(1) column to a
single table is much less efficient than maintaining and administering a
complete duplicate schema and writing additional code to move rows from one
schema to the other each time you want to eliminate them from your reports.
I'm a little surprised you find it "easier" to implement code that does
this:
INSERT INTO copy_of_schema_table1
SELECT * FROM real_schema_table1
WHERE MainID = 100
INSERT INTO copy_of_schema_table2
SELECT * FROM real_schema_table2
WHERE MainID = 100
--repeat for each table...
DELETE FROM real_schema_table2
WHERE MainID = 100
DELETE FROM real_schema_table1
WHERE MainID = 100
--repeat for each table...
All this to archive One set of related rows. Ahhh, probably better wrap all
of these INSERTs and DELETEs into a single transaction, so you don't end up
with out-of-sync schemas. Oh yeah, can't forget the IDENTITY_INSERT
statements. And it's a 'simple' matter of doing the reverse to "un-archive"
it. Yet something like this is 'inefficient'?
UPDATE schema_table1
SET Archive = 'Y'
WHERE MainID = 100
To "archive" a record, and
UPDATE schema_table1
SET Archive = 'N'
WHERE MainID = 100
To "un-archive" it.
Wow. As they say, to each his own, and whatever you find most clever.|||> Well, I do have related records, thus I can't insert the row with a new
> IDENTITY if I want to keep the relations.
In fact it should be easy to do this. See the example below. However, I
entirely agree with Michael. It's unnecessary and inefficient to move data
around in this way. Copying data from one table to another is a lot more
expensive than adding a one-byte column by any measure that I can think of.
CREATE TABLE Customers (cust_id INTEGER IDENTITY PRIMARY KEY, cust_name
VARCHAR(50) NOT NULL UNIQUE /* Note the alternate key */ )
CREATE TABLE CustomerReports (..., cust_id INTEGER REFERENCES Customrers
(cust_id), ...)
INSERT INTO Customers (cust_name, ...)
SELECT cust_name,
FROM CustomersArchive
WHERE ...
INSERT INTO CustomerReports (cust_id, ... /* other columns */)
SELECT C.cust_id, R. ... /* other columns */
FROM CustomerReportsArchive AS R
JOIN CustomersArchive AS A
ON R.cust_id = A.cust_id
JOIN Customers AS C
ON A.cust_name = C.cust_name
--
David Portas
SQL Server MVP
--|||On Sun, 08 May 2005 11:25:15 -0700, Amir Shitrit wrote:
> Hi.
> I actually do have foreign key constrains, and when I'm moving a row to the
> archive, I move all of it's related child rows as well (as I explaind before).
> Besides, Adding a column to the original table costs alot more than moving
> rows to the archive - both in memory resources and performence.
> Managing the table this way is also eazyer.
> Thanks anyway.
Although I fully agree with Michael and David - this is just a bad idea,
as far as I can see, you could do it using application locks. Like this:
CREATE PROCEDURE NeverRunsInParallel
AS
DECLARE @.result int
EXEC @.result = sp_getapplock @.Resource = 'myLock', @.LockMode = 'Exclusive'
IF @.result => 0
BEGIN
-- Do your thing, secure in the fact that
-- no other connection will do it at the same
-- time.
END
EXEC sp_releaseapplock @.Resource = 'myLock1'
<<
HTH,
Andrés Taylor