Re: Defining a concatenated column size

From: Hugo Kornelis (hugo_at_pe_NO_rFact.in_SPAM_fo)
Date: 06/18/04

  • Next message: anonymous_at_discussions.microsoft.com: "Re: Using Stored Procedure returned fields in another Stored Proc or Query" 
    Date: Fri, 18 Jun 2004 22:06:57 +0200

    On Fri, 18 Jun 2004 07:49:01 -0700, Kirk wrote:

    >I have a column within a view that is a concatenation of several different columns of another table. It goes something like: tblA.columnA + '-' tblA.columnB + '-' tblA.columnC + '-' tblA.columnD As 'NewColumn'
    >
    >Is it possible to define the size of the 'NewColumn' within the view? I have a stored procedure that is going to create a temp table based on this view, but when I execute the stored procedure I get the following error:
    >
    >Line 1: Incorrect syntax near '*'.
    >
    >In looking at the stored procedure code, the error is a result of when the create table occurs, it defines the NewColumn as nvarchar(*), which is causing the error.
    >
    >Any help would be appreciated.
    >
    >Thanks.

    Hi Kirk,

    Define the column as nvarchar(4000) (the maximum length). Or, if you want
    the max. length to be shorter, use a lower number. The result of the
    concatenation will be trimmed to the max. length the column holds (and you
    may also choose to trim explicitly, using LEFT or CAST function).

    Best, Hugo 

    -- 
(Remove _NO_ and _SPAM_ to get my e-mail address)
  • Next message: anonymous_at_discussions.microsoft.com: "Re: Using Stored Procedure returned fields in another Stored Proc or Query"

    Relevant Pages

    • Defining a concatenated column size
      ... I have a stored procedure that is going to create a temp table based on this view, but when I execute the stored procedure I get the following error: ... In looking at the stored procedure code, the error is a result of when the create table occurs, it defines the NewColumn as nvarchar, which is causing the error. ...
      (microsoft.public.sqlserver.mseq)
    • Re: SQLserver and the WHERE x IN y
      ... to a stored procedure - that would only work if you introduced subtyping into ... the temp table from the calling procedure (the temp table is still in scope, ... INSERT INTO #MyTempTable EXECUTE YourPopulateProc 123 ... INSERT INTO @MyTableVar VALUES ...
      (microsoft.public.sqlserver.programming)
    • Re: How to navigate recursive stored procedures results
      ... connection is inherited by the next user, ... beforehand so any residual #Temp tables would be dropped. ... the identity is not "flow"ing to the SQL Server - ... union a SQL query and the results of a stored procedure. ...
      (microsoft.public.dotnet.framework.adonet)
    • Re: Recordset error when executing stored procedure (#3704)
      ... This reason described in next KB. ... > procedure allows the recordset to work when returning values from a SQL ... >> When I run the stored procedure in Query Analyzer ("EXEC ... >> The stored procedure should be returning data from a temp table in SQL ...
      (microsoft.public.vb.database.ado)
    • Re: Error running Stored Procedures
      ... Here is the code that I am using in the ASP code: ... The temp tables are required due to the nature of the complexity of the queries that are being used within the application and stored procedures. ... > within a session. ... > have ASP call one stored procedure, ...
      (microsoft.public.sqlserver.programming)