EXEC syntax in sp

[DavidC]

I have a stored procedure that I want to run another stored procedure inside
of it. I have the following line in the sp but it seems to be failing. What
am I missing? Thanks.

EXEC dbo.mc_insPayCheckDeductions @Branch=@Branch,
@CheckIDFrom=@CheckIDFrom, @CheckIDTo=@CheckIDTo, @PayPeriod=@PeriodEnding

--
David

[Tom]

What makes you think it is failing>? Please provide the code and the
error message.

[DavidC]

It was a newly inserted line in the sp that ran fine before. If I comment
this line out it runs without error. The error I get is below.

Conversion failed when converting date and/or time from character string.
--
David

"Tom" wrote:

> .
>

[DavidC]

I found the problem. It was in the called sp. I was using CAST to form a
date using code below.

SET @MonthStarts = CAST(CAST(YEAR(@PeriodEnding) as char(4)) +
CAST(MONTH(@PeriodEnding) as char(2)) + '01' as Date);

This works fine if the month > 9 but apparently fails to supply the leading
zero. I think I need an IF statement before setting.
--
David

"Tom" wrote:

> .
>

[Bob Barrows]

And you think we can tell what is wrong based on this information ...?

DavidC wrote:
> It was a newly inserted line in the sp that ran fine before. If I
> comment this line out it runs without error. The error I get is
> below.
>
> Conversion failed when converting date and/or time from character
> string.
>

>> On Sep 7, 6:50 pm, DavidC <dlch...@lifetimeinc.com> wrote:
>>> I have a stored procedure that I want to run another stored
>>> procedure inside of it. I have the following line in the sp but it
>>> seems to be failing. What am I missing? Thanks.
>>>
>>> EXEC dbo.mc_insPayCheckDeductions @Branch=@Branch,
>>> @CheckIDFrom=@CheckIDFrom, @CheckIDTo=@CheckIDTo,
>>> @PayPeriod=@PeriodEnding
>>>
>>

>> What makes you think it is failing>? Please provide the code and the
>> error message.
>> .

--
HTH,
Bob Barrows

[Tom]

> > .- Hide quoted text -
>
> - Show quoted text -

Try to print the @PeriodEnding column in the stored procedure prior to
executing the failing procedure. Without the code I am guessing that
that should be a datetime column and it is not or is not formatted
correctly.

[Tom Cooper]

Just add a leading '0', then take the rightmost two characters, for example

SET @MonthStarts = CAST(CAST(YEAR(@PeriodEnding) as char(4)) +

RIGHT('0' + CAST(MONTH(@PeriodEnding) as varchar(2)), 2) + '01' as Date);

Tom

"DavidC" <dlc...@lifetimeinc.com> wrote in message
news:D1620837-CBAB-4BAA...@microsoft.com...

[Bob Barrows]

There are two ways to address this problem:

1. Use arithmetic instead of string concatenation:
SET @MonthStarts = CAST(CAST(YEAR(@PeriodEnding) * 10000 +
MONTH(@PeriodEnding)*100 + 1 AS char(8)) AS Date);

2. Use the RIGHT function to ensure a leading zero:

SET @MonthStarts = CAST(CAST(YEAR(@PeriodEnding) as char(4)) +

RIGHT('00' + CAST(MONTH(@PeriodEnding) as varchar(2)),2) + '01' as
Date);

DavidC wrote:
> I found the problem. It was in the called sp. I was using CAST to
> form a date using code below.
>
> SET @MonthStarts = CAST(CAST(YEAR(@PeriodEnding) as char(4)) +
> CAST(MONTH(@PeriodEnding) as char(2)) + '01' as Date);
>
> This works fine if the month > 9 but apparently fails to supply the
> leading zero. I think I need an IF statement before setting.
>

>> On Sep 7, 6:50 pm, DavidC <dlch...@lifetimeinc.com> wrote:
>>> I have a stored procedure that I want to run another stored
>>> procedure inside of it. I have the following line in the sp but it
>>> seems to be failing. What am I missing? Thanks.
>>>
>>> EXEC dbo.mc_insPayCheckDeductions @Branch=@Branch,
>>> @CheckIDFrom=@CheckIDFrom, @CheckIDTo=@CheckIDTo,
>>> @PayPeriod=@PeriodEnding
>>>
>>

>> What makes you think it is failing>? Please provide the code and the
>> error message.
>> .

--
HTH,
Bob Barrows

[Bob Barrows]

Oops, I forgot to remove an unneeded CAST:

SET @MonthStarts = CAST(YEAR(@PeriodEnding) * 10000 +

[Erland Sommarskog]

DavidC (dlc...@lifetimeinc.com) writes:
> I found the problem. It was in the called sp. I was using CAST to form a
> date using code below.
>
> SET @MonthStarts = CAST(CAST(YEAR(@PeriodEnding) as char(4)) +
> CAST(MONTH(@PeriodEnding) as char(2)) + '01' as Date);
>
> This works fine if the month > 9 but apparently fails to supply the
> leading zero. I think I need an IF statement before setting.

SELECT @MonthStarts = convert(char(6), @PeriodEnding, 112) + '01'

Format 112 is YYYYMMDD which lends itself exquisitly for this type of
mainpulations.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[--CELKO--]

Your approach is wrong. Instead of computing temporal ranges, row by
row, over and over, build uniform report ranges look-up table. Every
procedure will use it and you do not have to worry about changes, and
differences among procedures.