locked
SQL Stored Procedure error RRS feed

  • Question

  • User810354248 posted

    i have a table named persa  and a stored procedure named SELECT_PNUMB

    the procedure is given blow

    SET

    ANSI_NULLS

    ON

    GO

    SET

    QUOTED_IDENTIFIER

    ON

    GO

    ALTER

    PROCEDURE SELECT_PNUMB

    @PNUMB

    INT

    AS

    BEGIN

    -- SET NOCOUNT ON added to prevent extra result sets from

    -- interfering with SELECT statements.

    SET NOCOUNT ON;

    SELECT

    TC

    char(2),

    PNUMB

    INT,

    SUFX

    varchar(1),

    CDPR

    varchar(1),

    PRANK

    varchar(50),

    SRANK

    varchar(50),

    DTSR

    DATETIME,

    FNAM

    varchar(100),

    SNAM

    varchar(100),

    DTBIR

    datetime,

    TFC

    varchar(50),

    DTCOM

    DATETIME,

    DTSEN

    DATETIME,

    BATCH

    int,

    DVB

    int,

    DVBAN

    Varchar(50),

    SFAC

    int,

    HFAC

    int,

    AFAC

    int,

    PFAC

    int,

    EFAC

    int,

    MED_CAT

    varchar(50),

    OAP

    FLOAT,

    HITECH

    varchar(50),

    HTECH

    datetime,

    DGBR

    varchar(50),

    SUPER

    varchar(50),

    REQ

    varchar(MAX),

    RMK

    varchar(MAX),

    RMK1

    varchar(MAX),

    RMK2

    varchar(MAX),

    RMK3

    varchar(MAX),

    AUTH_REQ

    varchar(MAX),

    UNAUTH_REQ

    varchar(MAX),

    INST

    varchar(50),

    CRIT

    VARCHAR (50),

    AE

    varchar(50),

    AE_FULL

    VARCHAR(50),

    PART

    VARCHAR(50),

    AEDT

    DATETIME,

    SPOUSE

    VARCHAR(50),

    RETIRE

    DATETIME,

    RETIRE_PMR

    DATETIME,

    ENTRY

    VARCHAR(50),

    SS_WS

    VARCHAR(50),

    TYPE_

    VARCHAR(50)

    FROM

    persa where PNUMB = @PNUMB

    END

    GO

     

    -------------------- when i execute this procedure the following error displays

    Msg 102, Level 15, State 1, Procedure SELECT_PNUMB, Line 10

    Incorrect syntax near '2'.

    -----------

    Tuesday, July 19, 2011 1:02 PM

Answers

  • User-2011733453 posted

    try like this

    SET
    ANSI_NULLS
    ON

    GO

    SET

    QUOTED_IDENTIFIER

    ON

    GO

    alter PROCEDURE
    SELECT_PNUMB(@PNUMB INT)
    AS
    BEGIN

    -- SET NOCOUNT ON added to prevent extra result sets from

    -- interfering with SELECT statements.

    SET NOCOUNT ON;

    SELECT

    TC
    char,

    PNUMB
    INT,

    SUFX
    varchar,

    CDPR

    varchar,

    PRANK

    varchar,

    SRANK

    varchar,

    DTSR

    DATETIME,

    FNAM

    varchar,

    SNAM

    varchar,

    DTBIR

    datetime,

    TFC

    varchar,

    DTCOM

    DATETIME,

    DTSEN

    DATETIME,

    BATCH

    int,

    DVB

    int,

    DVBAN

    Varchar,

    SFAC

    int,

    HFAC

    int,

    AFAC

    int,

    PFAC

    int,

    EFAC

    int,

    MED_CAT

    varchar,

    OAP

    FLOAT,

    HITECH

    varchar,

    HTECH

    datetime,

    DGBR

    varchar,

    SUPER

    varchar,

    REQ

    varchar,

    RMK

    varchar,

    RMK1

    varchar,

    RMK2

    varchar,

    RMK3

    varchar,

    AUTH_REQ

    varchar,

    UNAUTH_REQ

    varchar,

    INST

    varchar,

    CRIT

    VARCHAR ,

    AE

    varchar,

    AE_FULL

    VARCHAR,

    PART

    VARCHAR,

    AEDT

    DATETIME,

    SPOUSE

    VARCHAR,

    RETIRE

    DATETIME,

    RETIRE_PMR

    DATETIME,

    ENTRY

    VARCHAR,

    SS_WS

    VARCHAR,

    TYPE_

    VARCHAR

    FROM

    persa where PNUMB = @PNUMB

    END

    GO

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, July 21, 2011 9:42 AM

All replies

  • User-843484705 posted

    Please create SP  in a single line till end

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    ALTER PROCEDURE SELECT_PNUMB

    @PNUMB INT

    AS

    it will work. because the above line in not proper format.

    Tuesday, July 19, 2011 1:08 PM
  • User-2010311731 posted

    First, please use the "Insert Code" tool when you are pasting code into these forums.

    Next, I think you can remove all of the data types from your select statement (int, char(2), varchar(1), etc.)

    If you still have problems, please try to re-post with the proper formatting.  Thanks!

     

    Matt

    Tuesday, July 19, 2011 1:09 PM
  • User3866881 posted

    SELECT

    TC

    char(2),

    PNUMB

    INT,

    SUFX

    varchar(1),

    CDPR

    varchar(1),

    PRANK

    varchar(50),

    SRANK

    varchar(50),

    DTSR

    DATETIME,

    FNAM

    varchar(100),

    SNAM

    varchar(100),

    DTBIR

    datetime,

    TFC

    varchar(50),

    DTCOM

    DATETIME,

    DTSEN

    DATETIME,

    BATCH

    int,

    DVB

    int,

    DVBAN

    Varchar(50),

    SFAC

    int,

    HFAC

    int,

    AFAC

    int,

    PFAC

    int,

    EFAC

    int,

    MED_CAT

    varchar(50),

    OAP

    FLOAT,

    HITECH

    varchar(50),

    HTECH

    datetime,

    DGBR

    varchar(50),

    SUPER

    varchar(50),

    REQ

    varchar(MAX),

    RMK

    varchar(MAX),

    RMK1

    varchar(MAX),

    RMK2

    varchar(MAX),

    RMK3

    varchar(MAX),

    AUTH_REQ

    varchar(MAX),

    UNAUTH_REQ

    varchar(MAX),

    INST

    varchar(50),

    CRIT

    VARCHAR (50),

    AE

    varchar(50),

    AE_FULL

    VARCHAR(50),

    PART

    VARCHAR(50),

    AEDT

    DATETIME,

    SPOUSE

    VARCHAR(50),

    RETIRE

    DATETIME,

    RETIRE_PMR

    DATETIME,

    ENTRY

    VARCHAR(50),

    SS_WS

    VARCHAR(50),

    TYPE_

    VARCHAR(50)

    FROM

    persa where PNUMB = @PNUMB

    Hello:)

    You are worng in this sql select statement——Because sql syntax doesn't allow you to use select fieldname typename…… from xxx but

    select fieldname1,fieldname2……fieldnameN from xxx

    Wednesday, July 20, 2011 10:42 PM
  • User-2011733453 posted

    try like this

    SET
    ANSI_NULLS
    ON

    GO

    SET

    QUOTED_IDENTIFIER

    ON

    GO

    alter PROCEDURE
    SELECT_PNUMB(@PNUMB INT)
    AS
    BEGIN

    -- SET NOCOUNT ON added to prevent extra result sets from

    -- interfering with SELECT statements.

    SET NOCOUNT ON;

    SELECT

    TC
    char,

    PNUMB
    INT,

    SUFX
    varchar,

    CDPR

    varchar,

    PRANK

    varchar,

    SRANK

    varchar,

    DTSR

    DATETIME,

    FNAM

    varchar,

    SNAM

    varchar,

    DTBIR

    datetime,

    TFC

    varchar,

    DTCOM

    DATETIME,

    DTSEN

    DATETIME,

    BATCH

    int,

    DVB

    int,

    DVBAN

    Varchar,

    SFAC

    int,

    HFAC

    int,

    AFAC

    int,

    PFAC

    int,

    EFAC

    int,

    MED_CAT

    varchar,

    OAP

    FLOAT,

    HITECH

    varchar,

    HTECH

    datetime,

    DGBR

    varchar,

    SUPER

    varchar,

    REQ

    varchar,

    RMK

    varchar,

    RMK1

    varchar,

    RMK2

    varchar,

    RMK3

    varchar,

    AUTH_REQ

    varchar,

    UNAUTH_REQ

    varchar,

    INST

    varchar,

    CRIT

    VARCHAR ,

    AE

    varchar,

    AE_FULL

    VARCHAR,

    PART

    VARCHAR,

    AEDT

    DATETIME,

    SPOUSE

    VARCHAR,

    RETIRE

    DATETIME,

    RETIRE_PMR

    DATETIME,

    ENTRY

    VARCHAR,

    SS_WS

    VARCHAR,

    TYPE_

    VARCHAR

    FROM

    persa where PNUMB = @PNUMB

    END

    GO

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, July 21, 2011 9:42 AM
  • User810354248 posted

    Thank You it works

    Thursday, July 21, 2011 10:19 AM