Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > Stored proc

Reply
Thread Tools

Stored proc

 
 
bbawa1@yahoo.com
Guest
Posts: n/a
 
      06-18-2007
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

 
Reply With Quote
 
 
 
 
bruce barker
Guest
Posts: n/a
 
      06-18-2007
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)


http://www.velocityreviews.com/forums/(E-Mail Removed) 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
>

 
Reply With Quote
 
 
 
 
=?Utf-8?B?UGV0ZXIgQnJvbWJlcmcgW0MjIE1WUF0=?=
Guest
Posts: n/a
 
      06-18-2007
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




"(E-Mail Removed)" 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
>
>

 
Reply With Quote
 
bbawa1@yahoo.com
Guest
Posts: n/a
 
      06-18-2007
On Jun 18, 1:18 pm, Peter Bromberg [C# MVP]
<(E-Mail Removed)> 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
>
>
>
> "(E-Mail Removed)" 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.

 
Reply With Quote
 
bbawa1@yahoo.com
Guest
Posts: n/a
 
      06-18-2007
On Jun 18, 1:04 pm, bruce barker <(E-Mail Removed)> 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)
>
>
>
> (E-Mail Removed) 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.

 
Reply With Quote
 
=?Utf-8?B?UGV0ZXIgQnJvbWJlcmcgW0MjIE1WUF0=?=
Guest
Posts: n/a
 
      06-18-2007
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




"(E-Mail Removed)" wrote:

> On Jun 18, 1:18 pm, Peter Bromberg [C# MVP]
> <(E-Mail Removed)> 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
> >
> >
> >
> > "(E-Mail Removed)" 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.
>
>

 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Proc vs lambda vs proc Minkoo Seo Ruby 19 02-06-2007 11:13 AM
proc A def/calls proc B: variable scoping rules. NevilleDNZ Python 9 08-16-2006 04:36 AM
Convert VB.NET to TSQL PROC & Reference a Proc from another Proc David Lozzi ASP .Net 3 06-01-2005 06:35 PM
Why no Proc##[]=() ? Why no Proc##replace() ? Jean-Hugues ROBERT Ruby 14 05-05-2004 01:20 PM
What is the diff btwn 'sho proc' and 'sho proc cpu' William J King Cisco 1 12-18-2003 11:50 PM



Advertisments