Post reply

Converting datatypes in a Stored Procedure

  • March 6, 2006 at 1:43 pm

    #189556

    Hello. I have a question. How do you convert a datatype, for example, from varchar to money in a stored procedure? Anyone seen any examples?

  • March 7, 2006 at 6:35 am

    #625154

    Any reason why somethign like the following wouldnt' work?

    DECLARE @vcvalue varchar(8)

    SELECT @vcvalue = '$1123.45'

    SELECT CONVERT(money, @vcvalue, 0) AS MyvarcharASMoney

    To help us help you read this[/url]For better help with performance problems please read this[/url]

  • March 7, 2006 at 7:06 am

    #625167

    Thanks for the response Luke L. Can you help me? How would I make what you suggest work with what I have here? I'm trying to convert the variable "@Images" to a money type from varchar.

    Stored Procedure

    CREATE PROCEDURE spInsertRecord

    @PostDate datetime,

    @CustNameID varchar(255),

    @Images money

    AS

    BEGIN

    SET NOCOUNT ON

    DECLARE @DataTableID INT

    INSERT tblDataTableTemp

    (

    VolDate,

    CustomerNames,

    ImageItems

    )

    Values

    (

    @PostDate,

    @CustNameID,

    @Images

    )

    SELECT @DataTableID = @@IDENTITY

    SELECT DataTable = @DataTableID

    END

    GO

  • March 7, 2006 at 7:22 am

    #625173

    From you code it looks liek you are accepting a money variable for @images.  So when you call your stored procedure you just need to pass it to the SP as a money datatype.  You can use convert to accomplish this.  If you are instead tryign to write your money variable to your table into a varchar field, you'd want to convert it then.  Either way the syntax is still the same. 

    I'm not really sure at which point yiou wan to convert it.  Can you give me a bit more info?

    Thanks. Luke.

    To help us help you read this[/url]For better help with performance problems please read this[/url]

  • March 7, 2006 at 7:38 am

    #625177

    Thanks Luke L. Well, to begin with, let me show you what I'm doing.

    Form 1: In this form you see I have an input field called "txtImage" which is populated from a recordset. I'm posting the value of this variable to my submit page in the next example.

    <input type="text" border=0 style="border: 1px solid silver; ; text-align:center"  name="txtImage_<%=t_id%>" id="txtImage_<%=t_id%>" tabindex="1" value="<%=FormatNumber(rs_users("FullPaySingles") + rs_users("PartialSingles") + rs_users("Multiples") + rs_users("fldChecksOnly") + rs_users("fldCheckAndList"),0)%>" size="6" maxlength="6">

    Submit Page: Here I'm receiving the variable "txtImage" and then using the stored procedure to post it's value to the table. When I try to do this I get the error: "Error converting data type varchar to numeric.

    ". The datatype of the field in the SQLServer table is set to numeric. I guess the stored procedure is seeing the variable "txtImage" as varchar instead of numeric even though I'm using the "FormantNumber" function in the previous page. This happens only on fields that have their value set to numeric or money. Varchar works just fine. How can I get this to work?

    dim dataConn, adocmd, IsValid, sql, r, sql_updateSQLServer, arr_idsSQLServer

    set dataConn = Server.CreateObject("ADODB.Connection")

    dataConn.Open "Provider=sqloledb;Data Source=REMITCOCLTVOL;Initial Catalog=REMITCOSQLServer;Integrated Security=SSPI;"

    arr_idsSQLServer = Split(Request.Form("ua_id"),", ")

    'now loop through each id, build the sql, and execute the sql

    For Each id in arr_ids

    sql_updateSQLServer = "EXECUTE spInsertRecord  @PostDate='" & CleanInput("txtDated_" & id) & _

         "', @CustNameID='" & CleanInput("txtCustomerName_" & id) & _

         "', @Images='" & CleanInput("txtImage_" & id) & "' "

    dataConn.Execute sql_updateSQLServer

    next

  • March 7, 2006 at 8:05 am

    #625182

    Since I'm not a developer although I know Coldfusion and I'm just beginning to learn VB6, vb.net I'm not sure which language you are using there. 

    However, in T-SQL, Coldfusion and VB there are conversion functions and formatting function.  For example in T-SQL you'd use Convert, however in VB I'd convert my string to a decimal before passing it to the SP using the CDec() command.  That way Sql Server doesn't have to handle the conversion it's already done for you before you execute the SP.  Alternatively you could have the SP accept a varchar @Images and then use the convert statement to change it to dec or money before writing to the tabel using somethign like this...

    CREATE PROCEDURE spInsertRecord

    @PostDate datetime,

    @CustNameID varchar(255),

    @Images varchar(6)

    AS

    BEGIN

    SET NOCOUNT ON

    DECLARE @DataTableID INT

    INSERT tblDataTableTemp

    (

    VolDate,

    CustomerNames,

    ImageItems

    )

    Values

    (

    @PostDate,

    @CustNameID,

    CONVERT(money, @Images, 0) /* or to use a dec field CONVERT(decimal, @Images, 0) */

    )

    SELECT @DataTableID = @@IDENTITY

    SELECT DataTable = @DataTableID

    END

    GO

    To help us help you read this[/url]For better help with performance problems please read this[/url]

  • March 7, 2006 at 8:34 am

    #625206

    Luke L, you are the man!!!!! I'm using vbscript and what you suggested is just what the doctor ordered! Thank you man!

  • March 7, 2006 at 8:37 am

    #625207

    Glad to help

    To help us help you read this[/url]For better help with performance problems please read this[/url]

  • March 7, 2006 at 8:50 am

    #625212

    The statement 'SELECT DataTable = @DataTableID' is not valid syntax.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply