Maximum length of VARCHAR

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Chandler Chao (ChandlerChao_at_discussions.microsoft.com)
Date: 10/20/04 

Date: Wed, 20 Oct 2004 09:19:07 -0700

I'm having trouble exceeding 256 characters in a variable I declare in my
stored procedure. I've included the stored procedure below as well as the
output. The reason that I'm writing my sp this way is that the input
variable, @LeadList, is a list of comma seperated numbers. It is the @SQL
variable that does not seem to want to exceed 256 characters no matter how
large I declare it. Could someone help me out?

----------------------------------------------------------------------------------
CREATE PROCEDURE sp_Web_GetDocs
        @LeadList varchar(256)
AS
BEGIN
DECLARE @SQL varchar(600)

SET @SQL = CONVERT(VARCHAR(600),'SELECT Documents.FileName,
Documents.FilePath, LeadAttachments.LeadID, Leads.Subject, Leads.Event,
Documents.Description
FROM LeadAttachments INNER JOIN Documents ON LeadAttachments.DocumentID =
Documents.DocumentID
INNER JOIN Leads ON LeadAttachments.LeadID = Leads.LeadID
INNER JOIN DocumentTypes ON DocumentTypes.DocumentTypeID =
Documents.DocumentTypeID W
HERE DocumentTypes.FileExtension <>''RTF''AND LeadAttachments.LeadID IN
('+@LeadList + ')
ORDER BY Leads.Subject DESC, DocumentTypes.PrintPriority DESC')

SELECT @SQL
--EXEC (@SQL)
END
GO

----------------------------------------------------------
Output

SELECT Documents.FileName, Documents.FilePath, LeadAttachments.LeadID,
Leads.Subject, Leads.Event, Documents.Description
FROM LeadAttachments INNER JOIN Documents ON LeadAttachments.DocumentID =
Documents.DocumentID
INNER JOIN Leads ON LeadAttachments.

Relevant Pages

  • Re: Nested Iif in View / Stored Procedure
    ... my stored procedure will finally save. ... functions are only allowed on output fields of the record ... have the word OUTPUT when I declare each variable and the ... >FROM Device d INNER JOIN Day ON ...
    (microsoft.public.access.adp.sqlserver)
  • RE: Trying to creatively work around 8000 Character limit with sp_exec
    ... declare @str2 varchar ... > trigger to dynamically re-create a stored procedure within our system. ... > than 8000 characters, contrary to what I have read on this newsgroup ... > to actually execute the output. ...
    (microsoft.public.sqlserver.programming)
  • Convert Varchar(13) to Integer
    ... Stored Procedure, to strip off the KB characters output ... DECLARE database_table_name CURSOR ...
    (microsoft.public.sqlserver.programming)
  • Re: stored proc code always returns null?
    ... described by "but when i call this from inside another stored procedure ... >now the odd thing about this procedure, if the parameter of @tiercode is ... >declare @lastEffectiveDate as datetime ... >RetroContractCountsTierData.TierCode as TierCode from (retrocontractcounts ...
    (microsoft.public.sqlserver.programming)
  • Re: procedure tuning
    ... Pro SQL Server 2000 Database Design - ... >>> INNER JOIN tblVRM ... >>> INNER JOIN tblFDVSourceSurvey ... I am having problems re-writting a stored procedure to run quicker, ...
    (microsoft.public.sqlserver.programming)