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 |
|
ronstone
Starting Member
32 Posts |
 Posted - 2004-11-23 : 11:02:31
|
| I've got a adhoc report that builds a custom sql query. I started out with a varchar(8000) to build the sqlstr to execute.Problem is, seems since multiple values for a parameter can be passed, the query string could potentially surpass the 8k char limit.One suggestion (which works), is to gang up multiple variables (e.g. EXEC @sq1 + @sql2 ...), but it is difficult to know when to start using the next variable.Any ideas on how to accomplish this? |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-11-23 : 11:11:05
|
| You can build it in a single row temp table with a text column then split it into strings.You can even do that in dynamic sql so that you don't restrict the number of variables (apart from the size of that variable).==========================================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. |
 |
|
|
ehorn
Master Smack Fu Yak Hacker
1632 Posts |
Posted - 2004-11-23 : 11:11:16
|
| Are you certain Dynamic SQL is necessary to accomplish your task? |
|
|
|
ronstone
Starting Member
32 Posts |
Posted - 2004-11-23 : 11:20:10
|
| nr - interesting, how do I call the EXEC on that column? Edit: Store the text field value into a temp text field, and EXEC the temp?ehorn - what are you thinking of? It's a web form that has multiple dropdownlists that can have multiple fields selected, and several fields with optional =/>/>=/</<= fields that I insert into the dynamic sql statement. There are business rules I need to translate. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-11-23 : 11:26:33
|
| Nope - you have to split the text field into character variables to exec it.==========================================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. |
|
|
|
ronstone
Starting Member
32 Posts |
Posted - 2004-11-23 : 11:43:27
|
| Gotcha, thanks! |
|
|
|
ashbylane
Starting Member
1 Post |
Posted - 2004-12-01 : 12:22:02
|
| Hey NR - do you by chance have any sample code on doing this in dynamic SQL? I'm not sure I get what you mean by saying "You can even do that in dynamic sql so that you don't restrict the number of variables (apart from the size of that variable)."Thanks! |
|
|
|
|
|
|
Dynamic sql query string EXEC