What if your dynamic sql statement is too long?

// from http://www.sommarskog.se/dynamic_sql.html#sp_executesqlong
// not tested yet.
There is a limitation with sp_executesql on SQL Server, since you cannot use longer SQL strings than 4000 characters. If you want to use sp_executesql despite you query string is longer, because you want to make use of parameterised query plans, there is actually a workaround. To wit, you can wrap sp_executesql in EXEC():

DECLARE @sql1 nvarchar(4000),
@sql2 nvarchar(4000),
@state char(2)
SELECT @state = 'CA'
SELECT @sql1 = N'SELECT COUNT(*)'
SELECT @sql2 = N'FROM dbo.authors WHERE state = @state'
EXEC('EXEC sp_executesql N''' + @sql1 + @sql2 + ''',
N''@state char(2)'',
@state = ''' + @state + '''')

I spend a whold day to write a script today. Since I am not a experienced script writer, I will 

paste it here to remind me of something maybe useful in future:

SET

NOCOUNT ON

declare

@folderIn varchar ( 500 )

declare

@folderOut varchar ( 500 )

declare

@IPTableName varchar ( 100 )

set

@folderIn = '$(folderIn)'

set

@folderOut = '$(folderOut)'

set

@IPTableName = '$(ipTableName)'

--set @folderIn = 'g:\v-yukxin\LiteSpeedFiles'

--set @folderOut = 'G:\v-yukxin\IPQuova'

--set @IPTableName = 'IP_031508'

DECLARE

@backFiles as table

( FName Varchar ( 255 ));

DECLARE

@SQLStatement VARCHAR ( MAX )

DECLARE

@TempStatement NVARCHAR ( 4000 )

DECLARE

@SourceFiles VARCHAR ( 8000 )

SET

@TempStatement = STUFF ( 'dir \*.bak* /B' , 5 , 0 , @folderIn );

insert

into @backFiles

EXEC

xp_cmdshell @TempStatement

 

DECLARE

FileCursor CURSOR FOR

SELECT

FName FROM @backFiles

where

FName is not null;

 

DECLARE

@BackUpFile Varchar ( 500 );

OPEN

FileCursor

FETCH

NEXT FROM FileCursor INTO @BackUpFile

SET

@SQLStatement = 'exec master.dbo.xp_restore_database @database = ''dimension20'','

SET

@SourceFiles = ''

WHILE

@@FETCH_STATUS = 0

BEGIN

SET @SQLStatement = @SQLStatement + '@filename = N''' + @folderIn + '\' + @BackUpFile + ''', '

SET @SourceFiles = @SourceFiles + '@filename = N''' + @folderIn + '\' + @BackUpFile + ''', '

FETCH NEXT FROM FileCursor INTO @BackUpFile

END

Close

FileCursor ;

Deallocate

FileCursor ;

SET

@SQLStatement = @SQLStatement + '@filenumber = 1, @with = N''RECOVERY'',
@with = N''NOUNLOAD'', @with = N''STATS = 10'', @with = N''REPLACE'','

SET

@SourceFiles = substring ( @SourceFiles , 1 , len ( @SourceFiles ) - 1 )

SET

@TempStatement = 'exec master.dbo.xp_restore_filelistonly ' + @SourceFiles

declare

@dbInfo as table

(

LogicalName

varchar ( max ),

PhysicalName

varchar ( max ),

[Type]

varchar ( max ),

FileGroupName

varchar ( max ) null,

[Size]

bigint ,

[MaxSize]

bigint

);

insert

into @dbInfo EXEC sp_executesql @TempStatement

DECLARE

DbInfoCursor CURSOR FOR

SELECT

LogicalName , PhysicalName FROM @dbInfo

declare

@lname varchar ( max )

declare

@pname varchar ( max )

open

DbInfoCursor

fetch

next from DbInfoCursor into @lname , @pname

while

@@FETCH_STATUS = 0

begin

set @pname = reverse ( @pname )

set @pname = left( @pname , patindex ( '%\%' , @pname )- 1 )

set @pname = reverse ( @pname )

 

set @SQLStatement = @SQLStatement + ' @with = ''MOVE N''''' + @lname + ''''' TO N'''''

+ @folderOut + '\' + @pname + ''''''', '

fetch next from DbInfoCursor into @lname , @pname

end

set

@SQLStatement = substring ( @SQLStatement , 1 , len ( @SQLStatement ) - 1 );

close

DbInfoCursor ;

deallocate

DbInfoCursor ;

set

@SQLStatement = @SQLStatement +

'

GO

alter database dimension20 set recovery simple;

Use [dimension20];

/*

Step 1

create new ip table.

We need to change the null columns into '''' or -1, which will make the join much faster.

*/

--drop table [ip1]

CREATE TABLE [dbo].[ip1](

[MinIP] [bigint] NULL,

[Continent] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[CountryISO] [varchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[State] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[RegionCF] [smallint] NULL,

[StateCF] [smallint] NULL,

[CityCF] [smallint] NULL,

[ZIPCF] [smallint] NULL,

[AreaCodeCF] [smallint] NULL

) ON [PRIMARY]

insert [dbo].[ip1]

(MinIP,Continent,CountryISO,

State,RegionCF,StateCF,

CityCF,ZIPCF,AreaCodeCF)

select

MinIP,isnull(Continent,'''') as Continent,isnull(CountryISO,'''') as CountryISO,

isnull(State, '''') as State,RegionCF,StateCF,

CityCF,ZIPCF,AreaCodeCF

from '

;

set

@SQLStatement = @SQLStatement + @IPTableName ;

set

@SQLStatement = @SQLStatement + ';

update [ip1] set RegionCF = -1 where RegionCF is null

update [ip1] set StateCF = -1 where StateCF is null

update [ip1] set CityCF = -1 where CityCF is null

update [ip1] set ZIPCF = -1 where ZIPCF is null

update [ip1] set AreaCodeCF = -1 where AreaCodeCF is null

/*

Step 2

Generate the location Table.

*/

--drop table [IPGSKToGeo1]

CREATE TABLE [dbo].[IPGSKToGeo1](

[GeographicSK] [int] IDENTITY(1,1) NOT NULL,

[ContinentName] [nvarchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[CountryCode] [varchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[StateProvinceName] [nvarchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[RegionCF] [int] NULL,

[CityCF] [int] NULL,

[ZIPCF] [int] NULL,

[AreaCodeCF] [int] NULL,

[CreatedDateTime] [datetime] NOT NULL CONSTRAINT [DF_IPGSKToGeo_CreatedDateTime]
DEFAULT (getutcdate()),

[ModifiedDateTime] [datetime] NULL,

CONSTRAINT [IPGSKToGeo1_PK] PRIMARY KEY CLUSTERED

(

[GeographicSK] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

 

insert [dbo].[IPGSKToGeo1]

(ContinentName,CountryCode,StateProvinceName,RegionCF,CityCF,ZIPCF,AreaCodeCF,
CreatedDateTime,ModifiedDateTime)

select

Continent as ContinentName,

CountryISO as CountryCode,

State as StateProvinceName,

RegionCF,

CityCF,

ZIPCF,

AreaCodeCF,

getdate() as CreatedDateTime,

getdate() as ModifiedDateTime

from IP1

group by Continent,CountryISO,State,RegionCF,CityCF,ZIPCF,AreaCodeCF

/*

Step 3

Set up the map between MINIp and Location Table.

*/

CREATE TABLE [dbo].[MinIPToGSK1](

[MinIP] [bigint] NOT NULL,

[GeographicSK] [int] NULL,

[CreatedDateTime] [datetime] NOT NULL CONSTRAINT [DF_MinIPToGS_CreatedDateTime]
DEFAULT (getutcdate()),

[ModifiedDateTime] [datetime] NULL

) ON [PRIMARY]

INSERT [MinIPToGSK1]

([MinIP], [GeographicSK], [CreatedDateTime], [ModifiedDateTime])

select ip.MinIP, loc.[GeographicSK], loc.CreatedDateTime, loc.ModifiedDateTime

from IP1 as ip

inner join IPGSKToGeo1 loc

on ip.Continent = loc.ContinentName

and ip.CountryIso = loc.CountryCode

and ip.State = loc.StateProvinceName

and ip.RegionCF = loc.RegionCF

and ip.CityCF = loc.CityCF

and ip.ZIPCF = loc.ZIPCF

and ip.AreaCodeCF = loc.AreaCodeCF

/*

Step 4

bcp out the data to files

*/

declare @BCPStatement varchar(8000);

set @BCPStatement = ''bcp "select MinIP,GeographicSK from dimension20.dbo.MinIPToGSK1"
queryout '

;

set

@SQLStatement = @SQLStatement + @folderOut + '\IP2GSK.txt -T -t "," -c''

exec xp_cmdshell @BCPStatement

set @BCPStatement = ''bcp "select GeographicSK,ContinentName,CountryCode,StateProvinceName
from dimension20.dbo.IPGSKtogeo1" queryout '

;

set

@SQLStatement = @SQLStatement + @folderOut + '\IPGSK2Geo.txt -T -t "," -c''

exec xp_cmdshell @BCPStatement

'

;

print

@SQLStatement

 

转载于:https://www.cnblogs.com/xingyukun/archive/2008/04/09/1144061.html