Bind DataSource at runtime and Adding fields [A little help please]

hi,

I am using Crystal Reports 8.5 and Visual Biasic 6.0, the technique that I
was using was I connect the database using the ODBC to the crytal reports and design my report by draging the fields to the reports.

In my new assignment I have to bind a ADODB recordset to the report at
run time.

I got 2 Problems.

01. How can I bind a ADODB recordset to the report at run time
02. After binding the recordset how can I add the fileds to the report
content area

A small help on this please.

Thank you.Dim conn As ADODB.Connection 'CONNECTION TO BROKER QUERIES
Dim rs As ADODB.Recordset 'HOLDS ALL DATA RETURNED FROM QUERY
Dim crystal As CRAXDRT.Application 'LOADS REPORT FROM FILE
Dim Report As CRAXDRT.Report 'HOLDS REPORT

Set conn = New ADODB.Connection
conn.Open "Provider=MSDAORA.1;User ID=scott;Data Source=qb;Persist Security Info=False", "sebsv4", "sebsv4pwd" 'THESE OPTION VALUES ARE BEST FOR VB

Set rs = New ADODB.Recordset
rs.Open q, conn, adOpenStatic, adLockReadOnly

Set crystal = New CRAXDRT.Application 'MANAGES REPORTS

Set Report = crystal.OpenReport(App.Path & "\report1.rpt") 'OPEN OUR REPORT

Report.DiscardSavedData 'CLEARS REPORT SO WE WORK FROM RECORDSET
Report.Database.SetDataSource rs 'LINK REPORT TO RECORDSET

CRViewer1.ReportSource = Report 'LINK VIEWER TO REPORT
CRViewer1.ViewReport 'SHOW REPORT|||thanks for the quick reply sraheem

another small problem,

if I am using the report designer in VB like that, when I have draged a field name in to the report
it shows the field name as

field:<TableName>.<ColumnName>

now If I am doing the data binding at run time how can I add the recordset columns in to the report, since there is no data property on report fileds

Thanx.|||just capture report sections like below and add fields...

Dim crxdetail As CRAXDDRT.Section 'Section Decleration
Dim crxPageHeader As CRAXDDRT.Section 'Section Decleration
Dim crxPageFooter As CRAXDDRT.Section 'Section Decleration

Set crxdetail = Report.Sections.Item("D") 'Detail Section
Set crxPageHeader = Report.Sections.Item("PH") 'Detail Section
Set crxPageFooter = Report.Sections.Item("PF") 'Detail Section

crxdetail.AddFieldObject "{e." & rs(i).Name & "}", 1000, 0

where e is the report command object name or table name in your case.

Binary Tree Problem in SQLServer

Hi Members
I m new in this forum, I have have Binary Tree database in SQL Server with
table fields(Mcode,Mname,LeftMCode,RightMCode). i want to display downline and pairs of each member.
can any one help me out?
Regards
Amirbkkread this.
is for OLAP, but, maybe help you.

http://www.codeproject.com/cs/database/tree_olap.asp|||From your column names, I'm guessing your are dealing with a Nested Set model ala' Joe Celko, rather than the more common Adjencency Model.
You should be able to find some code samples here:
http://www.intelligententerprise.com/001020/celko.jhtml?_requestid=1266295
...or by googling "Celko Nested Sets".|||Thanx for reply

But i have little prob that all nodes will be freely put on any side means any member can be put on left or right side and my db will grow very fast and estimated records will be more than 100000 with in few months

so if i use lineage and depth methods can i store path in one fields
and if use set model than i think , left node must be fill first and right node must be +1 than its left node.
and in my db any node(member) should be put on any side

so guys and suggestion will be higly appreciated.
i m going to build db with in few days , plz help me out.

Regards,

Amir|||Thanx for reply

But i have little prob that all nodes will be freely put on any side means any member can be put on left or right side and my db will grow very fast and estimated records will be more than 100000 with in few months

so if i use lineage and depth methods can i store path in one fields
and if use set model than i think , left node must be fill first and right node must be +1 than its left node.
and in my db any node(member) should be put on any side

so guys and suggestion will be higly appreciated.
i m going to build db with in few days , plz help me out.

Regards,

Amir

Binary fields efficiency

Hi there,
I have a record where associated with it are upto 1000 floating point
values, would it be more efficient to have a variable sized binary field to
hold an array of floating point values, or is it fine to create another tabl
e
to hold these floating points required of the records. I'll have thousands
of the records, although they wont be accessed often - ie, once during an
import.
Thanks
K.Create the table. You are using a Relational database, which works
really well when the data is structured relationally. Use the
relational approach and you probably won't have to ask for help here
very often. Use the non-relational approach and you will generally
get a lot of heartburn when SQL Server doesn't have the tools to work
with the data. And when you end up asking for help here, you will
mostly get criticism of your database design!
So create the table. SQL Server handles thousands of rows very
efficiently, and the SQL you will need to manipulate them will be
clear and straightforward.
Roy Harvey
Beacon Falls, CT
On Thu, 2 Mar 2006 02:45:28 -0800, "Katie Viola"
<KatieViola@.discussions.microsoft.com> wrote:

>Hi there,
>I have a record where associated with it are upto 1000 floating point
>values, would it be more efficient to have a variable sized binary field t
o
>hold an array of floating point values, or is it fine to create another tab
le
>to hold these floating points required of the records. I'll have thousands
>of the records, although they wont be accessed often - ie, once during an
>import.
>Thanks
>K.

Binary field usage in SQL Server

Can anyone point me in the right direction to find documentation for the problem below?
I need to store and retrieve ten fields of 16-bits each for testing 16 true-false conditions (a total of 160 bits in each record) so I think I'd like to use ten 2-byte binary fields (160 "bit" fields would be quite unmanageble, if even possible [I think there is some kind of limit to the number of fields in a single record]).
I'm not quickly finding in the SQL Server's online documentation how to test for, use and update binary fields. I'll keep looking, but can anyone point me in the right direction? I'm using VB, if that makes any difference.

The post below is from SQL Server BOL (books online) documentation on BIT, BINARY and VARBINARY data types. I am assuming you know BIT is proprietry because of three valued logic there is no Boolean data type in ANSI SQL. If you need more information post again. Hope this helps.

bit
Integer data type 1, 0, or NULL.

Remarks
Columns of type bit cannot have indexes on them.

Microsoft? SQL Server? optimizes the storage used for bit columns. If there are 8 or fewer bit columns in a table, the columns are stored as 1 byte. If there are from 9 through 16 bit columns, they are stored as 2 bytes, and so on.

binary and varbinary
Binary data types of either fixed-length (binary) or variable-length (varbinary).

binary [ ( n ) ]

Fixed-length binary data of n bytes. n must be a value from 1 through 8,000. Storage size is n+4 bytes.

varbinary [ ( n ) ]

Variable-length binary data of n bytes. n must be a value from 1 through 8,000. Storage size is the actual length of the data entered + 4 bytes, not n bytes. The data entered can be 0 bytes in length. The SQL-92 synonym for varbinary is binary varying.

Remarks
When n is not specified in a data definition, or variable declaration statement, the default length is 1. When n is not specified with the CAST function, the default length is 30.

Use binary when column data entries are consistent in size.

Use varbinary when column data entries are inconsistent in size.

BIDS - How to turn-off auto naming of fields

Hi:

When I create a report, the wizard auto-renames fields.

E.g. if Table_COLUMN_Name=CustomerName, the list column header in the report will change to Customer Name. The wizard figures out Friendly names based on capitalization, and underscores and so on.

Similar behavior occurs even in SSAS projects Dimensions Wizard.

Is there a way to turn of this extra cuteness?

TIA

Kar

Hi:

Request help once again on this.

TIA

Kar

BIDS - How to turn-off auto naming of fields

Hi:

When I create a report, the wizard auto-renames fields.

E.g. if Table_COLUMN_Name=CustomerName, the list column header in the report will change to Customer Name. The wizard figures out Friendly names based on capitalization, and underscores and so on.

Similar behavior occurs even in SSAS projects Dimensions Wizard.

Is there a way to turn of this extra cuteness?

TIA

Kar

Hi:

Request help once again on this.

TIA

Kar

BETWEEN A AND F

Hi All.
Intervals on varchar fields.
I just want to pick all customers from my DB who has Customernames starting
with A, b, c, d, e, f. Some kind of BETWEEN A AND F. (Starting letters).
Any ideas.
Even better. I have the query ... WHERE CustomerName LIKE @.CustomerName. Is
there any combinations of %, <, > or any other characters that can give me
all customers between A and F. (E.g...WHERE CustomerName LIKE 'A-F' or
something...
Thanx all
GeirLook up wildcards in the books online; you basically want a variation
of :
WHERE CustomerName Like '[a-f]%'
Stu|||Just what I was looking for. Thanx a lot!
Still learning.....:-)
-gh
"Stu" <stuart.ainsworth@.gmail.com> wrote in message
news:1126525507.534946.152760@.f14g2000cwb.googlegroups.com...
> Look up wildcards in the books online; you basically want a variation
> of :
> WHERE CustomerName Like '[a-f]%'
> Stu
>|||Been there :)
Books Online will be your best friend if you're trying to learn the
distinctive characteristics of SQL Server. Of course, if means you
have to know what you're looking for because it's an encyclopedia, not
a textbook.
Stu

Better way to check row for values?

I have a group of fields that I want to check for len() = 0. If one of
them in that group is blank len()=0 then I will set some output
parameter to yes. Then the client will report to the user that they need
to complete that section.
My solution now is to set a each field to a variable and if any one of
them are 0 (using or), set the parameter.
Is there a better way to do this?
I was thinking it would be great if there was a function that would just
return true or false if any field in that function was null/len()=0
Example
IFEmpty(Field1, Field2, etc) return True
____________________________
Steven K. Lewis
Website Nation, LLC
Website Development Services
www dot Website Nation dot comyes, use NullIf in combination with Coalesce...
NullIf(CoalA, '') returns a Null if COlA = '' (empty String), else it
returns value of ColA... So NullIf(ColA, '') will return Null if ColA IS
Null, or if ColA = '' (empty)
Then compare Coalesce of all columns together
If Coalesce(NullIf(ColA, ''), NullIf(ColB, ''), NullIf(ColC, ''),
NullIf(ColD, ''), etc, ) Is Null,
-- Then at one (or more) of them is null or empty
"Steve Lewis - Website Nation" wrote:

> I have a group of fields that I want to check for len() = 0. If one of
> them in that group is blank len()=0 then I will set some output
> parameter to yes. Then the client will report to the user that they need
> to complete that section.
> My solution now is to set a each field to a variable and if any one of
> them are 0 (using or), set the parameter.
> Is there a better way to do this?
> I was thinking it would be great if there was a function that would just
> return true or false if any field in that function was null/len()=0
> Example
> IFEmpty(Field1, Field2, etc) return True
>
> --
> ____________________________
> Steven K. Lewis
> Website Nation, LLC
> Website Development Services
> www dot Website Nation dot com
>|||Cool, thanks, I will check that out. I have used NullIf before, but not
Coalesce. That looks like it will solve my problem.
____________________________
Steven K. Lewis
Website Nation, LLC
Website Development Services
www dot Website Nation dot com
On 3/9/2005 8:49 PM, CBretana wrote:
> yes, use NullIf in combination with Coalesce...
> NullIf(CoalA, '') returns a Null if COlA = '' (empty String), else it
> returns value of ColA... So NullIf(ColA, '') will return Null if ColA IS
> Null, or if ColA = '' (empty)
> Then compare Coalesce of all columns together
> If Coalesce(NullIf(ColA, ''), NullIf(ColB, ''), NullIf(ColC, ''),
> NullIf(ColD, ''), etc, ) Is Null,
> -- Then at one (or more) of them is null or empty
> "Steve Lewis - Website Nation" wrote:
>|||I believe that your suggestion will test whether all of the fields are empty
.
Whereas the original poster was trying to test for any one of the fields
being empty.
You could try something like...
create table testtable
(
colA varchar(10),
colB varchar(10)
)
go
insert into testtable values ('', '')
insert into testtable values (null, null)
insert into testtable values ('a', '')
insert into testtable values ('a', null)
insert into testtable values (null, 'a')
insert into testtable values ('', 'a')
insert into testtable values ('a', 'a')
go
select case when substring(nullif(colA, ''), 1, 1) + substring(nullif(colb,
''), 1, 1) is null then 1 else 0 end from testtable
go
drop table testtable
"CBretana" wrote:
> yes, use NullIf in combination with Coalesce...
> NullIf(CoalA, '') returns a Null if COlA = '' (empty String), else it
> returns value of ColA... So NullIf(ColA, '') will return Null if ColA IS
> Null, or if ColA = '' (empty)
> Then compare Coalesce of all columns together
> If Coalesce(NullIf(ColA, ''), NullIf(ColB, ''), NullIf(ColC, ''),
> NullIf(ColD, ''), etc, ) Is Null,
> -- Then at one (or more) of them is null or empty
> "Steve Lewis - Website Nation" wrote:
>|||Yes, I am trying to test for any one of the fields. But, between the two
if you, I think I have an answer.
____________________________
Steven K. Lewis
Website Nation, LLC
Website Development Services
www dot Website Nation dot com
On 3/9/2005 9:55 PM, Mike Gemmell wrote:
> I believe that your suggestion will test whether all of the fields are emp
ty.
> Whereas the original poster was trying to test for any one of the fields
> being empty.
> You could try something like...
> create table testtable
> (
> colA varchar(10),
> colB varchar(10)
> )
> go
> insert into testtable values ('', '')
> insert into testtable values (null, null)
> insert into testtable values ('a', '')
> insert into testtable values ('a', null)
> insert into testtable values (null, 'a')
> insert into testtable values ('', 'a')
> insert into testtable values ('a', 'a')
> go
> select case when substring(nullif(colA, ''), 1, 1) + substring(nullif(colb
,
> ''), 1, 1) is null then 1 else 0 end from testtable
> go
> drop table testtable
> "CBretana" wrote:
>|||Mike,
Right, Coalesce returns the first non-null parameter in the parameter list,
So only if all of them are null, will the whole Coalesce expression will be
null.
Concatenating the NullIfs, as you suggest, is the right answer...
"Mike Gemmell" wrote:
> I believe that your suggestion will test whether all of the fields are emp
ty.
> Whereas the original poster was trying to test for any one of the fields
> being empty.
> You could try something like...
> create table testtable
> (
> colA varchar(10),
> colB varchar(10)
> )
> go
> insert into testtable values ('', '')
> insert into testtable values (null, null)
> insert into testtable values ('a', '')
> insert into testtable values ('a', null)
> insert into testtable values (null, 'a')
> insert into testtable values ('', 'a')
> insert into testtable values ('a', 'a')
> go
> select case when substring(nullif(colA, ''), 1, 1) + substring(nullif(colb
,
> ''), 1, 1) is null then 1 else 0 end from testtable
> go
> drop table testtable
> "CBretana" wrote:
>|||OK , I see how the concatenation will work, but doesn't
CONCAT_NULL_YIELDS_NULL have to be set ON or is that ON by default?
According to books online:
When SET CONCAT_NULL_YIELDS_NULL is ON, concatenating a null value with
a string yields a NULL result. For example, SELECT 'abc' + NULL yields
NULL. When SET CONCAT_NULL_YIELDS_NULL is OFF, concatenating a null
value with a string yields the string itself (the null value is treated
as an empty string). For example, SELECT 'abc' + NULL yields abc.
____________________________
Steven K. Lewis
Website Nation, LLC
Website Development Services
www dot Website Nation dot com
On 3/10/2005 12:03 AM, CBretana wrote:
> Mike,
> Right, Coalesce returns the first non-null parameter in the parameter lis
t,
> So only if all of them are null, will the whole Coalesce expression will
be
> null.
> Concatenating the NullIfs, as you suggest, is the right answer...
>
> "Mike Gemmell" wrote:
>|||OK , i just found that is on by default:
Option Default Setting
Set nocount OFF
Set noexec OFF
Set parseonly OFF
Set concat_null_yields_null ON
Set rowcount 0
Set ansi_defaults ON
Set arithabort ON
Set showplant_text OFF
Set statistics time OFF
Set statistics 10 OFF
Set ansi_nulls ON
Set ansi_null_dflt_on ON
Set ansi_padding ON
Set ansi_warnings ON
Set cursor_close_on_commit OFF
Set implicit_transactions OFF
Set quoted_identifier ON
____________________________
Steven K. Lewis
Website Nation, LLC
Website Development Services
www dot Website Nation dot com
On 3/10/2005 12:15 AM, Steve Lewis - Website Nation wrote:
> OK , I see how the concatenation will work, but doesn't
> CONCAT_NULL_YIELDS_NULL have to be set ON or is that ON by default?
> According to books online:
> When SET CONCAT_NULL_YIELDS_NULL is ON, concatenating a null value with
> a string yields a NULL result. For example, SELECT 'abc' + NULL yields
> NULL. When SET CONCAT_NULL_YIELDS_NULL is OFF, concatenating a null
> value with a string yields the string itself (the null value is treated
> as an empty string). For example, SELECT 'abc' + NULL yields abc.
>
> ____________________________
> Steven K. Lewis
> Website Nation, LLC
> Website Development Services
> www dot Website Nation dot com
>
> On 3/10/2005 12:03 AM, CBretana wrote:
>|||Ok , this is what I have. I will test it tomorrow:
select case when substring(nullif(colA, ''), 1, 1) +
substring(nullif(colb, ''), 1, 1) is null then set @.Incomplete = 'Yes'
else set @.Incomplete = 'No' end from tblEmployee where EmployeeID =
@.EmployeeID
____________________________
Steven K. Lewis
Website Nation, LLC
Website Development Services
www dot Website Nation dot com
On 3/10/2005 12:17 AM, Steve Lewis - Website Nation wrote:
> OK , i just found that is on by default:
> Option Default Setting
> Set nocount OFF
> Set noexec OFF
> Set parseonly OFF
> Set concat_null_yields_null ON
> Set rowcount 0
> Set ansi_defaults ON
> Set arithabort ON
> Set showplant_text OFF
> Set statistics time OFF
> Set statistics 10 OFF
> Set ansi_nulls ON
> Set ansi_null_dflt_on ON
> Set ansi_padding ON
> Set ansi_warnings ON
> Set cursor_close_on_commit OFF
> Set implicit_transactions OFF
> Set quoted_identifier ON
> ____________________________
> Steven K. Lewis
> Website Nation, LLC
> Website Development Services
> www dot Website Nation dot com
>
> On 3/10/2005 12:15 AM, Steve Lewis - Website Nation wrote:
>|||Why don't you use CHECK constraints to validate the data on entry?
CREATE TABLE foo (... col VARCHAR(10) NOT NULL CHECK (col > '')...)
David Portas
SQL Server MVP
--

Best way to capture/export about 45 fields - 3000 records

I am new to SQL server 2005 and trying to help a charity org move selected kids data out of a POS SQL POS database.

There are maybe 65 tables (only 5 - 7 tables will apply that will contain the 45 fields that I need). About 3000 records need to filtered to create a new database.

Ideally, with the small amout of data I actually need, we thought it would be best to just pump t into Excel so that I can pass the data to the charity where they can scrub the data themselves. We would then pump it into an online app that likes Excel imports.

After restoring the data sucessfully from a .bak file I am now failing miserably at converting a few tables with data into Excel even though it appeared to be straightforward. HELP please?

Can some experienced folks maybe help me out with a good plan?

Quote:

Originally Posted by computerchick

I am new to SQL server 2005 and trying to help a charity org move selected kids data out of a POS SQL POS database.

There are maybe 65 tables (only 5 - 7 tables will apply that will contain the 45 fields that I need). About 3000 records need to filtered to create a new database.

Ideally, with the small amout of data I actually need, we thought it would be best to just pump t into Excel so that I can pass the data to the charity where they can scrub the data themselves. We would then pump it into an online app that likes Excel imports.

After restoring the data sucessfully from a .bak file I am now failing miserably at converting a few tables with data into Excel even though it appeared to be straightforward. HELP please?

Can some experienced folks maybe help me out with a good plan?

There are a number of ways.

One is (if you need it only once) to query the tables in Query Analyzer (to a grid), then copy/paste into Excel. (You will lose the header row, though.)

Another one is to use an ODBC connection to the SQL database from Excel and retrieve the data from an "External data source".

The third one is that you use MS Access, pull the data into it (either from the source, or through ODBC), then export the tables into Excel.

If you need it regularly I suggest the second one; we have been using it widely.|||You can try the IMPORT/EXPORT Wizard which is quite easy to use.

Aash.|||Thank you for mucho for responding - it is much appreciated. What happened was that some of the tables were in am "incompatible format". I did end up getting it ported to Excel 2007 which in turn worked like a charm porting it into all other data formats as well. Yea! A process that works! :)

Quote:

Originally Posted by azimmer

There are a number of ways.

One is (if you need it only once) to query the tables in Query Analyzer (to a grid), then copy/paste into Excel. (You will lose the header row, though.)

Another one is to use an ODBC connection to the SQL database from Excel and retrieve the data from an "External data source".

The third one is that you use MS Access, pull the data into it (either from the source, or through ODBC), then export the tables into Excel.

If you need it regularly I suggest the second one; we have been using it widely.

|||Thank you too - that is what I was using but it would not work on the specific formatting applied to a few of my tables (the first time I had a corrupt bak file - woe is me! I did get it resolved after a bit of head banging!

Quote:

Originally Posted by patelaashish

You can try the IMPORT/EXPORT Wizard which is quite easy to use.

Aash.

Best way for Stored Procedure to update fields conditionally?

I want to write a stored procedure that updates a record, only updating
the fields where the value passed is not null.
So ideally in the UPDATE statement I want something like
CREATE PROCEDURE myproc
@.param1 nvarchar(20),
@.param2 nvarchar(20)
UPDATE myrec
SET
IF @.param1 IS NOT null
param1=@.param1,
IF @.param2 IS NOT null
param1=@.param2
etc.
but I'm guessing I can't do that.
Obviously I don't want to have to read the record first to compare the
current values with the ones I'm passing in.
What's the best (shortest, most efficient) way to do this?
Thanks,
ChrisNHi,
what about set Col1 = COALESCE(@.Param1, Col1)
HTH, Jens K. Suessmeyer.
--
http://www.sqlserver2005.de
--|||First off I would ask why you care? If you are going to update the row the
extra overhead to update columns that don't need to is extremely small and
trivial compared to the other operations that must occur for an update.
Second how can you check for NULL? Are you saying none of your columns
allow NULL's? If so how would you pass in a NULL?
--
Andrew J. Kelly SQL MVP
"ChrisN" <yeltsin27@.yahoo.co.uk> wrote in message
news:1159084549.818998.105970@.e3g2000cwe.googlegroups.com...
>I want to write a stored procedure that updates a record, only updating
> the fields where the value passed is not null.
> So ideally in the UPDATE statement I want something like
> CREATE PROCEDURE myproc
> @.param1 nvarchar(20),
> @.param2 nvarchar(20)
> UPDATE myrec
> SET
> IF @.param1 IS NOT null
> param1=@.param1,
> IF @.param2 IS NOT null
> param1=@.param2
> etc.
> but I'm guessing I can't do that.
> Obviously I don't want to have to read the record first to compare the
> current values with the ones I'm passing in.
> What's the best (shortest, most efficient) way to do this?
> Thanks,
> ChrisN
>|||If you want to update based on the values passed in, you can do something
such as this
IF @.param1 IS NOT NULL
BEGIN
UPDATE tabel
SET column1 = @.param1
WHERE ...
END
IF @.param2 IS NOT NULL
BEGIN
UPDATE table
SET column2 = @.param2
WHERE ..
END
Or you could split it up into multiple stored proceduces.
Why you would want to do this, I am not sure...
Keep in mind that when using IF conditions in a stored procedure, SQL is
less likely to re-use execution plans, lessening the benefit of using a
stored procedure.
"ChrisN" <yeltsin27@.yahoo.co.uk> wrote in message
news:1159084549.818998.105970@.e3g2000cwe.googlegroups.com...
>I want to write a stored procedure that updates a record, only updating
> the fields where the value passed is not null.
> So ideally in the UPDATE statement I want something like
> CREATE PROCEDURE myproc
> @.param1 nvarchar(20),
> @.param2 nvarchar(20)
> UPDATE myrec
> SET
> IF @.param1 IS NOT null
> param1=@.param1,
> IF @.param2 IS NOT null
> param1=@.param2
> etc.
> but I'm guessing I can't do that.
> Obviously I don't want to have to read the record first to compare the
> current values with the ones I'm passing in.
> What's the best (shortest, most efficient) way to do this?
> Thanks,
> ChrisN
>

Best way for Stored Procedure to update fields conditionally?

I want to write a stored procedure that updates a record, only updating
the fields where the value passed is not null.
So ideally in the UPDATE statement I want something like
CREATE PROCEDURE myproc
@.param1 nvarchar(20),
@.param2 nvarchar(20)
UPDATE myrec
SET
IF @.param1 IS NOT null
param1=@.param1,
IF @.param2 IS NOT null
param1=@.param2
etc.
but I'm guessing I can't do that.
Obviously I don't want to have to read the record first to compare the
current values with the ones I'm passing in.
What's the best (shortest, most efficient) way to do this?
Thanks,
ChrisNHi,
what about set Col1 = COALESCE(@.Param1, Col1)
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
--|||First off I would ask why you care? If you are going to update the row the
extra overhead to update columns that don't need to is extremely small and
trivial compared to the other operations that must occur for an update.
Second how can you check for NULL? Are you saying none of your columns
allow NULL's? If so how would you pass in a NULL?
Andrew J. Kelly SQL MVP
"ChrisN" <yeltsin27@.yahoo.co.uk> wrote in message
news:1159084549.818998.105970@.e3g2000cwe.googlegroups.com...
>I want to write a stored procedure that updates a record, only updating
> the fields where the value passed is not null.
> So ideally in the UPDATE statement I want something like
> CREATE PROCEDURE myproc
> @.param1 nvarchar(20),
> @.param2 nvarchar(20)
> UPDATE myrec
> SET
> IF @.param1 IS NOT null
> param1=@.param1,
> IF @.param2 IS NOT null
> param1=@.param2
> etc.
> but I'm guessing I can't do that.
> Obviously I don't want to have to read the record first to compare the
> current values with the ones I'm passing in.
> What's the best (shortest, most efficient) way to do this?
> Thanks,
> ChrisN
>|||If you want to update based on the values passed in, you can do something
such as this
IF @.param1 IS NOT NULL
BEGIN
UPDATE tabel
SET column1 = @.param1
WHERE ...
END
IF @.param2 IS NOT NULL
BEGIN
UPDATE table
SET column2 = @.param2
WHERE ..
END
Or you could split it up into multiple stored proceduces.
Why you would want to do this, I am not sure...
Keep in mind that when using IF conditions in a stored procedure, SQL is
less likely to re-use execution plans, lessening the benefit of using a
stored procedure.
"ChrisN" <yeltsin27@.yahoo.co.uk> wrote in message
news:1159084549.818998.105970@.e3g2000cwe.googlegroups.com...
>I want to write a stored procedure that updates a record, only updating
> the fields where the value passed is not null.
> So ideally in the UPDATE statement I want something like
> CREATE PROCEDURE myproc
> @.param1 nvarchar(20),
> @.param2 nvarchar(20)
> UPDATE myrec
> SET
> IF @.param1 IS NOT null
> param1=@.param1,
> IF @.param2 IS NOT null
> param1=@.param2
> etc.
> but I'm guessing I can't do that.
> Obviously I don't want to have to read the record first to compare the
> current values with the ones I'm passing in.
> What's the best (shortest, most efficient) way to do this?
> Thanks,
> ChrisN
>

Best way for Stored Procedure to update fields conditionally?

I want to write a stored procedure that updates a record, only updating
the fields where the value passed is not null.
So ideally in the UPDATE statement I want something like
CREATE PROCEDURE myproc
@.param1 nvarchar(20),
@.param2 nvarchar(20)
UPDATE myrec
SET
IF @.param1 IS NOT null
param1=@.param1,
IF @.param2 IS NOT null
param1=@.param2
etc.
but I'm guessing I can't do that.
Obviously I don't want to have to read the record first to compare the
current values with the ones I'm passing in.
What's the best (shortest, most efficient) way to do this?
Thanks,
ChrisN
Hi,
what about set Col1 = COALESCE(@.Param1, Col1)
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||First off I would ask why you care? If you are going to update the row the
extra overhead to update columns that don't need to is extremely small and
trivial compared to the other operations that must occur for an update.
Second how can you check for NULL? Are you saying none of your columns
allow NULL's? If so how would you pass in a NULL?
Andrew J. Kelly SQL MVP
"ChrisN" <yeltsin27@.yahoo.co.uk> wrote in message
news:1159084549.818998.105970@.e3g2000cwe.googlegro ups.com...
>I want to write a stored procedure that updates a record, only updating
> the fields where the value passed is not null.
> So ideally in the UPDATE statement I want something like
> CREATE PROCEDURE myproc
> @.param1 nvarchar(20),
> @.param2 nvarchar(20)
> UPDATE myrec
> SET
> IF @.param1 IS NOT null
> param1=@.param1,
> IF @.param2 IS NOT null
> param1=@.param2
> etc.
> but I'm guessing I can't do that.
> Obviously I don't want to have to read the record first to compare the
> current values with the ones I'm passing in.
> What's the best (shortest, most efficient) way to do this?
> Thanks,
> ChrisN
>
|||If you want to update based on the values passed in, you can do something
such as this
IF @.param1 IS NOT NULL
BEGIN
UPDATE tabel
SET column1 = @.param1
WHERE ...
END
IF @.param2 IS NOT NULL
BEGIN
UPDATE table
SET column2 = @.param2
WHERE ..
END
Or you could split it up into multiple stored proceduces.
Why you would want to do this, I am not sure...
Keep in mind that when using IF conditions in a stored procedure, SQL is
less likely to re-use execution plans, lessening the benefit of using a
stored procedure.
"ChrisN" <yeltsin27@.yahoo.co.uk> wrote in message
news:1159084549.818998.105970@.e3g2000cwe.googlegro ups.com...
>I want to write a stored procedure that updates a record, only updating
> the fields where the value passed is not null.
> So ideally in the UPDATE statement I want something like
> CREATE PROCEDURE myproc
> @.param1 nvarchar(20),
> @.param2 nvarchar(20)
> UPDATE myrec
> SET
> IF @.param1 IS NOT null
> param1=@.param1,
> IF @.param2 IS NOT null
> param1=@.param2
> etc.
> but I'm guessing I can't do that.
> Obviously I don't want to have to read the record first to compare the
> current values with the ones I'm passing in.
> What's the best (shortest, most efficient) way to do this?
> Thanks,
> ChrisN
>