Dynamic SQL variable length 1

[wbodger]

I have to, upon occasion, create views for our statisticians that follow some general rules, so I created a script to help with this. Below is one instance of this script

use dbEDRN115

Declare @sql2 varchar(8000);
--Here you need to name the view you want to create
select @sql2 = 'Create VIEW dbo.vwParticipantC AS' + char(13)
select @sql2 = @sql2 + 'SELECT '

DECLARE @NewCol varchar(75), @SASName varchar(12), @formversion char(4)

-- This creates a very quick forward-only cursor to loop thru the recordset from the imported 
-- data to build the columns in the new view. SAS variable was added to vwStatisticianVariables
-- as an agreed upon naming convention that would allow easy lookup for the statisticians. As an
-- added bonus, this process will catch any duplicated rows

DECLARE VariableNames CURSOR FAST_FORWARD
FOR
SELECT ('['+rtrim(tblname)+'].['+rtrim(variablename)+']') AS NewCol, SAS_Variable, max(formversion) 
FROM vwStatisticianVariables
where (rtrim(tblname)+'.'+rtrim(variablename)) IN 
	(SELECT (rtrim(tblname)+'.'+rtrim(variablename)) FROM tblvwParticipantCFields) --This is the table of imported tables and columns from Deanna
GROUP BY ('['+rtrim(tblname)+'].['+rtrim(variablename)+']'), SAS_Variable

OPEN VariableNames
FETCH NEXT FROM VariableNames
INTO @NewCol, @SASName, @formversion

WHILE @@FETCH_STATUS = 0

BEGIN

select @sql2 = @sql2 + @NewCol+' AS ' + @SASName + ','

FETCH NEXT FROM VariableNames
INTO @NewCol, @SASName, @formversion

END

CLOSE VariableNames

DEALLOCATE VariableNames

--Done with the cursor, now any derived columns and the joins. Not sure how nicely this part will automate.
--There are issues with the identified PKs, so as of now 3/14/12 This part is not automated.

select @sql2 = @sql2 + 'getdate() AS AccessDate '
select @sql2 = @sql2 + 'FROM (SELECT * FROM dbo.tblParticipantC WHERE deleteflag=0 and entryglag=1) AS tblParticipantC'
select @sql2 = @sql2 + '	JOIN (SELECT * FROM dbo.tblClinicalCaseC WHERE deleteflag=0) AS tblClinicalCaseC ON tblParticipantC.[Study_Participant-ID_Text] = tblClinicalCaseC.[Study_Participant-ID_Text] AND tblParticipantC.Study_Cite_ID=tblClinicalCaseC.study_Site_ID'
select @sql2 = @sql2 + '	JOIN (SELECT * FROM dbo.TBLCLINICALCNTLHRC WHERE deleteflag=0) AS TBLCLINICALCNTLHRC ON tblParticipantC.[Study_Participant-ID_Text] = TBLCLINICALCNTLHRC.[Study_Participant-ID_Text] AND tblParticipantC.Study_Cite_ID=TBLCLINICALCNTLHRC.study_Site_ID'
select @sql2 = @sql2 + '	JOIN (SELECT * FROM dbo.TBLCLINICALCNTLOCC WHERE deleteflag=0) AS TBLCLINICALCNTLOCC ON tblParticipantC.[Study_Participant-ID_Text] = TBLCLINICALCNTLOCC.[Study_Participant-ID_Text] AND tblParticipantC.Study_Cite_ID=TBLCLINICALCNTLOCC.study_Site_ID'
select @sql2 = @sql2 + '	JOIN (SELECT * FROM dbo.TBLFOLLOWUPCNTLHRC WHERE deleteflag=0) AS TBLFOLLOWUPCNTLHRC ON tblParticipantC.[Study_Participant-ID_Text] = TBLFOLLOWUPCNTLHRC.[Study_Participant-ID_Text] AND tblParticipantC.Study_Cite_ID=TBLFOLLOWUPCNTLHRC.study_Site_ID'
select @sql2 = @sql2 + '	JOIN (SELECT * FROM dbo.TBLHRCNTRLTOCASEC WHERE deleteflag=0) AS TBLHRCNTRLTOCASEC ON tblParticipantC.[Study_Participant-ID_Text] = TBLHRCNTRLTOCASEC.[Study_Participant-ID_Text] AND tblParticipantC.Study_Cite_ID=TBLHRCNTRLTOCASEC.study_Site_ID'

--print @sql2
Exec(@sql2)

Now, the script works great everyplace EXCEPT for this instance, where there are 170 columns in the view and, if it could finish, more than 8,000 characters in the view creation script. I do have to stick with the long table and variable (columns really, but this is for SAS folks) names, so am looking for some way to get past 8,000 characters.

Thoughts, suggestions, magical beans?

wb

 

[markros]

Are you using SQL 2000? Because otherwise you can use varchar(max) instead.

PluralSight Learning Library

 

[born2program]

Hi WB, Declaring the variable as varchar(max) will get you past the 8,000 character/byte restriction. varchar(max) allows up to 2 GB of data in the field/variable.

 

[bborissov]

Not sure that with EXEC(@sql2) you could past over the 8K limit.
You could use EXEC sp_executesql @sql2. But you should declare your variable as nvarchar(max)

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[wbodger]

Ahh... varchar(max). I am on 2008 r2, so I will give it a shot!

Thx!!
wb