Dynamic SQL with more than 4000 char

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)

Dynamic SQL with more than 4000 char

Author

Topic

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-09-08 : 22:31:36

How does one split a dynamic sql if longer than 4000 the max for nvarchar? sp_Executesql does seem to take varchar which is 8000 and that would be fine:)

I currently have something like this.

ALTER PROCEDURE [dbo].[TotalDuration]

@SiteName nvarchar(50),
@EventType nvarchar(50),
@DateRange nvarchar(30),
@DBName nvarchar (100)

AS

SET NOCOUNT ON
Declare @sql nvarchar(max)
Declare @ParamDefinition AS nvarchar(1000)
Select @sql=''
Select @sql=@sql+ 'SELECT blah blah blah.......
' from sys.databases where name='VC' or name like 'VCA%'

Set @ParamDefinition =
' @SiteName nvarchar(50),
@EventType nvarchar(50),
@DateRange nvarchar(30),
@DBName nvarchar (100)'

Create table #t([Days] int, [Hours] int, [Minutes] int, [Seconds] int, [TSec] int, [DatabaseName] nvarchar(50), [EventCount] int)
Insert into #t

Execute sp_Executesql @sql, @ParamDefinition, @SiteName, @EventType, @DateRange, @DBName
Print @sql

Select [Days], [Hours], [Minutes], [Seconds], [TSec], [DatabaseName], [EventCount]
From #t

Drop Table #t

SET NOCOUNT OFF

Return

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-09-08 : 22:59:38

if you need more chars you can do this:

EXEC(@sql1 + @sql2 + @sql3 + ...)

where each of @sql is nvarchar(4000)

However I don't see the need for dynamic sql here at all. why use it?

elsasoft.org

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-09-08 : 23:27:00

Thanks jezemine but I a not sure if this helps as I dont think I can split the statement up plus I am using sp_Executesql which I dont think your suggestion works with? I started using exec only but run into problems with datatype definitions. Also exec can use varchar and hence 8000 chars. I seem to be always swapping one problem for another:) Here is a complete working code example, I have many stored procedures and this is just one of many. I am a beginner so my idea was to develop the sql I needed and then put a dyanmic sql wrapper around each piece of code to run against 1 to x databases. I would prefer not to use dyanmic sql if at all possible. Do you have a better way of looping through the databases and storing the result into one result set for reporting services? I know the databases will be named like I have used in the like statement but do not know how many there will be other than returning a query to find out I guess:) This example works as it is less than 4000 chars but others I have are around 5800 chars.

USE [VC]
GO
/****** Object: StoredProcedure [dbo].[TotalDuration_MTS] Script Date: 09/08/2008 10:40:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[TotalDuration_MTS]

@SiteName nvarchar(50),
@EventType nvarchar(50),
@StartDateRange DateTime,
@EndDateRange DateTime,
@DBName nvarchar (100)

AS

SET NOCOUNT ON
Declare @sql AS nvarchar (max)
Declare @ParamDefinition AS nvarchar(1000)
SELECT @sql=''
SELECT @sql=@sql +'SELECT Days, Hours, Minutes, Seconds, TSec, '''+name+''' AS DatabaseName, EventCount
FROM (SELECT SUM(TotalSeconds) / 86400 AS Days, SUM(TotalSeconds) % 86400 / 3600 AS Hours, SUM(TotalSeconds) % 3600 / 60 AS Minutes, SUM(TotalSeconds) % 60 AS Seconds, Sum(TotalSeconds) AS TSec, Count(EventID) AS EventCount
FROM (SELECT DateLimit.EventID, DATEDIFF(second, DateLimit.StartTime, DateLimit.EndTime) AS TotalSeconds
FROM (SELECT EventID,
CASE
WHEN EventDetails.EventStartTime < @StartDateRange THEN @StartDateRange
WHEN EventDetails.EventStartTime IS NULL THEN @StartDateRange
ELSE EventDetails.EventStartTime
END AS StartTime,
CASE
WHEN EventDetails.EventEndTime > @EndDateRange THEN @EndDateRange
WHEN EventDetails.EventEndTime IS NULL THEN @EndDateRange
ELSE EventDetails.EventEndTime
END AS EndTime FROM '+name+'.dbo.EventDetails) AS DateLimit INNER JOIN
'+name+'.dbo.EventDetails AS EventDetails_1 ON DateLimit.EventID = EventDetails_1.EventID INNER JOIN
'+name+'.dbo.SiteDetails ON EventDetails_1.SiteID = '+name+'.dbo.SiteDetails.SiteID
WHERE (
(
(NOT((EventDetails_1.EventStartTime < @StartDateRange) AND (EventDetails_1.EventEndTime < @StartDateRange)))
AND
(NOT((EventDetails_1.EventStartTime > @EndDateRange) AND (EventDetails_1.EventEndTime > @EndDateRange)))
AND
(NOT((EventDetails_1.EventStartTime > @EndDateRange) AND (EventDetails_1.EventEndTime IS NULL)))
)
OR
(
((EventDetails_1.EventStartTime < @EndDateRange) AND (EventDetails_1.EventEndTime IS NULL))
)
)
AND SiteName IN (@SiteName)
AND EventType IN (@EventType)
AND '''+name+''' IN (@DBName))
AS DateDif) AS FinalSel
GROUP BY Days, Hours, Minutes, Seconds, TSec, EventCount
' from sys.databases where name='VC' or name like 'VCA%'

Set @ParamDefinition =
' @SiteName nvarchar(50),
@EventType nvarchar(50),
@StartDateRange DateTime,
@EndDateRange DateTime,
@DBName nvarchar (100)'

Create table #t([Days] int, [Hours] int, [Minutes] int, [Seconds] int, [TSec] int, [DatabaseName] nvarchar(50), [EventCount] int)
Insert into #t

Execute sp_Executesql @sql, @ParamDefinition, @SiteName, @EventType, @StartDateRange, @EndDateRange, @DBName
Print @sql

Select [Days], [Hours], [Minutes], [Seconds], [TSec], [DatabaseName], [EventCount]
From #t

Drop Table #t

SET NOCOUNT OFF

Return

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-09-09 : 06:51:28

quote:
Originally posted by harlingtonthewizard

Thanks jezemine but I a not sure if this helps as I dont think I can split the statement up plus I am using sp_Executesql which I dont think your suggestion works with? I started using exec only but run into problems with datatype definitions. Also exec can use varchar and hence 8000 chars. I seem to be always swapping one problem for another:) Here is a complete working code example, I have many stored procedures and this is just one of many. I am a beginner so my idea was to develop the sql I needed and then put a dyanmic sql wrapper around each piece of code to run against 1 to x databases. I would prefer not to use dyanmic sql if at all possible. Do you have a better way of looping through the databases and storing the result into one result set for reporting services? I know the databases will be named like I have used in the like statement but do not know how many there will be other than returning a query to find out I guess:) This example works as it is less than 4000 chars but others I have are around 5800 chars.

You can use sp_executeSql with any length of NVARCHAR. Are you aware of NVARCHAR(MAX)? -- you've posted this in a sqlserver2005 forum so if you are using sqlserver2005 then this datatype is available to you.

You just declare it so..

DECLARE @sql NVARCHAR(MAX)

And then you have 2 gig of data to play with (which I think should be enough for anyone).

If you are using sql server 2000 you can still use sp_executeSql with more than 4000 characters (because it actually takes the NTEXT datatype) but you have to use some wrapping.

Please check out. http://www.sommarskog.se/dynamic_sql.html for a *complete* guide to any dynamic question you might want to ask.

-------------
Charlie

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources