dynamic sql query (maximum length reached)

[bunmiA]

I have created a sql query which executes another sql query within it. Unfortunately the query within is quite long and seems to exceed the 8000 limit for varchar. Does anyone have any ideas on how I can can execute this query... unfortunately, I can't split the query.. I am using a table which has loads of fields in a cursor.

Thanks

 

[mjp483]

I discovered a way to split your query into two parts, then concatenate, then Exec.

/*
Table: PartnerTypes
Fields: ptId,PartnerType, voucherID, isSpecial
*/
declare @FirstHalf varchar(8000)
declare @SecondHalf varchar(8000)

set @FirstHalf = 'select ptid, partnerType '
set @SecondHalf = ',voucherID, isSpecial from PartnerTypes'

--print(@FirstHalf+@SecondHalf)
exec(@FirstHalf+@SecondHalf)

 

[nigelrivett]

One way of doing this is to build the query in a text field of a temp table then split it into variables which you concatenate in an exec.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.