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
Showing posts with label mytable. Show all posts
Showing posts with label mytable. Show all posts
Wednesday, March 28, 2012
If exists
Any idea why
BEGIN IF EXISTS (select * from mytable)print '0' else print '1' end
works and not
BEGIN IF EXISTS (select * from mytable) '0' else '1' end
I am trying to put the latter into a case statement.
Regards,
Jamie
You cannot just use '0' or '1', according to SQL syntax you need a SELECT.
This will work:
IF EXISTS (select * from mytable) SELECT '0' else SELECT '1'
Regards,
Plamen Ratchev
http://www.SQLStudio.com
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:2C00DCB8-2CEA-45FD-9094-F69D49777DAF@.microsoft.com...
> Any idea why
> BEGIN IF EXISTS (select * from mytable)print '0' else print '1' end
> works and not
> BEGIN IF EXISTS (select * from mytable) '0' else '1' end
> I am trying to put the latter into a case statement.
> --
> Regards,
> Jamie
|||Hi Jamie
It may be clearer if you indented!!
BEGIN
IF EXISTS (SELECT * FROM mytable)
PRINT '0'
ELSE
PRINT '1'
END
This will work, but you can't use it with a PRINT statement:
BEGIN
SELECT CASE WHEN EXISTS (SELECT * FROM mytable)
THEN '0'
ELSE '1'
END
END
Although you can do:
BEGIN
DECLARE @.output char(1)
SET @.output = CASE WHEN EXISTS (SELECT * FROM mytable)
THEN '0'
ELSE '1'
END
PRINT @.output
END
HTH
John
"thejamie" wrote:
> Any idea why
> BEGIN IF EXISTS (select * from mytable)print '0' else print '1' end
> works and not
> BEGIN IF EXISTS (select * from mytable) '0' else '1' end
> I am trying to put the latter into a case statement.
> --
> Regards,
> Jamie
|||John,
I see part of the error of my ways. This is a condition for a case
statement thus
Begin
Select
case when (if exists(Select * from atoms.dbo.tpreallocations) select 0 else
select 1 end)=0 then
print 'green'
else
print 'red'
end
Still get an error here...
Regards,
Jamie
"John Bell" wrote:
[vbcol=seagreen]
> Hi Jamie
> It may be clearer if you indented!!
> BEGIN
> IF EXISTS (SELECT * FROM mytable)
> PRINT '0'
> ELSE
> PRINT '1'
> END
> This will work, but you can't use it with a PRINT statement:
> BEGIN
> SELECT CASE WHEN EXISTS (SELECT * FROM mytable)
> THEN '0'
> ELSE '1'
> END
> END
> Although you can do:
> BEGIN
> DECLARE @.output char(1)
> SET @.output = CASE WHEN EXISTS (SELECT * FROM mytable)
> THEN '0'
> ELSE '1'
> END
> PRINT @.output
> END
> HTH
> John
> "thejamie" wrote:
|||Seems like some of the logic here is a bit redundant. Maybe you can simplify
like this:
Select
case when exists(Select * from atoms.dbo.tpreallocations) then
'green'
else
'red'
end
Regards,
Plamen Ratchev
http://www.SQLStudio.com
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:33B939B4-8BFA-43CF-805E-0DA0456BD47F@.microsoft.com...[vbcol=seagreen]
> John,
> I see part of the error of my ways. This is a condition for a case
> statement thus
> Begin
> Select
> case when (if exists(Select * from atoms.dbo.tpreallocations) select 0
> else
> select 1 end)=0 then
> print 'green'
> else
> print 'red'
> end
> Still get an error here...
> --
> Regards,
> Jamie
>
> "John Bell" wrote:
|||Hi Jamie
You don't need to do the if, Case can take a boolean expression in the WHEN
clause, but you can not use PRINT with a CASE statement or use print within a
SELECT statement
To use PRINT
BEGIN
DECLARE @.output char(5)
SET @.output = CASE WHEN EXISTS (SELECT *
FROM
atoms.dbo.tpreallocations)
THEN 'green'
ELSE 'ref'
END
PRINT @.output
END
To use SELECT to return a result set
BEGIN
SELECT CASE WHEN EXISTS (SELECT *
FROM
atoms.dbo.tpreallocations)
THEN 'green'
ELSE 'ref'
END
END
HTH
John
"thejamie" wrote:
[vbcol=seagreen]
> John,
> I see part of the error of my ways. This is a condition for a case
> statement thus
> Begin
> Select
> case when (if exists(Select * from atoms.dbo.tpreallocations) select 0 else
> select 1 end)=0 then
> print 'green'
> else
> print 'red'
> end
> Still get an error here...
> --
> Regards,
> Jamie
>
> "John Bell" wrote:
|||I read that WHEN EXISTS... is faster than the following logic:
CASE (select count(*) from atoms.dbo.tpreallocations tp with(nolock) join
atoms.dbo.torderlines tol with(nolock) on tp.orderlinekey = tol.orderlinekey
where isnull(packstatus,0) = 0 and tol.bol = tbols.bolid)
WHEN 0 THEN ...
http://www.sql-server-performance.com/transact_sql.asp
Hoping this is correct - am trying to improve performance of the view.
Thanks for help from you both.
Regards,
Jamie
"Plamen Ratchev" wrote:
> Seems like some of the logic here is a bit redundant. Maybe you can simplify
> like this:
> Select
> case when exists(Select * from atoms.dbo.tpreallocations) then
> 'green'
> else
> 'red'
> end
> Regards,
> Plamen Ratchev
> http://www.SQLStudio.com
>
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:33B939B4-8BFA-43CF-805E-0DA0456BD47F@.microsoft.com...
>
>
|||>I read that WHEN EXISTS... is faster than the following logic:
Just because you read it somewhere, doesn't always make it true.
> CASE (select count(*) from atoms.dbo.tpreallocations tp with(nolock) join
> atoms.dbo.torderlines tol with(nolock) on tp.orderlinekey =
> tol.orderlinekey
> where isnull(packstatus,0) = 0 and tol.bol = tbols.bolid)
> WHEN 0 THEN ...
> http://www.sql-server-performance.com/transact_sql.asp
> Hoping this is correct - am trying to improve performance of the view.
Don't hope - test and verify. BTW, the link correctly indicates that EXISTS
**may** be more efficient.
In general, your entire approach to addressing the issue within this thread
isn't helping you. You continue to dribble out information, bit by bit.
Trying to help you with problems involving the syntax you are using, without
knowing exactly how you are using it, is difficult at best. It is likely to
lead to inappropriate suggestions. You started asking about syntax, but the
real issue seems to be performance. The most correct syntax in the world
will not necessarily make your query more efficient. No offense, but
trouble with relatively basic syntax likely means you are in over your head
at this point. Perhaps you should consider some additional expertise for
help with the tuning/management problems you seem to be experiencing.
Improving the query involves looking at the entire query and examining all
aspects of the logic and data to ensure that both the database and the query
have been optimized. Optimization is a two-way street. Often, there are
performance tradeoffs that must be made to provide an overall level of
performance for the entire system. Tuning one particular query to the
maximum degree possible can have a detrimental effect on other aspects of
the system. In general, you shouldn't need locking hints. You should use
them only if you understand the locking architecture of sql server and how
the hints affect the overall system as a whole.
|||The help works fine. Testing shows no measurable difference. Thought maybe
I missed something but thanks for your reply.
Regards,
Jamie
"Scott Morris" wrote:
> Just because you read it somewhere, doesn't always make it true.
>
> Don't hope - test and verify. BTW, the link correctly indicates that EXISTS
> **may** be more efficient.
> In general, your entire approach to addressing the issue within this thread
> isn't helping you. You continue to dribble out information, bit by bit.
> Trying to help you with problems involving the syntax you are using, without
> knowing exactly how you are using it, is difficult at best. It is likely to
> lead to inappropriate suggestions. You started asking about syntax, but the
> real issue seems to be performance. The most correct syntax in the world
> will not necessarily make your query more efficient. No offense, but
> trouble with relatively basic syntax likely means you are in over your head
> at this point. Perhaps you should consider some additional expertise for
> help with the tuning/management problems you seem to be experiencing.
> Improving the query involves looking at the entire query and examining all
> aspects of the logic and data to ensure that both the database and the query
> have been optimized. Optimization is a two-way street. Often, there are
> performance tradeoffs that must be made to provide an overall level of
> performance for the entire system. Tuning one particular query to the
> maximum degree possible can have a detrimental effect on other aspects of
> the system. In general, you shouldn't need locking hints. You should use
> them only if you understand the locking architecture of sql server and how
> the hints affect the overall system as a whole.
>
>
|||Hi Jamie
You are probably not testing on significant numbers of records, the EXISTS
clause is usually more scalable. As per Scotts reply, without the whole
picture it is difficult to know if you are looking at the best solution
anyhow. You may also want to read http://www.aspfaq.com/etiquette.asp?id=5006
which gives you some guidelines on posting.
John
"thejamie" wrote:
[vbcol=seagreen]
> The help works fine. Testing shows no measurable difference. Thought maybe
> I missed something but thanks for your reply.
> --
> Regards,
> Jamie
>
> "Scott Morris" wrote:
BEGIN IF EXISTS (select * from mytable)print '0' else print '1' end
works and not
BEGIN IF EXISTS (select * from mytable) '0' else '1' end
I am trying to put the latter into a case statement.
Regards,
Jamie
You cannot just use '0' or '1', according to SQL syntax you need a SELECT.
This will work:
IF EXISTS (select * from mytable) SELECT '0' else SELECT '1'
Regards,
Plamen Ratchev
http://www.SQLStudio.com
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:2C00DCB8-2CEA-45FD-9094-F69D49777DAF@.microsoft.com...
> Any idea why
> BEGIN IF EXISTS (select * from mytable)print '0' else print '1' end
> works and not
> BEGIN IF EXISTS (select * from mytable) '0' else '1' end
> I am trying to put the latter into a case statement.
> --
> Regards,
> Jamie
|||Hi Jamie
It may be clearer if you indented!!
BEGIN
IF EXISTS (SELECT * FROM mytable)
PRINT '0'
ELSE
PRINT '1'
END
This will work, but you can't use it with a PRINT statement:
BEGIN
SELECT CASE WHEN EXISTS (SELECT * FROM mytable)
THEN '0'
ELSE '1'
END
END
Although you can do:
BEGIN
DECLARE @.output char(1)
SET @.output = CASE WHEN EXISTS (SELECT * FROM mytable)
THEN '0'
ELSE '1'
END
PRINT @.output
END
HTH
John
"thejamie" wrote:
> Any idea why
> BEGIN IF EXISTS (select * from mytable)print '0' else print '1' end
> works and not
> BEGIN IF EXISTS (select * from mytable) '0' else '1' end
> I am trying to put the latter into a case statement.
> --
> Regards,
> Jamie
|||John,
I see part of the error of my ways. This is a condition for a case
statement thus
Begin
Select
case when (if exists(Select * from atoms.dbo.tpreallocations) select 0 else
select 1 end)=0 then
print 'green'
else
print 'red'
end
Still get an error here...
Regards,
Jamie
"John Bell" wrote:
[vbcol=seagreen]
> Hi Jamie
> It may be clearer if you indented!!
> BEGIN
> IF EXISTS (SELECT * FROM mytable)
> PRINT '0'
> ELSE
> PRINT '1'
> END
> This will work, but you can't use it with a PRINT statement:
> BEGIN
> SELECT CASE WHEN EXISTS (SELECT * FROM mytable)
> THEN '0'
> ELSE '1'
> END
> END
> Although you can do:
> BEGIN
> DECLARE @.output char(1)
> SET @.output = CASE WHEN EXISTS (SELECT * FROM mytable)
> THEN '0'
> ELSE '1'
> END
> PRINT @.output
> END
> HTH
> John
> "thejamie" wrote:
|||Seems like some of the logic here is a bit redundant. Maybe you can simplify
like this:
Select
case when exists(Select * from atoms.dbo.tpreallocations) then
'green'
else
'red'
end
Regards,
Plamen Ratchev
http://www.SQLStudio.com
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:33B939B4-8BFA-43CF-805E-0DA0456BD47F@.microsoft.com...[vbcol=seagreen]
> John,
> I see part of the error of my ways. This is a condition for a case
> statement thus
> Begin
> Select
> case when (if exists(Select * from atoms.dbo.tpreallocations) select 0
> else
> select 1 end)=0 then
> print 'green'
> else
> print 'red'
> end
> Still get an error here...
> --
> Regards,
> Jamie
>
> "John Bell" wrote:
|||Hi Jamie
You don't need to do the if, Case can take a boolean expression in the WHEN
clause, but you can not use PRINT with a CASE statement or use print within a
SELECT statement
To use PRINT
BEGIN
DECLARE @.output char(5)
SET @.output = CASE WHEN EXISTS (SELECT *
FROM
atoms.dbo.tpreallocations)
THEN 'green'
ELSE 'ref'
END
PRINT @.output
END
To use SELECT to return a result set
BEGIN
SELECT CASE WHEN EXISTS (SELECT *
FROM
atoms.dbo.tpreallocations)
THEN 'green'
ELSE 'ref'
END
END
HTH
John
"thejamie" wrote:
[vbcol=seagreen]
> John,
> I see part of the error of my ways. This is a condition for a case
> statement thus
> Begin
> Select
> case when (if exists(Select * from atoms.dbo.tpreallocations) select 0 else
> select 1 end)=0 then
> print 'green'
> else
> print 'red'
> end
> Still get an error here...
> --
> Regards,
> Jamie
>
> "John Bell" wrote:
|||I read that WHEN EXISTS... is faster than the following logic:
CASE (select count(*) from atoms.dbo.tpreallocations tp with(nolock) join
atoms.dbo.torderlines tol with(nolock) on tp.orderlinekey = tol.orderlinekey
where isnull(packstatus,0) = 0 and tol.bol = tbols.bolid)
WHEN 0 THEN ...
http://www.sql-server-performance.com/transact_sql.asp
Hoping this is correct - am trying to improve performance of the view.
Thanks for help from you both.
Regards,
Jamie
"Plamen Ratchev" wrote:
> Seems like some of the logic here is a bit redundant. Maybe you can simplify
> like this:
> Select
> case when exists(Select * from atoms.dbo.tpreallocations) then
> 'green'
> else
> 'red'
> end
> Regards,
> Plamen Ratchev
> http://www.SQLStudio.com
>
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:33B939B4-8BFA-43CF-805E-0DA0456BD47F@.microsoft.com...
>
>
|||>I read that WHEN EXISTS... is faster than the following logic:
Just because you read it somewhere, doesn't always make it true.
> CASE (select count(*) from atoms.dbo.tpreallocations tp with(nolock) join
> atoms.dbo.torderlines tol with(nolock) on tp.orderlinekey =
> tol.orderlinekey
> where isnull(packstatus,0) = 0 and tol.bol = tbols.bolid)
> WHEN 0 THEN ...
> http://www.sql-server-performance.com/transact_sql.asp
> Hoping this is correct - am trying to improve performance of the view.
Don't hope - test and verify. BTW, the link correctly indicates that EXISTS
**may** be more efficient.
In general, your entire approach to addressing the issue within this thread
isn't helping you. You continue to dribble out information, bit by bit.
Trying to help you with problems involving the syntax you are using, without
knowing exactly how you are using it, is difficult at best. It is likely to
lead to inappropriate suggestions. You started asking about syntax, but the
real issue seems to be performance. The most correct syntax in the world
will not necessarily make your query more efficient. No offense, but
trouble with relatively basic syntax likely means you are in over your head
at this point. Perhaps you should consider some additional expertise for
help with the tuning/management problems you seem to be experiencing.
Improving the query involves looking at the entire query and examining all
aspects of the logic and data to ensure that both the database and the query
have been optimized. Optimization is a two-way street. Often, there are
performance tradeoffs that must be made to provide an overall level of
performance for the entire system. Tuning one particular query to the
maximum degree possible can have a detrimental effect on other aspects of
the system. In general, you shouldn't need locking hints. You should use
them only if you understand the locking architecture of sql server and how
the hints affect the overall system as a whole.
|||The help works fine. Testing shows no measurable difference. Thought maybe
I missed something but thanks for your reply.
Regards,
Jamie
"Scott Morris" wrote:
> Just because you read it somewhere, doesn't always make it true.
>
> Don't hope - test and verify. BTW, the link correctly indicates that EXISTS
> **may** be more efficient.
> In general, your entire approach to addressing the issue within this thread
> isn't helping you. You continue to dribble out information, bit by bit.
> Trying to help you with problems involving the syntax you are using, without
> knowing exactly how you are using it, is difficult at best. It is likely to
> lead to inappropriate suggestions. You started asking about syntax, but the
> real issue seems to be performance. The most correct syntax in the world
> will not necessarily make your query more efficient. No offense, but
> trouble with relatively basic syntax likely means you are in over your head
> at this point. Perhaps you should consider some additional expertise for
> help with the tuning/management problems you seem to be experiencing.
> Improving the query involves looking at the entire query and examining all
> aspects of the logic and data to ensure that both the database and the query
> have been optimized. Optimization is a two-way street. Often, there are
> performance tradeoffs that must be made to provide an overall level of
> performance for the entire system. Tuning one particular query to the
> maximum degree possible can have a detrimental effect on other aspects of
> the system. In general, you shouldn't need locking hints. You should use
> them only if you understand the locking architecture of sql server and how
> the hints affect the overall system as a whole.
>
>
|||Hi Jamie
You are probably not testing on significant numbers of records, the EXISTS
clause is usually more scalable. As per Scotts reply, without the whole
picture it is difficult to know if you are looking at the best solution
anyhow. You may also want to read http://www.aspfaq.com/etiquette.asp?id=5006
which gives you some guidelines on posting.
John
"thejamie" wrote:
[vbcol=seagreen]
> The help works fine. Testing shows no measurable difference. Thought maybe
> I missed something but thanks for your reply.
> --
> Regards,
> Jamie
>
> "Scott Morris" wrote:
If exists
Any idea why
BEGIN IF EXISTS (select * from mytable)print '0' else print '1' end
works and not
BEGIN IF EXISTS (select * from mytable) '0' else '1' end
I am trying to put the latter into a case statement.
--
Regards,
JamieYou cannot just use '0' or '1', according to SQL syntax you need a SELECT.
This will work:
IF EXISTS (select * from mytable) SELECT '0' else SELECT '1'
Regards,
Plamen Ratchev
http://www.SQLStudio.com
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:2C00DCB8-2CEA-45FD-9094-F69D49777DAF@.microsoft.com...
> Any idea why
> BEGIN IF EXISTS (select * from mytable)print '0' else print '1' end
> works and not
> BEGIN IF EXISTS (select * from mytable) '0' else '1' end
> I am trying to put the latter into a case statement.
> --
> Regards,
> Jamie|||Hi Jamie
It may be clearer if you indented!!
BEGIN
IF EXISTS (SELECT * FROM mytable)
PRINT '0'
ELSE
PRINT '1'
END
This will work, but you can't use it with a PRINT statement:
BEGIN
SELECT CASE WHEN EXISTS (SELECT * FROM mytable)
THEN '0'
ELSE '1'
END
END
Although you can do:
BEGIN
DECLARE @.output char(1)
SET @.output = CASE WHEN EXISTS (SELECT * FROM mytable)
THEN '0'
ELSE '1'
END
PRINT @.output
END
HTH
John
"thejamie" wrote:
> Any idea why
> BEGIN IF EXISTS (select * from mytable)print '0' else print '1' end
> works and not
> BEGIN IF EXISTS (select * from mytable) '0' else '1' end
> I am trying to put the latter into a case statement.
> --
> Regards,
> Jamie|||John,
I see part of the error of my ways. This is a condition for a case
statement thus
Begin
Select
case when (if exists(Select * from atoms.dbo.tpreallocations) select 0 else
select 1 end)=0 then
print 'green'
else
print 'red'
end
Still get an error here...
--
Regards,
Jamie
"John Bell" wrote:
[vbcol=seagreen]
> Hi Jamie
> It may be clearer if you indented!!
> BEGIN
> IF EXISTS (SELECT * FROM mytable)
> PRINT '0'
> ELSE
> PRINT '1'
> END
> This will work, but you can't use it with a PRINT statement:
> BEGIN
> SELECT CASE WHEN EXISTS (SELECT * FROM mytable)
> THEN '0'
> ELSE '1'
> END
> END
> Although you can do:
> BEGIN
> DECLARE @.output char(1)
> SET @.output = CASE WHEN EXISTS (SELECT * FROM mytable)
> THEN '0'
> ELSE '1'
> END
> PRINT @.output
> END
> HTH
> John
> "thejamie" wrote:
>|||Seems like some of the logic here is a bit redundant. Maybe you can simplify
like this:
Select
case when exists(Select * from atoms.dbo.tpreallocations) then
'green'
else
'red'
end
Regards,
Plamen Ratchev
http://www.SQLStudio.com
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:33B939B4-8BFA-43CF-805E-0DA0456BD47F@.microsoft.com...[vbcol=seagreen]
> John,
> I see part of the error of my ways. This is a condition for a case
> statement thus
> Begin
> Select
> case when (if exists(Select * from atoms.dbo.tpreallocations) select 0
> else
> select 1 end)=0 then
> print 'green'
> else
> print 'red'
> end
> Still get an error here...
> --
> Regards,
> Jamie
>
> "John Bell" wrote:
>|||Hi Jamie
You don't need to do the if, Case can take a boolean expression in the WHEN
clause, but you can not use PRINT with a CASE statement or use print within
a
SELECT statement
To use PRINT
BEGIN
DECLARE @.output char(5)
SET @.output = CASE WHEN EXISTS (SELECT *
FROM
atoms.dbo.tpreallocations)
THEN 'green'
ELSE 'ref'
END
PRINT @.output
END
To use SELECT to return a result set
BEGIN
SELECT CASE WHEN EXISTS (SELECT *
FROM
atoms.dbo.tpreallocations)
THEN 'green'
ELSE 'ref'
END
END
HTH
John
"thejamie" wrote:
[vbcol=seagreen]
> John,
> I see part of the error of my ways. This is a condition for a case
> statement thus
> Begin
> Select
> case when (if exists(Select * from atoms.dbo.tpreallocations) select 0 els
e
> select 1 end)=0 then
> print 'green'
> else
> print 'red'
> end
> Still get an error here...
> --
> Regards,
> Jamie
>
> "John Bell" wrote:
>
BEGIN IF EXISTS (select * from mytable)print '0' else print '1' end
works and not
BEGIN IF EXISTS (select * from mytable) '0' else '1' end
I am trying to put the latter into a case statement.
--
Regards,
JamieYou cannot just use '0' or '1', according to SQL syntax you need a SELECT.
This will work:
IF EXISTS (select * from mytable) SELECT '0' else SELECT '1'
Regards,
Plamen Ratchev
http://www.SQLStudio.com
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:2C00DCB8-2CEA-45FD-9094-F69D49777DAF@.microsoft.com...
> Any idea why
> BEGIN IF EXISTS (select * from mytable)print '0' else print '1' end
> works and not
> BEGIN IF EXISTS (select * from mytable) '0' else '1' end
> I am trying to put the latter into a case statement.
> --
> Regards,
> Jamie|||Hi Jamie
It may be clearer if you indented!!
BEGIN
IF EXISTS (SELECT * FROM mytable)
PRINT '0'
ELSE
PRINT '1'
END
This will work, but you can't use it with a PRINT statement:
BEGIN
SELECT CASE WHEN EXISTS (SELECT * FROM mytable)
THEN '0'
ELSE '1'
END
END
Although you can do:
BEGIN
DECLARE @.output char(1)
SET @.output = CASE WHEN EXISTS (SELECT * FROM mytable)
THEN '0'
ELSE '1'
END
PRINT @.output
END
HTH
John
"thejamie" wrote:
> Any idea why
> BEGIN IF EXISTS (select * from mytable)print '0' else print '1' end
> works and not
> BEGIN IF EXISTS (select * from mytable) '0' else '1' end
> I am trying to put the latter into a case statement.
> --
> Regards,
> Jamie|||John,
I see part of the error of my ways. This is a condition for a case
statement thus
Begin
Select
case when (if exists(Select * from atoms.dbo.tpreallocations) select 0 else
select 1 end)=0 then
print 'green'
else
print 'red'
end
Still get an error here...
--
Regards,
Jamie
"John Bell" wrote:
[vbcol=seagreen]
> Hi Jamie
> It may be clearer if you indented!!
> BEGIN
> IF EXISTS (SELECT * FROM mytable)
> PRINT '0'
> ELSE
> PRINT '1'
> END
> This will work, but you can't use it with a PRINT statement:
> BEGIN
> SELECT CASE WHEN EXISTS (SELECT * FROM mytable)
> THEN '0'
> ELSE '1'
> END
> END
> Although you can do:
> BEGIN
> DECLARE @.output char(1)
> SET @.output = CASE WHEN EXISTS (SELECT * FROM mytable)
> THEN '0'
> ELSE '1'
> END
> PRINT @.output
> END
> HTH
> John
> "thejamie" wrote:
>|||Seems like some of the logic here is a bit redundant. Maybe you can simplify
like this:
Select
case when exists(Select * from atoms.dbo.tpreallocations) then
'green'
else
'red'
end
Regards,
Plamen Ratchev
http://www.SQLStudio.com
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:33B939B4-8BFA-43CF-805E-0DA0456BD47F@.microsoft.com...[vbcol=seagreen]
> John,
> I see part of the error of my ways. This is a condition for a case
> statement thus
> Begin
> Select
> case when (if exists(Select * from atoms.dbo.tpreallocations) select 0
> else
> select 1 end)=0 then
> print 'green'
> else
> print 'red'
> end
> Still get an error here...
> --
> Regards,
> Jamie
>
> "John Bell" wrote:
>|||Hi Jamie
You don't need to do the if, Case can take a boolean expression in the WHEN
clause, but you can not use PRINT with a CASE statement or use print within
a
SELECT statement
To use PRINT
BEGIN
DECLARE @.output char(5)
SET @.output = CASE WHEN EXISTS (SELECT *
FROM
atoms.dbo.tpreallocations)
THEN 'green'
ELSE 'ref'
END
PRINT @.output
END
To use SELECT to return a result set
BEGIN
SELECT CASE WHEN EXISTS (SELECT *
FROM
atoms.dbo.tpreallocations)
THEN 'green'
ELSE 'ref'
END
END
HTH
John
"thejamie" wrote:
[vbcol=seagreen]
> John,
> I see part of the error of my ways. This is a condition for a case
> statement thus
> Begin
> Select
> case when (if exists(Select * from atoms.dbo.tpreallocations) select 0 els
e
> select 1 end)=0 then
> print 'green'
> else
> print 'red'
> end
> Still get an error here...
> --
> Regards,
> Jamie
>
> "John Bell" wrote:
>
Wednesday, March 7, 2012
Identity columns
Hi,
Quick question, Can I use the 'Alter table mytable alter column' to
change the Identity column to include the 'not for replication' clause? Or
do I need to drop and recreated the table?
thanks,
Nope, but you can use this:
sp_configure 'allow_updates', 1
go
reconfigure with override
go
update syscolumns set colstat=colstat|0x0008 where colstat & 0x0001 <> 0 and
colstat & 0x0008 =0
go
sp_configure 'allow updates', 0
this will fix your identity keys on all of your tables for the not for
replication switch
If you want to limit it to a set of tables you are best to do this
sp_configure 'allow_updates', 1
go
reconfigure with override
go
update syscolumns set colstat=colstat|0x0008 where colstat & 0x0001 <> 0 and
colstat & 0x0008 =0 and id=object_id('tablename')
go
sp_configure 'allow updates', 0
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Joe" <jkdriscoll@.qg.com> wrote in message
news:d2hjin$2kfu$1@.sxnews1.qg.com...
> Hi,
> Quick question, Can I use the 'Alter table mytable alter column' to
> change the Identity column to include the 'not for replication' clause? Or
> do I need to drop and recreated the table?
> thanks,
>
|||Hilary,
Thank you for the response. I can't begin to tell you how much work this
will save me. We've had this one SQL Server, that is production and was
originally only used a little bit. But, as you can guess, over time it's
grown in use. Now we need to set up replication. And what a project this will
be. This server now as 23 production databases, none of which where designed
with replication in mind.
So, once again I say Thanks.
Joe.
"Hilary Cotter" wrote:
> Nope, but you can use this:
> sp_configure 'allow_updates', 1
> go
> reconfigure with override
> go
> update syscolumns set colstat=colstat|0x0008 where colstat & 0x0001 <> 0 and
> colstat & 0x0008 =0
> go
> sp_configure 'allow updates', 0
>
> this will fix your identity keys on all of your tables for the not for
> replication switch
>
> If you want to limit it to a set of tables you are best to do this
>
> sp_configure 'allow_updates', 1
> go
> reconfigure with override
> go
> update syscolumns set colstat=colstat|0x0008 where colstat & 0x0001 <> 0 and
> colstat & 0x0008 =0 and id=object_id('tablename')
> go
> sp_configure 'allow updates', 0
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Joe" <jkdriscoll@.qg.com> wrote in message
> news:d2hjin$2kfu$1@.sxnews1.qg.com...
>
>
|||I have thought of a follow up question.
Will this sql affect any current or future Identity column values?
thanks again.
Joe
"Hilary Cotter" wrote:
> Nope, but you can use this:
> sp_configure 'allow_updates', 1
> go
> reconfigure with override
> go
> update syscolumns set colstat=colstat|0x0008 where colstat & 0x0001 <> 0 and
> colstat & 0x0008 =0
> go
> sp_configure 'allow updates', 0
>
> this will fix your identity keys on all of your tables for the not for
> replication switch
>
> If you want to limit it to a set of tables you are best to do this
>
> sp_configure 'allow_updates', 1
> go
> reconfigure with override
> go
> update syscolumns set colstat=colstat|0x0008 where colstat & 0x0001 <> 0 and
> colstat & 0x0008 =0 and id=object_id('tablename')
> go
> sp_configure 'allow updates', 0
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Joe" <jkdriscoll@.qg.com> wrote in message
> news:d2hjin$2kfu$1@.sxnews1.qg.com...
>
>
|||no it won't.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"JD" <John316@.online.nospam> wrote in message
news:3715E8B9-22F3-40EB-BDBF-09036E45A71D@.microsoft.com...[vbcol=seagreen]
> I have thought of a follow up question.
> Will this sql affect any current or future Identity column values?
> thanks again.
> Joe
> "Hilary Cotter" wrote:
and[vbcol=seagreen]
and[vbcol=seagreen]
to[vbcol=seagreen]
clause? Or[vbcol=seagreen]
Quick question, Can I use the 'Alter table mytable alter column' to
change the Identity column to include the 'not for replication' clause? Or
do I need to drop and recreated the table?
thanks,
Nope, but you can use this:
sp_configure 'allow_updates', 1
go
reconfigure with override
go
update syscolumns set colstat=colstat|0x0008 where colstat & 0x0001 <> 0 and
colstat & 0x0008 =0
go
sp_configure 'allow updates', 0
this will fix your identity keys on all of your tables for the not for
replication switch
If you want to limit it to a set of tables you are best to do this
sp_configure 'allow_updates', 1
go
reconfigure with override
go
update syscolumns set colstat=colstat|0x0008 where colstat & 0x0001 <> 0 and
colstat & 0x0008 =0 and id=object_id('tablename')
go
sp_configure 'allow updates', 0
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Joe" <jkdriscoll@.qg.com> wrote in message
news:d2hjin$2kfu$1@.sxnews1.qg.com...
> Hi,
> Quick question, Can I use the 'Alter table mytable alter column' to
> change the Identity column to include the 'not for replication' clause? Or
> do I need to drop and recreated the table?
> thanks,
>
|||Hilary,
Thank you for the response. I can't begin to tell you how much work this
will save me. We've had this one SQL Server, that is production and was
originally only used a little bit. But, as you can guess, over time it's
grown in use. Now we need to set up replication. And what a project this will
be. This server now as 23 production databases, none of which where designed
with replication in mind.
So, once again I say Thanks.
Joe.
"Hilary Cotter" wrote:
> Nope, but you can use this:
> sp_configure 'allow_updates', 1
> go
> reconfigure with override
> go
> update syscolumns set colstat=colstat|0x0008 where colstat & 0x0001 <> 0 and
> colstat & 0x0008 =0
> go
> sp_configure 'allow updates', 0
>
> this will fix your identity keys on all of your tables for the not for
> replication switch
>
> If you want to limit it to a set of tables you are best to do this
>
> sp_configure 'allow_updates', 1
> go
> reconfigure with override
> go
> update syscolumns set colstat=colstat|0x0008 where colstat & 0x0001 <> 0 and
> colstat & 0x0008 =0 and id=object_id('tablename')
> go
> sp_configure 'allow updates', 0
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Joe" <jkdriscoll@.qg.com> wrote in message
> news:d2hjin$2kfu$1@.sxnews1.qg.com...
>
>
|||I have thought of a follow up question.
Will this sql affect any current or future Identity column values?
thanks again.
Joe
"Hilary Cotter" wrote:
> Nope, but you can use this:
> sp_configure 'allow_updates', 1
> go
> reconfigure with override
> go
> update syscolumns set colstat=colstat|0x0008 where colstat & 0x0001 <> 0 and
> colstat & 0x0008 =0
> go
> sp_configure 'allow updates', 0
>
> this will fix your identity keys on all of your tables for the not for
> replication switch
>
> If you want to limit it to a set of tables you are best to do this
>
> sp_configure 'allow_updates', 1
> go
> reconfigure with override
> go
> update syscolumns set colstat=colstat|0x0008 where colstat & 0x0001 <> 0 and
> colstat & 0x0008 =0 and id=object_id('tablename')
> go
> sp_configure 'allow updates', 0
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Joe" <jkdriscoll@.qg.com> wrote in message
> news:d2hjin$2kfu$1@.sxnews1.qg.com...
>
>
|||no it won't.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"JD" <John316@.online.nospam> wrote in message
news:3715E8B9-22F3-40EB-BDBF-09036E45A71D@.microsoft.com...[vbcol=seagreen]
> I have thought of a follow up question.
> Will this sql affect any current or future Identity column values?
> thanks again.
> Joe
> "Hilary Cotter" wrote:
and[vbcol=seagreen]
and[vbcol=seagreen]
to[vbcol=seagreen]
clause? Or[vbcol=seagreen]
Subscribe to:
Posts (Atom)