Ability to create dynamic subreports within report

I have the need to create a report that contains four subreports. Each of the four subreports can be any one of 20-30 different reports (layouts differ).
Is there a way to programatically change the name (or source) of the subreport at runtime? Would this end up creating a new rdl for each report?
Any help or ideas are greatly appreciated!!
RickGcan you put "all" your sub reports under the master report and hide your sub
reports.
then dynamically, simply show the targeted sub reports.
"RickG" <RickG@.discussions.microsoft.com> a écrit dans le message de
news:BAF79348-FB1D-45B0-861B-4A3BDA30DE58@.microsoft.com...
> I have the need to create a report that contains four subreports. Each of
the four subreports can be any one of 20-30 different reports (layouts
differ).
> Is there a way to programatically change the name (or source) of the
subreport at runtime? Would this end up creating a new rdl for each report?
> Any help or ideas are greatly appreciated!!
> RickGsql

A view based on a parameter

Hello.

After upsizing an access mdb backend to SQL Server 2005, some of the access frontend queries need rewriting.

One of my Access queries contains several joined tables (tables that used to live on users local c drive) and also references the value of a form control as its where criteria. When this is run against linked sql tables it is very slow.

So I thought I would use a view to represent the joined tables and link to that in Access, but views don't allow you to pass parameters. How do I create a view that I can link to in my access front end whose contents will vary based on a passed parameter from Access? I am OK at vba coding so I can call to this object in code if necessary. The recordsource needs to be updateable.

Any help would be appreciated!

Shirley

It sounds like you might want a stored procedure or user-defined function. Both of those allow you to pass parameters, both in and out. I found a really good reference to an Access-to-SQL Server information site here:

http://www.informit.com/discussion/index.asp?postid=6a35b938-8029-40a8-9511-95bd1b7f1255&rl=1

Buck Woody

A view based on a parameter

Hello.

After upsizing an access mdb backend to SQL Server 2005, some of the access frontend queries need rewriting.

One of my Access queries contains several joined tables (tables that used to live on users local c drive) and also references the value of a form control as its where criteria. When this is run against linked sql tables it is very slow.

So I thought I would use a view to represent the joined tables and link to that in Access, but views don't allow you to pass parameters. How do I create a view that I can link to in my access front end whose contents will vary based on a passed parameter from Access? I am OK at vba coding so I can call to this object in code if necessary. The recordsource needs to be updateable.

Any help would be appreciated!

Shirley

It sounds like you might want a stored procedure or user-defined function. Both of those allow you to pass parameters, both in and out. I found a really good reference to an Access-to-SQL Server information site here:

http://www.informit.com/discussion/index.asp?postid=6a35b938-8029-40a8-9511-95bd1b7f1255&rl=1

Buck Woody

A small COUNT() question

Hello,

I have a table which contains a boolean column. I need to write a query which returns a count() of all the TRUE occurences and a count() of all the FALSE occurences in this table, in 2 different columns in the result table.

Can it be done?

SELECT (SELECT COUNT() FROM TABLEx WHERE FIELDx=1) as CTrue,

(SELECT COUNT() FROM TABLEx WHERE FIELDx=0) as CFalse

|||

you should be using following SQL

select sum(case when field = 1 then 1 else 0 end) true, sum(case when field = 1 then 0 else 1 end) false

from table x

In this way, you can gain more performance

A simple query that returns the most current address

Hi,

I have an Address table which contains more than one addresses for a
particular member. I want to write a query that would only display
most current address. All addresses have a unique ID (addID).
Example:

memberID addID address1
--- -- ----------------
295 69 13 Auster St
295 70 465 Lorre Ct
295 71 P.O. Box 321
722 171 10 Hannaford Rd
722 172 Dubai, United Arab Emirates

Quote:

Originally Posted by

>From the table data above. The query should only return

memberID addID address1
--- -- ----------------
295 71 P.O. Box 321
722 172 Dubai, United Arab Emirates

I tried using Max and Group by function but it shows me all the rows.
If you can provide me with a sample code that would greatly
appreciated.

cheersRex wrote:

Quote:

Originally Posted by

I have an Address table which contains more than one addresses for a
particular member. I want to write a query that would only display
most current address. All addresses have a unique ID (addID).
Example:
>
memberID addID address1
--- -- ----------------
295 69 13 Auster St
295 70 465 Lorre Ct
295 71 P.O. Box 321
722 171 10 Hannaford Rd
722 172 Dubai, United Arab Emirates
>

Quote:

Originally Posted by

>>From the table data above. The query should only return

>
memberID addID address1
--- -- ----------------
295 71 P.O. Box 321
722 172 Dubai, United Arab Emirates
>
I tried using Max and Group by function but it shows me all the rows.
If you can provide me with a sample code that would greatly
appreciated.

Assuming that each member's most current address has the largest
addID value, and that addID values are not re-used from one member
to the next:

select memberID, addID, address1
from the_table
where addID in (
select max(addID)
from the_table
group by memberID
)

A Referense All Query

I have a table that contains a bunch of ID's and I am trying to write a query that will find every combination of id (order of t he ID's is not needed...IE: a,b is the same as b,a) but I cant seem to figure it out. Any help?

Here is my example:

Table

[ ID ]
[ a ]
[ b ]
[ c ]

Results

[ ID1 | ID2 ]
[ a | a ]
[ a | b ]
[ a | c ]
[ b | b ]
[ b | c ]

maybe this:

Code Snippet

create table #t (ID char(1))

insert into #t

select 'a'

union all select 'b'

union all select 'c'

;with cte as

(

select t1.id as id1, t2.id as id2, t1.cksum + t2.cksum as hash,

row_number() over(partition by t1.cksum + t2.cksum order by t1.id, t2.id) as rno

from

( select id, checksum(id) as cksum from #t) t1

full join ( select id, checksum(id) as cksum from #t) t2

on 1=1

)

select id1, id2

from cte

where rno = 1

A question related to stored procedure

Suppose there is a database table which contains information of a
complete binary tree.
A complete binary tree is always populated depth wise. A certain level
is filled up completely before going further level
down.
The table contains sequential information of the tree. As shown in the
following, the table on the right contains the tree information on the
left after inserting 4 nodes.
ID Name tree
1 N0 N0
2 N1
N1 N2
3 N2
And after inserting 4 nodes the table becomes as below.
ID Name Tree
1 N0 N0
2 N1 N1 N2
3 N2 N3
4 N3
Given a table containing the node information (the number of nodes is
arbitrary) of a complete binary tree as above.
A stored procedure have to write to construct and print a table,
where
the names of the nodes will appear in different rows and
columns of
the table and
the total arrangement will appear like a binary tree of the
following figure.
N0
N1 N2
N3
N4 N5 N6
N7 N8
Hi
"babu" wrote:

> Suppose there is a database table which contains information of a
> complete binary tree.
> A complete binary tree is always populated depth wise. A certain level
> is filled up completely before going further level
> down.
> The table contains sequential information of the tree. As shown in the
> following, the table on the right contains the tree information on the
> left after inserting 4 nodes.
> ID Name tree
> 1 N0 N0
> 2 N1
> N1 N2
> 3 N2
> And after inserting 4 nodes the table becomes as below.
> ID Name Tree
> 1 N0 N0
> 2 N1 N1 N2
> 3 N2 N3
> 4 N3
> Given a table containing the node information (the number of nodes is
> arbitrary) of a complete binary tree as above.
> A stored procedure have to write to construct and print a table,
> where
> · the names of the nodes will appear in different rows and
> columns of
> the table and
> · the total arrangement will appear like a binary tree of the
> following figure.
> N0
> N1 N2
> N3
> N4 N5 N6
> N7 N8
>
Printing is a function for the client to deal with not SQL Server. How you
store and return the hierarchy/tree from SQL Server depends on the model you
are using e.g. nested set or adjacency. Joe Celko's Trees and Hierarchies
book ISBN 1-55860-920-2 (and there are plenty of posts on this!) will give
you some background on this. SQL Server 2005 has the ability for recursive
queries using CTEs which can be ustilised to traverse your hierarchy if you
are using this version.
John
|||I believe one or more of Itzik Ben-Gan's books cover trees/graphs as well.
Very good reads.
TheSQLGuru
President
Indicium Resources, Inc.
"babu" <nasif4003@.gmail.com> wrote in message
news:1179497643.034074.249710@.p77g2000hsh.googlegr oups.com...
Suppose there is a database table which contains information of a
complete binary tree.
A complete binary tree is always populated depth wise. A certain level
is filled up completely before going further level
down.
The table contains sequential information of the tree. As shown in the
following, the table on the right contains the tree information on the
left after inserting 4 nodes.
ID Name tree
1 N0 N0
2 N1
N1 N2
3 N2
And after inserting 4 nodes the table becomes as below.
ID Name Tree
1 N0 N0
2 N1 N1 N2
3 N2 N3
4 N3
Given a table containing the node information (the number of nodes is
arbitrary) of a complete binary tree as above.
A stored procedure have to write to construct and print a table,
where
the names of the nodes will appear in different rows and
columns of
the table and
the total arrangement will appear like a binary tree of the
following figure.
N0
N1 N2
N3
N4 N5 N6
N7 N8

A question related to stored procedure

Suppose there is a database table which contains information of a
complete binary tree.
A complete binary tree is always populated depth wise. A certain level
is filled up completely before going further level
down.
The table contains sequential information of the tree. As shown in the
following, the table on the right contains the tree information on the
left after inserting 4 nodes.
ID Name tree
1 N0 N0
2 N1
N1 N2
3 N2
And after inserting 4 nodes the table becomes as below.
ID Name Tree
1 N0 N0
2 N1 N1 N2
3 N2 N3
4 N3
Given a table containing the node information (the number of nodes is
arbitrary) of a complete binary tree as above.
A stored procedure have to write to construct and print a table,
where
=B7 the names of the nodes will appear in different rows and
columns of
the table and
=B7 the total arrangement will appear like a binary tree of the
following figure.
N0
=20
N1 N2
N3
N4 N5 N6
N7 N8Hi
"babu" wrote:

> Suppose there is a database table which contains information of a
> complete binary tree.
> A complete binary tree is always populated depth wise. A certain level
> is filled up completely before going further level
> down.
> The table contains sequential information of the tree. As shown in the
> following, the table on the right contains the tree information on the
> left after inserting 4 nodes.
> ID Name tree
> 1 N0 N0
> 2 N1
> N1 N2
> 3 N2
> And after inserting 4 nodes the table becomes as below.
> ID Name Tree
> 1 N0 N0
> 2 N1 N1 N2
> 3 N2 N3
> 4 N3
> Given a table containing the node information (the number of nodes is
> arbitrary) of a complete binary tree as above.
> A stored procedure have to write to construct and print a table,
> where
> · the names of the nodes will appear in different rows and
> columns of
> the table and
> · the total arrangement will appear like a binary tree of the
> following figure.
> N0
> N1 N2
> N3
> N4 N5 N6
> N7 N8
>
Printing is a function for the client to deal with not SQL Server. How you
store and return the hierarchy/tree from SQL Server depends on the model you
are using e.g. nested set or adjacency. Joe Celko's Trees and Hierarchies
book ISBN 1-55860-920-2 (and there are plenty of posts on this!) will give
you some background on this. SQL Server 2005 has the ability for recursive
queries using CTEs which can be ustilised to traverse your hierarchy if you
are using this version.
John|||I believe one or more of Itzik Ben-Gan's books cover trees/graphs as well.
Very good reads.
TheSQLGuru
President
Indicium Resources, Inc.
"babu" <nasif4003@.gmail.com> wrote in message
news:1179497643.034074.249710@.p77g2000hsh.googlegroups.com...
Suppose there is a database table which contains information of a
complete binary tree.
A complete binary tree is always populated depth wise. A certain level
is filled up completely before going further level
down.
The table contains sequential information of the tree. As shown in the
following, the table on the right contains the tree information on the
left after inserting 4 nodes.
ID Name tree
1 N0 N0
2 N1
N1 N2
3 N2
And after inserting 4 nodes the table becomes as below.
ID Name Tree
1 N0 N0
2 N1 N1 N2
3 N2 N3
4 N3
Given a table containing the node information (the number of nodes is
arbitrary) of a complete binary tree as above.
A stored procedure have to write to construct and print a table,
where
the names of the nodes will appear in different rows and
columns of
the table and
the total arrangement will appear like a binary tree of the
following figure.
N0
N1 N2
N3
N4 N5 N6
N7 N8

A question related to stored procedure

Suppose there is a database table which contains information of a
complete binary tree.
A complete binary tree is always populated depth wise. A certain level
is filled up completely before going further level
down.
The table contains sequential information of the tree. As shown in the
following, the table on the right contains the tree information on the
left after inserting 4 nodes.
ID Name tree
1 N0 N0
2 N1
N1 N2
3 N2
And after inserting 4 nodes the table becomes as below.
ID Name Tree
1 N0 N0
2 N1 N1 N2
3 N2 N3
4 N3
Given a table containing the node information (the number of nodes is
arbitrary) of a complete binary tree as above.
A stored procedure have to write to construct and print a table,
where
=B7 the names of the nodes will appear in different rows and
columns of
the table and
=B7 the total arrangement will appear like a binary tree of the
following figure.
N0
N1 N2
N3
N4 N5 N6
N7 N8Hi
"babu" wrote:
> Suppose there is a database table which contains information of a
> complete binary tree.
> A complete binary tree is always populated depth wise. A certain level
> is filled up completely before going further level
> down.
> The table contains sequential information of the tree. As shown in the
> following, the table on the right contains the tree information on the
> left after inserting 4 nodes.
> ID Name tree
> 1 N0 N0
> 2 N1
> N1 N2
> 3 N2
> And after inserting 4 nodes the table becomes as below.
> ID Name Tree
> 1 N0 N0
> 2 N1 N1 N2
> 3 N2 N3
> 4 N3
> Given a table containing the node information (the number of nodes is
> arbitrary) of a complete binary tree as above.
> A stored procedure have to write to construct and print a table,
> where
> · the names of the nodes will appear in different rows and
> columns of
> the table and
> · the total arrangement will appear like a binary tree of the
> following figure.
> N0
> N1 N2
> N3
> N4 N5 N6
> N7 N8
>
Printing is a function for the client to deal with not SQL Server. How you
store and return the hierarchy/tree from SQL Server depends on the model you
are using e.g. nested set or adjacency. Joe Celko's Trees and Hierarchies
book ISBN 1-55860-920-2 (and there are plenty of posts on this!) will give
you some background on this. SQL Server 2005 has the ability for recursive
queries using CTEs which can be ustilised to traverse your hierarchy if you
are using this version.
John|||I believe one or more of Itzik Ben-Gan's books cover trees/graphs as well.
Very good reads.
--
TheSQLGuru
President
Indicium Resources, Inc.
"babu" <nasif4003@.gmail.com> wrote in message
news:1179497643.034074.249710@.p77g2000hsh.googlegroups.com...
Suppose there is a database table which contains information of a
complete binary tree.
A complete binary tree is always populated depth wise. A certain level
is filled up completely before going further level
down.
The table contains sequential information of the tree. As shown in the
following, the table on the right contains the tree information on the
left after inserting 4 nodes.
ID Name tree
1 N0 N0
2 N1
N1 N2
3 N2
And after inserting 4 nodes the table becomes as below.
ID Name Tree
1 N0 N0
2 N1 N1 N2
3 N2 N3
4 N3
Given a table containing the node information (the number of nodes is
arbitrary) of a complete binary tree as above.
A stored procedure have to write to construct and print a table,
where
· the names of the nodes will appear in different rows and
columns of
the table and
· the total arrangement will appear like a binary tree of the
following figure.
N0
N1 N2
N3
N4 N5 N6
N7 N8

A question about udf versus sp in a specific context...

Given the following objective:

1. Assume that I have a table that contains two fields: an auto-numbered id and an integer value
2. Check to see if a record exists in a table based on a parameter query of the integer value
3. If the record exists, return the record id
4. If the record does not exist, insert a new record into the table (using the parameter value as data) and return the auto-numbered id of the new record

I can do each of these things as a sequence of individual steps, of course, but it seems to me that I ought to be able to do it with a single udf (or perhaps a specialized query) that would be more efficient. I couldn't find something like this in the beginning SQL Express books I have on hand and I also didn't find anything exactly on point on this newsgroup or a search of Google. However, I am sure the answer is 'out there' and I am hoping that someone can point me in the right direction. Thanks!

Duncan

In thinking further about my original question and digging into one of my old SQL 2000 books, I concocted the following usp which seems to work:

CREATE PROC [dbo].[usp_GetKitId] @.product_variant_id int, @.kit_id int OUTPUT
AS
SELECT @.kit_id = id FROM kits WHERE (product_variant_id = @.product_variant_id)
IF @.@.ROWCOUNT = 1
RETURN @.kit_id
ELSE
BEGIN
INSERT INTO [dbo].[kits]([product_variant_id]) VALUES (@.product_variant_id)
SET @.kit_id = SCOPE_IDENTITY()
RETURN @.kit_id
END

Does anyone see any particular issues with the above code? Thanks.

Duncan

|||

Perhaps something like this:

CREATE PROC [dbo].[usp_GetKitId] @.product_variant_id int, @.kit_id int OUTPUT AS
BEGIN
SET NOCOUNT ON

IF EXISTS(SELECT * FROM kits WHERE product_variant_id = @.product_variant_id)

BEGIN
SELECT @.kit_id = id FROM kits WHERE product_variant_id = product_variant_id
END
ELSE
BEGIN
INSERT INTO [dbo].[kits]([product_variant_id]) VALUES (@.product_variant_id)
SET @.kit_id = SCOPE_IDENTITY()
END

SET NOCOUNT OFF

END

|||Thanks for helping me out!|||

To give back in a small way, here is a slightly modified version of my earlier repsonse:

CREATE PROC [dbo].[usp_GetKitId] @.kit_id int OUTPUT, @.product_variant_id int
AS
SET NOCOUNT ON
SELECT @.kit_id = id FROM kits WHERE (product_variant_id = @.product_variant_id)
IF NOT @.@.ROWCOUNT = 1
BEGIN
INSERT INTO [dbo].[kits]([product_variant_id]) VALUES (@.product_variant_id)
SET @.kit_id = SCOPE_IDENTITY()
END
SET NOCOUNT OFF

Then called from code-behind like so:

PublicSharedFunction GetKitId(ByVal product_variant_id)AsInteger'If a kit exists for the product variant, return the kit id; if a kit does'not exist for the product variant, create a new kit and return the new'kit's id.Dim connectionStringAsString = ConfigurationManager.ConnectionStrings("atheniqueonlineConnectionString").ConnectionStringDim connectionAsNew SqlConnection(connectionString)Dim cmdAsNew SqlCommandDim kitIdAsIntegerWith cmd

.Connection = connection

.CommandText =

"usp_GetKitId"

.CommandType = CommandType.StoredProcedure

.Parameters.Clear()

.Parameters.Add(

New SqlParameter("kit_id", SqlDbType.Int))

.Parameters(

"kit_id").Direction = ParameterDirection.Output

.Parameters(

"kit_id").Value = 0

.Parameters.Add(

New SqlParameter("product_variant_id", SqlDbType.Int))

.Parameters(

"product_variant_id").Direction = ParameterDirection.Input

.Parameters(

"product_variant_id").Value = product_variant_idEndWithTry'Fill table

connection.Open()

Try

cmd.ExecuteNonQuery()

Catch exAs Exception'Handle exceptionsEndTryCatch eAs SqlException' Handle exceptionsFinally

connection.Close()

EndTry'Return the result

kitId = cmd.Parameters(

"kit_id").ValueReturn kitIdEndFunction

A question about association rules

Hi

I am doing the Market basket analysis for a retailer using association rule. The whole data set is huge which contains grocery, clothes and books etc. If I want to check out the relationship between several different clothes brands, (e.g. LEVI'S and adidas), should I just remove all the grocery and books transactions, use the subset which only contains clothes transactions to re-run the association rules? Is this gonna work?

Thanks in advance!

You can create differents data source views (dsv) as a support for a model desired. For the dsv you can build the apropriate view in SQL Server that select that several different clothes brands (in WHERE clause). You don't have to remove any transaction.

Gigi Ciubuc

www.sqlserver.ro

|||Great help, thanks a lot.

a problem with transactions

hi
i created an explicite transaction which contains 2 update statements
both of these two update statements act on a single table with the name of
"TABLE1".
these table has two column "name varchar(20)" & "account (int)".
this table also has a check constraint on the "account" column which resric
ts
the value of this column to be greater than 100.
here is the table before execting the transaction:
<<
NAME ACCOUNT
' ali ' 140
' nima ' 200
here is the code of my transaction:
<<
begin transaction
update table1 set account=account-50 where name='ali'
update table1 set account=account+50 where name='esi'
commit transaction
and this is table1 after executing the transaction:
<<
NAME ACCOUNT
' ali ' 140
' nima ' 250
if the value of the "account" column be for example " 140" before executing
the
transaction , with executing the transaction a check constraint violation
happens
and the first update statement does not affect on the table but the second
update statement affect permanently on the table.
alltransactions are "ATOMIC" .Either all the satements inside them should
perform or none of them. with executing this transaction the "CONSISTANCY"
feature off transactions is ignored too.(140+200 before execution) &
(140+250 after execution).
wwwwhhhhhhhhhhhhhhyyyyyyyyyyyyyyyy'''The problem is that you don't have any error handling. A constraint
violation does not terminate a batch or a transaction, it only terminates
the offending statement and sets @.@.ERROR. You must always, Always, ALWAYS
check @.@.ERROR after every DML statement (INSERT, UPDATE, DELETE) and every
stored procedure call. It is also possible for a transaction to be rolled
back when an error occurs, but for the batch to continue executing. That
means that statements following an error are no longer protected by a
transaction! Failing to include error handling defeats the purpose of
transactions.
There is a statement, SET XACT_ABORT, which is supposed to cause every
transaction to be rolled back and terminated whenever an error occurs, but I
don't rely on it. I use @.@.ERROR and @.@.ROWCOUNT because that gives me more
control over the error recovery process.
"pooyan_pdm" <pooyan_pdm@.discussions.microsoft.com> wrote in message
news:3C02A013-830C-42C7-AB75-C53348528FDA@.microsoft.com...
> hi
> i created an explicite transaction which contains 2 update statements
> both of these two update statements act on a single table with the name of
> "TABLE1".
> these table has two column "name varchar(20)" & "account (int)".
> this table also has a check constraint on the "account" column which
resricts
> the value of this column to be greater than 100.
> here is the table before execting the transaction:
> <<
> NAME ACCOUNT
>
> ' ali ' 140
> ' nima ' 200
>
>
>
> here is the code of my transaction:
> <<
> begin transaction
> update table1 set account=account-50 where name='ali'
> update table1 set account=account+50 where name='esi'
> commit transaction
>
>
> and this is table1 after executing the transaction:
> <<
> NAME ACCOUNT
>
> ' ali ' 140
> ' nima ' 250
>
>
>
> if the value of the "account" column be for example " 140" before
executing
> the
> transaction , with executing the transaction a check constraint
violation
> happens
> and the first update statement does not affect on the table but the
second
> update statement affect permanently on the table.
> alltransactions are "ATOMIC" .Either all the satements inside them should
> perform or none of them. with executing this transaction the
"CONSISTANCY"
> feature off transactions is ignored too.(140+200 before execution) &
> (140+250 after execution).
>
> wwwwhhhhhhhhhhhhhhyyyyyyyyyyyyyyyy'''
>|||Brian
But there are some kind of error that terminate batches and @.@.ERROR does not
catch them
"Brian Selzer" <brian@.selzer-software.com> wrote in message
news:%23wF7tWdkFHA.2916@.TK2MSFTNGP14.phx.gbl...
> The problem is that you don't have any error handling. A constraint
> violation does not terminate a batch or a transaction, it only terminates
> the offending statement and sets @.@.ERROR. You must always, Always, ALWAYS
> check @.@.ERROR after every DML statement (INSERT, UPDATE, DELETE) and every
> stored procedure call. It is also possible for a transaction to be rolled
> back when an error occurs, but for the batch to continue executing. That
> means that statements following an error are no longer protected by a
> transaction! Failing to include error handling defeats the purpose of
> transactions.
> There is a statement, SET XACT_ABORT, which is supposed to cause every
> transaction to be rolled back and terminated whenever an error occurs, but
> I
> don't rely on it. I use @.@.ERROR and @.@.ROWCOUNT because that gives me more
> control over the error recovery process.
> "pooyan_pdm" <pooyan_pdm@.discussions.microsoft.com> wrote in message
> news:3C02A013-830C-42C7-AB75-C53348528FDA@.microsoft.com...
> resricts
> executing
> violation
> second
> "CONSISTANCY"
>|||You're absolutely right. It's important to keep that in mind. It's also
important to understand that the nature of error and transaction management
change within triggers. However, neither of these supplant the requirement
to manage errors that do not terminate a batch.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uylJrjekFHA.3936@.TK2MSFTNGP10.phx.gbl...
> Brian
> But there are some kind of error that terminate batches and @.@.ERROR does
not
> catch them
>
>
> "Brian Selzer" <brian@.selzer-software.com> wrote in message
> news:%23wF7tWdkFHA.2916@.TK2MSFTNGP14.phx.gbl...
terminates
ALWAYS
every
rolled
That
but
more
should
>

a Null check for a single record

Hi,
I have a table that may contains Null filled columns.
I have a SP that do Update and insert.
How can i do a null check to avoid trying to update null fields?
let say Type_1 column is null, I need a condition that will do a quick check (single record is sufficient) to see that it has null value and to skip the update command:

CREATE PROCEDURE Lan_Insert_Data_Type
as
DECLARE @.Single_Rec int
Begin

-- ??? HERE comes the condition --

UPDATE Data_type
SET Product_Num = LanTable.ProductNum,
Data =LanTable.Type_1
FROM Data_Type p
JOIN LanTable
ON Lantable.ProductNum = p.Product_num where p.Data_Type =1

Thanks

Yossi--Try This

CASE When Type_1 IS NULL
Then
--Do nothing
SET @.Single_Rec = @.Single_Rec

ELSE

UPDATE Data_type
SET Product_Num = LanTable.ProductNum,
Data =LanTable.Type_1
FROM Data_Type p
JOIN LanTable
ON Lantable.ProductNum = p.Product_num where p.Data_Type =1

END|||Originally posted by eschapir
--Try This

CASE When Type_1 IS NULL
Then
--Do nothing
SET @.Single_Rec = @.Single_Rec

ELSE

UPDATE Data_type
SET Product_Num = LanTable.ProductNum,
Data =LanTable.Type_1
FROM Data_Type p
JOIN LanTable
ON Lantable.ProductNum = p.Product_num where p.Data_Type =1

END
Thaks for the reply
but its not working,
maybe i missed some thing|||/* something like this ? */

if not exists(
select *
FROM Data_Type p
JOIN LanTable
ON Lantable.ProductNum = p.Product_num
where p.Data_Type=1 and LanTable.Type_1 is null
)
UPDATE Data_type
SET Product_Num = LanTable.ProductNum,
Data =LanTable.Type_1
FROM Data_Type p
JOIN LanTable
ON Lantable.ProductNum = p.Product_num where p.Data_Type =1|||Originally posted by ispaleny
/* something like this ? */

if not exists(
select *
FROM Data_Type p
JOIN LanTable
ON Lantable.ProductNum = p.Product_num
where p.Data_Type=1 and LanTable.Type_1 is null
)
UPDATE Data_type
SET Product_Num = LanTable.ProductNum,
Data =LanTable.Type_1
FROM Data_Type p
JOIN LanTable
ON Lantable.ProductNum = p.Product_num where p.Data_Type =1

Works great...
Thanks mate

A hashtable that contains millions of records

My database has a hash table that contains millions of records.
Each day thousands of records will be inserted/updated/deleted/read from
this table.
Are there potential problems that I should be awared of?
Is there a better solution not to use just one table?I don't understand your question. A hash table is not a physical database
structure. It's an in-memory structure that is created at the time of query
execution to optimize direct retrieval by key. This is often created when
no appropriate index exists on the table.
SQL Server indexes are b-trees. These are efficient even with tables
containing of billions of rows.
Happy Holidays
Dan Guzman
SQL Server MVP
"Lang" <Lang@.discussions.microsoft.com> wrote in message
news:D3E18EB5-2318-49DC-8178-03FF7980563E@.microsoft.com...
> My database has a hash table that contains millions of records.
> Each day thousands of records will be inserted/updated/deleted/read from
> this table.
> Are there potential problems that I should be awared of?
> Is there a better solution not to use just one table?|||> Is there a better solution not to use just one table?
Just to add to Dan's answer: this one depends on your business problem. Data
model should be done logically correct first, afterwards you can do some
compromises because of performance.
Dejan Sarka, SQL Server MVP
Mentor
www.SolidQualityLearning.com|||"Lang" <Lang@.discussions.microsoft.com> wrote in message
news:D3E18EB5-2318-49DC-8178-03FF7980563E@.microsoft.com...
> My database has a hash table that contains millions of records.
> Each day thousands of records will be
inserted/updated/deleted/read from
> this table.
> Are there potential problems that I should be awared of?
> Is there a better solution not to use just one table?
Lang,
You have asked a question about the design of your table.
But you have not provided any information about that table (please
understand that, "The database has a hash table," is not a table
description that is useful in answering your question).
The link http://www.aspfaq.com/etiquette.asp?id=5006,
is excellent when it comes to detailing how to provide
the information that will best enable others to answer
your questions.
Sincerely,
Chris O.