Binary Column always returns 0 for ISNULL

Why don't i ever get return value of 1 when the following binary column (profSignature) is null?

RETURN SELECT ISNULL

(profSignature, profSignature)FROMmpProfilesWHEREApplicantID =CAST(@.CIDAS INT)ANDProfileID =CAST(@.PIDAS INT)

There is no conversion between GUID and INT that is what I think you are trying to do, try the link below for SQL Server data type conversion chart. Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms187928.aspx

|||I forgot to add that the column type is "Image" in SQL 2005|||

Dup post

|||How would I get it so a <NULL> column value makes the stored procedure return 1 or 0?|||

I have checked the conversion chart again that is not valid either so check the create table statement below from Microsoft AdventureWorks it pick the columns relevant to your situation and you can get the information you are looking for. Sorry about the duplicate post. Hope this helps.

CREATE TABLE [EmployeePhoto] (
[EmployeePhotoID] [int] IDENTITY (1, 1) NOT NULL ,
[ThumbNailPhoto] [image] NULL ,
[ThumbnailPhotoFileName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LargePhoto] [image] NULL ,
[LargePhotoFileName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_EmployeePhoto_ModifiedDate] DEFAULT (getdate()),
CONSTRAINT [PK_EmployeePhoto_EmployeePhotoID] PRIMARY KEY CLUSTERED
(
[EmployeePhotoID]
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

|||

If you need 1 on null try this:

RETURN SELECT ISNULL(profSignature,1)
FROMmpProfiles
WHEREApplicantID =CAST(@.CIDAS INT)
ANDProfileID =CAST(@.PIDAS INT)

or

RETURN (case
when exist(Select * FROMmpProfiles
WHEREApplicantID =CAST(@.CIDAS INT)
ANDProfileID =CAST(@.PIDAS INT)
and profSignature is null) then 1 -- for null

when exist(Select * FROMmpProfiles
WHEREApplicantID =CAST(@.CIDAS INT)
ANDProfileID =CAST(@.PIDAS INT)
and not profSignature is null) then 2 -- for not nulls

else

0 -- does not exists in database

end)

|||Thanks ... the case statement did the trick.sql

Best way to get the next auto number

Hi all, let say I have a table:
CREATE TABLE test (
[MyID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (50) NOT NULL
)
How do I know the next value that MyID gonna have before inserting a
new record?
What is the best way to do that?
Get the max number plus one? Wouldn't it be expensive to do it this
way?
Thanks
ky
Hi
CREATE TABLE test (
[MyID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (50) NOT NULL
)
DECLARE @.nextid INT
INSERT INTO test([Name]) VALUES ('John')
SELECT @.nextid=SCOPE_IDENTITY()
Print 'Next id is '+CAST(@.nextid+1 AS VARCHAR(10))
DROP TABLE Test
--Or not using an identity property
CREATE PROC Get_nextID
@.ID char(20),
@.nextID numeric(18) output
as
update NextIDs
set @.nextID = NextID = NextID + 1
where ID = @.ID
<nonamehkg@.hotmail.com> wrote in message
news:1141275178.710563.65560@.i40g2000cwc.googlegro ups.com...
> Hi all, let say I have a table:
> CREATE TABLE test (
> [MyID] [int] IDENTITY (1, 1) NOT NULL ,
> [Name] [varchar] (50) NOT NULL
> )
> How do I know the next value that MyID gonna have before inserting a
> new record?
> What is the best way to do that?
> Get the max number plus one? Wouldn't it be expensive to do it this
> way?
>
> Thanks
> --
> ky
>
|||nonamehkg@.hotmail.com wrote:
> Hi all, let say I have a table:
> CREATE TABLE test (
> [MyID] [int] IDENTITY (1, 1) NOT NULL ,
> [Name] [varchar] (50) NOT NULL
> )
> How do I know the next value that MyID gonna have before inserting a
> new record?
> What is the best way to do that?
>
You don't. You retrieve the IDENTITY value afterwards. Take a look at
the SCOPE_IDENTITY function in Books Online.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||To add the other responses, if you need to assign a known surrogate key
value before insertion, you need to use something other than identity. One
method is to use uniqueidentifier so that you can assign the value with
NEWID() or via application code. Another method is to create a table that
contains a row with the last (or next) assigned key value and use that table
to update and retrieve the key value prior to insertion.
Hope this helps.
Dan Guzman
SQL Server MVP
<nonamehkg@.hotmail.com> wrote in message
news:1141275178.710563.65560@.i40g2000cwc.googlegro ups.com...
> Hi all, let say I have a table:
> CREATE TABLE test (
> [MyID] [int] IDENTITY (1, 1) NOT NULL ,
> [Name] [varchar] (50) NOT NULL
> )
> How do I know the next value that MyID gonna have before inserting a
> new record?
> What is the best way to do that?
> Get the max number plus one? Wouldn't it be expensive to do it this
> way?
>
> Thanks
> --
> ky
>
|||Thanks eveyone, I think SCOPE_IDENTITY is what I should use.
I actually need to know the id of a newly inserted record, and then
create a folder using that id as name.
It helps a lot thanks again.

Best way to get the next auto number

Hi all, let say I have a table:
CREATE TABLE test (
[MyID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (50) NOT NULL
)
How do I know the next value that MyID gonna have before inserting a
new record?
What is the best way to do that?
Get the max number plus one? Wouldn't it be expensive to do it this
way?
Thanks
kyHi
CREATE TABLE test (
[MyID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (50) NOT NULL
)
DECLARE @.nextid INT
INSERT INTO test([Name]) VALUES ('John')
SELECT @.nextid=SCOPE_IDENTITY()
Print 'Next id is '+CAST(@.nextid+1 AS VARCHAR(10))
DROP TABLE Test
--Or not using an identity property
CREATE PROC Get_nextID
@.ID char(20),
@.nextID numeric(18) output
as
update NextIDs
set @.nextID = NextID = NextID + 1
where ID = @.ID
<nonamehkg@.hotmail.com> wrote in message
news:1141275178.710563.65560@.i40g2000cwc.googlegroups.com...
> Hi all, let say I have a table:
> CREATE TABLE test (
> [MyID] [int] IDENTITY (1, 1) NOT NULL ,
> [Name] [varchar] (50) NOT NULL
> )
> How do I know the next value that MyID gonna have before inserting a
> new record?
> What is the best way to do that?
> Get the max number plus one? Wouldn't it be expensive to do it this
> way?
>
> Thanks
> --
> ky
>|||nonamehkg@.hotmail.com wrote:
> Hi all, let say I have a table:
> CREATE TABLE test (
> [MyID] [int] IDENTITY (1, 1) NOT NULL ,
> [Name] [varchar] (50) NOT NULL
> )
> How do I know the next value that MyID gonna have before inserting a
> new record?
> What is the best way to do that?
>
You don't. You retrieve the IDENTITY value afterwards. Take a look at
the SCOPE_IDENTITY function in Books Online.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||To add the other responses, if you need to assign a known surrogate key
value before insertion, you need to use something other than identity. One
method is to use uniqueidentifier so that you can assign the value with
NEWID() or via application code. Another method is to create a table that
contains a row with the last (or next) assigned key value and use that table
to update and retrieve the key value prior to insertion.
Hope this helps.
Dan Guzman
SQL Server MVP
<nonamehkg@.hotmail.com> wrote in message
news:1141275178.710563.65560@.i40g2000cwc.googlegroups.com...
> Hi all, let say I have a table:
> CREATE TABLE test (
> [MyID] [int] IDENTITY (1, 1) NOT NULL ,
> [Name] [varchar] (50) NOT NULL
> )
> How do I know the next value that MyID gonna have before inserting a
> new record?
> What is the best way to do that?
> Get the max number plus one? Wouldn't it be expensive to do it this
> way?
>
> Thanks
> --
> ky
>|||Thanks eveyone, I think SCOPE_IDENTITY is what I should use.
I actually need to know the id of a newly inserted record, and then
create a folder using that id as name.
It helps a lot thanks again.

Best way to get the next auto number

Hi all, let say I have a table:
CREATE TABLE test (
[MyID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (50) NOT NULL
)
How do I know the next value that MyID gonna have before inserting a
new record?
What is the best way to do that?
Get the max number plus one? Wouldn't it be expensive to do it this
way?
Thanks
--
kyHi
CREATE TABLE test (
[MyID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (50) NOT NULL
)
DECLARE @.nextid INT
INSERT INTO test([Name]) VALUES ('John')
SELECT @.nextid=SCOPE_IDENTITY()
Print 'Next id is '+CAST(@.nextid+1 AS VARCHAR(10))
DROP TABLE Test
--Or not using an identity property
CREATE PROC Get_nextID
@.ID char(20),
@.nextID numeric(18) output
as
update NextIDs
set @.nextID = NextID = NextID + 1
where ID = @.ID
<nonamehkg@.hotmail.com> wrote in message
news:1141275178.710563.65560@.i40g2000cwc.googlegroups.com...
> Hi all, let say I have a table:
> CREATE TABLE test (
> [MyID] [int] IDENTITY (1, 1) NOT NULL ,
> [Name] [varchar] (50) NOT NULL
> )
> How do I know the next value that MyID gonna have before inserting a
> new record?
> What is the best way to do that?
> Get the max number plus one? Wouldn't it be expensive to do it this
> way?
>
> Thanks
> --
> ky
>|||nonamehkg@.hotmail.com wrote:
> Hi all, let say I have a table:
> CREATE TABLE test (
> [MyID] [int] IDENTITY (1, 1) NOT NULL ,
> [Name] [varchar] (50) NOT NULL
> )
> How do I know the next value that MyID gonna have before inserting a
> new record?
> What is the best way to do that?
>
You don't. You retrieve the IDENTITY value afterwards. Take a look at
the SCOPE_IDENTITY function in Books Online.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||To add the other responses, if you need to assign a known surrogate key
value before insertion, you need to use something other than identity. One
method is to use uniqueidentifier so that you can assign the value with
NEWID() or via application code. Another method is to create a table that
contains a row with the last (or next) assigned key value and use that table
to update and retrieve the key value prior to insertion.
--
Hope this helps.
Dan Guzman
SQL Server MVP
<nonamehkg@.hotmail.com> wrote in message
news:1141275178.710563.65560@.i40g2000cwc.googlegroups.com...
> Hi all, let say I have a table:
> CREATE TABLE test (
> [MyID] [int] IDENTITY (1, 1) NOT NULL ,
> [Name] [varchar] (50) NOT NULL
> )
> How do I know the next value that MyID gonna have before inserting a
> new record?
> What is the best way to do that?
> Get the max number plus one? Wouldn't it be expensive to do it this
> way?
>
> Thanks
> --
> ky
>|||Thanks eveyone, I think SCOPE_IDENTITY is what I should use.
I actually need to know the id of a newly inserted record, and then
create a folder using that id as name.
It helps a lot thanks again.

Best way to check if a field Is NULL

I usually check is a field is Null by doing something like this:

WHERE IsNull(myField,'') = ''

However, I just noticed that this is also return records where myField = 0

Does anyone know why?

What is the best and most common way to check if a field is null?

The best way to check if a field is null is

WHERE myField IS NULL

Are you by chance checking for a "blank" field instead of a null field?

|||

Zero is not null.

Try this:

Code Snippet

select *

from MyTable

where MyField IS NULL

|||Arnie,

The fact that rows with MyField = 0 are returned is not a problem with the query. If the type of MyField is integer and its value is zero, the query will test 0 = '', which is true. This is because '' is implicitly converted to integer in the comparison, and in SQL Server, cast('' as integer) has the value zero.

Steve Kass
Drew University
http://www.stevekass.com

|||

Steve,

...such rows would indicate that there is a problem in the query.

Thanks for the clarification. I was referring to the nature of the query -not the evaluation of implicit zero. The OP wanted NULL rows, so the way the query was written (invoking implicit conversions) would not satisfy his/her needs.

I could have more precise, but I dropped it. I'm glad you jumped in to dispel any misconceptions I may have created.

best way to add column not null

Hi.

I've read up on this, and have something that works, but I was wondering if
there is anything I'm overlooking with this.

Situation is:

I have a bunch of tables.. I need to modify table2 as part of an upgrade of a
database schema.

I am using T-SQL scripts to do the trick which I'm writing myself.

I need to add a new varchar(8) column that is not null to the primary key.
I have a default I would like to use for the initial ddl modification.
I want to get rid of the default after the modification is complete, but leave
the column not null for future operations.
..

(Some if the code I'm using I took from one of Erlands posts.. hope I'm not
abusing it).
Here is the code I'm using now.. it basically adds the column 'institution_id'
as not null along with a default.
Then I jump through a couple of hoops trying to get rid of the default.
Finally I setup the primary key again.

I can only feel I'm supposed to be maybe using a constraint column with a name
to do this easier/more properly.

set @.dynamicsql = ' alter table institution_xref add institution_id
varchar(60) not null default ''' + @.default_institution_id + ''' '
EXEC (@.dynamicsql)
set @.dynamicsql = ' alter table institution_xref alter column
institution_id varchar(60) not null '
EXEC (@.dynamicsql)
select @.institution_iddefault = object_name(cdefault) from syscolumns
where id = object_id('institution_xref') and name = 'institution_id'
exec(' alter table institution_xref drop constraint ' +
@.institution_iddefault)
set @.dynamicsql = ' alter table institution_xref drop constraint
institution_xref_pk '
EXEC (@.dynamicsql)
set @.dynamicsql = ' alter table institution_xref with nocheck add
constraint institution_xref_pk primary key clustered (originalcode,
institution_id) '
EXEC (@.dynamicsql)

thanks
Jeff
Jeff KishJeff Kish (jeff.kish@.mro.com) writes:

Quote:

Originally Posted by

I need to add a new varchar(8) column that is not null to the primary key.

Ouch! That can be paintful, at least if there are foreign keys
referencing the table.

Quote:

Originally Posted by

abusing it).
Here is the code I'm using now.. it basically adds the column
'institution_id'
as not null along with a default.
Then I jump through a couple of hoops trying to get rid of the default.
Finally I setup the primary key again.
>
I can only feel I'm supposed to be maybe using a constraint column with
a name to do this easier/more properly.

Yes, if you name the constraint it's a little easier:

Quote:

Originally Posted by

set @.dynamicsql = ' alter table institution_xref add institution_id
varchar(60) not null default ''' + @.default_institution_id + ''' '

...not null CONSTRAINT my_temp_default DEFAULT ''' ...

Quote:

Originally Posted by

EXEC (@.dynamicsql)

ALTER TABLE ... DROP CONSTRAINT my_temp_default

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Tue, 5 Jun 2007 22:28:21 +0000 (UTC), Erland Sommarskog
<esquel@.sommarskog.sewrote:

Quote:

Originally Posted by

>Jeff Kish (jeff.kish@.mro.com) writes:

Quote:

Originally Posted by

>I need to add a new varchar(8) column that is not null to the primary key.

>
>Ouch! That can be paintful, at least if there are foreign keys
>referencing the table.
>

Quote:

Originally Posted by

>abusing it).
>Here is the code I'm using now.. it basically adds the column
>'institution_id'
>as not null along with a default.
>Then I jump through a couple of hoops trying to get rid of the default.
>Finally I setup the primary key again.
>>
>I can only feel I'm supposed to be maybe using a constraint column with
>a name to do this easier/more properly.

>
>Yes, if you name the constraint it's a little easier:
>

Quote:

Originally Posted by

> set @.dynamicsql = ' alter table institution_xref add institution_id
>varchar(60) not null default ''' + @.default_institution_id + ''' '

>
>...not null CONSTRAINT my_temp_default DEFAULT ''' ...
>

Quote:

Originally Posted by

> EXEC (@.dynamicsql)

>
ALTER TABLE ... DROP CONSTRAINT my_temp_default

thanks. much!
Jeff Kish

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
>