nvarchar(max) problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

nvarchar(max) problem

Author

Topic

Laurens
Starting Member

4 Posts

Posted - 2007-07-03 : 07:18:18

Hi,

I'm having some weird issues that are, I think, caused by the NVARCHAR(MAX).

I've got two SP's that look pretty much alike, both get a number of parameters and insert those into a predefined string (select statement) in the SP like the following:

CREATE PROCEDURE RetrieveEmployees
(
--lots of input parameters
)
AS

DECLARE @sql NVARCHAR(MAX)

SET @sql = '
SELECT *
FROM
(
SELECT e.EmployeeId
e.FirstName,
e.LastName

FROM Employee e

--lots of joins

--bigass where statement

) AS EmployeeInfo
WHERE RowNumber BETWEEN ' + CONVERT(nvarchar(10), @StartRowIndex) + ' AND (' + CONVERT(nvarchar(10), @StartRowIndex) + ' + ' + CONVERT(nvarchar(10), @MaximumRows) + ') - 1'

PRINT LEN(@sql)
PRINT @sql

EXEC sp_executesql @sql

GO

The problem is the whole string becoming longer than 4000 characters.. however since I'm using an NVARCHAR(MAX) I would guess that shouldn't be a problem?

The result (and the actual problem) is the following:
Result of SP 1:
Length = 3999 (length of dynamic string)
ERROR (he only wants to execute 3999 characters of a ~4100 char long string, so he is executing bad sql and gets an error on syntax)

Result of SP 2:
Length = 4092
RESULT (sp works without a problem)

I can't see any difference between the two SP's, yet one works and one doesn't.
Any thoughts on the subject?

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-07-03 : 07:26:49

my first question would be, do you REALLY, REALLY, REALLY need to use Dynamic SQL?

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Laurens
Starting Member

4 Posts

Posted - 2007-07-03 : 07:34:31

Unfortunatly, yes.

DaleJ
Starting Member

7 Posts

Posted - 2007-07-03 : 08:14:40

You say "pretty much alike"....so, there must be some (all be it small) difference.

have you tried splitting up the set operation?

set @sql = N'.....part 1......'
set @sql = @sql + N'.....part 2.....'

Laurens
Starting Member

4 Posts

Posted - 2007-07-03 : 08:21:07

There are differences but mainly in the parameters and the joins, nothing that would influence this behavior I think.

And yes it does work when I put different parts in seperate variables and then concat them together, but I'd prefer to have it in one string.

Kristen
Test

22859 Posts

Posted - 2007-07-03 : 08:22:09

Perhaps something you are concatenating is implicitly forcing the CAST to Varchar rather than Varchar(MAX) ?

Kristen

Laurens
Starting Member

4 Posts

Posted - 2007-07-03 : 08:41:52

There are a lot of '...' + CONVERT(nvarchar(10), @param) + '...' concats in the sp, but both SP's have them.

Is there a way to get the type of a parameter so I can check at the end of the concat which type my @sql parameter is ?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources