Professional Web Applications Themes

Question on Stored procedure - Microsoft SQL / MS SQL Server

I assume login and password are parameters. If so, try this SELECT COUNT(Usuario_ID) FROM Usuario WHERE Usuario_login = login and Usuario_password = password i.e. withour single quotation marks.   do is do a count  I can check to see  table. The stored  query itself  with the query and not  = 'login' and ...

  1. #1

    Default Question on Stored procedure

    I assume login and password are parameters. If so, try
    this

    SELECT COUNT(Usuario_ID) FROM Usuario WHERE Usuario_login
    = login and Usuario_password = password

    i.e. withour single quotation marks.
     
    do is do a count 
    I can check to see 
    table. The stored 
    query itself 
    with the query and not 
    = 'login' and 
    Ricky Guest

  2. #2

    Default Re: Question on Stored procedure

    You have no OUTPUT specified. Tyr this:

    ALTER PROCEDURE dbo.checklogin

    login varchar(50),

    password varchar(50),
    intOutPut INT OUTPUT


    AS

    SET intOutPut=SELECT COUNT(Usuario_ID) FROM Usuario WHERE Usuario_login =
    'login' and
    Usuario_password = 'password'

    RETURN intOutPut





    "Teo" <net> wrote in message
    news:%phx.gbl... 
    see 
    not 


    Ty Guest

  3. #3

    Default Re: Question on Stored procedure

    Here's the output I get when I run my Stored procedure with the values for
    login and password.
    I did declare the intOutPut INT OUTPUT, but I keep getting this error.

    Teo
    Running dbo."checklogin" ( login = thomsany, password = 1dicie79,
    intOutPut = <DEFAULT> ).

    Procedure 'checklogin' expects parameter 'intOutPut', which was not
    supplied.



    "Ty [MSFT]" <microsoft.com> wrote in message
    news:phx.gbl... [/ref]
    count 
    > see [/ref]
    stored 
    > not 
    >
    >[/ref]


    Teo Guest

  4. #4

    Default Re: Question on Stored procedure

    Doesn't make a difference, still doesn't work :-(

    Teo
    "Ty [MSFT]" <microsoft.com> wrote in message
    news:phx.gbl... [/ref]
    = [/ref]
    > count [/ref][/ref]
    to [/ref]
    > stored [/ref][/ref]
    and [/ref][/ref]
    and 
    > >
    > >[/ref]
    >
    >[/ref]


    Teo Guest

  5. #5

    Default Re: Question on Stored procedure

    Teo (net) writes: 

    Jupp. intOutPut does not have any default value, so that this is
    the same as you did not pass the parameter. You need to include the
    parameter in your parameter array, and give it a direction of
    adParamInputOutput. As for the value you can pass anything, but
    NULL would be the most logical.


    --
    Erland Sommarskog, SQL Server MVP, se

    Books Online for SQL Server SP3 at
    http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
    Erland Guest

  6. #6

    Default Re: Question on Stored procedure

    do you do that in the Stored procedure itself or in VB?

    THanks,
    Teo
    "Erland Sommarskog" <se> wrote in message
    news:0.0.1... [/ref]
    for 
    >
    > Jupp. intOutPut does not have any default value, so that this is
    > the same as you did not pass the parameter. You need to include the
    > parameter in your parameter array, and give it a direction of
    > adParamInputOutput. As for the value you can pass anything, but
    > NULL would be the most logical.
    >
    >
    > --
    > Erland Sommarskog, SQL Server MVP, se
    >
    > Books Online for SQL Server SP3 at
    > http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp[/ref]


    Teo Guest

  7. #7

    Default Re: Question on Stored procedure

    This is what I have so far, however, I get an error that intOutPut does not
    have a value.

    How can I get it to return a 1 if there is a match in the query or a 0 if
    there is no match?

    Teo



    ALTER PROCEDURE dbo.checklogin

    login varchar(50),

    password varchar(50),

    intOutPut int OUTPUT


    AS

    SELECT COUNT(Usuario_ID)

    FROM Usuario

    WHERE Usuario_login = login AND Usuario_password = password




    RETURN intOutPut

    "Erland Sommarskog" <se> wrote in message
    news:0.0.1... [/ref]
    for 
    >
    > Jupp. intOutPut does not have any default value, so that this is
    > the same as you did not pass the parameter. You need to include the
    > parameter in your parameter array, and give it a direction of
    > adParamInputOutput. As for the value you can pass anything, but
    > NULL would be the most logical.
    >
    >
    > --
    > Erland Sommarskog, SQL Server MVP, se
    >
    > Books Online for SQL Server SP3 at
    > http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp[/ref]


    Teo Guest

  8. #8

    Default Re: Question on Stored procedure





    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Tim Guest

  9. #9

    Default Re: Question on Stored procedure

    ALTER PROCEDURE dbo.checklogin
    (
    login varchar(50),
    password varchar(50)
    )
    AS

    DECLARE intOutPut int

    SELECT intOutPut = COUNT(Usuario_ID)
    FROM Usuario
    WHERE Usuario_login = login AND Usuario_password = password

    return intOutPut



    Tim Guest

  10. #10

    Default Re: Question on Stored procedure

    Teo (net) writes: 

    In Visual Basic. It may help if you post your VB code.
     

    This is somewhat redudant. An OUTPUT parameter is an output parameter,
    and a return value is a return value. So now you are returning this
    value in two different ways.

    Personally, I am of the opinion that return values should only be used
    to indicate success/failure.


    --
    Erland Sommarskog, SQL Server MVP, se

    Books Online for SQL Server SP3 at
    http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
    Erland Guest

  11. #11

    Default Re: Question on Stored procedure

    Here's the VB Code, thanks so much. I know VB adds a third variable in my
    query called RETURN_VALUE automatically, so I don't think I need to declare
    another variable to do the count inside the query.



    login = txtlogin.Text

    password = txtpassword.Text

    cn.Open()

    cm.Connection = cn



    cm.Parameters.Add("login", login)

    cm.Parameters.Add("password", password)

    result = cm.Parameters("RETURN_VALUE").Value

    MsgBox(result)

    cn.Close()

    "Erland Sommarskog" <se> wrote in message
    news:0.0.1... 
    >
    > In Visual Basic. It may help if you post your VB code.

    >
    > This is somewhat redudant. An OUTPUT parameter is an output parameter,
    > and a return value is a return value. So now you are returning this
    > value in two different ways.
    >
    > Personally, I am of the opinion that return values should only be used
    > to indicate success/failure.
    >
    >
    > --
    > Erland Sommarskog, SQL Server MVP, se
    >
    > Books Online for SQL Server SP3 at
    > http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp[/ref]


    Teo Guest

  12. #12

    Default Re: Question on Stored procedure

    Teo (net) writes: 

    RETURN_VALUE is for the RETURN value of the stored procedure. This is
    not the same as your OUTPUT parameter.

    You need to add:

    cm.Parameters.Append CreateParameter("intOutPut", adInteger,
    adParamInputOutput)
     

    You don't seem to actually call your procedure?


    --
    Erland Sommarskog, SQL Server MVP, se

    Books Online for SQL Server SP3 at
    http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
    Erland Guest

Similar Threads

  1. stored procedure help
    By Maria in forum Dreamweaver AppDev
    Replies: 3
    Last Post: April 20th, 06:55 PM
  2. is it a bug in Stored Procedure?
    By Rickey in forum Microsoft SQL / MS SQL Server
    Replies: 4
    Last Post: July 16th, 01:47 PM
  3. Stored Procedure Newbie Question --
    By Tibor Karaszi in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: July 9th, 01:26 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