SQL - Problem converting int to varchar RSS

• • Reply
  

  slimbunny

  Member

  437 Points

  508 Posts

  SQL - Problem converting int to varchar

  Jul 23, 2012 04:12 AM|LINK

  Problem with this stored Procedure, can someone help me fix it?

  I get the following error message and I don't not know how to fix it.

  Conversion failed when converting the VARCHAR value 'KCmuj' to data type INT.

  The storedProcedures generates a password and returns a string of 6 characters.

  ----------------------------------

  ALTER PROCEDURE [dbo].[GeneratePassword]
        @Length int = 5
      , @newid VARCHAR(50) ='abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789&'
      , @rand REAL ='200' --RETURNS VARCHAR(100)
      , @len int =5
      , @min tinyint = 48
      , @range tinyint = 74
      , @exclude varchar(50) = '0:;<=>?O[]`^\/-_'
      , @output varchar(50)= 5 output
  AS
  
      --Generate Password
      DECLARE @char char
      SET @output = ''
   
      WHILE @len > 0 BEGIN
         SELECT @char = char(round(rand() * @range + @min, 0))
         IF charindex(@char, @exclude) = 0
         BEGIN
             SET @output += @char
             SET @len = @len - 1
         END
      END
     
      RETURN @output

  A Lifetime ASP.NET DEVELOPER
  • Edited by slimbunny on Jul 23, 2012 04:38 AM
• • Reply
  

  Basquiat

  Contributor

  2378 Points

  625 Posts

  Re: SQL - Problem converting int to varchar

  Jul 23, 2012 04:55 AM|LINK

  It look like you are trying to multiply and do addition with a varchar
• • Reply
  

  slimbunny

  Member

  437 Points

  508 Posts

  Re: SQL - Problem converting int to varchar

  Jul 23, 2012 05:05 AM|LINK

  The code generates a password (5 characters) alpha and numeric.

  the characters are:

  @newid VARCHAR(50) ='abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789&'

  The Return @output give me an error message...

  error message:

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

  How do I fix this where the @output returns a string characters....?

  return string should be: 2gAtU

  A Lifetime ASP.NET DEVELOPER
  • Edited by slimbunny on Jul 23, 2012 05:11 AM
• • Reply
  

  sandippatil4...

  Participant

  1659 Points

  315 Posts

  Re: SQL - Problem converting int to varchar

  Jul 23, 2012 05:35 AM|LINK

  hi

  slimbunny

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

   

  Just Make simple Change in as below

  Stored Procedures naver

  Stored Procedures naver use return key word you need to change from Retun to select

  Use this

  Alter PROCEDURE [dbo].[GeneratePassword] 
        @Length int = 5
      , @newid VARCHAR(150) ='abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789&'
      , @rand REAL ='200' --RETURNS VARCHAR(100)
      , @len int =5
      , @min int = 48
      , @range int = 74
      , @exclude varchar(50) = '0:;<=>?O[]`^\/-_'
      , @output varchar(50)= '' output 
  AS
  
      --Generate Password
      DECLARE @char char
      SET @output = ''
   
      WHILE @len > 0 BEGIN
         SELECT @char = char((rand() * @range + @min))
         IF charindex(@char, @exclude) = 0 
         BEGIN
             SET @output = @output + @char
   
             SET @len = @len - 1
         END
      END
     
      select  @output
  

  " Mark As Answer" If my reply help you --
  
  
  • Edited by sandippatil44 on Jul 23, 2012 05:47 AM
  • Marked as answer by slimbunny on Jul 23, 2012 04:20 PM
• • Reply
  

  Basquiat

  Contributor

  2378 Points

  625 Posts

  Re: SQL - Problem converting int to varchar

  Jul 23, 2012 05:47 AM|LINK

  Is it not this part that is causing the error?

  SELECT @char = char(round(rand() * @range + @min, 0))
  

• • Reply
  

  sandippatil4...

  Participant

  1659 Points

  315 Posts

  Re: SQL - Problem converting int to varchar

  Jul 23, 2012 05:58 AM|LINK

  sandippatil44

  Stored Procedures naver use return key word

  sorry for this Line

  we can use Return keyword in Stored Procedure but

  RETURN [ integer_expression ]

  integer_expression  : s the integer value that is returned. Stored procedures can return an integer value to a calling procedure or an application.

  in case of

  sandippatil44

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

  we need to use  Select keyword to retun value

   

  " Mark As Answer" If my reply help you --
  
  
• • Reply
  

  Mikee1504

  Member

  123 Points

  59 Posts

  Re: SQL - Problem converting int to varchar

  Jul 23, 2012 07:25 AM|LINK

  Previous poster is correct, my version of this is 

  ALTER PROCEDURE [dbo].[GENERATE_STRING]
  @STRINGRET VARCHAR(20) OUTPUT
  AS
  DECLARE @RandomID varchar(32)
  DECLARE @counter smallint
  DECLARE @RandomNumber float
  DECLARE @RandomNumberInt tinyint
  DECLARE @CurrentCharacter varchar(1)
  DECLARE @ValidCharacters varchar(255)
  SET @ValidCharacters = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789'
  DECLARE @ValidCharactersLength int
  SET @ValidCharactersLength = len(@ValidCharacters)
  SET @CurrentCharacter = ''
  SET @RandomNumber = 0
  SET @RandomNumberInt = 0
  SET @RandomID = ''
  SET NOCOUNT ON
  
  SET @counter = 1
  
  WHILE @counter < (5 + 1)
  
  BEGIN
  
          SET @RandomNumber = Rand()
          SET @RandomNumberInt = Convert(tinyint, ((@ValidCharactersLength - 1) * @RandomNumber + 1))
  
          SELECT @CurrentCharacter = SUBSTRING(@ValidCharacters, @RandomNumberInt, 1)
  
          SET @counter = @counter + 1
  
          SET @RandomID = @RandomID + @CurrentCharacter
  
  END
  
  SELECT @STRINGRET = @RandomID

  Just a differnt way of acheiving the same thing
  
  

  If you notice this notice you'll notice this notice is not worth noticing!
  
  • Edited by Mikee1504 on Jul 23, 2012 07:28 AM
• • Reply
  

  slimbunny

  Member

  437 Points

  508 Posts

  Re: SQL - Problem converting int to varchar

  Jul 23, 2012 04:19 PM|LINK

  Thanks sandip,

  Awesome results and thanks much.

  A Lifetime ASP.NET DEVELOPER