none
How to concatenate N Prefix to a parameter in sqlserver

    Question

  • How we can concatenate N-Prefix to sqlparameter,i am trying to insert japanese characters into the DB.if i tried like below it's working.

    1.  insert into japanese (a,b) values (1,N'C川崎市幸区');

    above is working fine.
    if i tried to execute the same by appending to the parameter,it's not working

    2.declare @msg nvarchar(max)

    set @msg= 'C川崎市幸区'

    DECLARE @SQLString nvarchar(500);

    SET @SQLString =

         'insert into japanese (a,b)values(1,@msg)'

    exec SQLString

    Can any one please let me know how i can append N prefix to the sqlparameter


    Sivananda Vanukuri
    Monday, November 16, 2009 9:57 AM

Answers

  • Wow, I found the solution:

    Into your DotNet project, use adLongVarWChar as the type of your parameter. adLongVarWChar (or any with W) corresponds to a null-terminated Unicode character string.

    Set par = cmd.CreateParameter("@Content", adLongVarWChar, adParamInput, 2147483647, content)

    Cheers

    Deza

    Friday, September 24, 2010 5:09 PM

All replies

  • declare @msg nvarchar(max)
    set @msg= 'C川崎市幸区'
    DECLARE @SQLString nvarchar(500);
    SET @SQLString = 'insert into japanese (a,b)values(1,N''' + @msg + ''')'
    
    exec (@SQLString )
    

    every day is a school day
    Monday, November 16, 2009 10:11 AM
    Moderator
  • when i tried into like this .. values are converting to C?????. All japanese characters into the '?'

    insert into japanese (a,b)values(1,N'C?????')

    finally value is getting inserted as C??????


    Sivananda Vanukuri
    Monday, November 16, 2009 10:16 AM
  • You will need to specify that the value of @msg is unicode:

    set @msg= N'C川崎市幸区'


    http://support.microsoft.com/default.aspx/kb/239530


    every day is a school day
    Monday, November 16, 2009 10:29 AM
    Moderator
  • i am passing value from dotnet to a storedprocedure through the parameter. how can i prefix N to a parameter values.if i concatenate and send to db it is taking as string.


    Sivananda Vanukuri
    Tuesday, November 17, 2009 7:28 AM
  • Sivananda, did you found a solution? I have the same problem.

     

    TIA

    Deza

    Friday, September 24, 2010 4:39 PM
  • Can you post your .NET code and SQL Stored procedure code? I think you need to just properly specify the type of the parameter when you define the parameter. And don't use AddWithValue.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Friday, September 24, 2010 4:49 PM
    Moderator
  • Wow, I found the solution:

    Into your DotNet project, use adLongVarWChar as the type of your parameter. adLongVarWChar (or any with W) corresponds to a null-terminated Unicode character string.

    Set par = cmd.CreateParameter("@Content", adLongVarWChar, adParamInput, 2147483647, content)

    Cheers

    Deza

    Friday, September 24, 2010 5:09 PM
  • See this link http://msdn.microsoft.com/en-us/library/bb399384.aspx part starting with 

    Using Large Value Type Parameters

    Large value types can be used in SqlParameter objects the same way you use smaller value types in SqlParameter objects. You can retrieve large value types as SqlParametervalues, as shown in the following example. The code assumes that the following GetDocumentSummary stored procedure exists in the AdventureWorks sample database. The stored procedure takes an input parameter named @DocumentID and returns the contents of the DocumentSummary column in the @DocumentSummary output parameter.

    CREATE PROCEDURE GetDocumentSummary 
    (
      @DocumentID int,
      @DocumentSummary nvarchar(MAX) OUTPUT
    )
    AS
    SET NOCOUNT ON
    SELECT @DocumentSummary=Convert(nvarchar(MAX), DocumentSummary)
    FROM  Production.Document
    WHERE  DocumentID=@DocumentID
    

    Example

    The ADO.NET code creates SqlConnection and SqlCommand objects to execute the GetDocumentSummary stored procedure and retrieve the document summary, which is stored as a large value type. The code passes a value for the @DocumentID input parameter, and displays the results passed back in the @DocumentSummary output parameter in the Console window.

    Private Function GetDocumentSummary( _
     ByVal documentID As Integer) As String
    
      ' Assumes GetConnectionString returns a valid connection string.
      Using connection As New SqlConnection(GetConnectionString())
        connection.Open()
        Dim command As SqlCommand = connection.CreateCommand()
    
        ' Setup the command to execute the stored procedure.
        command.CommandText = "GetDocumentSummary"
        command.CommandType = CommandType.StoredProcedure
    
        ' Set up the input parameter for the DocumentID.
        Dim paramID As SqlParameter = _
          New SqlParameter("@DocumentID", SqlDbType.Int)
        paramID.Value = documentID
        command.Parameters.Add(paramID)
    
        ' Set up the output parameter to retrieve the summary.
        Dim paramSummary As SqlParameter = _
          New SqlParameter("@DocumentSummary", _
            SqlDbType.NVarChar, -1)
        paramSummary.Direction = ParameterDirection.Output
        command.Parameters.Add(paramSummary)
    
        ' Execute the stored procedure.
        command.ExecuteNonQuery()
        Console.WriteLine(paramSummary.Value)
        Return paramSummary.Value.ToString
      End Using
    End Function


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Friday, September 24, 2010 5:21 PM
    Moderator