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 |
|
vito1281
Starting Member
12 Posts |
 Posted - 2004-06-03 : 16:40:45
|
| Hi,I'm writing a stored procedure that will create a view based on some dynamic data. The problem I'm having is that the dynamic sql statement ends up being huge (I'm estimating about 50K characters). The part of the view that's growing like this is the list of joins that need to be performed. As a result, I am running out of "varchar" space to store that sql string.Has anyone ran into this kind of issue before? I am thinking of writing a module in the VB .NET application(this application is the one that needs the view) and create the sql string there, then run it on the database. Since the maximum batch size is 65,536 for SQL Server, I think I could fit into this constraint.Can anyone suggest something better? Thanks |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-06-03 : 17:26:38
|
| You can build up the string in a text field of a sible column temp table. The split it into lots of varchars for the exec.You can do the splitting in dynamic sql if you don't want to fix the number of variables.I put an example in a thread here some time ago.==========================================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. |
 |
|
|
vito1281
Starting Member
12 Posts |
Posted - 2004-06-03 : 21:43:06
|
quote:
Originally posted by nr
You can build up the string in a text field of a sible column temp table. The split it into lots of varchars for the exec.You can do the splitting in dynamic sql if you don't want to fix the number of variables.I put an example in a thread here some time ago.==========================================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.
|
|
|
|
vito1281
Starting Member
12 Posts |
Posted - 2004-06-03 : 21:47:09
|
Sorry, the post didn't come out right before.nr, thanks for the suggestion. Do you think building up the sql statement in a text field of a temp table is a BETTER solution than using .NET, or just an alternative?It seems to me like it would be a pain in the neck to build the executable portion using multiple varchar variables.Do you have a link to the example thread or know the search phrase I can use to find it? Do you build the sql like this:INSERT INTO #TempSELECT field_name + dynamic_content FROM #Temp Thanks |
|
|
|
vito1281
Starting Member
12 Posts |
Posted - 2004-06-07 : 10:47:52
|
Hi Nigel,I've decided to give your approach a shot. However, I'm having a problem building up the string in the text field. SQL doesn't allow concatenation of "text" types, so I have no way of updating the field in the temp table. If I try and cast the existing string in the temp table to varchar(8000), I'm back to the space problem.What's the proper way to go about this? Thanksquote:
Originally posted by nr
You can build up the string in a text field of a sible column temp table. The split it into lots of varchars for the exec.You can do the splitting in dynamic sql if you don't want to fix the number of variables.I put an example in a thread here some time ago.==========================================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.
|
|
|
|
|
|
|
|
|
Extremely long dynamic SQL