any way to check if all the values in a column returned are the same value?

say i have a table, and in it are two columns, column1 and column2 and i do the following query:

SELECT column1, column2 FROM table WHERE column2 = '12345'

i want to check if column1 has all the same values, so in the first case, no

column1 column2
--- ---
4 12345
9 12345
5 12345

column1 column2
--- ---
9 12345
9 12345
9 12345

in the 2nd case, column1 contains all the same values, so yes

is there anyway i can check this? i would be doing this in a trigger.. say when a new row is inserted, the value of column1 is inserted, but col 2 is null.. so when they try to fill in the value for col2 of that row, the trigger checks to see if the value they put for col 2 is already in the table.. if it isn't, then everything is ok. but if it is already in teh table, then it checks col1 to see if all the values of col1 are the same

i hope this makes sense

thanksWithin your trigger, you may determine the min() and max() value of Column1 regarding a certain value for column 2:

SELECT min(Column1), max(column1)
Into Min1, Max1
FROM <YourTable>
WHERE Column2 = <Your current value>
GROUP BY Column2

If Min1=Max1, all values are the same.|||SELECT COUNT(NumValues) FROM (
SELECT COUNT(*) AS NumValues FROM MyTable
WHERE Column2 = MyValue
GROUP BY Column1) AS TempCount;

Any suggestions?

I have a database which contains more than 20000 stored procedures
which were created with
ansi nulls off. This i found out using the query
SELECT name,AnsiNullsOn FROM
(
SELECT name, OBJECTPROPERTY(id, 'ExecIsAnsiNullsOn') AS AnsiNullsOn
FROM sysobjects WHERE type = 'P' ) A WHERE AnsiNullsOn=0

Is there any way that i can set this property to 1 for all the stored
procedures i have??

I know the alternate method is to drop the procedure and execute the
scripts again with AnsiNullsOn = 1.

Is there any other simple ways?? It will be very helpful for me..I believe this will do the job, though I suggest some small-scale
tests first.

1) Script out all the procs as CREATEs, but do not include the DROP
option.

2) Edit the script.

- Change CREATE PROC to ALTER PROC for all procedures.

- Change all the SET ANSI_NULLS ( and possibly the SET
QUOTED_IDENTIFIER) commands as you want them.

3) Run the script.

Roy Harvey
Beacon Falls, CT

On 21 Nov 2006 14:17:38 -0800, "balaji" <mailbalajijagan@.gmail.com>
wrote:

Quote:

Originally Posted by

>I have a database which contains more than 20000 stored procedures
>which were created with
>ansi nulls off. This i found out using the query
>SELECT name,AnsiNullsOn FROM
>(
>SELECT name, OBJECTPROPERTY(id, 'ExecIsAnsiNullsOn') AS AnsiNullsOn
>FROM sysobjects WHERE type = 'P' ) A WHERE AnsiNullsOn=0
>
>Is there any way that i can set this property to 1 for all the stored
>procedures i have??
>
>I know the alternate method is to drop the procedure and execute the
>scripts again with AnsiNullsOn = 1.
>
>Is there any other simple ways?? It will be very helpful for me..

|||balaji wrote:

Quote:

Originally Posted by

I have a database which contains more than 20000 stored procedures
which were created with
ansi nulls off. This i found out using the query
SELECT name,AnsiNullsOn FROM
(
SELECT name, OBJECTPROPERTY(id, 'ExecIsAnsiNullsOn') AS AnsiNullsOn
FROM sysobjects WHERE type = 'P' ) A WHERE AnsiNullsOn=0
>
Is there any way that i can set this property to 1 for all the stored
procedures i have??
>
I know the alternate method is to drop the procedure and execute the
scripts again with AnsiNullsOn = 1.
>
Is there any other simple ways?? It will be very helpful for me..

Just to state the obvious. Since there are potential behaviour changes
associated with changing the setting it seems like the bigger task
could be testing the procs rather than making the change.

--
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/...US,SQL.90).aspx
--