Stored proc

Discussion in 'ASP .Net' started by bbawa1@yahoo.com, Jun 18, 2007.

  1. Guest

    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, 2007
    #1
    1. Advertising

  2. bruce barker Guest

    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)


    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
    >
     
    bruce barker, Jun 18, 2007
    #2
    1. Advertising

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




    "" 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=?=, Jun 18, 2007
    #3
  4. Guest

    On Jun 18, 1:18 pm, Peter Bromberg [C# MVP]
    <> 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
    >
    >
    >
    > "" 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, 2007
    #4
  5. Guest

    On Jun 18, 1:04 pm, bruce barker <> 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)
    >
    >
    >
    > 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, 2007
    #5
  6. 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




    "" wrote:

    > On Jun 18, 1:18 pm, Peter Bromberg [C# MVP]
    > <> 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
    > >
    > >
    > >
    > > "" 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=?=, Jun 18, 2007
    #6
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Jason Collins
    Replies:
    5
    Views:
    1,978
    Jason Collins
    Aug 28, 2003
  2. David Lozzi
    Replies:
    3
    Views:
    1,948
    David Lozzi
    Jun 1, 2005
  3. NevilleDNZ
    Replies:
    9
    Views:
    451
    NevilleDNZ
    Aug 16, 2006
  4. Jean-Hugues ROBERT

    Why no Proc##[]=() ? Why no Proc##replace() ?

    Jean-Hugues ROBERT, May 1, 2004, in forum: Ruby
    Replies:
    14
    Views:
    296
    Jean-Hugues ROBERT
    May 5, 2004
  5. Minkoo Seo

    Proc vs lambda vs proc

    Minkoo Seo, Feb 4, 2007, in forum: Ruby
    Replies:
    19
    Views:
    248
    Brian Candler
    Feb 6, 2007
Loading...

Share This Page