Over coming 4000 char limit

In SQL 2000 the limit is 4000 (nvarchar), in SQL 2005 there is no limit : varchar(MAX).

You could test the lenght of your variable and when you are reaching the limit continue in a new variable and then concatenate the result:

EXEC (@sProcText1 + @sProcText2)
will work

... something else. If not using any special characters, use varchar() data types instead of nvarchar() !
If you have a nvarchar(100) data type, t allows you to store 50 characters (2 bytes per char)
If you have a varchar(100) data type, it allows you to store 100 characters (1 byte per char)

But as mahopman said, in SQL 2005 varchar(max) makes sure the length is not an issue any more ... you can sore up tot 2GB of data in a field of the varchar(max) data type.

Hope this helps ...

some tidbit info for you

SET @sProcText = '
CREATE VIEW VIEW_EMPLOYEE_CHECK_COMPOSITE AS
SELECT E.Employee_ID,
E.Employee_Name,
CK.Department_Code,'

is far more readable.  SQL server can handle linebreaks in strings.

When using SQL 2000 it´s possible to execute statements dynamicly that are over NVARCHAR(4000) or VARCHAR(8000) by using the EXEC statement. Simply declare multiple variables of the type NVARCHAR(4000) or VARCHAR(8000) and concatenate them when executing:

EXEC (@sProcText1 + @sProcText2 + @sProcText3 + ...)

so... if this is my code, and I need to split it due to size - what exactly would the altered text be (assume I break it at "midlname"
Would I rename the 2nd half as sproctext3 (see execution lines at end)
Plz cut/paste exactly how'd it go THX!!

SET @sProcText = @sProcText + 'CREATE VIEW VIEW_EMPLOYEE AS '+ @sCRLF
SET @sProcText = @sProcText + 'SELECT '+ @sCRLF
SET @sProcText = @sProcText + 'E.FRSTNAME AS FirstName,'+ @sCRLF
SET @sProcText = @sProcText + 'E.LASTNAME AS LastName,'+ @sCRLF
SET @sProcText = @sProcText + 'E.MIDLNAME AS Middle,'+ @sCRLF
SET @sProcText = @sProcText + 'E.JOBTITLE AS Job_Title,'+ @sCRLF
SET @sProcText = @sProcText + 'E.BRTHDATE AS Birth_Date,'+ @sCRLF
SET @sProcText = @sProcText + 'FROM '+ @sCRLF
SET @sProcText = @sProcText + 'UPR00100 E '+ @sCRLF
SET @sProcText = @sProcText + 'JOIN UPR00102 A ON E.EMPLOYID = A.EMPLOYID AND (A.ADRSCODE LIKE ''PR%'' OR A.ADRSCODE LIKE ''MA%'')'+ @sCRLF
SET @sProcText = @sProcText + 'JOIN ETHNIC_GROUP EG ON E.ETHNORGN = EG.ETHNIC_GROUP_CODE '+ @sCRLF

SET @sProcText = @sProcText + ' '+ @sCRLF
SET @sProcText2 = 'USE '+@sDatabaseName + @sCRLF + 'EXECUTE dbo.sp_executesql @sProcText'
EXECUTE dbo.sp_executesql @sProcText2 , N'@sProcText nvarchar(4000)', @sProcText
PRINT('CREATE View for Database '+@sDatabaseName+' Function : VIEW_EMPLOYEE' )

More specifically - if I rname some of the lines to sProcText3
and concatenante as said... I get a Syntax Error... below is the before and after:

BEFORE:
SET @sProcText2 = 'USE '+@sDatabaseName + @sCRLF + 'EXECUTE dbo.sp_executesql @sProcText
EXECUTE dbo.sp_executesql @sProcText2 , N'@sProcText nvarchar(4000)', @sProcText

AFTER:
SET @sProcText2 = 'USE '+@sDatabaseName + @sCRLF + 'EXECUTE dbo.sp_executesql (@sProcText+@sProcText3)
EXECUTE dbo.sp_executesql @sProcText2 , N'@sProcText nvarchar(4000)', @sProcText

Ohhh - almost there!
I am creating a view in this example - I don't know if that effects it
I am getting an error that says :

Server: Msg 111, Level 15, State 1, Line 2
'CREATE VIEW' must be the first statement in a query batch.

ANy ideas ??
The first segment starts off "Create view..."
The 2nd 1/2 obviously doesn't have that wording, snce its a continuation of the view...

Seems like if I don't use the .sp_executesql I get that error - even when I take the concatenation off...
Sorry this is becoming such a pain - I REALLY appreciate your help

SET @sProcText = '
CREATE VIEW VIEW_EMPLOYEE AS 
SELECT 
E.FRSTNAME AS FirstName,
E.LASTNAME AS LastName,
E.MIDLNAME AS Middle,
E.JOBTITLE AS Job_Title,
E.BRTHDATE AS Birth_Date,'
SET @sProcText2 = '
FROM 
UPR00100 E 
JOIN UPR00102 A ON E.EMPLOYID = A.EMPLOYID
  AND (A.ADRSCODE LIKE ''PR%'' OR A.ADRSCODE LIKE ''MA%'')
JOIN ETHNIC_GROUP EG ON E.ETHNORGN = EG.ETHNIC_GROUP_CODE'
 
SET @sProcText3 = '
USE '+@sDatabaseName + '
GO
' -- extra line here after GO
 
EXEC( @sProcText3 + @sProcText1 + @sProcText2 )
PRINT('CREATE View for Database '+@sDatabaseName+' Function : VIEW_EMPLOYEE' )

Select allOpen in new window

Sooo close
One error =
Server: Msg 137, Level 15, State 2, Line 2
Must declare the variable '@sProcText3'.


I do have, at the top:
DECLARE @sProcText3 nvarchar(4000),
@sProcText2 nvarchar(4000),
@sProcText nvarchar(4000),
@sCRLF char(2),
@sTAB char(1)

SET @sTAB = char(9)
SET @sCRLF = char(13) + char(10)

can you please paste the entire SQL again please?
@sProcText3 is clearly defined at the top already.. are you sure it's not quoted somewhere it shouldn't be, like

EXEC( '@sProcText3' + @sProcText1 + @sProcText2 )
?

I REALLY appreciate this !!
Here's the whole enchilada
The Code Snippet attached gives me the Error:
Must declare the variable '@sProcText3'

If I change the quotes on the line before the GO to:
SET @sProcText3 = ' USE BSOL9
GO
'
I get this error:

Server: Msg 105, Level 15, State 1, Line 78
Unclosed quotation mark before the character string ' USE BSOL9
'.
Server: Msg 170, Level 15, State 1, Line 78
Line 78: Incorrect syntax near ' USE BSOL9
'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '

EXEC( @sProcText3 + @sProcText + @sProcText2 )
PRINT('.
Server: Msg 111, Level 15, State 1, Line 4
'CREATE VIEW' must be the first statement in a query batch.
Server: Msg 105, Level 15, State 1, Line 4
Unclosed quotation mark before the character string ' )

DECLARE @sProcText3 nvarchar(4000),
@sProcText2 nvarchar(4000),
@sProcText nvarchar(4000),
@sCRLF char(2),
@sTAB char(1)
 
SET @sTAB = char(9)
SET @sCRLF = char(13) + char(10)
 
 
SET @sProcText = ''
SET @sProcText = @sProcText + 'if exists (select * from dbo.sysobjects where id = object_id(N''[dbo].[VIEW_EMPLOYEE_CHECK_DEPT]'') and OBJECTPROPERTY(id, N''IsView'') = 1)' + @sCRLF
SET @sProcText = @sProcText + 'drop view [dbo].[VIEW_EMPLOYEE_CHECK_DEPT] ' + @sCRLF
SET @sProcText2 = 'USE BSOL9' + @sCRLF + 'EXECUTE dbo.sp_executesql @sProcText'
EXECUTE dbo.sp_executesql @sProcText2 , N'@sProcText nvarchar(4000)', @sProcText
PRINT('Drop VIEW for Database BSOL9 View : VIEW_EMPLOYEE_CHECK_DEPT' )
 
SET @sProcText ='
CREATE VIEW VIEW_EMPLOYEE_CHECK_DEPT AS 
SELECT W.EMPLOYID AS Employee_ID,
ISNULL(dbo.FN_GET_DEPARTMENT_CODE(W.DEPRTMNT),dbo.FN_GET_DEPARTMENT_CODE(MAX(E.DEPRTMNT))) AS Department_Code,
ISNULL(dbo.FN_GET_DEPARTMENT(W.DEPRTMNT),dbo.FN_GET_DEPARTMENT(MAX(E.DEPRTMNT))) AS Department,
dbo.FN_GET_EMP_ACCOUNT(W.EMPLOYID) AS Account#, 
MAX(SUBSTRING(E.SOCSCNUM,1,3) + ''-'' + SUBSTRING(E.SOCSCNUM,4,2) + ''-'' + SUBSTRING(E.SOCSCNUM,6,4)) AS SSN,
W.CHEKNMBR AS Check_Number,
MAX(SUBSTRING(W.EMPLOYID, 3, 3)) AS Client_Code,
dbo.FN_GET_EMP_CLIENT_NAME(W.EMPLOYID) AS Client_Name,'
 
SET @sProcText2 = '
ISNULL(dbo.FN_EMP_AMT(W.EMPLOYID, ''SICK EARNINGS'', W.CHEKDATE, W.DEPRTMNT, W.CHEKNMBR,''1''),0) AS Sick,
ISNULL(dbo.FN_EMP_AMT(W.EMPLOYID, ''HOLIDAY EARNINGS'', W.CHEKDATE, W.DEPRTMNT, W.CHEKNMBR,''1''),0) AS Holiday,
ISNULL(dbo.FN_EMP_AMT(W.EMPLOYID, ''REGULAR'', W.CHEKDATE, W.DEPRTMNT, W.CHEKNMBR,''1''),0) AS Regular,
ISNULL(dbo.FN_EMP_AMT(W.EMPLOYID, ''401K LOAN'', W.CHEKDATE, W.DEPRTMNT, W.CHEKNMBR,''2''),0) AS K401_Loan,
ISNULL(dbo.FN_EMP_AMT(W.EMPLOYID, ''401K LOAN2'', W.CHEKDATE, W.DEPRTMNT, W.CHEKNMBR,''2''),0) AS K401_Loan2,
ISNULL(dbo.FN_GET_EMP_PAYTYPE_AMT(W.EMPLOYID,''1'', W.CHEKDATE, W.DEPRTMNT, W.CHEKNMBR),0) AS Gross 
 FROM UPR30300 W INNER JOIN UPR00100 E ON W.EMPLOYID = E.EMPLOYID 
 GROUP BY W.DEPRTMNT, W.EMPLOYID, W.CHEKDATE, W.CHEKNMBR'
 
SET @sProcText3 =  'USE BSOL9' 
GO
 
EXEC( @sProcText3 + @sProcText + @sProcText2 )

Select allOpen in new window

Remove the 'Go' from line 40.
After each 'Go' you have to declare every parameter again as you start a new batch from that point on.

Hope this helps ...

and if that doesn't do it, then just
SET @sProcText3 =  'USE BSOL9'
without the GO should also work

Not quite :(
I am getting an error w/ the GO at the end=
Line 2: Incorrect syntax near 'GO'.
'CREATE VIEW' must be the first statement in a query batch.

Then - if I try w/o the GO, I get:
'CREATE VIEW' must be the first statement in a query batch.

I am stuck w/ SQL 2000

Putting the database there gives me the error:

'CREATE VIEW' does not allow specifying the database name as a prefix to the object name.

I know I must be close - I appreciate your help/perseverence...
Is this possible?

I changed the NvarChar(4000)  to varchar(8000)
Then changed the statement to one long vs. two to concatenante
added back the GO line and voila! It worked
BUT
My drop line gives me an error - I am deducing its looking for an "n" type varchar (based on the error) - what do I change on the attached DROP statement to make it work ? Then I am done :)
THXXXX
ERROR=
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.

SET @sProcText = ''
SET @sProcText = @sProcText + 'if exists (select * from dbo.sysobjects where id = object_id(N''[dbo].[VIEW_EMPLOYEE_CHECK_DEPT]'') and OBJECTPROPERTY(id, N''IsView'') = 1)' + @sCRLF
SET @sProcText = @sProcText + 'drop view [dbo].[VIEW_EMPLOYEE_CHECK_DEPT] ' + @sCRLF
SET @sProcText2 = 'USE BSOL9' + @sCRLF + 'EXECUTE dbo.sp_executesql @sProcText'
EXECUTE dbo.sp_executesql @sProcText2 , N'@sProcText nvarchar(4000)', @sProcText
PRINT('Drop VIEW for Database BSOL9 View : VIEW_EMPLOYEE_CHECK_DEPT' )

Select allOpen in new window

Thanks for your perseverence - I got it to run finally...

From BOL:

sp_executesql [ @stmt = ] stmt
[
    {, [@params=] N'@parameter_name data_type [ OUT | OUTPUT ][,...n]' }
     {, [ @param1 = ] 'value1' [ ,...n ] }
]
 

Arguments
[ @stmt = ] stmt
Is a Unicode string that ...

So it has to be nvarchar() ...

Hope this helps ...