EXEC of a sproc within another sproc

I'm sorta new with using stored procedures and I'm at a loss of how to achieve my desired result.

What I am trying to do is retrieve a value from a table before it is updated and then use this original value to update another table. If I execute the first called sproc in query analyzer it does return the value I'm looking for, but I'm not really sure how to capture the returned value. Also, is there a more direct way to do this?

Thanks,
Peggy

Sproc that is called from ASP.NET:

ALTER PROCEDURE BP_UpdateLedgerEntry
(
@.EntryLogID int,
@.ProjectID int,
@.NewCategoryID int,
@.Expended decimal(10,2)
)
AS
DECLARE@.OldCategoryID int

EXEC @.OldCategoryID = BP_GetLedgerCategory @.EntryLogID

UPDATE
BP_EntryLog
SET
ProjectID = @.ProjectID,
CategoryID = @.NewCategoryID,
Expended = @.Expended

WHERE
EntryLogID = @.EntryLogID

EXEC BP_UpdateCategories @.ProjectID, @.NewCategoryID, @.Expended, @.OldCategoryID

Called Sprocs:

*********************************************
BP_GetLedgerCategory
*********************************************
ALTER PROCEDURE BP_GetLedgerCategory
(
@.EntryLogID int
)
AS

SELECT CategoryID
FROM BP_EntryLog
WHERE EntryLogID = @.EntryLogID

RETURN

*********************************************
BP_UpdateCategories
*********************************************
ALTER PROCEDURE BP_UpdateCategories
(
@.ProjectID int,
@.NewCategoryID int,
@.Expended decimal(10,2),
@.OldCategoryID int
)
AS

UPDATE
BP_Categories
SET CatExpended = CatExpended + @.Expended
WHERE
ProjectID = @.ProjectID
AND
CategoryID = @.NewCategoryID

UPDATE
BP_Categories
SET CatExpended = CatExpended - @.Expended
WHERE
ProjectID = @.ProjectID
AND
CategoryID = @.OldCategoryIDyou need to use OUTPUT parameters to return a value..check BOL (Books On Line ) its a free download from microsoft...check for correct synax and documentation...

hth|||Thank you...though...I found more info later last night after I came across the phrase 'OUTPUT' parameters.

This thread in particular ...
http://www.asp.net/Forums/ShowPost.aspx?tabindex=1&PostID=399995

My sproc is finished and I've moved on...

exec in a function

I am creating a dynamic query and using exec to execute it inside of a function. This query will return only one value. How can I get the value the query returns into a variable?
Functions can not call stored procedures, and they can not use temporary tables.
Thanks muchI don't think you can do Dynamic SQL in a user defined fuction.

Tim S

Exclusive OR condition needed for Check Constraint

I want to put a check constraint on a table to enforce the following
condition:
Of four columns A, B, C, D one must have a value and the other three must be
null.
A is a varchar column, the others are integer.
It seemed like I needed to create a bitwise Exclusive OR expression, with
some function to return true/false for each column having a value/Null.
The best I could do was this:
( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
This stops inserts when 0, 2 or 4 of the columns have values, and allows
inserts when 1 column has a value (which is correct). However it also allows
inserts when 3 of the columns have values. I have no idea why. Can anyone
fix the expression, or give me an alternative expression that fits the
requirements?
ThanksTry this:
... CHECK (
CASE WHEN A IS NOT NULL THEN 1 ELSE 0 END+
CASE WHEN B IS NOT NULL THEN 1 ELSE 0 END+
CASE WHEN C IS NOT NULL THEN 1 ELSE 0 END+
CASE WHEN D IS NOT NULL THEN 1 ELSE 0 END=1) ...
You could also consider changing your design. Perhaps you only need one
column.
David Portas
SQL Server MVP
--|||Laurence,
Perhaps not the shortest nor efficient but this seems to work:
ALTER TABLE <TABLE> ADD CONSTRAINT <CONSTRAINTNAME> CHECK ((A IS NOT NULL
AND B IS NULL AND C IS NULL AND D IS NULL)OR (A IS NULL AND B IS NOT NULL
AND C IS NULL AND D IS NULL) OR (A IS NULL AND B IS NULL AND C IS NOT NULL
AND D IS NULL) OR (A IS NULL AND B IS NULL AND C IS NULL AND D IS NOT NULL))
HTH
Jerry
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:%23%233KVUY2FHA.3592@.TK2MSFTNGP12.phx.gbl...
>I want to put a check constraint on a table to enforce the following
> condition:
> Of four columns A, B, C, D one must have a value and the other three must
> be
> null.
> A is a varchar column, the others are integer.
> It seemed like I needed to create a bitwise Exclusive OR expression, with
> some function to return true/false for each column having a value/Null.
> The best I could do was this:
> ( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
> This stops inserts when 0, 2 or 4 of the columns have values, and allows
> inserts when 1 column has a value (which is correct). However it also
> allows
> inserts when 3 of the columns have values. I have no idea why. Can anyone
> fix the expression, or give me an alternative expression that fits the
> requirements?
> Thanks
>|||Yet another solution:
CHECK ( COALESCE(A, CAST( COALESCE(B,C,D) AS varchar(..)) ) = COALESCE(
CAST( COALESCE(D,C,B) AS varchar(..)), A) )
This solution approach would be cleaner if A had the same data type as
B, C and D. Then it would simply be:
CHECK ( COALESCE(A,B,C,D) = COALESCE(D,C,B,A) )
And I have to agree with David. If you need this check, then your data
model might not be properly normalized. In that case you might want to
review your design.
HTH,
Gert-Jan
Laurence Neville wrote:
> I want to put a check constraint on a table to enforce the following
> condition:
> Of four columns A, B, C, D one must have a value and the other three must
be
> null.
> A is a varchar column, the others are integer.
> It seemed like I needed to create a bitwise Exclusive OR expression, with
> some function to return true/false for each column having a value/Null.
> The best I could do was this:
> ( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
> This stops inserts when 0, 2 or 4 of the columns have values, and allows
> inserts when 1 column has a value (which is correct). However it also allo
ws
> inserts when 3 of the columns have values. I have no idea why. Can anyone
> fix the expression, or give me an alternative expression that fits the
> requirements?
> Thanks|||Ah, forget this solution, it is no good :-(
Gert-Jan
Gert-Jan Strik wrote:
> Yet another solution:
> CHECK ( COALESCE(A, CAST( COALESCE(B,C,D) AS varchar(..)) ) = COALESCE(
> CAST( COALESCE(D,C,B) AS varchar(..)), A) )
> This solution approach would be cleaner if A had the same data type as
> B, C and D. Then it would simply be:
> CHECK ( COALESCE(A,B,C,D) = COALESCE(D,C,B,A) )
> And I have to agree with David. If you need this check, then your data
> model might not be properly normalized. In that case you might want to
> review your design.
> HTH,
> Gert-Jan
> Laurence Neville wrote:|||I went with David's solution because it is the easiest to interpret.
Mikito's solution also worked.
I know the table design is unusual and could be normalized. It is
deliberately this way to make certain queries perform faster (less joins to
make).
Thanks for so many quick replies!
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1130260200.705449.86860@.g49g2000cwa.googlegroups.com...
> Try this:
> ... CHECK (
> CASE WHEN A IS NOT NULL THEN 1 ELSE 0 END+
> CASE WHEN B IS NOT NULL THEN 1 ELSE 0 END+
> CASE WHEN C IS NOT NULL THEN 1 ELSE 0 END+
> CASE WHEN D IS NOT NULL THEN 1 ELSE 0 END=1) ...
> You could also consider changing your design. Perhaps you only need one
> column.
> --
> David Portas
> SQL Server MVP
> --
>|||Laurence Neville wrote:
> I want to put a check constraint on a table to enforce the following
> condition:
> Of four columns A, B, C, D one must have a value and the other three must
be
> null.
> A is a varchar column, the others are integer.
> It seemed like I needed to create a bitwise Exclusive OR expression, with
> some function to return true/false for each column having a value/Null.
> The best I could do was this:
> ( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
> This stops inserts when 0, 2 or 4 of the columns have values, and allows
> inserts when 1 column has a value (which is correct). However it also allo
ws
> inserts when 3 of the columns have values. I have no idea why. Can anyone
> fix the expression, or give me an alternative expression that fits the
> requirements?
isnumeric(len(A)) + isnumeric(B) + isnumeric(C) + isnumeric(D) = 1 ?

Exclusive OR condition needed for Check Constraint

I want to put a check constraint on a table to enforce the following
condition:
Of four columns A, B, C, D one must have a value and the other three must be
null.
A is a varchar column, the others are integer.
It seemed like I needed to create a bitwise Exclusive OR expression, with
some function to return true/false for each column having a value/Null.
The best I could do was this:
( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
This stops inserts when 0, 2 or 4 of the columns have values, and allows
inserts when 1 column has a value (which is correct). However it also allows
inserts when 3 of the columns have values. I have no idea why. Can anyone
fix the expression, or give me an alternative expression that fits the
requirements?
ThanksLaurence Neville wrote:
> I want to put a check constraint on a table to enforce the following
> condition:
> Of four columns A, B, C, D one must have a value and the other three must
be
> null.
> A is a varchar column, the others are integer.
> It seemed like I needed to create a bitwise Exclusive OR expression, with
> some function to return true/false for each column having a value/Null.
> The best I could do was this:
> ( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
> This stops inserts when 0, 2 or 4 of the columns have values, and allows
> inserts when 1 column has a value (which is correct). However it also allo
ws
> inserts when 3 of the columns have values. I have no idea why. Can anyone
> fix the expression, or give me an alternative expression that fits the
> requirements?
isnumeric(len(A)) + isnumeric(B) + isnumeric(C) + isnumeric(D) = 1 ?|||Try this:
... CHECK (
CASE WHEN A IS NOT NULL THEN 1 ELSE 0 END+
CASE WHEN B IS NOT NULL THEN 1 ELSE 0 END+
CASE WHEN C IS NOT NULL THEN 1 ELSE 0 END+
CASE WHEN D IS NOT NULL THEN 1 ELSE 0 END=1) ...
You could also consider changing your design. Perhaps you only need one
column.
David Portas
SQL Server MVP
--|||Laurence,
Perhaps not the shortest nor efficient but this seems to work:
ALTER TABLE <TABLE> ADD CONSTRAINT <CONSTRAINTNAME> CHECK ((A IS NOT NULL
AND B IS NULL AND C IS NULL AND D IS NULL)OR (A IS NULL AND B IS NOT NULL
AND C IS NULL AND D IS NULL) OR (A IS NULL AND B IS NULL AND C IS NOT NULL
AND D IS NULL) OR (A IS NULL AND B IS NULL AND C IS NULL AND D IS NOT NULL))
HTH
Jerry
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:%23%233KVUY2FHA.3592@.TK2MSFTNGP12.phx.gbl...
>I want to put a check constraint on a table to enforce the following
> condition:
> Of four columns A, B, C, D one must have a value and the other three must
> be
> null.
> A is a varchar column, the others are integer.
> It seemed like I needed to create a bitwise Exclusive OR expression, with
> some function to return true/false for each column having a value/Null.
> The best I could do was this:
> ( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
> This stops inserts when 0, 2 or 4 of the columns have values, and allows
> inserts when 1 column has a value (which is correct). However it also
> allows
> inserts when 3 of the columns have values. I have no idea why. Can anyone
> fix the expression, or give me an alternative expression that fits the
> requirements?
> Thanks
>|||Yet another solution:
CHECK ( COALESCE(A, CAST( COALESCE(B,C,D) AS varchar(..)) ) = COALESCE(
CAST( COALESCE(D,C,B) AS varchar(..)), A) )
This solution approach would be cleaner if A had the same data type as
B, C and D. Then it would simply be:
CHECK ( COALESCE(A,B,C,D) = COALESCE(D,C,B,A) )
And I have to agree with David. If you need this check, then your data
model might not be properly normalized. In that case you might want to
review your design.
HTH,
Gert-Jan
Laurence Neville wrote:
> I want to put a check constraint on a table to enforce the following
> condition:
> Of four columns A, B, C, D one must have a value and the other three must
be
> null.
> A is a varchar column, the others are integer.
> It seemed like I needed to create a bitwise Exclusive OR expression, with
> some function to return true/false for each column having a value/Null.
> The best I could do was this:
> ( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
> This stops inserts when 0, 2 or 4 of the columns have values, and allows
> inserts when 1 column has a value (which is correct). However it also allo
ws
> inserts when 3 of the columns have values. I have no idea why. Can anyone
> fix the expression, or give me an alternative expression that fits the
> requirements?
> Thanks|||Ah, forget this solution, it is no good :-(
Gert-Jan
Gert-Jan Strik wrote:[vbcol=seagreen]
> Yet another solution:
> CHECK ( COALESCE(A, CAST( COALESCE(B,C,D) AS varchar(..)) ) = COALESCE(
> CAST( COALESCE(D,C,B) AS varchar(..)), A) )
> This solution approach would be cleaner if A had the same data type as
> B, C and D. Then it would simply be:
> CHECK ( COALESCE(A,B,C,D) = COALESCE(D,C,B,A) )
> And I have to agree with David. If you need this check, then your data
> model might not be properly normalized. In that case you might want to
> review your design.
> HTH,
> Gert-Jan
> Laurence Neville wrote:|||I went with David's solution because it is the easiest to interpret.
Mikito's solution also worked.
I know the table design is unusual and could be normalized. It is
deliberately this way to make certain queries perform faster (less joins to
make).
Thanks for so many quick replies!
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1130260200.705449.86860@.g49g2000cwa.googlegroups.com...
> Try this:
> ... CHECK (
> CASE WHEN A IS NOT NULL THEN 1 ELSE 0 END+
> CASE WHEN B IS NOT NULL THEN 1 ELSE 0 END+
> CASE WHEN C IS NOT NULL THEN 1 ELSE 0 END+
> CASE WHEN D IS NOT NULL THEN 1 ELSE 0 END=1) ...
> You could also consider changing your design. Perhaps you only need one
> column.
> --
> David Portas
> SQL Server MVP
> --
>sql

Exclusive OR condition needed for Check Constraint

I want to put a check constraint on a table to enforce the following
condition:
Of four columns A, B, C, D one must have a value and the other three must be
null.
A is a varchar column, the others are integer.
It seemed like I needed to create a bitwise Exclusive OR expression, with
some function to return true/false for each column having a value/Null.
The best I could do was this:
( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
This stops inserts when 0, 2 or 4 of the columns have values, and allows
inserts when 1 column has a value (which is correct). However it also allows
inserts when 3 of the columns have values. I have no idea why. Can anyone
fix the expression, or give me an alternative expression that fits the
requirements?
ThanksLaurence Neville wrote:
> I want to put a check constraint on a table to enforce the following
> condition:
> Of four columns A, B, C, D one must have a value and the other three must be
> null.
> A is a varchar column, the others are integer.
> It seemed like I needed to create a bitwise Exclusive OR expression, with
> some function to return true/false for each column having a value/Null.
> The best I could do was this:
> ( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
> This stops inserts when 0, 2 or 4 of the columns have values, and allows
> inserts when 1 column has a value (which is correct). However it also allows
> inserts when 3 of the columns have values. I have no idea why. Can anyone
> fix the expression, or give me an alternative expression that fits the
> requirements?
isnumeric(len(A)) + isnumeric(B) + isnumeric(C) + isnumeric(D) = 1 ?|||Try this:
... CHECK (
CASE WHEN A IS NOT NULL THEN 1 ELSE 0 END+
CASE WHEN B IS NOT NULL THEN 1 ELSE 0 END+
CASE WHEN C IS NOT NULL THEN 1 ELSE 0 END+
CASE WHEN D IS NOT NULL THEN 1 ELSE 0 END=1) ...
You could also consider changing your design. Perhaps you only need one
column.
--
David Portas
SQL Server MVP
--|||Laurence,
Perhaps not the shortest nor efficient but this seems to work:
ALTER TABLE <TABLE> ADD CONSTRAINT <CONSTRAINTNAME> CHECK ((A IS NOT NULL
AND B IS NULL AND C IS NULL AND D IS NULL)OR (A IS NULL AND B IS NOT NULL
AND C IS NULL AND D IS NULL) OR (A IS NULL AND B IS NULL AND C IS NOT NULL
AND D IS NULL) OR (A IS NULL AND B IS NULL AND C IS NULL AND D IS NOT NULL))
HTH
Jerry
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:%23%233KVUY2FHA.3592@.TK2MSFTNGP12.phx.gbl...
>I want to put a check constraint on a table to enforce the following
> condition:
> Of four columns A, B, C, D one must have a value and the other three must
> be
> null.
> A is a varchar column, the others are integer.
> It seemed like I needed to create a bitwise Exclusive OR expression, with
> some function to return true/false for each column having a value/Null.
> The best I could do was this:
> ( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
> This stops inserts when 0, 2 or 4 of the columns have values, and allows
> inserts when 1 column has a value (which is correct). However it also
> allows
> inserts when 3 of the columns have values. I have no idea why. Can anyone
> fix the expression, or give me an alternative expression that fits the
> requirements?
> Thanks
>|||Yet another solution:
CHECK ( COALESCE(A, CAST( COALESCE(B,C,D) AS varchar(..)) ) = COALESCE(
CAST( COALESCE(D,C,B) AS varchar(..)), A) )
This solution approach would be cleaner if A had the same data type as
B, C and D. Then it would simply be:
CHECK ( COALESCE(A,B,C,D) = COALESCE(D,C,B,A) )
And I have to agree with David. If you need this check, then your data
model might not be properly normalized. In that case you might want to
review your design.
HTH,
Gert-Jan
Laurence Neville wrote:
> I want to put a check constraint on a table to enforce the following
> condition:
> Of four columns A, B, C, D one must have a value and the other three must be
> null.
> A is a varchar column, the others are integer.
> It seemed like I needed to create a bitwise Exclusive OR expression, with
> some function to return true/false for each column having a value/Null.
> The best I could do was this:
> ( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
> This stops inserts when 0, 2 or 4 of the columns have values, and allows
> inserts when 1 column has a value (which is correct). However it also allows
> inserts when 3 of the columns have values. I have no idea why. Can anyone
> fix the expression, or give me an alternative expression that fits the
> requirements?
> Thanks|||Ah, forget this solution, it is no good :-(
Gert-Jan
Gert-Jan Strik wrote:
> Yet another solution:
> CHECK ( COALESCE(A, CAST( COALESCE(B,C,D) AS varchar(..)) ) = COALESCE(
> CAST( COALESCE(D,C,B) AS varchar(..)), A) )
> This solution approach would be cleaner if A had the same data type as
> B, C and D. Then it would simply be:
> CHECK ( COALESCE(A,B,C,D) = COALESCE(D,C,B,A) )
> And I have to agree with David. If you need this check, then your data
> model might not be properly normalized. In that case you might want to
> review your design.
> HTH,
> Gert-Jan
> Laurence Neville wrote:
> >
> > I want to put a check constraint on a table to enforce the following
> > condition:
> >
> > Of four columns A, B, C, D one must have a value and the other three must be
> > null.
> >
> > A is a varchar column, the others are integer.
> >
> > It seemed like I needed to create a bitwise Exclusive OR expression, with
> > some function to return true/false for each column having a value/Null.
> >
> > The best I could do was this:
> >
> > ( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
> >
> > This stops inserts when 0, 2 or 4 of the columns have values, and allows
> > inserts when 1 column has a value (which is correct). However it also allows
> > inserts when 3 of the columns have values. I have no idea why. Can anyone
> > fix the expression, or give me an alternative expression that fits the
> > requirements?
> >
> > Thanks|||I went with David's solution because it is the easiest to interpret.
Mikito's solution also worked.
I know the table design is unusual and could be normalized. It is
deliberately this way to make certain queries perform faster (less joins to
make).
Thanks for so many quick replies!
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1130260200.705449.86860@.g49g2000cwa.googlegroups.com...
> Try this:
> ... CHECK (
> CASE WHEN A IS NOT NULL THEN 1 ELSE 0 END+
> CASE WHEN B IS NOT NULL THEN 1 ELSE 0 END+
> CASE WHEN C IS NOT NULL THEN 1 ELSE 0 END+
> CASE WHEN D IS NOT NULL THEN 1 ELSE 0 END=1) ...
> You could also consider changing your design. Perhaps you only need one
> column.
> --
> David Portas
> SQL Server MVP
> --
>

Exclusive OR condition needed for Check Constraint

I want to put a check constraint on a table to enforce the following
condition:
Of four columns A, B, C, D one must have a value and the other three must be
null.
A is a varchar column, the others are integer.
It seemed like I needed to create a bitwise Exclusive OR expression, with
some function to return true/false for each column having a value/Null.
The best I could do was this:
( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
This stops inserts when 0, 2 or 4 of the columns have values, and allows
inserts when 1 column has a value (which is correct). However it also allows
inserts when 3 of the columns have values. I have no idea why. Can anyone
fix the expression, or give me an alternative expression that fits the
requirements?
Thanks
Laurence Neville wrote:
> I want to put a check constraint on a table to enforce the following
> condition:
> Of four columns A, B, C, D one must have a value and the other three must be
> null.
> A is a varchar column, the others are integer.
> It seemed like I needed to create a bitwise Exclusive OR expression, with
> some function to return true/false for each column having a value/Null.
> The best I could do was this:
> ( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
> This stops inserts when 0, 2 or 4 of the columns have values, and allows
> inserts when 1 column has a value (which is correct). However it also allows
> inserts when 3 of the columns have values. I have no idea why. Can anyone
> fix the expression, or give me an alternative expression that fits the
> requirements?
isnumeric(len(A)) + isnumeric(B) + isnumeric(C) + isnumeric(D) = 1 ?
|||Try this:
... CHECK (
CASE WHEN A IS NOT NULL THEN 1 ELSE 0 END+
CASE WHEN B IS NOT NULL THEN 1 ELSE 0 END+
CASE WHEN C IS NOT NULL THEN 1 ELSE 0 END+
CASE WHEN D IS NOT NULL THEN 1 ELSE 0 END=1) ...
You could also consider changing your design. Perhaps you only need one
column.
David Portas
SQL Server MVP
|||Laurence,
Perhaps not the shortest nor efficient but this seems to work:
ALTER TABLE <TABLE> ADD CONSTRAINT <CONSTRAINTNAME> CHECK ((A IS NOT NULL
AND B IS NULL AND C IS NULL AND D IS NULL)OR (A IS NULL AND B IS NOT NULL
AND C IS NULL AND D IS NULL) OR (A IS NULL AND B IS NULL AND C IS NOT NULL
AND D IS NULL) OR (A IS NULL AND B IS NULL AND C IS NULL AND D IS NOT NULL))
HTH
Jerry
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:%23%233KVUY2FHA.3592@.TK2MSFTNGP12.phx.gbl...
>I want to put a check constraint on a table to enforce the following
> condition:
> Of four columns A, B, C, D one must have a value and the other three must
> be
> null.
> A is a varchar column, the others are integer.
> It seemed like I needed to create a bitwise Exclusive OR expression, with
> some function to return true/false for each column having a value/Null.
> The best I could do was this:
> ( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
> This stops inserts when 0, 2 or 4 of the columns have values, and allows
> inserts when 1 column has a value (which is correct). However it also
> allows
> inserts when 3 of the columns have values. I have no idea why. Can anyone
> fix the expression, or give me an alternative expression that fits the
> requirements?
> Thanks
>
|||Yet another solution:
CHECK ( COALESCE(A, CAST( COALESCE(B,C,D) AS varchar(..)) ) = COALESCE(
CAST( COALESCE(D,C,B) AS varchar(..)), A) )
This solution approach would be cleaner if A had the same data type as
B, C and D. Then it would simply be:
CHECK ( COALESCE(A,B,C,D) = COALESCE(D,C,B,A) )
And I have to agree with David. If you need this check, then your data
model might not be properly normalized. In that case you might want to
review your design.
HTH,
Gert-Jan
Laurence Neville wrote:
> I want to put a check constraint on a table to enforce the following
> condition:
> Of four columns A, B, C, D one must have a value and the other three must be
> null.
> A is a varchar column, the others are integer.
> It seemed like I needed to create a bitwise Exclusive OR expression, with
> some function to return true/false for each column having a value/Null.
> The best I could do was this:
> ( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
> This stops inserts when 0, 2 or 4 of the columns have values, and allows
> inserts when 1 column has a value (which is correct). However it also allows
> inserts when 3 of the columns have values. I have no idea why. Can anyone
> fix the expression, or give me an alternative expression that fits the
> requirements?
> Thanks
|||Ah, forget this solution, it is no good :-(
Gert-Jan
Gert-Jan Strik wrote:[vbcol=seagreen]
> Yet another solution:
> CHECK ( COALESCE(A, CAST( COALESCE(B,C,D) AS varchar(..)) ) = COALESCE(
> CAST( COALESCE(D,C,B) AS varchar(..)), A) )
> This solution approach would be cleaner if A had the same data type as
> B, C and D. Then it would simply be:
> CHECK ( COALESCE(A,B,C,D) = COALESCE(D,C,B,A) )
> And I have to agree with David. If you need this check, then your data
> model might not be properly normalized. In that case you might want to
> review your design.
> HTH,
> Gert-Jan
> Laurence Neville wrote:
|||I went with David's solution because it is the easiest to interpret.
Mikito's solution also worked.
I know the table design is unusual and could be normalized. It is
deliberately this way to make certain queries perform faster (less joins to
make).
Thanks for so many quick replies!
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1130260200.705449.86860@.g49g2000cwa.googlegro ups.com...
> Try this:
> ... CHECK (
> CASE WHEN A IS NOT NULL THEN 1 ELSE 0 END+
> CASE WHEN B IS NOT NULL THEN 1 ELSE 0 END+
> CASE WHEN C IS NOT NULL THEN 1 ELSE 0 END+
> CASE WHEN D IS NOT NULL THEN 1 ELSE 0 END=1) ...
> You could also consider changing your design. Perhaps you only need one
> column.
> --
> David Portas
> SQL Server MVP
> --
>

Exchanging a "?" with NULL value

Hi SQL Champs!

I have a text file source where some data are an question mark (?). Importing this to SQL serever, I want to exchange these (?) with an NULL -value.

How do I do this most easy? Do I need a another tmptable first?

Many thaks

kurlan

While importing this you can use the replace function to replace these values on the fly.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||

Rule!

Thanks

Exception Handling

Hello,

I am trying to catch an error, but not able to. I am storing the value of @.@.Error soon after the statement which I believe would generate error. In order to produce the error I have deliberately used a wrong table name. So, the statement breaks but never comes to my error handling code snippet. Instead, it just throws the SQL server error message and quits. What am I doing wrong here? Here is the code snippet. The actual name of the temporary table is #TEMPO_TABLE, but in order to generate the error I have used #TEMPO_. Since this would surely error out, it should go to the label ROLLITBACK. But is not going to that label. Neither is it printing the error number as per the PRINT statement there. It just throws the SQL error and quits the execution. (Or should it NOT?). The Error that it throws it this:

Server: Msg 208, Level 16, State 1, Procedure FEED_PULL_XX, Line 157
Invalid object name '#TEMPO_'.

Can you please let me know whats going wrong here? Thanks a lot in advance.

INSERT INTO Table1
(
[ITAM_ASSET_EUP_BUS_LINE],
[ITAM_MACHINE_STATUS],
[ITAM_OWNER_ID],
[ITAM_ASSET_ADMIN],
[ITAM_MODEL],
[ITAM_ASSET_NAME],
[ITAMMACHINE_PURPOSE]
)
SELECT
[ITAM_ASSET_EUP_BUS_LINE],
[ITAM_MACHINE_STATUS],
[ITAM_OWNER_ID],
[ITAM_ASSET_ADMIN],
[ITAM_MODEL],
[ITAM_ASSET_NAME],
[ITAMMACHINE_PURPOSE]
FROM
#TEMPO_

SET @.ErrNo = @.@.Error

PRINT '@.ErrNo is ' + ltrim(str(@.ErrNo)) + '.'

IF @.ErrNo <> 0
Begin
PRINT '@.ErrNo is ' + ltrim(str(@.ErrNo)) + '.'
GOTO ROLLITBACK
End

Hi Mannubhai,

Which version of SQL server you are using.. if you are using SQL05, try using Try..catch block.

If you are using SQL2k.. then in above script there is no variable declared and there is no rollitback lable..

And YOU SHOULD GET THE ERROR SAYING THAT THE TABLE DOES NOT EXISTS.. AND THIS ERROR HAS THE SEVERITY LEVEL OF 16.. which says that the error can be fixed by the user and terminates the batch.. so you are not able to catch it.

Have a look on the severity levels for more details.

Regards,

|||

There are errors that cannot be caught.

Erland has written up some good articles on the subject. Please take a look.

http://www.sommarskog.se/error-handling-I.html

http://www.sommarskog.se/error-handling-II.html

|||

Hey Thanks Sajid. I am using SQL 2000. The Code that I have given is just a snippet. It is not the complete one. The Complete SP has the variables declared and yes, the ROLLITBACK label is also present. The error as I said is, INVALID OBJECT #TEMPO_. But can this error NOT be caught in the way I have attempted? I think it does not even execute this statement --> SET @.ErrNo = @.@.Error. Why does this happen? Please let me know. Appreciate the response.

Mannu.

|||

You can refer to Erland's documents on Error handline.. spcially the first link provided by OJ. .that will surely help you out to understand the Error handling in details..

Regards,

|||

Hi All,

I seem to have solved this. The below insert which I was using, I put that into a variable and executed it using sp_executesql. A Dynamic SQL.

INSERT INTO Table1
(
[ITAM_ASSET_EUP_BUS_LINE],
[ITAM_MACHINE_STATUS],
[ITAM_OWNER_ID],
[ITAM_ASSET_ADMIN],
[ITAM_MODEL],
[ITAM_ASSET_NAME],
[ITAMMACHINE_PURPOSE]
)
SELECT
[ITAM_ASSET_EUP_BUS_LINE],
[ITAM_MACHINE_STATUS],
[ITAM_OWNER_ID],
[ITAM_ASSET_ADMIN],
[ITAM_MODEL],
[ITAM_ASSET_NAME],
[ITAMMACHINE_PURPOSE]
FROM
#TEMPO_

I put this like this:

SET @.SQL = '

INSERT INTO Table1
(
[ITAM_ASSET_EUP_BUS_LINE],
[ITAM_MACHINE_STATUS],
[ITAM_OWNER_ID],
[ITAM_ASSET_ADMIN],
[ITAM_MODEL],
[ITAM_ASSET_NAME],
[ITAMMACHINE_PURPOSE]
)
SELECT
[ITAM_ASSET_EUP_BUS_LINE],
[ITAM_MACHINE_STATUS],
[ITAM_OWNER_ID],
[ITAM_ASSET_ADMIN],
[ITAM_MODEL],
[ITAM_ASSET_NAME],
[ITAMMACHINE_PURPOSE]
FROM
#TEMPO_'

EXEC sp_Executesql @.SQL

SET @.ErrNo = @.@.Error.

IF ErrNo <> 0

....

....

This gave the proper results. Let me know if more information is required.

Mannu.

|||

I partially agree with your solution.

But there is few risks you might face if you given the access permissions on SP level (readonly at Table)..

The alternate possible solution might be,

Option 1#:

Code Snippet

If Exists (Select * from tempdb..sysobjects where id=object_id('tempdb..#tempo_'))

Insert into Table1

(

[itam_asset_eup_bus_line],

[itam_machine_status],

[itam_owner_id],

[itam_asset_admin],

[itam_model],

[itam_asset_name],

[itammachine_purpose]

)

Select

[itam_asset_eup_bus_line],

[itam_machine_status],

[itam_owner_id],

[itam_asset_admin],

[itam_model],

[itam_asset_name],

[itammachine_purpose]

From

#tempo_

Else

Goto RollITBack

SET @.ErrNo = @.@.Error

PRINT '@.ErrNo is ' + ltrim(str(@.ErrNo)) + '.'

IF @.ErrNo <> 0

Begin

PRINT '@.ErrNo is ' + ltrim(str(@.ErrNo)) + '.'

Goto RollItback

End

Option 2#:

Wrapp the insert statement in new proc & call it from your main proc.

Code Snippet

Create Proc InsertFromTemp

as

Insert into Table1

(

[itam_asset_eup_bus_line],

[itam_machine_status],

[itam_owner_id],

[itam_asset_admin],

[itam_model],

[itam_asset_name],

[itammachine_purpose]

)

Select

[itam_asset_eup_bus_line],

[itam_machine_status],

[itam_owner_id],

[itam_asset_admin],

[itam_model],

[itam_asset_name],

[itammachine_purpose]

From

#tempo_

Go

Exec InsertFromTemp

SET @.ErrNo = @.@.Error

PRINT '@.ErrNo is ' + ltrim(str(@.ErrNo)) + '.'

IF @.ErrNo <> 0

Begin

PRINT '@.ErrNo is ' + ltrim(str(@.ErrNo)) + '.'

Goto RollItback

End

Excel to MS SQL

Hai all,
I want to export the data from Excel sheet to SQL database. In the Excel sheet one column contains the date value and non date like 0 and blank. I want to transfer this to my table by changing the format to dd/mm/yyyy.

If I open and format the column to date, and if I export then in the table i am getting different formats ( based on the client machines Date format , The column in the table is Varchar becaust the Excel sheet column will contain date and other non date like 0 , blank etc )

Now how can I export the Excel column that contains date , 0 and blanks to the table in the database with date format dd/mm/yyyy and null for non date values?

Thanks
NarayanaswamyYou should import your data as VARCHAR in a temporary table, and transform it in a second step.|||Hai ,
I tried that also, but the problem is
If In the excel sheet first field is a text or 0 then All thetext values are transfered to the Table but the date fields are transfered as NULL.
If the first row in the excel is Date then All the date are transferred properly and the text and 0 are transfered as NULL.

But the date transfed is of various type ( as per the client machine Date format how can i convert it to same format?)

Can you help me in solving this problem?

Thanks

Narayanaswamy|||Yeah, Excel is great, but not for storing data.

I would take another approach, and would include some VBA code into your workbooks to transform your sheets first into the proper format before exporting them. Your could also consider to export them by VBA.|||Originally posted by Narayanaswamy
Hai ,
I tried that also, but the problem is
If In the excel sheet first field is a text or 0 then All thetext values are transfered to the Table but the date fields are transfered as NULL.
If the first row in the excel is Date then All the date are transferred properly and the text and 0 are transfered as NULL.

But the date transfed is of various type ( as per the client machine Date format how can i convert it to same format?)

Can you help me in solving this problem?

Thanks

Narayanaswamy

I did have the same problem with importing excel data to MSSQL.
After some fighting I just imported data to Access and then to MSSQL.
It was needed to do not often. :)

Excel Pivot Table

When I use Excel 2002 Sp3 to connect to the AS2000. I can select multiple value in the Page field. But when I install the OLAP AS 9.0 provider to connect to the AS2005. The multiple value selection is disapper. How can I fix this?There is a 'select multiple items' checkbox in the bottom area of the dropdown box.

If this is not visible - I'd almost think that your Excel platform at that specific time is Excel 2000.
Try recreating your pivot table from the as2005 cube|||I am using Excel 2002. It is funny sometime the muti value check box appear, sometime it disappear. I also try to reinstall the Excel, but it doesn't help|||

Did you upgrade MSXML to 6.0 as well as upgrading the AS Provider ?

Must say I don't get this behaviour in 2003 having added both the upgrades

|||Yes MSXML 6.0 Parser and MS OLAP Provide For AS 9.0 have been installed in my computer. Now I find the tempoary solution. If I run another application such as IE6 to overlap the Excel, then I can see the multi value check box. It is really @.$%...?|||

It's good that you're getting somewhere!

I remember that in some cases, I've had to have MDAC 2.8 installed if it wasn't already present. You shouldn't be getting that far in excel w/o it, but it's worth a try.

http://www.microsoft.com/downloads/details.aspx?DisplayLang=en&FamilyID=6c050fe3-c795-4b7d-b037-185d0506396c

Excel MODE function - value that occurs the most

Hi

I am looking for an implementation of the EXCEL MODE function for Analysis Services calculation. MEDIAN is available as integrated function, but MODE is missing.

Has someone of you an MDX implementation for this ? (for an Sum Measure) (- I am concerned about the performance if I do this over >50.000 Member Sets manually.)

Best Regards

HANNES

I have programmed my one procedure the do the job.

If you are interested the code is available at http://www.hmayer.net/tiki-list_file_gallery.php?galleryId=24

HANNES

|||

Interesting problem, Hannes. It is possible in MDX - here's a solution:

Code Snippet

with

member measures.countsame as

count(

filter(union([Date].[Calendar].currentmember.level.members, {[Date].[Calendar].currentmember} as currentmonth)

, ([Date].[Calendar].currentmember, [Measures].[Customer Count])

= (currentmonth.item(0).item(0), [Measures].[Customer Count])

)

)

member measures.mode as

(topcount([Date].[Calendar].[Month].members,1, measures.countsame).item(0).item(0), [Measures].[Customer Count])

select {[Measures].[Customer Count], measures.countsame, measures.mode} on 0,

[Date].[Calendar].[Month].members on 1

from [Adventure Works]

Although I suspect there might be a more a efficient way of doing it in MDX (I need to think a bit), custom code may well give you the best performance. If you could test this against your procedure I'd be interested to hear the result!

Regards,

Chris

Excel MODE function - value that occurs the most

Hi

I am looking for an implementation of the EXCEL MODE function for Analysis Services calculation. MEDIAN is available as integrated function, but MODE is missing.

Has someone of you an MDX implementation for this ? (for an Sum Measure) (- I am concerned about the performance if I do this over >50.000 Member Sets manually.)

Best Regards

HANNES

I have programmed my one procedure the do the job.

If you are interested the code is available at http://www.hmayer.net/tiki-list_file_gallery.php?galleryId=24

HANNES

|||

Interesting problem, Hannes. It is possible in MDX - here's a solution:

Code Snippet

with

member measures.countsame as

count(

filter(union([Date].[Calendar].currentmember.level.members, {[Date].[Calendar].currentmember} as currentmonth)

, ([Date].[Calendar].currentmember, [Measures].[Customer Count])

= (currentmonth.item(0).item(0), [Measures].[Customer Count])

)

)

member measures.mode as

(topcount([Date].[Calendar].[Month].members,1, measures.countsame).item(0).item(0), [Measures].[Customer Count])

select {[Measures].[Customer Count], measures.countsame, measures.mode} on 0,

[Date].[Calendar].[Month].members on 1

from [Adventure Works]

Although I suspect there might be a more a efficient way of doing it in MDX (I need to think a bit), custom code may well give you the best performance. If you could test this against your procedure I'd be interested to hear the result!

Regards,

Chris