Velocity Reviews

Velocity Reviews (http://www.velocityreviews.com/forums/index.php)
-   ASP .Net (http://www.velocityreviews.com/forums/f29-asp-net.html)
-   -   Stored proc (http://www.velocityreviews.com/forums/t515501-stored-proc.html)

bbawa1@yahoo.com 06-18-2007 06:52 PM

Stored proc
 
I have the following stroed procedue. But whebnnI execute it it gives
me following errors.
Could you please tell me what is wrong

Msg 156, Level 15, State 1, Procedure usp_DateDiff, Line 43
Incorrect syntax near the keyword 'SELECT'.
Msg 156, Level 15, State 1, Procedure usp_DateDiff, Line 47
Incorrect syntax near the keyword 'select'.
Msg 156, Level 15, State 1, Procedure usp_DateDiff, Line 52
Incorrect syntax near the keyword 'select'.
Msg 137, Level 15, State 2, Procedure usp_DateDiff, Line 58
Must declare the variable '@table'.
Msg 137, Level 15, State 2, Procedure usp_DateDiff, Line 59
Must declare the variable '@table'.



alter PROCEDURE [twcsan].[usp_DateDiff]
-- Add the parameters for the stored procedure here
@endDate DateTime

AS
BEGIN
DECLARE @Diff INT
DECLARE @Day INT
DECLARE @Hour INT
DECLARE @Minute INT
DECLARE @Start_Date DateTime
DECLARE @End_Date DateTime
DECLARE @itemReceived DateTime
DECLARE @ID INT
DECLARE @message VARCHAR(50)


DECLARE @table TABLE
(
ItemReceived DateTime,
ID INT,
message VARCHAR(100),
Differnce VARCHAR(20)

)
SET NOCOUNT ON;
SET @Start_Date = DateTime.Now
SET @End_Date = @endDate

SET @Day = DATEDIFF( day, @Start_Date, @End_Date)
SET @Hour = DATEDIFF(hour , @Start_Date, @End_Date)
SET @Minute = DATEDIFF(minute , @Start_Date, @End_Date)
SET @Minute = @Minute-(@HOUR* 60)
SET @Hour = @Hour-(24* @Day)
SET @Diff = CONVERT(Varchar, @Day) +'d ' + CONVERT(Varchar , @Hour) +
'h ' + CONVERT(Varchar , @Minute) +'m'
SET @itemReceived = SELECT tck.tcktreceived
from tbtickets tck inner join tbticketsmessages tckmsg
on tck.ticketid = tckmsg.ticketid

SET @ID = select tck.ticketid
from tbtickets tck inner join tbticketsmessages tckmsg
on tck.ticketid = tckmsg.ticketid


SET @message = select tckmsg.tcktmessage
from tbtickets tck inner join tbticketsmessages tckmsg
on tck.ticketid = tckmsg.ticketid

INSERT INTO @table(ItemReceived, ID,message,Differnce)
Values(@itemReceived, @ID, @message, @Diff)
PRINT @table
return @table
END


bruce barker 06-18-2007 08:04 PM

Re: Stored proc
 
SET @itemReceived = SELECT tck.tcktreceived
from tbtickets tck
inner join tbticketsmessages tckmsg
on tck.ticketid = tckmsg.ticketid

is an invalid syntax, the right side must be an expression.

try:

SELECT @itemReceived = tck.tcktreceived
from tbtickets tck
inner join tbticketsmessages tckmsg
on tck.ticketid = tckmsg.ticketid


or

SET @itemReceived = (SELECT tck.tcktreceived
from tbtickets tck
inner join tbticketsmessages tckmsg
on tck.ticketid = tckmsg.ticketid)

-- bruce (sqlwork.com)


bbawa1@yahoo.com wrote:
> I have the following stroed procedue. But whebnnI execute it it gives
> me following errors.
> Could you please tell me what is wrong
>
> Msg 156, Level 15, State 1, Procedure usp_DateDiff, Line 43
> Incorrect syntax near the keyword 'SELECT'.
> Msg 156, Level 15, State 1, Procedure usp_DateDiff, Line 47
> Incorrect syntax near the keyword 'select'.
> Msg 156, Level 15, State 1, Procedure usp_DateDiff, Line 52
> Incorrect syntax near the keyword 'select'.
> Msg 137, Level 15, State 2, Procedure usp_DateDiff, Line 58
> Must declare the variable '@table'.
> Msg 137, Level 15, State 2, Procedure usp_DateDiff, Line 59
> Must declare the variable '@table'.
>
>
>
> alter PROCEDURE [twcsan].[usp_DateDiff]
> -- Add the parameters for the stored procedure here
> @endDate DateTime
>
> AS
> BEGIN
> DECLARE @Diff INT
> DECLARE @Day INT
> DECLARE @Hour INT
> DECLARE @Minute INT
> DECLARE @Start_Date DateTime
> DECLARE @End_Date DateTime
> DECLARE @itemReceived DateTime
> DECLARE @ID INT
> DECLARE @message VARCHAR(50)
>
>
> DECLARE @table TABLE
> (
> ItemReceived DateTime,
> ID INT,
> message VARCHAR(100),
> Differnce VARCHAR(20)
>
> )
> SET NOCOUNT ON;
> SET @Start_Date = DateTime.Now
> SET @End_Date = @endDate
>
> SET @Day = DATEDIFF( day, @Start_Date, @End_Date)
> SET @Hour = DATEDIFF(hour , @Start_Date, @End_Date)
> SET @Minute = DATEDIFF(minute , @Start_Date, @End_Date)
> SET @Minute = @Minute-(@HOUR* 60)
> SET @Hour = @Hour-(24* @Day)
> SET @Diff = CONVERT(Varchar, @Day) +'d ' + CONVERT(Varchar , @Hour) +
> 'h ' + CONVERT(Varchar , @Minute) +'m'
> SET @itemReceived = SELECT tck.tcktreceived
> from tbtickets tck inner join tbticketsmessages tckmsg
> on tck.ticketid = tckmsg.ticketid
>
> SET @ID = select tck.ticketid
> from tbtickets tck inner join tbticketsmessages tckmsg
> on tck.ticketid = tckmsg.ticketid
>
>
> SET @message = select tckmsg.tcktmessage
> from tbtickets tck inner join tbticketsmessages tckmsg
> on tck.ticketid = tckmsg.ticketid
>
> INSERT INTO @table(ItemReceived, ID,message,Differnce)
> Values(@itemReceived, @ID, @message, @Diff)
> PRINT @table
> return @table
> END
>


=?Utf-8?B?UGV0ZXIgQnJvbWJlcmcgW0MjIE1WUF0=?= 06-18-2007 08:18 PM

RE: Stored proc
 
CREATE PROCEDURE [dbo].[usp_DateDiff]
-- Add the parameters for the stored procedure here
@endDate DateTime

AS
BEGIN
DECLARE @Diff INT
DECLARE @Day INT
DECLARE @Hour INT
DECLARE @Minute INT
DECLARE @Start_Date DateTime
DECLARE @End_Date DateTime
DECLARE @itemReceived DateTime
DECLARE @ID INT
DECLARE @message VARCHAR(50)


DECLARE @table1 TABLE
(
ItemReceived DateTime,
ID INT,
message VARCHAR(100),
Differnce VARCHAR(20)

)
SET NOCOUNT ON;
SET @Start_Date = getDate()
SET @End_Date = @endDate

SET @Day = DATEDIFF( day, @Start_Date, @End_Date)
SET @Hour = DATEDIFF(hour , @Start_Date, @End_Date)
SET @Minute = DATEDIFF(minute , @Start_Date, @End_Date)
SET @Minute = @Minute-(@HOUR* 60)
SET @Hour = @Hour-(24* @Day)
SET @Diff = CONVERT(Varchar, @Day) +'d ' + CONVERT(Varchar , @Hour) +
'h ' + CONVERT(Varchar , @Minute) +'m'
SELECT @itemReceived = tck.tcktreceived
from tbtickets tck inner join tbticketsmessages tckmsg
on tck.ticketid = tckmsg.ticketid

SELECT @ID = tck.ticketid
from tbtickets tck inner join tbticketsmessages tckmsg
on tck.ticketid = tckmsg.ticketid


SELECT @message = tckmsg.tcktmessage
from tbtickets tck inner join tbticketsmessages tckmsg
on tck.ticketid = tckmsg.ticketid

INSERT INTO @table1(ItemReceived, ID,message,Differnce)
Values(@itemReceived, @ID, @message, @Diff)
--PRINT @table -- you can't "print" a table variable
SELECT ItemReceived, ID,message,Differnce FROM @table1
END
--
Site: http://www.eggheadcafe.com
UnBlog: http://petesbloggerama.blogspot.com
Short urls & more: http://ittyurl.net




"bbawa1@yahoo.com" wrote:

> I have the following stroed procedue. But whebnnI execute it it gives
> me following errors.
> Could you please tell me what is wrong
>
> Msg 156, Level 15, State 1, Procedure usp_DateDiff, Line 43
> Incorrect syntax near the keyword 'SELECT'.
> Msg 156, Level 15, State 1, Procedure usp_DateDiff, Line 47
> Incorrect syntax near the keyword 'select'.
> Msg 156, Level 15, State 1, Procedure usp_DateDiff, Line 52
> Incorrect syntax near the keyword 'select'.
> Msg 137, Level 15, State 2, Procedure usp_DateDiff, Line 58
> Must declare the variable '@table'.
> Msg 137, Level 15, State 2, Procedure usp_DateDiff, Line 59
> Must declare the variable '@table'.
>
>
>
> alter PROCEDURE [twcsan].[usp_DateDiff]
> -- Add the parameters for the stored procedure here
> @endDate DateTime
>
> AS
> BEGIN
> DECLARE @Diff INT
> DECLARE @Day INT
> DECLARE @Hour INT
> DECLARE @Minute INT
> DECLARE @Start_Date DateTime
> DECLARE @End_Date DateTime
> DECLARE @itemReceived DateTime
> DECLARE @ID INT
> DECLARE @message VARCHAR(50)
>
>
> DECLARE @table TABLE
> (
> ItemReceived DateTime,
> ID INT,
> message VARCHAR(100),
> Differnce VARCHAR(20)
>
> )
> SET NOCOUNT ON;
> SET @Start_Date = DateTime.Now
> SET @End_Date = @endDate
>
> SET @Day = DATEDIFF( day, @Start_Date, @End_Date)
> SET @Hour = DATEDIFF(hour , @Start_Date, @End_Date)
> SET @Minute = DATEDIFF(minute , @Start_Date, @End_Date)
> SET @Minute = @Minute-(@HOUR* 60)
> SET @Hour = @Hour-(24* @Day)
> SET @Diff = CONVERT(Varchar, @Day) +'d ' + CONVERT(Varchar , @Hour) +
> 'h ' + CONVERT(Varchar , @Minute) +'m'
> SET @itemReceived = SELECT tck.tcktreceived
> from tbtickets tck inner join tbticketsmessages tckmsg
> on tck.ticketid = tckmsg.ticketid
>
> SET @ID = select tck.ticketid
> from tbtickets tck inner join tbticketsmessages tckmsg
> on tck.ticketid = tckmsg.ticketid
>
>
> SET @message = select tckmsg.tcktmessage
> from tbtickets tck inner join tbticketsmessages tckmsg
> on tck.ticketid = tckmsg.ticketid
>
> INSERT INTO @table(ItemReceived, ID,message,Differnce)
> Values(@itemReceived, @ID, @message, @Diff)
> PRINT @table
> return @table
> END
>
>


bbawa1@yahoo.com 06-18-2007 08:30 PM

Re: Stored proc
 
On Jun 18, 1:18 pm, Peter Bromberg [C# MVP]
<pbromb...@yahoo.yabbadabbadoo.com> wrote:
> CREATE PROCEDURE [dbo].[usp_DateDiff]
> -- Add the parameters for the stored procedure here
> @endDate DateTime
>
> AS
> BEGIN
> DECLARE @Diff INT
> DECLARE @Day INT
> DECLARE @Hour INT
> DECLARE @Minute INT
> DECLARE @Start_Date DateTime
> DECLARE @End_Date DateTime
> DECLARE @itemReceived DateTime
> DECLARE @ID INT
> DECLARE @message VARCHAR(50)
>
> DECLARE @table1 TABLE
> (
> ItemReceived DateTime,
> ID INT,
> message VARCHAR(100),
> Differnce VARCHAR(20)
>
> )
> SET NOCOUNT ON;
> SET @Start_Date = getDate()
> SET @End_Date = @endDate
>
> SET @Day = DATEDIFF( day, @Start_Date, @End_Date)
> SET @Hour = DATEDIFF(hour , @Start_Date, @End_Date)
> SET @Minute = DATEDIFF(minute , @Start_Date, @End_Date)
> SET @Minute = @Minute-(@HOUR* 60)
> SET @Hour = @Hour-(24* @Day)
> SET @Diff = CONVERT(Varchar, @Day) +'d ' + CONVERT(Varchar , @Hour) +
> 'h ' + CONVERT(Varchar , @Minute) +'m'
> SELECT @itemReceived = tck.tcktreceived
> from tbtickets tck inner join tbticketsmessages tckmsg
> on tck.ticketid = tckmsg.ticketid
>
> SELECT @ID = tck.ticketid
> from tbtickets tck inner join tbticketsmessages tckmsg
> on tck.ticketid = tckmsg.ticketid
>
> SELECT @message = tckmsg.tcktmessage
> from tbtickets tck inner join tbticketsmessages tckmsg
> on tck.ticketid = tckmsg.ticketid
>
> INSERT INTO @table1(ItemReceived, ID,message,Differnce)
> Values(@itemReceived, @ID, @message, @Diff)
> --PRINT @table -- you can't "print" a table variable
> SELECT ItemReceived, ID,message,Differnce FROM @table1
> END
> --
> Site: http://www.eggheadcafe.com
> UnBlog: http://petesbloggerama.blogspot.com
> Short urls & more: http://ittyurl.net
>
>
>
> "bba...@yahoo.com" wrote:
> > I have the following stroed procedue. But whebnnI execute it it gives
> > me following errors.
> > Could you please tell me what is wrong

>
> > Msg 156, Level 15, State 1, Procedure usp_DateDiff, Line 43
> > Incorrect syntax near the keyword 'SELECT'.
> > Msg 156, Level 15, State 1, Procedure usp_DateDiff, Line 47
> > Incorrect syntax near the keyword 'select'.
> > Msg 156, Level 15, State 1, Procedure usp_DateDiff, Line 52
> > Incorrect syntax near the keyword 'select'.
> > Msg 137, Level 15, State 2, Procedure usp_DateDiff, Line 58
> > Must declare the variable '@table'.
> > Msg 137, Level 15, State 2, Procedure usp_DateDiff, Line 59
> > Must declare the variable '@table'.

>
> > alter PROCEDURE [twcsan].[usp_DateDiff]
> > -- Add the parameters for the stored procedure here
> > @endDate DateTime

>
> > AS
> > BEGIN
> > DECLARE @Diff INT
> > DECLARE @Day INT
> > DECLARE @Hour INT
> > DECLARE @Minute INT
> > DECLARE @Start_Date DateTime
> > DECLARE @End_Date DateTime
> > DECLARE @itemReceived DateTime
> > DECLARE @ID INT
> > DECLARE @message VARCHAR(50)

>
> > DECLARE @table TABLE
> > (
> > ItemReceived DateTime,
> > ID INT,
> > message VARCHAR(100),
> > Differnce VARCHAR(20)

>
> > )
> > SET NOCOUNT ON;
> > SET @Start_Date = DateTime.Now
> > SET @End_Date = @endDate

>
> > SET @Day = DATEDIFF( day, @Start_Date, @End_Date)
> > SET @Hour = DATEDIFF(hour , @Start_Date, @End_Date)
> > SET @Minute = DATEDIFF(minute , @Start_Date, @End_Date)
> > SET @Minute = @Minute-(@HOUR* 60)
> > SET @Hour = @Hour-(24* @Day)
> > SET @Diff = CONVERT(Varchar, @Day) +'d ' + CONVERT(Varchar , @Hour) +
> > 'h ' + CONVERT(Varchar , @Minute) +'m'
> > SET @itemReceived = SELECT tck.tcktreceived
> > from tbtickets tck inner join tbticketsmessages tckmsg
> > on tck.ticketid = tckmsg.ticketid

>
> > SET @ID = select tck.ticketid
> > from tbtickets tck inner join tbticketsmessages tckmsg
> > on tck.ticketid = tckmsg.ticketid

>
> > SET @message = select tckmsg.tcktmessage
> > from tbtickets tck inner join tbticketsmessages tckmsg
> > on tck.ticketid = tckmsg.ticketid

>
> > INSERT INTO @table(ItemReceived, ID,message,Differnce)
> > Values(@itemReceived, @ID, @message, @Diff)
> > PRINT @table
> > return @table
> > END- Hide quoted text -

>
> - Show quoted text -


But this is now giving me error
Subquery returned more than 1 value. This is not permitted when the
subquery follows =, !=, <, <= , >, >= or when the subquery is used as
an expression.


bbawa1@yahoo.com 06-18-2007 08:30 PM

Re: Stored proc
 
On Jun 18, 1:04 pm, bruce barker <nos...@nospam.com> wrote:
> SET @itemReceived = SELECT tck.tcktreceived
> from tbtickets tck
> inner join tbticketsmessages tckmsg
> on tck.ticketid = tckmsg.ticketid
>
> is an invalid syntax, the right side must be an expression.
>
> try:
>
> SELECT @itemReceived = tck.tcktreceived
> from tbtickets tck
> inner join tbticketsmessages tckmsg
> on tck.ticketid = tckmsg.ticketid
>
> or
>
> SET @itemReceived = (SELECT tck.tcktreceived
> from tbtickets tck
> inner join tbticketsmessages tckmsg
> on tck.ticketid = tckmsg.ticketid)
>
> -- bruce (sqlwork.com)
>
>
>
> bba...@yahoo.com wrote:
> > I have the following stroed procedue. But whebnnI execute it it gives
> > me following errors.
> > Could you please tell me what is wrong

>
> > Msg 156, Level 15, State 1, Procedure usp_DateDiff, Line 43
> > Incorrect syntax near the keyword 'SELECT'.
> > Msg 156, Level 15, State 1, Procedure usp_DateDiff, Line 47
> > Incorrect syntax near the keyword 'select'.
> > Msg 156, Level 15, State 1, Procedure usp_DateDiff, Line 52
> > Incorrect syntax near the keyword 'select'.
> > Msg 137, Level 15, State 2, Procedure usp_DateDiff, Line 58
> > Must declare the variable '@table'.
> > Msg 137, Level 15, State 2, Procedure usp_DateDiff, Line 59
> > Must declare the variable '@table'.

>
> > alter PROCEDURE [twcsan].[usp_DateDiff]
> > -- Add the parameters for the stored procedure here
> > @endDate DateTime

>
> > AS
> > BEGIN
> > DECLARE @Diff INT
> > DECLARE @Day INT
> > DECLARE @Hour INT
> > DECLARE @Minute INT
> > DECLARE @Start_Date DateTime
> > DECLARE @End_Date DateTime
> > DECLARE @itemReceived DateTime
> > DECLARE @ID INT
> > DECLARE @message VARCHAR(50)

>
> > DECLARE @table TABLE
> > (
> > ItemReceived DateTime,
> > ID INT,
> > message VARCHAR(100),
> > Differnce VARCHAR(20)

>
> > )
> > SET NOCOUNT ON;
> > SET @Start_Date = DateTime.Now
> > SET @End_Date = @endDate

>
> > SET @Day = DATEDIFF( day, @Start_Date, @End_Date)
> > SET @Hour = DATEDIFF(hour , @Start_Date, @End_Date)
> > SET @Minute = DATEDIFF(minute , @Start_Date, @End_Date)
> > SET @Minute = @Minute-(@HOUR* 60)
> > SET @Hour = @Hour-(24* @Day)
> > SET @Diff = CONVERT(Varchar, @Day) +'d ' + CONVERT(Varchar , @Hour) +
> > 'h ' + CONVERT(Varchar , @Minute) +'m'
> > SET @itemReceived = SELECT tck.tcktreceived
> > from tbtickets tck inner join tbticketsmessages tckmsg
> > on tck.ticketid = tckmsg.ticketid

>
> > SET @ID = select tck.ticketid
> > from tbtickets tck inner join tbticketsmessages tckmsg
> > on tck.ticketid = tckmsg.ticketid

>
> > SET @message = select tckmsg.tcktmessage
> > from tbtickets tck inner join tbticketsmessages tckmsg
> > on tck.ticketid = tckmsg.ticketid

>
> > INSERT INTO @table(ItemReceived, ID,message,Differnce)
> > Values(@itemReceived, @ID, @message, @Diff)
> > PRINT @table
> > return @table
> > END- Hide quoted text -

>
> - Show quoted text -


But this is now giving me error
Subquery returned more than 1 value. This is not permitted when the
subquery follows =, !=, <, <= , >, >= or when the subquery is used as
an expression.


=?Utf-8?B?UGV0ZXIgQnJvbWJlcmcgW0MjIE1WUF0=?= 06-18-2007 08:40 PM

Re: Stored proc
 
Well, obviously I don't have your tables or their contents.

But if you have a select @whatever =whatever from sometable and the
subquery returns more than one value, you need to fix it so that it only
returns one value.
Ain't rocket science, man. The error message is pretty clear to me.
Cheers,
Peter

--
Site: http://www.eggheadcafe.com
UnBlog: http://petesbloggerama.blogspot.com
Short urls & more: http://ittyurl.net




"bbawa1@yahoo.com" wrote:

> On Jun 18, 1:18 pm, Peter Bromberg [C# MVP]
> <pbromb...@yahoo.yabbadabbadoo.com> wrote:
> > CREATE PROCEDURE [dbo].[usp_DateDiff]
> > -- Add the parameters for the stored procedure here
> > @endDate DateTime
> >
> > AS
> > BEGIN
> > DECLARE @Diff INT
> > DECLARE @Day INT
> > DECLARE @Hour INT
> > DECLARE @Minute INT
> > DECLARE @Start_Date DateTime
> > DECLARE @End_Date DateTime
> > DECLARE @itemReceived DateTime
> > DECLARE @ID INT
> > DECLARE @message VARCHAR(50)
> >
> > DECLARE @table1 TABLE
> > (
> > ItemReceived DateTime,
> > ID INT,
> > message VARCHAR(100),
> > Differnce VARCHAR(20)
> >
> > )
> > SET NOCOUNT ON;
> > SET @Start_Date = getDate()
> > SET @End_Date = @endDate
> >
> > SET @Day = DATEDIFF( day, @Start_Date, @End_Date)
> > SET @Hour = DATEDIFF(hour , @Start_Date, @End_Date)
> > SET @Minute = DATEDIFF(minute , @Start_Date, @End_Date)
> > SET @Minute = @Minute-(@HOUR* 60)
> > SET @Hour = @Hour-(24* @Day)
> > SET @Diff = CONVERT(Varchar, @Day) +'d ' + CONVERT(Varchar , @Hour) +
> > 'h ' + CONVERT(Varchar , @Minute) +'m'
> > SELECT @itemReceived = tck.tcktreceived
> > from tbtickets tck inner join tbticketsmessages tckmsg
> > on tck.ticketid = tckmsg.ticketid
> >
> > SELECT @ID = tck.ticketid
> > from tbtickets tck inner join tbticketsmessages tckmsg
> > on tck.ticketid = tckmsg.ticketid
> >
> > SELECT @message = tckmsg.tcktmessage
> > from tbtickets tck inner join tbticketsmessages tckmsg
> > on tck.ticketid = tckmsg.ticketid
> >
> > INSERT INTO @table1(ItemReceived, ID,message,Differnce)
> > Values(@itemReceived, @ID, @message, @Diff)
> > --PRINT @table -- you can't "print" a table variable
> > SELECT ItemReceived, ID,message,Differnce FROM @table1
> > END
> > --
> > Site: http://www.eggheadcafe.com
> > UnBlog: http://petesbloggerama.blogspot.com
> > Short urls & more: http://ittyurl.net
> >
> >
> >
> > "bba...@yahoo.com" wrote:
> > > I have the following stroed procedue. But whebnnI execute it it gives
> > > me following errors.
> > > Could you please tell me what is wrong

> >
> > > Msg 156, Level 15, State 1, Procedure usp_DateDiff, Line 43
> > > Incorrect syntax near the keyword 'SELECT'.
> > > Msg 156, Level 15, State 1, Procedure usp_DateDiff, Line 47
> > > Incorrect syntax near the keyword 'select'.
> > > Msg 156, Level 15, State 1, Procedure usp_DateDiff, Line 52
> > > Incorrect syntax near the keyword 'select'.
> > > Msg 137, Level 15, State 2, Procedure usp_DateDiff, Line 58
> > > Must declare the variable '@table'.
> > > Msg 137, Level 15, State 2, Procedure usp_DateDiff, Line 59
> > > Must declare the variable '@table'.

> >
> > > alter PROCEDURE [twcsan].[usp_DateDiff]
> > > -- Add the parameters for the stored procedure here
> > > @endDate DateTime

> >
> > > AS
> > > BEGIN
> > > DECLARE @Diff INT
> > > DECLARE @Day INT
> > > DECLARE @Hour INT
> > > DECLARE @Minute INT
> > > DECLARE @Start_Date DateTime
> > > DECLARE @End_Date DateTime
> > > DECLARE @itemReceived DateTime
> > > DECLARE @ID INT
> > > DECLARE @message VARCHAR(50)

> >
> > > DECLARE @table TABLE
> > > (
> > > ItemReceived DateTime,
> > > ID INT,
> > > message VARCHAR(100),
> > > Differnce VARCHAR(20)

> >
> > > )
> > > SET NOCOUNT ON;
> > > SET @Start_Date = DateTime.Now
> > > SET @End_Date = @endDate

> >
> > > SET @Day = DATEDIFF( day, @Start_Date, @End_Date)
> > > SET @Hour = DATEDIFF(hour , @Start_Date, @End_Date)
> > > SET @Minute = DATEDIFF(minute , @Start_Date, @End_Date)
> > > SET @Minute = @Minute-(@HOUR* 60)
> > > SET @Hour = @Hour-(24* @Day)
> > > SET @Diff = CONVERT(Varchar, @Day) +'d ' + CONVERT(Varchar , @Hour) +
> > > 'h ' + CONVERT(Varchar , @Minute) +'m'
> > > SET @itemReceived = SELECT tck.tcktreceived
> > > from tbtickets tck inner join tbticketsmessages tckmsg
> > > on tck.ticketid = tckmsg.ticketid

> >
> > > SET @ID = select tck.ticketid
> > > from tbtickets tck inner join tbticketsmessages tckmsg
> > > on tck.ticketid = tckmsg.ticketid

> >
> > > SET @message = select tckmsg.tcktmessage
> > > from tbtickets tck inner join tbticketsmessages tckmsg
> > > on tck.ticketid = tckmsg.ticketid

> >
> > > INSERT INTO @table(ItemReceived, ID,message,Differnce)
> > > Values(@itemReceived, @ID, @message, @Diff)
> > > PRINT @table
> > > return @table
> > > END- Hide quoted text -

> >
> > - Show quoted text -

>
> But this is now giving me error
> Subquery returned more than 1 value. This is not permitted when the
> subquery follows =, !=, <, <= , >, >= or when the subquery is used as
> an expression.
>
>



All times are GMT. The time now is 05:05 AM.

Powered by vBulletin®. Copyright ©2000 - 2014, vBulletin Solutions, Inc.
SEO by vBSEO ©2010, Crawlability, Inc.