Stored proc

B

bbawa1

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
 
B

bruce barker

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)
 
G

Guest

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




[email protected] said:
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
Click to expand...
 
B

bbawa1

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



[email protected] said:
I have the following stroed procedue. But whebnnI execute it it gives
me following errors.
Could you please tell me what is wrong
Click to expand...
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'.
Click to expand...
alter PROCEDURE [twcsan].[usp_DateDiff]
-- Add the parameters for the stored procedure here
@endDate DateTime
Click to expand...
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)
Click to expand...
DECLARE @table TABLE
(
ItemReceived DateTime,
ID INT,
message VARCHAR(100),
Differnce VARCHAR(20)
Click to expand...
)
SET NOCOUNT ON;
SET @Start_Date = DateTime.Now
SET @End_Date = @endDate
Click to expand...
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
Click to expand...
SET @ID = select tck.ticketid
from tbtickets tck inner join tbticketsmessages tckmsg
on tck.ticketid = tckmsg.ticketid
Click to expand...
SET @message = select tckmsg.tcktmessage
from tbtickets tck inner join tbticketsmessages tckmsg
on tck.ticketid = tckmsg.ticketid
Click to expand...
INSERT INTO @table(ItemReceived, ID,message,Differnce)
Values(@itemReceived, @ID, @message, @Diff)
PRINT @table
return @table
END- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

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.
 
B

bbawa1

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)



[email protected] said:
I have the following stroed procedue. But whebnnI execute it it gives
me following errors.
Could you please tell me what is wrong
Click to expand...
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'.
Click to expand...
alter PROCEDURE [twcsan].[usp_DateDiff]
-- Add the parameters for the stored procedure here
@endDate DateTime
Click to expand...
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)
Click to expand...
DECLARE @table TABLE
(
ItemReceived DateTime,
ID INT,
message VARCHAR(100),
Differnce VARCHAR(20)
Click to expand...
)
SET NOCOUNT ON;
SET @Start_Date = DateTime.Now
SET @End_Date = @endDate
Click to expand...
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
Click to expand...
SET @ID = select tck.ticketid
from tbtickets tck inner join tbticketsmessages tckmsg
on tck.ticketid = tckmsg.ticketid
Click to expand...
SET @message = select tckmsg.tcktmessage
from tbtickets tck inner join tbticketsmessages tckmsg
on tck.ticketid = tckmsg.ticketid
Click to expand...
INSERT INTO @table(ItemReceived, ID,message,Differnce)
Values(@itemReceived, @ID, @message, @Diff)
PRINT @table
return @table
END- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

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.
 
G

Guest

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




[email protected] said:
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



[email protected] said:
I have the following stroed procedue. But whebnnI execute it it gives
me following errors.
Could you please tell me what is wrong
Click to expand...
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'.
Click to expand...
alter PROCEDURE [twcsan].[usp_DateDiff]
-- Add the parameters for the stored procedure here
@endDate DateTime
Click to expand...
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)
Click to expand...
DECLARE @table TABLE
(
ItemReceived DateTime,
ID INT,
message VARCHAR(100),
Differnce VARCHAR(20)
Click to expand...
)
SET NOCOUNT ON;
SET @Start_Date = DateTime.Now
SET @End_Date = @endDate
Click to expand...
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
Click to expand...
SET @ID = select tck.ticketid
from tbtickets tck inner join tbticketsmessages tckmsg
on tck.ticketid = tckmsg.ticketid
Click to expand...
SET @message = select tckmsg.tcktmessage
from tbtickets tck inner join tbticketsmessages tckmsg
on tck.ticketid = tckmsg.ticketid
Click to expand...
INSERT INTO @table(ItemReceived, ID,message,Differnce)
Values(@itemReceived, @ID, @message, @Diff)
PRINT @table
return @table
END- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

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.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Pass values 1
calling Function 1
Error message 1
Select Statement 2
Help needed with insert stored procedure 0
Stored Proc with parameters 2
Urgent: Regarding query in sql 0
Calling all TreeView developers!!! 1

Members online

Total: 68 (members: 2, guests: 66)
Robots: 101

Forum statistics

Threads
473,968
Messages
2,570,154
Members
46,702
Latest member
LukasConde

Latest Threads

Top