concatenate text to TSQL | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

concatenate text to TSQL

hi – I used to use variables in SP as follows: CREATE PROCEDURE [dbo].[MyProc] (@Crit nvarchar(4000)=”)
AS
Begin
SET NOCOUNT ON;
declare @SQL varchar(8000)
SET @SQL=’SELECT * FROM ITEMS’
if NOT (@Crit=” OR @Crit IS NULL) SET @SQL=@SQL + ‘ Where ‘ + @Crit
execute (@SQL)
End
and it is working fine as the criteria is optional, but due to the text limit I had to use direct cursor as follows CREATE PROCEDURE [dbo].[MyProc] (@Crit nvarchar(4000)=”)
AS
Begin
SET NOCOUNT ON;
SELECT * FROM ITEMS
End – it is working fine except that I am not able to add criteria part which is optional.
Please help.
Hello tarekj, What version of SQL Server are you using? If that is 2005, you may use nvarchar(max) otherwise if it is versions 7 or 2000, you are limited to 8000. You may also want to use some parameter lists…check on sp_executesql.
Hi Jon,
it is SQL Server 2005, I found nvarchar(max) also is limited to 8000, what do you suggest?
Hello tarekj, What version of SQL Server are you using? If that is 2005, you may use nvarchar(max) otherwise if it is versions 7 or 2000, you are limited to 8000. You may also want to use some parameter lists…check on sp_executesql.
nvarchar(MAX) is almost certainly not limited to 8000 characters What is happening is that you are assigning the result of a concatenation of a varchar(8000), a string constant, and an nvarchar(MAX) to a variable of type varchar(8000) which you then pass on to the EXEC() function. Try to be consistent in your choice of data types, as you’ll see that you can use more then 8000 characters.
FOUND IT…
it was due the limitation of varchar(Max), I had to cast the concatenated variable to (MAX):
Limitation of varchar(Max)
———————————————————-
1) If a variable hold more than 8000 character. But in print option it display only 8000 character. We should use select statement instead of print.
2) If you add a literal value in a variable of varchar(max) datatype without casting. It will only store 8000 character. See the below example. Declare @tst Varchar(max)
Set @tst = space(7998) + ‘test’
Select len(@tst) Output
———
8000 But when you add literal value after casting, it can hold more than 8000 characters. See the below example. Set @tst = space(7998) + CAST(‘test’ as varchar(max))
Select len(@tst)
Output
——–
8002

]]>

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |