Tech Off Thread

6 posts

Forum Read Only

This forum has been made read only by the site admins. No new threads or comments can be added.

SQL SERVER 2005 STORED PROC CONVERT VARCHAR ERROR

Back to Forum: Tech Off
  • User profile image
    JuNK64

     

    This is my stored proc prepared on mssql 2005
    ---------------------------------------------
    ALTER PROCEDURE JK_USRLOGIN

    (

    @USR VARCHAR(20),

    @PWD VARCHAR(20)

    )

    AS

    BEGIN

    DECLARE @AD VARCHAR(50)

    DECLARE @SOYAD VARCHAR(50)

    DECLARE @YETKI INT

    SELECT @AD=AD,@SOYAD=SOYAD FROM USRS WHERE USR=@USR AND PWD=@PWD

    return( @AD)

    return( @SOYAD)

    END

    GO
    ------------------------------
     I get the error message
    -------------------

    Msg 245, Level 16, State 1, Procedure JK_USRLOGIN, Line 14

    Conversion failed when converting the varchar value 'xxx' to data type int.

    -------------------
    But sp execute normally WHY PROC WANTS TO CONVERT MY VARCHAR FIELD TO INT ? 
    haow can ı solve this please help its emergency

  • User profile image
    lorad

    CREATE PROCEDURE JK_USRLOGIN
    (
    @USR VARCHAR(20),
    @PWD VARCHAR(20)
    )
    AS
    BEGIN
       DECLARE @AD VARCHAR(50)
       DECLARE @SOYAD VARCHAR(50)
       SELECT @AD=AD,@SOYAD=SOYAD 
          FROM USRS 
          WHERE USR=@USR AND PWD=@PWD 
       select @AD,@SOYAD
    END

    Or you could make the @AD, @SOYAD as out parameters and use them directly. But this gives you what you are looking for I think. A single data row with 2 columns.
    You can only return int values from a stored proc directly.

  • User profile image
    Shark_M

    return takes int as argument, so make sure that the variables return ints.

  • User profile image
    Jim Lynn

    It's because you're trying to return two parameters, and they're both varchars, but the procedure thinks it needs to return an int.

    why don't you return a simple result set, like this:

    ALTER PROCEDURE JK_USRLOGIN

    (

    @USR VARCHAR(20),

    @PWD VARCHAR(20)

    )

    AS

    BEGIN

    SELECT AD,SOYAD FROM USRS WHERE USR=@USR AND PWD=@PWD

    END

    GO

    If you definitely need to return variables, then you'll have to use output parameters:

    ALTER PROCEDURE JK_USRLOGIN

    (

    @USR VARCHAR(20),

    @PWD VARCHAR(20),
    @AD VARCHAR(50) OUTPUT,
    @SOYAD VARCHAR(50) OUTPUT

    )

    AS

    BEGIN

    DECLARE @YETKI INT

    SELECT @AD=AD,@SOYAD=SOYAD FROM USRS WHERE USR=@USR AND PWD=@PWD

    END

    GO

  • User profile image
    JuNK64

    God Bless You My friend. Smiley its so useful for me my problem solved thanks a a lot ....

  • User profile image
    JuNK64

    SqlConnection conFriends = new SqlConnection("initial catalog=KAZANIM 2.0;data source=localhost;integrated security=sspi;packet size=4096");

    conFriends.Open();
    SqlCommand cmdInsert = new SqlCommand("JK_USRLOGIN", conFriends);

    cmdInsert.CommandType = CommandType.StoredProcedure;

    SqlParameter paramUSR = cmdInsert.Parameters.Add("@USR", SqlDbType.VarChar, 20);

    SqlParameter paramPWD = cmdInsert.Parameters.Add("@PWD", SqlDbType.VarChar, 20);

    SqlParameter paramAD = cmdInsert.Parameters.Add("@AD",

    SqlDbType.VarChar, 50);paramAD.Direction =ParameterDirection.ReturnValue;
    paramUSR.Value = textBox1.Text;

    paramPWD.Value = textBox2.Text;

    cmdInsert.ExecuteNonQuery();

    label1.Text = (string)paramAD.Value;

    /*
    HOW CAN I TAKE THE VALUE OF THE PARAM ????? AND INSERT TO LABEL1.TEXT?THE LINE ABOVE DOESN'T WORKS......  
    */

    conFriends.Close();

Conversation locked

This conversation has been locked by the site admins. No new comments can be made.