If Nothing Selected Into Variable What Is The Value Of Variable?

DECLARE @.PayTypeValue numeric (18, 5)
SET @.PayTypeValue = (SELECT [LoadPayValue] FROM [CSITSS].[dbo].[LoadPayType] WHERE [CompanyDiv] = @.CompDiv AND [Deleted] = 0 AND [LoadPayType] = @.LoadPay)
IF THIS QUERY RETURNS NOTHING WHAT DOES IT SET @.PayTypeValue to?
NULL OR 0DECLARE @.PayTypeValue numeric (18, 5)

SET @.PayTypeValue = (SELECT [LoadPayValue] FROM [CSITSS].[dbo].[LoadPayType] WHERE [CompanyDiv] = @.CompDiv AND [Deleted] = 0 AND [LoadPayType] = @.LoadPay)

IF THIS QUERY RETURNS NOTHING WHAT DOES IT SET @.PayTypeValue to?

NULL OR 0

why don't you try it and see for yourself:

SELECT @.PayTypeValue= [LoadPayValue] FROM [CSITSS].[dbo].[LoadPayType] WHERE 1=0
select @.PayTypeValue|||Also, try
SET @.PayTypeValue = 42
SELECT @.PayTypeValue= [LoadPayValue] FROM [CSITSS].[dbo].[LoadPayType] WHERE 1=0
select @.PayTypeValue

and see what happens.|||i thought it would return NULL.....|||But it returns...

The answer would be useful for people with similar questions ;)|||The answer is 42.
If the query returns no rows, the variable keeps its old value.|||Ahh that's clever - kinda like having an "else" value.
Cheers ivon|||hmmm...

That could be dangerous if you were using that variable in a loop.
I would wrap the SQL in ISNULL()

set @.somevariable=ISNULL((select somevalue from sometable),-9999999)
If @.somevariable=-9999999
'no rows were returned
Else|||That could be dangerous if you were using that variable in a loop.

How so? It retained it's initially declared value...
If you looped round, @.PayTypeValue would equal 42 each time..?|||Yes, but if you were expecting the value to change with each iteration...
What is returned from the select statement is also dependant on
@.compdiv and @.loadpay, so if those values change, it's quite possilbe that @.paytypevalue would change, too.|||Yes, but if you were expecting the value to change with each iteration...
That's how I found out; I got some very peculiar results.

BTW using IsNull() won't help: the query returns no rows, so there is no null value to replace with something else.

Solutions I'm using are
- Setting the variable to a default value before filling it with the query,
- Checking @.@.ROWCOUNT to see if any rows were returned from the query.|||I think it all depends on how you put it together.

declare @.tmp integer
set @.tmp=42
set @.tmp=isnull((select 18 where 1=0),0)
select @.tmp

Returns 0, which is what I'd expect, since
SELECT 18 WHERE 1=0
returns no rows|||How do I use @.@.ROWCOUNT to do this?

I haven't set this variable to anything before this and at least this instance doesn't loop. This SP is called once per order and checks 14 different things. Then feeds the results into a verification table. Those results and then selected by Crystal Reports and displayed to the user for an entire batch of orders one at a time by order number.|||RedNeckGeek: I now see that you put the isnull around the entire query.

tdecker81:
Something like
SELECT @.l_var = value
FROM table
WHERE <conditions>

SELECT @.l_records = @.@.ROWCOUNT

IF @.l_records > 0
BEGIN
' Do stuff with @.l_var
END|||Interesting. Since I rarely use the first syntax, I was unaware of this behavior:
set nocount on

declare @.MyValue int
set @.MyValue = 42

--This method retains @.MyValue
select @.MyValue = id from sysobjects where 1 = 0
select @.MyValue as 'Unchanged!'

--This method sets @.MyValue to NULL
set @.MyValue = (select id from sysobjects where 1 = 0)
select @.MyValue as 'Set to NULL!'

Learn something new every day...|||Learn something new every day...

my problem is I forget 2 somethings every day, so I think I'm losing the battle...

(most of it is stuff my wife tells me so I guess it's ok)

:)|||I'm sure you're wife will be there to constantly remind you too.sql

if exists help

Hi There,
What is the problem here, I can not find out, it always execute sql instead
of printing 'no'
if exists (select * from dbo.sysobjects where id = object_id('dividend') )
if exists (select syscolumns.name
from sysobjects inner join
syscolumns on
sysobjects.id = syscolumns.id
where sysobjects.name = 'member' and syscolumns.name = 'adgdr1' )
begin
SELECT 'ADMINSYS_DN_AVC' as [SchemeName], MEMBER.[PLAN],
DIVIDEND.DIVIDREF, DIVIDEND.AMOUNT, DIVIDEND.TAX_RELIEF, DIVIDEND.SECURITYRF
,
DIVIDEND.TAX_REL_PD,
DIVIDEND.TAXTRAN
FROM MEMBER INNER JOIN
DIVIDEND ON MEMBER.[PLAN] = DIVIDEND.[PLAN]
WHERE (DIVIDEND.TAX_RELIEF <> 0) AND (DIVIDEND.TAXTRAN = 0) AND
(MEMBER.MigrationFlag = 'C') OR
(DIVIDEND.TAX_RELIEF <> 0) AND (DIVIDEND.TAXTRAN IS
NULL) AND (MEMBER.MigrationFlag = 'C')
end
else
print 'no'
if write something like this it works fine
if exists (select * from dbo.sysobjects where id = object_id('dividend') )
if exists (select syscolumns.name
from sysobjects inner join
syscolumns on
sysobjects.id = syscolumns.id
where sysobjects.name = 'member' and syscolumns.name = 'addkr1' )
print 'yes'
else
print 'no'
Thanks
GaneshGanesh wrote:
> Hi There,
> What is the problem here, I can not find out, it always execute sql
> instead of printing 'no'
>
> if exists (select * from dbo.sysobjects where id =
> object_id('dividend') )
> if exists (select syscolumns.name
> from sysobjects inner join
> syscolumns on
> sysobjects.id = syscolumns.id
> where sysobjects.name = 'member' and syscolumns.name = 'adgdr1' )
> begin
> SELECT 'ADMINSYS_DN_AVC' as [SchemeName], MEMBER.[PLAN],
> DIVIDEND.DIVIDREF, DIVIDEND.AMOUNT, DIVIDEND.TAX_RELIEF,
> DIVIDEND.SECURITYRF, DIVIDEND.TAX_REL_PD,
> DIVIDEND.TAXTRAN
> FROM MEMBER INNER JOIN
> DIVIDEND ON MEMBER.[PLAN] = DIVIDEND.[PLAN]
> WHERE (DIVIDEND.TAX_RELIEF <> 0) AND (DIVIDEND.TAXTRAN = 0) AND
> (MEMBER.MigrationFlag = 'C') OR
> (DIVIDEND.TAX_RELIEF <> 0) AND (DIVIDEND.TAXTRAN
> IS NULL) AND (MEMBER.MigrationFlag = 'C')
> end
> else
> print 'no'
>
> if write something like this it works fine
>
> if exists (select * from dbo.sysobjects where id =
> object_id('dividend') )
> if exists (select syscolumns.name
> from sysobjects inner join
> syscolumns on
> sysobjects.id = syscolumns.id
> where sysobjects.name = 'member' and syscolumns.name = 'addkr1' )
> print 'yes'
> else
> print 'no'
Can you explain what you want the script to do so we can validate the
code you posted?
David Gugick - SQL Server MVP
Quest Software|||I have to guess a bit here.
Change the beginning of your procedure to this:

> if exists (select * from dbo.sysobjects where id = object_id('dividend') )
AND exists (select syscolumns.name ...
"Ganesh" <gsganesh@.yahoo.com> wrote in message
news:99A82BDA-F5B5-40DC-8C18-434D6B9C2665@.microsoft.com...
> Hi There,
> What is the problem here, I can not find out, it always execute sql
> instead
> of printing 'no'
>
> if exists (select * from dbo.sysobjects where id = object_id('dividend') )
> if exists (select syscolumns.name
> from sysobjects inner join
> syscolumns on
> sysobjects.id = syscolumns.id
> where sysobjects.name = 'member' and syscolumns.name = 'adgdr1' )
> begin
> SELECT 'ADMINSYS_DN_AVC' as [SchemeName], MEMBER.[PLAN],
> DIVIDEND.DIVIDREF, DIVIDEND.AMOUNT, DIVIDEND.TAX_RELIEF,
> DIVIDEND.SECURITYRF,
> DIVIDEND.TAX_REL_PD,
> DIVIDEND.TAXTRAN
> FROM MEMBER INNER JOIN
> DIVIDEND ON MEMBER.[PLAN] = DIVIDEND.[PLAN]
> WHERE (DIVIDEND.TAX_RELIEF <> 0) AND (DIVIDEND.TAXTRAN = 0) AND
> (MEMBER.MigrationFlag = 'C') OR
> (DIVIDEND.TAX_RELIEF <> 0) AND (DIVIDEND.TAXTRAN IS
> NULL) AND (MEMBER.MigrationFlag = 'C')
> end
> else
> print 'no'
>
> if write something like this it works fine
>
> if exists (select * from dbo.sysobjects where id = object_id('dividend') )
> if exists (select syscolumns.name
> from sysobjects inner join
> syscolumns on
> sysobjects.id = syscolumns.id
> where sysobjects.name = 'member' and syscolumns.name = 'addkr1' )
> print 'yes'
> else
> print 'no'
>
> --
> Thanks
> Ganesh|||Basically,
I want to execute a select statement based on the table and field existence
for each database
/* Powered by General SQL Parser at www.sqlparser.com */
IF EXISTS (SELECT *
FROM DBO.SYSOBJECTS
WHERE ID = OBJECT_ID('dividend'))
AND EXISTS (SELECT SYSCOLUMNS.NAME
FROM SYSOBJECTS
INNER JOIN SYSCOLUMNS
ON SYSOBJECTS.ID = SYSCOLUMNS.ID
WHERE SYSOBJECTS.NAME = 'member'
AND SYSCOLUMNS.NAME = 'adgdr1')
BEGIN
SELECT 'ADMINSYS_DN_AVC' AS [SchemeName],
MEMBER.[PLAN],
DIVIDEND.DIVIDREF,
DIVIDEND.AMOUNT,
DIVIDEND.TAX_RELIEF,
DIVIDEND.SECURITYRF,
DIVIDEND.TAX_REL_PD,
DIVIDEND.TAXTRAN
FROM MEMBER
INNER JOIN DIVIDEND
ON MEMBER.[PLAN] = DIVIDEND.[PLAN]
WHERE (DIVIDEND.TAX_RELIEF <> 0)
AND (DIVIDEND.TAXTRAN = 0)
AND (MEMBER.MIGRATIONFLAG = 'C')
OR (DIVIDEND.TAX_RELIEF <> 0)
AND (DIVIDEND.TAXTRAN IS NULL )
AND (MEMBER.MIGRATIONFLAG = 'C')
END
ELSE
PRINT 'no'
Thanks
Ganesh
"Raymond D'Anjou" wrote:

> I have to guess a bit here.
> Change the beginning of your procedure to this:
>
> AND exists (select syscolumns.name ...
> "Ganesh" <gsganesh@.yahoo.com> wrote in message
> news:99A82BDA-F5B5-40DC-8C18-434D6B9C2665@.microsoft.com...
>
>

If Exists Column ?

Hello

How do you check if a column exist ?

for a table :

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[myTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[myTable]
GO

but I dont find it for a column

Thank youif exists ( select * from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME='tablename'
and COLUMN_NAME='columname' )
drop table [dbo].[myTable]|||I want to drop the column not the table ?

thank you for helping|||Here we go.......
--alter table tablename drop column columnname

if exists ( select * from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME='tablename'
and COLUMN_NAME='columname' )
alter table tablename drop column columnname
go|||This is one way....

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(Col1 int, Col2 varchar(25))
GO

INSERT INTO myTable99(Col1, Col2)
SELECT 1, 'a' UNION ALL SELECT 2, 'b' UNION ALL SELECT 3, 'c'
GO

SELECT * FROM myTable99

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = 'myTable99' AND COLUMN_NAME = 'Col2')
ALTER TABLE myTable99 DROP COLUMN Col2

SELECT * FROM myTable99
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO

IDENTITY_INSERT Error

I have been trying to get this to work but am failing can anybody help
SET IDENTITY_INSERT bossdata.dbo.DailyOverLimits ON
INSERT INTO bossdata.dbo.DailyOverLimits
SELECT *
FROM OPENDATASOURCE ('SQLOLEDB', 'Data Source=@.Server;User
ID=@.UserName;Password=@.Psw' ).Bossdata.dbo.DailyOverLimits AS A
WHERE (NOT EXISTS
(SELECT Licence, Companyname, fileseq
FROM bossdata.dbo.DailyOverLimits AS b
WHERE (a.licence = b.licence AND
a.Companyname = b.Companyname
AND a.fileseq = b.fileseq )))
SET IDENTITY_INSERT bossdata.dbo.DailyOverLimits OFF
Error:
Msg 8101, Level 16, State 1, Line 2
An explicit value for the identity column in table
'bossdata.dbo.DailyOverLimits' can only be specified when a column list is
used and IDENTITY_INSERT is ON.Don't use select * from opendatasource. Give column names.
Idenityt insert doesn't work with *.
Hope this helps|||and its always a good practice to not use select * and to also specify the
column list in the insert.
Use it this way
INSERT INTO TBL1(COL1,COL2,COL3)
SELECT COLA,COLB,COLC FROM tbl2
because that way you make sure the right values goes to the right columns.|||Thanks Omnibuzz,
Seems like im jumping from one error to another lol, any clues
SET IDENTITY_INSERT bossdata.dbo.DailyOverLimits ON
INSERT INTO bossdata.dbo.DailyOverLimits
SELECT Licence, CompanyName, FileSEQ, AccountCode, ANLRef, FileTotal,
LimitExceeded,
LimitValue, CurentAccum, ExceededValue, ClearingDate
FROM OPENDATASOURCE ('SQLOLEDB', 'Data Source=BACSSYS;User
ID=sa;Password=22559226' ).Bossdata.dbo.DailyOverLimits AS A
WHERE (NOT EXISTS
(SELECT Licence, CompanyName, FileSEQ,
AccountCode, ANLRef, FileTotal, LimitExceeded,
LimitValue, CurentAccum, ExceededValue, ClearingDate
FROM bossdata.dbo.DailyOverLimits AS b
WHERE (a.licence = b.licence AND
a.Companyname = b.Companyname
AND a.fileseq = b.fileseq Collate
SQL_Latin1_General_CP1_CI_AS)))
SET IDENTITY_INSERT bossdata.dbo.DailyOverLimits OFF
Explicit value must be specified for identity column in table
'DailyOverLimits' either when IDENTITY_INSERT is set to ON or when a
replication user is inserting into a NOT FOR REPLICATION identity column.
"Omnibuzz" wrote:

> and its always a good practice to not use select * and to also specify the
> column list in the insert.
> Use it this way
> INSERT INTO TBL1(COL1,COL2,COL3)
> SELECT COLA,COLB,COLC FROM tbl2
> because that way you make sure the right values goes to the right columns.
>|||:)
The answer is there in my second post..
give the column name with the insert table.
See if that works.|||Always list columns, don't leave SQL Server guessing:
insert <destination table>
(
<column 1>
,<column 2>
,...
)
select <column 1>
,<column 2>
,...
from <source table>
ML
http://milambda.blogspot.com/

IDENTITY_INSERT Error

I have been trying to get this to work but am failing can anybody help
SET IDENTITY_INSERT bossdata.dbo.DailyOverLimits ON
INSERT INTO bossdata.dbo.DailyOverLimits
SELECT *
FROM OPENDATASOURCE ('SQLOLEDB', 'Data Source=@.Server;User
ID=@.UserName;Password=@.Psw' ).Bossdata.dbo.DailyOverLimits AS A
WHERE (NOT EXISTS
(SELECT Licence, Companyname, fileseq
FROM bossdata.dbo.DailyOverLimits AS b
WHERE (a.licence = b.licence AND
a.Companyname = b.Companyname
AND a.fileseq = b.fileseq )))
SET IDENTITY_INSERT bossdata.dbo.DailyOverLimits OFF
Error:
Msg 8101, Level 16, State 1, Line 2
An explicit value for the identity column in table
'bossdata.dbo.DailyOverLimits' can only be specified when a column list is
used and IDENTITY_INSERT is ON.Don't use select * from opendatasource. Give column names.
Idenityt insert doesn't work with *.
Hope this helps|||and its always a good practice to not use select * and to also specify the
column list in the insert.
Use it this way
INSERT INTO TBL1(COL1,COL2,COL3)
SELECT COLA,COLB,COLC FROM tbl2
because that way you make sure the right values goes to the right columns.|||Thanks Omnibuzz,
Seems like im jumping from one error to another lol, any clues
SET IDENTITY_INSERT bossdata.dbo.DailyOverLimits ON
INSERT INTO bossdata.dbo.DailyOverLimits
SELECT Licence, CompanyName, FileSEQ, AccountCode, ANLRef, FileTotal,
LimitExceeded,
LimitValue, CurentAccum, ExceededValue, ClearingDate
FROM OPENDATASOURCE ('SQLOLEDB', 'Data Source=BACSSYS;User
ID=sa;Password=22559226' ).Bossdata.dbo.DailyOverLimits AS A
WHERE (NOT EXISTS
(SELECT Licence, CompanyName, FileSEQ,
AccountCode, ANLRef, FileTotal, LimitExceeded,
LimitValue, CurentAccum, ExceededValue, ClearingDate
FROM bossdata.dbo.DailyOverLimits AS b
WHERE (a.licence = b.licence AND
a.Companyname = b.Companyname
AND a.fileseq = b.fileseq Collate
SQL_Latin1_General_CP1_CI_AS)))
SET IDENTITY_INSERT bossdata.dbo.DailyOverLimits OFF
Explicit value must be specified for identity column in table
'DailyOverLimits' either when IDENTITY_INSERT is set to ON or when a
replication user is inserting into a NOT FOR REPLICATION identity column.
"Omnibuzz" wrote:

> and its always a good practice to not use select * and to also specify the
> column list in the insert.
> Use it this way
> INSERT INTO TBL1(COL1,COL2,COL3)
> SELECT COLA,COLB,COLC FROM tbl2
> because that way you make sure the right values goes to the right columns.
>|||:)
The answer is there in my second post..
give the column name with the insert table.
See if that works.|||Always list columns, don't leave SQL Server guessing:
insert <destination table>
(
<column 1>
,<column 2>
,...
)
select <column 1>
,<column 2>
,...
from <source table>
ML
http://milambda.blogspot.com/

identity insert issue

I am trying to update an identity column with a new value. I am doing the
following:
set identity_insert tbgfmla4.dbo.[tblname] on
go
update [tblname]
set identitycolumn= 124926
where [fieldname] = 'ABCD'
and it gives me an error
Server: Msg 8102, Level 16, State 1, Line 1
Cannot update identity column 'identitycolumn'.
What am I doing wrong? Thanks in advance."sharman" <sharman@.discussions.microsoft.com> wrote in message
news:8E0F1ECE-30E1-4780-B5A0-E89BCBE8ABF9@.microsoft.com...
>I am trying to update an identity column with a new value. I am doing the
> following:
> set identity_insert tbgfmla4.dbo.[tblname] on
> go
> update [tblname]
> set identitycolumn= 124926
> where [fieldname] = 'ABCD'
> and it gives me an error
> Server: Msg 8102, Level 16, State 1, Line 1
> Cannot update identity column 'identitycolumn'.
> What am I doing wrong? Thanks in advance.
An IDENTITY column cannot be updated under any circumstances, irrespective
of the IDENTITY_INSERT setting. If this is a problem for you then don't use
IDENTITY.
You can however DELETE and the re-INSERT the IDENTITY value if
IDENTITY_INSERT is on, assuming you avoid violating any constraints by doing
so.
--
David Portas|||On Nov 27, 3:37 am, sharman <shar...@.discussions.microsoft.com> wrote:
> I am trying to update an identity column with a new value. I am doing the
> following:
> set identity_insert tbgfmla4.dbo.[tblname] on
> go
> update [tblname]
> set identitycolumn= 124926
> where [fieldname] = 'ABCD'
> and it gives me an error
> Server: Msg 8102, Level 16, State 1, Line 1
> Cannot update identity column 'identitycolumn'.
> What am I doing wrong? Thanks in advance.
Note that with set identity_insert tbgfmla4.dbo.[tblname] on, you can
only add value to the column and you cant update it
Why do you want to update identity column?

identity insert issue

I am trying to update an identity column with a new value. I am doing the
following:
set identity_insert tbgfmla4.dbo.[tblname] on
go
update [tblname]
set identitycolumn= 124926
where [fieldname] = 'ABCD'
and it gives me an error
Server: Msg 8102, Level 16, State 1, Line 1
Cannot update identity column 'identitycolumn'.
What am I doing wrong? Thanks in advance.
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:8E0F1ECE-30E1-4780-B5A0-E89BCBE8ABF9@.microsoft.com...
>I am trying to update an identity column with a new value. I am doing the
> following:
> set identity_insert tbgfmla4.dbo.[tblname] on
> go
> update [tblname]
> set identitycolumn= 124926
> where [fieldname] = 'ABCD'
> and it gives me an error
> Server: Msg 8102, Level 16, State 1, Line 1
> Cannot update identity column 'identitycolumn'.
> What am I doing wrong? Thanks in advance.
An IDENTITY column cannot be updated under any circumstances, irrespective
of the IDENTITY_INSERT setting. If this is a problem for you then don't use
IDENTITY.
You can however DELETE and the re-INSERT the IDENTITY value if
IDENTITY_INSERT is on, assuming you avoid violating any constraints by doing
so.
David Portas
|||On Nov 27, 3:37 am, sharman <shar...@.discussions.microsoft.com> wrote:
> I am trying to update an identity column with a new value. I am doing the
> following:
> set identity_insert tbgfmla4.dbo.[tblname] on
> go
> update [tblname]
> set identitycolumn= 124926
> where [fieldname] = 'ABCD'
> and it gives me an error
> Server: Msg 8102, Level 16, State 1, Line 1
> Cannot update identity column 'identitycolumn'.
> What am I doing wrong? Thanks in advance.
Note that with set identity_insert tbgfmla4.dbo.[tblname] on, you can
only add value to the column and you cant update it
Why do you want to update identity column?

identity insert issue

I am trying to update an identity column with a new value. I am doing the
following:
set identity_insert tbgfmla4.dbo.[tblname] on
go
update [tblname]
set identitycolumn= 124926
where [fieldname] = 'ABCD'
and it gives me an error
Server: Msg 8102, Level 16, State 1, Line 1
Cannot update identity column 'identitycolumn'.
What am I doing wrong? Thanks in advance."sharman" <sharman@.discussions.microsoft.com> wrote in message
news:8E0F1ECE-30E1-4780-B5A0-E89BCBE8ABF9@.microsoft.com...
>I am trying to update an identity column with a new value. I am doing the
> following:
> set identity_insert tbgfmla4.dbo.[tblname] on
> go
> update [tblname]
> set identitycolumn= 124926
> where [fieldname] = 'ABCD'
> and it gives me an error
> Server: Msg 8102, Level 16, State 1, Line 1
> Cannot update identity column 'identitycolumn'.
> What am I doing wrong? Thanks in advance.
An IDENTITY column cannot be updated under any circumstances, irrespective
of the IDENTITY_INSERT setting. If this is a problem for you then don't use
IDENTITY.
You can however DELETE and the re-INSERT the IDENTITY value if
IDENTITY_INSERT is on, assuming you avoid violating any constraints by doing
so.
David Portas|||On Nov 27, 3:37 am, sharman <shar...@.discussions.microsoft.com> wrote:
> I am trying to update an identity column with a new value. I am doing the
> following:
> set identity_insert tbgfmla4.dbo.[tblname] on
> go
> update [tblname]
> set identitycolumn= 124926
> where [fieldname] = 'ABCD'
> and it gives me an error
> Server: Msg 8102, Level 16, State 1, Line 1
> Cannot update identity column 'identitycolumn'.
> What am I doing wrong? Thanks in advance.
Note that with set identity_insert tbgfmla4.dbo.[tblname] on, you can
only add value to the column and you cant update it
Why do you want to update identity column?