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.
| Author |
Topic |
|
SexyChick
Starting Member
6 Posts |
 Posted - 2010-05-11 : 11:50:56
|
| I have a sproc that builds a large query that is too large for 1 varchar variable so I have divided the query into 2 varchar variables but I get an error Msg 102, Level 15, State 1, Procedure usp_Get_Patient_Call_Data, Line 127Incorrect syntax near '+'.when I try to run the following:Exec SP_ExecuteSQL @SQL + @SQLFilterAny way around this? |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2010-05-11 : 12:17:35
|
| Are you using sql server 2005?If so declare @sql as NVARCHAR(MAX) Then you won't run out of characters.Otherwise read here: All your dynamic sql questions should go here first:http://www.sommarskog.se/dynamic_sql.htmlCharlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-05-11 : 13:39:01
|
| From SQL Documentation:"[@stmt = ] stmt Is a Unicode string that contains a Transact-SQL statement or batch. stmt must be either a Unicode constant or a Unicode variable. More complex Unicode expressions, such as concatenating two strings with the + operator, are not allowed ... The size of the string is limited only by available database server memory. On 64-bit servers, the size of the string is limited to 2 GB, the maximum size of nvarchar(max)."As you aren't passing the Parameter list, or Parameters, then consider using EXEC(@SQL + @SQLFilter) instead (although IMHO you should be using sp_ExecuteSQL with its additional parameters |
|
|
|
|
|
|
|
|
Very Large Query held in 2 Vars with SP_ExecuteSQL