By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,534 Members | 1,811 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,534 IT Pros & Developers. It's quick & easy.

Stored proc

P: n/a
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

Jun 18 '07 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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)
bb****@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
Jun 18 '07 #2

P: n/a
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


"bb****@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

Jun 18 '07 #3

P: n/a
On Jun 18, 1:18 pm, Peter Bromberg [C# MVP]
<pbromb...@yahoo.yabbadabbadoo.comwrote:
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.

Jun 18 '07 #4

P: n/a
On Jun 18, 1:04 pm, bruce barker <nos...@nospam.comwrote:
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.

Jun 18 '07 #5

P: n/a
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


"bb****@yahoo.com" wrote:
On Jun 18, 1:18 pm, Peter Bromberg [C# MVP]
<pbromb...@yahoo.yabbadabbadoo.comwrote:
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.

Jun 18 '07 #6

This discussion thread is closed

Replies have been disabled for this discussion.