Professional Web Applications Themes

stored procedure syntax help - Microsoft SQL / MS SQL Server

When I run the following stored procedure I'm getting the following error return: Server: Msg 201, Level 16, State 4, Procedure ldman_proc1, Line 0 Procedure 'ldman_proc1' expects parameter 'em_date', which was not supplied. CREATE PROCEDURE dbo.ldman_proc1 em_date nvarchar(10) AS create table #tmp_lman1(leaderman_code char(2) null, shift_code char(1) null, employee_number char(5) null) BEGIN TRANSACTION INSERT INTO #tmp_lman1 (leaderman_code, shift_code, employee_number) SELECT leaderman_code, shift_code, employee_number FROM timesheet_hist where convert(char(10), timesheet_date, 101) = em_date AND payroll_group in ('LY','SC') AND hours > 0 AND convert(float,job_number) >= 10000 if error <> 0 begin rollback transaction return end Anyone have any idea what I'm doing wrong? Many ...

  1. #1

    Default stored procedure syntax help

    When I run the following stored procedure I'm getting the
    following error return:

    Server: Msg 201, Level 16, State 4, Procedure
    ldman_proc1, Line 0

    Procedure 'ldman_proc1' expects parameter 'em_date',
    which was not supplied.


    CREATE PROCEDURE dbo.ldman_proc1
    em_date nvarchar(10)

    AS
    create table #tmp_lman1(leaderman_code char(2) null,
    shift_code char(1) null, employee_number char(5) null)

    BEGIN TRANSACTION
    INSERT INTO #tmp_lman1
    (leaderman_code, shift_code, employee_number)

    SELECT
    leaderman_code, shift_code, employee_number

    FROM timesheet_hist

    where convert(char(10), timesheet_date, 101) = em_date
    AND payroll_group in ('LY','SC')
    AND hours > 0
    AND convert(float,job_number) >= 10000
    if error <> 0
    begin

    rollback transaction

    return
    end


    Anyone have any idea what I'm doing wrong?

    Many thanks,
    Anjelina

    anjelina Guest

  2. #2

    Default Re: stored procedure syntax help

    How are you calling this SP?? Are you supplying the parameter??

    Also, some points on coding SP's
    [A] Always enclose parameters within ( and ). This helps readers to
    understand the parameter block for SP's well.
    [B] Delimit your SP's by BEGIN ... END so that the scope of the SP is well
    known.
    --
    HTH,
    SriSamp
    Please reply to the whole group only!
    http://www32.brinkster.com/srisamp

    "anjelina" <com> wrote in message
    news:051801c349c8$a94cdee0$gbl... 


    SriSamp Guest

  3. #3

    Default Re: stored procedure syntax help

    The idea of having a parameter is to ensure that you can pass different
    values in each invocation. You can use default parameters in stored
    procedures, to ensure that if the parameter is not passed, it defaults to a
    certain value. Is this what you want??
    --
    HTH,
    SriSamp
    Please reply to the whole group only!
    http://www32.brinkster.com/srisamp

    "Anjelina" <com> wrote in message
    news:014901c349d1$da981ba0$gbl... 
    > parameter?? 
    > readers to 
    > of the SP is well [/ref]
    > the 
    > >
    > >
    > >.
    > >[/ref][/ref]


    SriSamp Guest

  4. #4

    Default Re: stored procedure syntax help

    This stored procedure works using the native powerbuilder
    drivers, but when it is run using odbc is where I get the
    odbc error.

    What I want to do, is pass a date in the char(10) 101
    format and receive the result set. I do not have a set
    date the I will be passing.

    Anjelina 
    pass different 
    parameters in stored 
    passed, it defaults to a [/ref]

    >> parameter?? [/ref][/ref]
    helps [/ref][/ref]
    scope [/ref][/ref]
    getting [/ref][/ref]
    parameter 'em_date', [/ref][/ref]
    null) [/ref][/ref]
    em_date [/ref]
    >
    >
    >.
    >[/ref]
    anjelina Guest

  5. #5

    Default Re: stored procedure syntax help

    looks like:

    exec dbo.ldman_proc1 '07/04/2003'

    error message:

    Procedure ldman_proc1 has no parameters and arguments
    were supplied.
     
    something like: EXEC [/ref]
    powerbuilder [/ref]
    the [/ref][/ref]
    can 
    >> parameters in stored 
    >> passed, it defaults to a [/ref][/ref]
    want [/ref][/ref]
    what I 
    >> helps 
    >> scope 
    >> getting 
    >> parameter 'em_date', [/ref][/ref]
    null, 
    >> null) 
    >> em_date [/ref][/ref]
    10000 [/ref]
    >
    >
    >.
    >[/ref]
    anjelina Guest

  6. #6

    Default Re: stored procedure syntax help

    I tried a similar example and everything is fine :-)
    One thing you can try is to type "sp_help yourProcedure" and check whether
    SQL Server reports the presence of a parameter. If not, then you procedure
    is possibly wrong. Second, try running this call directly in Query yzer
    to see if everything is fine.
    --
    HTH,
    SriSamp
    Please reply to the whole group only!
    http://www32.brinkster.com/srisamp

    "anjelina" <com> wrote in message
    news:023f01c349e7$9a76dd20$gbl... 
    > something like: EXEC [/ref]
    > powerbuilder [/ref]
    > the [/ref]
    > can [/ref]
    > want [/ref]
    > what I [/ref]
    > null, [/ref]
    > 10000 
    > >
    > >
    > >.
    > >[/ref][/ref]


    SriSamp Guest

  7. #7

    Default Re: stored procedure syntax help

    You're right, it works fine in the Query yser. Just
    not in Powerbuilder.

    hmmm. thanks for your help!
     
    and check whether 
    then you procedure 
    directly in Query yzer [/ref][/ref]
    as?? 
    >> something like: EXEC 
    >> powerbuilder [/ref][/ref]
    get [/ref][/ref]
    101 [/ref][/ref]
    set [/ref][/ref]
    you 
    >> want 
    >> what I [/ref][/ref]
    the [/ref][/ref]
    This [/ref][/ref]
    the [/ref][/ref]
    message 
    >> null, [/ref][/ref]
    (5) 
    >> 10000 [/ref]
    >
    >
    >.
    >[/ref]
    anjelina Guest

  8. #8

    Default Re: stored procedure syntax help

    Well one more try and I expect someone who has worked in PowerBuilder to
    step in here :-)
    If you are familiar with SQL Profiler, you can see what command is going to
    SQL Server when you make this call from PowerBuilder. Essentially you need
    to "trace" the call. The profiler application is very easy to use. You just
    set a connection to the database using the user-name and password that you
    are using from your application to connect to SQL Server and then start a
    new trace to see the statement that is going to SQL Server. I think
    something is not right with the parameter being sent from your application.
    --
    HTH,
    SriSamp
    Please reply to the whole group only!
    http://www32.brinkster.com/srisamp

    "anjelina" <com> wrote in message
    news:069501c349f6$1123fee0$gbl... 
    > and check whether 
    > then you procedure 
    > directly in Query yzer [/ref]
    > as?? [/ref]
    > get [/ref]
    > 101 [/ref]
    > set [/ref]
    > you [/ref]
    > the [/ref]
    > This [/ref]
    > the [/ref]
    > message [/ref]
    > (5) 
    > >
    > >
    > >.
    > >[/ref][/ref]


    SriSamp Guest

Similar Threads

  1. stored procedure help
    By Maria in forum Dreamweaver AppDev
    Replies: 3
    Last Post: April 20th, 06:55 PM
  2. stored procedure value
    By -D- in forum Dreamweaver AppDev
    Replies: 1
    Last Post: March 28th, 07:48 PM
  3. Stored procedure?
    By SG via DotNetMonster.com in forum ASP.NET Web Services
    Replies: 0
    Last Post: February 23rd, 01:06 PM
  4. help with a stored procedure
    By Jason Tesser in forum PostgreSQL / PGSQL
    Replies: 1
    Last Post: January 3rd, 05:35 PM
  5. ASP and stored procedure problem (syntax error ?)
    By Steven Scaife in forum ASP Database
    Replies: 4
    Last Post: August 13th, 12:11 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139