Case sensitive

Hi all, I'm using Thai_BIN collation for my database. When i build my SELECT
statement, i have to be case sensitive on the table name and field name. Is
there anywhere to ignore this so that i don't have to care about the case s
ensitivity? Is there any se
tting on the server that i can turn this off? Or is it a design by feature?On Tue, 16 Mar 2004 19:11:05 -0800, Loon wrote:

>Hi all, I'm using Thai_BIN collation for my database. When i build my SELECT statem
ent, i have to be case sensitive on the table name and field name. Is there anywhere
to ignore this so that i don't have to care about the case sensitivity? Is there an
y s
etting on the server that i can turn this off? Or is it a design by feature?
A binary sort order makes sorting and comparisons faster than any
other collation sequence can be, since SQL Server only has to compare
the byte values of the characters.
The drawback is that characters with different byte values (like 'A'
and 'a') compare unequal.
The only way to change collation sequence after installation is to
rebuild the master database and then recreate all your databases with
the new collation sequence. Refer to BOL for details.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

CASE problems with select statement.

I'm trying to build a select statement using the CASE expression. All
I want is to build the WHERE piece of the select statement based on a
parameter value.
I want to somehow CASE the WHERE clause based on a parameter of let's
say, @.DateType
SELECT *
FROM <table>
WHERE <columnA> >= @.BeginDate AND <columnA> <= @.EndDate
SELECT *
FROM <table>
WHERE <columnB> >= @.BeginDate AND <columnB> <= @.EndDate
I know there is something simple i'm over looking.
thanks folks...
-fdAre you talking about something like this? Note this sample works in the
AdventureWorks sample database:
DECLARE @.start DATETIME;
DECLARE @.end DATETIME;
DECLARE @.dateType VARCHAR(5);
SELECT @.start = '2001-08-01',
@.end = '2001-08-15',
@.dateType = 'Ship';
SELECT *
FROM Sales.SalesOrderHeader
WHERE CASE @.dateType WHEN 'Order' THEN OrderDate
WHEN 'Due' THEN DueDate
WHEN 'Ship' THEN ShipDate
END BETWEEN @.start AND @.end;
"forest demon" <mete.hanap@.gmail.com> wrote in message
news:588a87ea-7d70-45c8-83dc-ac06df09f331@.e25g2000prg.googlegroups.com...
> I'm trying to build a select statement using the CASE expression. All
> I want is to build the WHERE piece of the select statement based on a
> parameter value.
> I want to somehow CASE the WHERE clause based on a parameter of let's
> say, @.DateType
> SELECT *
> FROM <table>
> WHERE <columnA> >= @.BeginDate AND <columnA> <= @.EndDate
> SELECT *
> FROM <table>
> WHERE <columnB> >= @.BeginDate AND <columnB> <= @.EndDate
> I know there is something simple i'm over looking.
> thanks folks...
> -fd|||On Mar 6, 10:23=A0pm, "Mike C#" <x...@.xyz.com> wrote:
> Are you talking about something like this? Note this sample works in the
> AdventureWorks sample database:
> DECLARE @.start DATETIME;
> DECLARE @.end DATETIME;
> DECLARE @.dateType VARCHAR(5);
> SELECT @.start =3D '2001-08-01',
> =A0 @.end =3D '2001-08-15',
> =A0 @.dateType =3D 'Ship';
> SELECT *
> FROM Sales.SalesOrderHeader
> WHERE CASE @.dateType WHEN 'Order' THEN OrderDate
> =A0 WHEN 'Due' THEN DueDate
> =A0 WHEN 'Ship' THEN ShipDate
> =A0 END BETWEEN @.start AND @.end;
> "forest demon" <mete.ha...@.gmail.com> wrote in message
> news:588a87ea-7d70-45c8-83dc-ac06df09f331@.e25g2000prg.googlegroups.com...
>
> > I'm trying to build a select statement using the CASE expression. All
> > I want is to build the WHERE piece of the select statement based on a
> > parameter value.
> > I want to somehow CASE the WHERE clause based on a parameter of let's
> > say, @.DateType
> > SELECT *
> > FROM <table>
> > WHERE =A0 <columnA> =A0 >=3D =A0 @.BeginDate =A0AND =A0 <columnA> =A0 <==3D @.EndDate
> > SELECT *
> > FROM <table>
> > WHERE =A0 <columnB> =A0 >=3D =A0 @.BeginDate =A0AND =A0 <columnB> =A0 <==3D @.EndDate
> > I know there is something simple i'm over looking.
> > thanks folks...
> > -fd- Hide quoted text -
> - Show quoted text -
sorry, i should have added a little more info. i use this in a stored
procedure and
i have the following thus far.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spDataReaderStoredProcedure]
(
-- Add the parameters for the stored procedure here
@.BeginDate datetime,
@.EndDate datetime,
@.DateType varchar(50)
)
AS
BEGIN
SET NOCOUNT ON;
SELECT *
FROM Permits
WHERE CASE @.dateType
WHEN 'S_DATE' THEN 'S_DATE >=3D @.BeginDate AND S_DATE <=3D @.EndDate'
WHEN 'G_DATE' THEN 'G_DATE >=3D @.BeginDate AND G_DATE <=3D @.EndDate'
END
END
the DateType comes in as one of two different date types. DateType
either comes
in as 'G_DATE' or 'S_DATE'. the columns in the table are also G_DATE
and S_DATE.
thanks for your time...|||On Mar 6, 11:01=A0pm, forest demon <mete.ha...@.gmail.com> wrote:
> On Mar 6, 10:23=A0pm, "Mike C#" <x...@.xyz.com> wrote:
>
>
> > Are you talking about something like this? Note this sample works in the=
> > AdventureWorks sample database:
> > DECLARE @.start DATETIME;
> > DECLARE @.end DATETIME;
> > DECLARE @.dateType VARCHAR(5);
> > SELECT @.start =3D '2001-08-01',
> > =A0 @.end =3D '2001-08-15',
> > =A0 @.dateType =3D 'Ship';
> > SELECT *
> > FROM Sales.SalesOrderHeader
> > WHERE CASE @.dateType WHEN 'Order' THEN OrderDate
> > =A0 WHEN 'Due' THEN DueDate
> > =A0 WHEN 'Ship' THEN ShipDate
> > =A0 END BETWEEN @.start AND @.end;
> > "forest demon" <mete.ha...@.gmail.com> wrote in message
> >news:588a87ea-7d70-45c8-83dc-ac06df09f331@.e25g2000prg.googlegroups.com...=
> > > I'm trying to build a select statement using the CASE expression. All
> > > I want is to build the WHERE piece of the select statement based on a
> > > parameter value.
> > > I want to somehow CASE the WHERE clause based on a parameter of let's
> > > say, @.DateType
> > > SELECT *
> > > FROM <table>
> > > WHERE =A0 <columnA> =A0 >=3D =A0 @.BeginDate =A0AND =A0 <columnA> =A0 <==3D @.EndDate
> > > SELECT *
> > > FROM <table>
> > > WHERE =A0 <columnB> =A0 >=3D =A0 @.BeginDate =A0AND =A0 <columnB> =A0 <==3D @.EndDate
> > > I know there is something simple i'm over looking.
> > > thanks folks...
> > > -fd- Hide quoted text -
> > - Show quoted text -
> sorry, i should have added a little more info. =A0i use this in a stored
> procedure and
> i have the following thus far.
> set ANSI_NULLS ON
> set QUOTED_IDENTIFIER ON
> GO
> ALTER PROCEDURE [dbo].[spDataReaderStoredProcedure]
> (
> =A0 =A0 =A0 =A0 -- Add the parameters for the stored procedure here
> =A0 =A0 =A0 =A0 @.BeginDate datetime,
> =A0 =A0 =A0 =A0 @.EndDate datetime,
> =A0 =A0 =A0 =A0 @.DateType varchar(50)
> )
> AS
> BEGIN
> =A0 =A0 =A0 =A0 SET NOCOUNT ON;
> SELECT *
> FROM Permits
> WHERE CASE @.dateType
> =A0 WHEN 'S_DATE' THEN 'S_DATE >=3D @.BeginDate AND S_DATE <=3D @.EndDate'
> =A0 WHEN 'G_DATE' THEN 'G_DATE >=3D @.BeginDate AND G_DATE <=3D @.EndDate'
> END
> END
> the DateType comes in as one of two different date types. DateType
> either comes
> in as 'G_DATE' or 'S_DATE'. =A0the columns in the table are also G_DATE
> and S_DATE.
> thanks for your time...- Hide quoted text -
> - Show quoted text -
i finally figured it out...damn, i hate making silly mistakes....
thanks for your input mike...
-fd|||There is case, But there is also Between
Select * from table where DateColumn Between @.BeginDate and @.enddate
Case should not be used in a where.
Better to create a Table Variable and load from an index of Quailifing
Primary keys.
Then Join on the Table variable.
!0 fold performance increase.
-R
"forest demon" <mete.hanap@.gmail.com> wrote in message
news:588a87ea-7d70-45c8-83dc-ac06df09f331@.e25g2000prg.googlegroups.com...
> I'm trying to build a select statement using the CASE expression. All
> I want is to build the WHERE piece of the select statement based on a
> parameter value.
> I want to somehow CASE the WHERE clause based on a parameter of let's
> say, @.DateType
> SELECT *
> FROM <table>
> WHERE <columnA> >= @.BeginDate AND <columnA> <= @.EndDate
> SELECT *
> FROM <table>
> WHERE <columnB> >= @.BeginDate AND <columnB> <= @.EndDate
> I know there is something simple i'm over looking.
> thanks folks...
> -fd|||"Randy Pitkin" <RandyPitkin@.ydpages.com> wrote in message
news:eVT$m35gIHA.5088@.TK2MSFTNGP02.phx.gbl...
> There is case, But there is also Between
> Select * from table where DateColumn Between @.BeginDate and @.enddate
> Case should not be used in a where.
> Better to create a Table Variable and load from an index of Quailifing
> Primary keys.
> Then Join on the Table variable.
> !0 fold performance increase.
BETWEEN replaces the >= AND <= but doesn't do anything for the OP's question
of dynamically determining which column to use to limit rows. Whether or
not he needs a performance increase probably depends on how much data he's
querying, etc.sql

CASE problems with select statement.

I'm trying to build a select statement using the CASE expression. All
I want is to build the WHERE piece of the select statement based on a
parameter value.
I want to somehow CASE the WHERE clause based on a parameter of let's
say, @.DateType
SELECT *
FROM <table>
WHERE <columnA> >= @.BeginDate AND <columnA> <= @.EndDate
SELECT *
FROM <table>
WHERE <columnB> >= @.BeginDate AND <columnB> <= @.EndDate
I know there is something simple i'm over looking.
thanks folks...
-fd
Are you talking about something like this? Note this sample works in the
AdventureWorks sample database:
DECLARE @.start DATETIME;
DECLARE @.end DATETIME;
DECLARE @.dateType VARCHAR(5);
SELECT @.start = '2001-08-01',
@.end = '2001-08-15',
@.dateType = 'Ship';
SELECT *
FROM Sales.SalesOrderHeader
WHERE CASE @.dateType WHEN 'Order' THEN OrderDate
WHEN 'Due' THEN DueDate
WHEN 'Ship' THEN ShipDate
END BETWEEN @.start AND @.end;
"forest demon" <mete.hanap@.gmail.com> wrote in message
news:588a87ea-7d70-45c8-83dc-ac06df09f331@.e25g2000prg.googlegroups.com...
> I'm trying to build a select statement using the CASE expression. All
> I want is to build the WHERE piece of the select statement based on a
> parameter value.
> I want to somehow CASE the WHERE clause based on a parameter of let's
> say, @.DateType
> SELECT *
> FROM <table>
> WHERE <columnA> >= @.BeginDate AND <columnA> <= @.EndDate
> SELECT *
> FROM <table>
> WHERE <columnB> >= @.BeginDate AND <columnB> <= @.EndDate
> I know there is something simple i'm over looking.
> thanks folks...
> -fd
|||On Mar 6, 10:23Xpm, "Mike C#" <x...@.xyz.com> wrote:
> Are you talking about something like this? Note this sample works in the
> AdventureWorks sample database:
> DECLARE @.start DATETIME;
> DECLARE @.end DATETIME;
> DECLARE @.dateType VARCHAR(5);
> SELECT @.start = '2001-08-01',
> X @.end = '2001-08-15',
> X @.dateType = 'Ship';
> SELECT *
> FROM Sales.SalesOrderHeader
> WHERE CASE @.dateType WHEN 'Order' THEN OrderDate
> X WHEN 'Due' THEN DueDate
> X WHEN 'Ship' THEN ShipDate
> X END BETWEEN @.start AND @.end;
> "forest demon" <mete.ha...@.gmail.com> wrote in message
> news:588a87ea-7d70-45c8-83dc-ac06df09f331@.e25g2000prg.googlegroups.com...
>
>
>
>
>
> - Show quoted text -
sorry, i should have added a little more info. i use this in a stored
procedure and
i have the following thus far.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spDataReaderStoredProcedure]
(
-- Add the parameters for the stored procedure here
@.BeginDate datetime,
@.EndDate datetime,
@.DateType varchar(50)
)
AS
BEGIN
SET NOCOUNT ON;
SELECT *
FROM Permits
WHERE CASE @.dateType
WHEN 'S_DATE' THEN 'S_DATE >= @.BeginDate AND S_DATE <= @.EndDate'
WHEN 'G_DATE' THEN 'G_DATE >= @.BeginDate AND G_DATE <= @.EndDate'
END
END
the DateType comes in as one of two different date types. DateType
either comes
in as 'G_DATE' or 'S_DATE'. the columns in the table are also G_DATE
and S_DATE.
thanks for your time...
|||On Mar 6, 11:01Xpm, forest demon <mete.ha...@.gmail.com> wrote:
> On Mar 6, 10:23Xpm, "Mike C#" <x...@.xyz.com> wrote:
>
>
>
>
>
>
>
>
>
> sorry, i should have added a little more info. Xi use this in a stored
> procedure and
> i have the following thus far.
> set ANSI_NULLS ON
> set QUOTED_IDENTIFIER ON
> GO
> ALTER PROCEDURE [dbo].[spDataReaderStoredProcedure]
> (
> X X X X -- Add the parameters for the stored procedure here
> X X X X @.BeginDate datetime,
> X X X X @.EndDate datetime,
> X X X X @.DateType varchar(50)
> )
> AS
> BEGIN
> X X X X SET NOCOUNT ON;
> SELECT *
> FROM Permits
> WHERE CASE @.dateType
> X WHEN 'S_DATE' THEN 'S_DATE >= @.BeginDate AND S_DATE <= @.EndDate'
> X WHEN 'G_DATE' THEN 'G_DATE >= @.BeginDate AND G_DATE <= @.EndDate'
> END
> END
> the DateType comes in as one of two different date types. DateType
> either comes
> in as 'G_DATE' or 'S_DATE'. Xthe columns in the table are also G_DATE
> and S_DATE.
> thanks for your time...- Hide quoted text -
> - Show quoted text -
i finally figured it out...damn, i hate making silly mistakes....
thanks for your input mike...
-fd
|||There is case, But there is also Between
Select * from table where DateColumn Between @.BeginDate and @.enddate
Case should not be used in a where.
Better to create a Table Variable and load from an index of Quailifing
Primary keys.
Then Join on the Table variable.
!0 fold performance increase.
-R
"forest demon" <mete.hanap@.gmail.com> wrote in message
news:588a87ea-7d70-45c8-83dc-ac06df09f331@.e25g2000prg.googlegroups.com...
> I'm trying to build a select statement using the CASE expression. All
> I want is to build the WHERE piece of the select statement based on a
> parameter value.
> I want to somehow CASE the WHERE clause based on a parameter of let's
> say, @.DateType
> SELECT *
> FROM <table>
> WHERE <columnA> >= @.BeginDate AND <columnA> <= @.EndDate
> SELECT *
> FROM <table>
> WHERE <columnB> >= @.BeginDate AND <columnB> <= @.EndDate
> I know there is something simple i'm over looking.
> thanks folks...
> -fd
|||"Randy Pitkin" <RandyPitkin@.ydpages.com> wrote in message
news:eVT$m35gIHA.5088@.TK2MSFTNGP02.phx.gbl...
> There is case, But there is also Between
> Select * from table where DateColumn Between @.BeginDate and @.enddate
> Case should not be used in a where.
> Better to create a Table Variable and load from an index of Quailifing
> Primary keys.
> Then Join on the Table variable.
> !0 fold performance increase.
BETWEEN replaces the >= AND <= but doesn't do anything for the OP's question
of dynamically determining which column to use to limit rows. Whether or
not he needs a performance increase probably depends on how much data he's
querying, etc.

CASE and Inserting Commas

I want to build a CASE statement in SQL to transform data. The SQL is based
on Race. I have the following columns...
RaceAA(American Indian or Alaska Native)
RaceNH(Native Hawaiian)
RaceA(Asian)
RaceW(White)
RaceB(Black)
There are 'X's in these columns designating the Race and more than one may
be 'X'ed.
I want to transform the 'X's into numbers with commas in between. For
Example, if Tiger Woods came into our DataBase, the RaceA would have an 'X'
and needs to be transposed to '2', RaceW would have an 'X' and needs to be
transformed to '5', and RaceB would have an 'X' and needs to be transposed t
o
'3'.
My result should look like...2,5,3
CASE WHEN RaceA = 'X' then '2,'
WHEN RaceW = 'X' then '5,'
WHEN RaceB = 'X' then '3,'
would give me 2,5,3,
My concern here is the trailing ",". Is there any way to CASE the comma if
multiple Race columns are checked and work in the commas appropriately?
Am I asking SQL questions in the right forum?> I have the following columns...
> RaceAA(American Indian or Alaska Native)
> RaceNH(Native Hawaiian)
> RaceA(Asian)
> RaceW(White)
> RaceB(Black)
Why? Why not have a column called "Race" and then provide values to that
column, such as 'AA','NH','A','W','B', etc.

> There are 'X's in these columns designating the Race and more than one may
> be 'X'ed.
Someone can be Asian and Black and White and American Indian?

> I want to transform the 'X's into numbers with commas in between. For
> Example, if Tiger Woods came into our DataBase, the RaceA would have an
> 'X'
> and needs to be transposed to '2', RaceW would have an 'X' and needs to be
> transformed to '5', and RaceB would have an 'X' and needs to be transposed
> to
> '3'.
Why do you want to store this race code mapping in your code? Wouldn't it
make more sense to use a numeric code *in the data* (and have a lookup
table)? Then you don't have to go read stored procedure code to figure out
what the heck kind of person a 3 is, and you don't have to worry about
making sure all queries that use this logic match.

> would give me 2,5,3,
> My concern here is the trailing ",". Is there any way to CASE the comma if
> multiple Race columns are checked and work in the commas appropriately?
Well, you could produce 2,5,3, in a subquery, and in the outer query, say:
SELECT RaceList = CASE RIGHT(List,1)
WHEN ',' THEN LEFT(List, LEN(List)-1)
ELSE List
END
FROM
(
SELECT List = '2,5,3,'
UNION
SELECT List = '4'
) x
Or, you could do something really novel, and inject the commas where they
belong, on the presentation tier.

> Am I asking SQL questions in the right forum?
Yes but you should look at alternative designs. This is far from optimal.|||CASE seems to Mutually Exclusive. Is there any way to continue the CASE
statement so it looks at all the race columns and builds the result column
accordingly. In other words, once it sees that the "White" column has an "X"
,
it goes to the end when I need it to look at ALL the race columns.
Any help you could provide me here would be GREATLY appreciated!
Thanks!
"Aaron Bertrand [SQL Server MVP]" wrote:

> Why? Why not have a column called "Race" and then provide values to that
> column, such as 'AA','NH','A','W','B', etc.
>
> Someone can be Asian and Black and White and American Indian?
>
> Why do you want to store this race code mapping in your code? Wouldn't it
> make more sense to use a numeric code *in the data* (and have a lookup
> table)? Then you don't have to go read stored procedure code to figure ou
t
> what the heck kind of person a 3 is, and you don't have to worry about
> making sure all queries that use this logic match.
>
> Well, you could produce 2,5,3, in a subquery, and in the outer query, say:
> SELECT RaceList = CASE RIGHT(List,1)
> WHEN ',' THEN LEFT(List, LEN(List)-1)
> ELSE List
> END
> FROM
> (
> SELECT List = '2,5,3,'
> UNION
> SELECT List = '4'
> ) x
> Or, you could do something really novel, and inject the commas where they
> belong, on the presentation tier.
>
> Yes but you should look at alternative designs. This is far from optimal.
>
>|||You can string the CASE expressions together:
CASE WHEN X = 1 THEN 'A' ELSE '' END+
CASE WHEN X = 2 THEN 'B' ELSE '' END+
CASE WHEN X = 3 THEN 'C' ELSE '' END+ ...
but I share Aaron's views about the weakness of the design you are
implying. In particular, it's a mistake to design tables to mirror a UI
- columns are not check boxes!
David Portas
SQL Server MVP
--|||or:
CASE WHEN col1 = 1 THEN 'A' ELSE '' END+
CASE WHEN col2 = 1 THEN 'B' ELSE '' END+
CASE WHEN col3 = 1 THEN 'C' ELSE '' END+ ...
David Portas
SQL Server MVP
--

cascade delete can not apply to two foreign keys

I have a table that contains two foreign keys of two different tables. I want to build a relationship so that when either primary keys deleted in the two tables, the record in the table should be deleted. But, SQL Server does not allow me to save the relationship, it complains that the circling delete might exist. I do not know why, how can I solve this?

Table A:
ID
ProductID <foreign key>
CustomerID <foreign key
Table Product
ProductID <primary key
Table Customer
CustomerID <primary key
I want to cascade delete the record in Table A when either the ProductID is deleted from Product table or the CustomerID is deleted from Customer table.In my opinion, relying on cascading updates and deletes isn't a good thing to do anyway. I think it's better to handle the deletes in your code when you explicitly want them to happen. I.e., the procedures to delete from either the product or customer table should include a delete statement against Table A. I don't think cascading deletes are a good idea to replace your own logic.

Capture Returned Value From Exec(@Build) into another variable

I am building a SQL statement that returns a number.
when I execute the Built SQL statment EXEC(@.Build). What I need to do
now is take that number that comes back and store it in another
variable so I can do some conditional logic. Any ideas? See SQL below.

Something like @.Count=Exec(@.Build) which I know doesnt work.

Thanks,
Phil

DECLARE @.PullDate varchar(12)

SET @.PullDate=''+CAST(DATEPART(mm,getdate()-31) AS varchar(2))
+'/'+CAST(DATEPART(dd,getdate()-31)AS varchar(2))
+'/'+CAST(DATEPART(yyyy,getdate()-31) AS varchar(4))+''

PRINT(@.PullDate)

DECLARE @.COUNTER BIGINT

DECLARE @.SELECT VARCHAR(500)
DECLARE @.SELECT2 VARCHAR(1000)
DECLARE @.BUILD VARCHAR(5000)

SET @.SELECT='

SELECT COUNTER FROM
OPENQUERY(PROD,'

SET @.SELECT2='''
SELECT
COUNT(WMB.COLLECTOR_RESULTS.ACCT_NUM) AS COUNTER
FROM
COLLECTOR_RESULTS,
WHERE
WMB.COLLECTOR_RESULTS.ACTIVITY_DATE =
to_date(''+@.PullDate+'',''''mm/dd/yyyy'''')
AND WMB.COLLECT_ACCOUNT.END_DATE ) =
to_date(''''12/31/9999'''',''''mm/dd/yyyy'''')
AND WMB.COLLECT_ACCT_SYS_DATA.END_DATE =
to_date(''+@.PullDate+'',''''mm/dd/yyyy'''')
)
GROUP BY
WMB.COLLECTOR_RESULTS.ACTIVITY_DATE '')'

SET @.BUILD=@.SELECT+@.SELECT2
PRINT(@.BUILD)
EXEC(@.BUILD)

--THIS IS WHERE IM UNSURE I NEED THE COUNT RETURNED FROM @.BUILD STORED
INTO @.COUNTER so I can do a conditional statement.)
if @.COUNTER>=1
begin
print('yes')
end<philipdm@.msn.com> wrote in message
news:1107271847.396749.170840@.z14g2000cwz.googlegr oups.com...
>I am building a SQL statement that returns a number.
> when I execute the Built SQL statment EXEC(@.Build). What I need to do
> now is take that number that comes back and store it in another
> variable so I can do some conditional logic. Any ideas? See SQL below.
> Something like @.Count=Exec(@.Build) which I know doesnt work.
> Thanks,
> Phil
>
>
> DECLARE @.PullDate varchar(12)
> SET @.PullDate=''+CAST(DATEPART(mm,getdate()-31) AS varchar(2))
> +'/'+CAST(DATEPART(dd,getdate()-31)AS varchar(2))
> +'/'+CAST(DATEPART(yyyy,getdate()-31) AS varchar(4))+''
> PRINT(@.PullDate)
> DECLARE @.COUNTER BIGINT
> DECLARE @.SELECT VARCHAR(500)
> DECLARE @.SELECT2 VARCHAR(1000)
> DECLARE @.BUILD VARCHAR(5000)
>
> SET @.SELECT='
> SELECT COUNTER FROM
> OPENQUERY(PROD,'
> SET @.SELECT2='''
> SELECT
> COUNT(WMB.COLLECTOR_RESULTS.ACCT_NUM) AS COUNTER
> FROM
> COLLECTOR_RESULTS,
> WHERE
> WMB.COLLECTOR_RESULTS.ACTIVITY_DATE =
> to_date(''+@.PullDate+'',''''mm/dd/yyyy'''')
> AND WMB.COLLECT_ACCOUNT.END_DATE ) =
> to_date(''''12/31/9999'''',''''mm/dd/yyyy'''')
> AND WMB.COLLECT_ACCT_SYS_DATA.END_DATE =
> to_date(''+@.PullDate+'',''''mm/dd/yyyy'''')
> )
> GROUP BY
> WMB.COLLECTOR_RESULTS.ACTIVITY_DATE '')'
>
> SET @.BUILD=@.SELECT+@.SELECT2
> PRINT(@.BUILD)
> EXEC(@.BUILD)
> --THIS IS WHERE IM UNSURE I NEED THE COUNT RETURNED FROM @.BUILD STORED
> INTO @.COUNTER so I can do a conditional statement.)
> if @.COUNTER>=1
> begin
> print('yes')
> end

Instead of EXEC(), you can use sp_executesql with an output parameter:

declare @.sql ntext, @.counter int
set @.sql = 'select @.counter = counter from openquery(...)'
exec sp_executesql @.sql, N'@.counter int', @.counter OUTPUT
select @.counter

See here for an example:

http://www.sommarskog.se/dynamic_sql.html#sp_executesql

Simon|||I am able to get this to return a value but I cant get this to work.
Any ideas?

IF @.Counter>1
Print('Yes')

Simon Hayes wrote:
> <philipdm@.msn.com> wrote in message
> news:1107271847.396749.170840@.z14g2000cwz.googlegr oups.com...
> >I am building a SQL statement that returns a number.
> > when I execute the Built SQL statment EXEC(@.Build). What I need to
do
> > now is take that number that comes back and store it in another
> > variable so I can do some conditional logic. Any ideas? See SQL
below.
> > Something like @.Count=Exec(@.Build) which I know doesnt work.
> > Thanks,
> > Phil
> > DECLARE @.PullDate varchar(12)
> > SET @.PullDate=''+CAST(DATEPART(mm,getdate()-31) AS varchar(2))
> > +'/'+CAST(DATEPART(dd,getdate()-31)AS varchar(2))
> > +'/'+CAST(DATEPART(yyyy,getdate()-31) AS varchar(4))+''
> > PRINT(@.PullDate)
> > DECLARE @.COUNTER BIGINT
> > DECLARE @.SELECT VARCHAR(500)
> > DECLARE @.SELECT2 VARCHAR(1000)
> > DECLARE @.BUILD VARCHAR(5000)
> > SET @.SELECT='
> > SELECT COUNTER FROM
> > OPENQUERY(PROD,'
> > SET @.SELECT2='''
> > SELECT
> > COUNT(WMB.COLLECTOR_RESULTS.ACCT_NUM) AS COUNTER
> > FROM
> > COLLECTOR_RESULTS,
> > WHERE
> > WMB.COLLECTOR_RESULTS.ACTIVITY_DATE =
> > to_date(''+@.PullDate+'',''''mm/dd/yyyy'''')
> > AND WMB.COLLECT_ACCOUNT.END_DATE ) =
> > to_date(''''12/31/9999'''',''''mm/dd/yyyy'''')
> > AND WMB.COLLECT_ACCT_SYS_DATA.END_DATE =
> > to_date(''+@.PullDate+'',''''mm/dd/yyyy'''')
> > )
> > GROUP BY
> > WMB.COLLECTOR_RESULTS.ACTIVITY_DATE '')'
> > SET @.BUILD=@.SELECT+@.SELECT2
> > PRINT(@.BUILD)
> > EXEC(@.BUILD)
> > --THIS IS WHERE IM UNSURE I NEED THE COUNT RETURNED FROM @.BUILD
STORED
> > INTO @.COUNTER so I can do a conditional statement.)
> > if @.COUNTER>=1
> > begin
> > print('yes')
> > end
> Instead of EXEC(), you can use sp_executesql with an output
parameter:
> declare @.sql ntext, @.counter int
> set @.sql = 'select @.counter = counter from openquery(...)'
> exec sp_executesql @.sql, N'@.counter int', @.counter OUTPUT
> select @.counter
> See here for an example:
> http://www.sommarskog.se/dynamic_sql.html#sp_executesql
> Simon|||Never mind I figured it out. I just needed to set a variable =to
outputvariable that can be used in the rest of the code for the
conditional statement.
Thanks a bunch Simon!|||Never mind I figured it out. All I need to do is set a
@.variable=@.Output Variable.
Thanks for your help Simon!
Phil|||Never mind I figured it out. I just needed to set a variable =to
outputvariable that can be used in the rest of the code for the
conditional statement.
Thanks a bunch Simon!|||Never mind I figured it out. I just needed to set a variable =to
outputvariable that can be used in the rest of the code for the
conditional statement.
Thanks a bunch Simon!

Capacity of SQL Server 2005 Express Edition

I am just starting to build my apps in asp.net 2.0. I have build apss in v. 1.x before using sql server 2000. However, before i make the shift, I would like to know the advantages and disadvantages of using the sql server 2005 express edition then using the sql server 2000.

for example;
+ How many data can it store?
+ How many concurrent users it supports.
+ Advantages and disadvantages of using sql server 2005 express ed.
+ Any other relevant information that developers should know / beware / watch out for when using sql server 2005 express edition.

Note: I am not asking about the actual SQL Server 2005 but the express edition!

Regards and thanks in advance

Microsoft'sSQL Server home page is a good place to start your research. TheEditions link will bring you the theSQL Server 2005 Features Comparison table which will answer most of your questions.