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 |
|
heze
Posting Yak Master
192 Posts |
 Posted - 2007-03-30 : 11:20:07
|
| Hi,Im trying to pass a long string to a stored procedure,its longer than 8000 characters so varchar outofthequestion.I tried:1-to declare the input parameter of the sproc as text and then pass it a text variable but assigning a text local variable is not possbile:--does not workcreate proc #erase@myText textasselect @myTextgodeclare @myText as text--not possibleset @myText=(select REPLICATE ( 'x' , 8000 ) + 'myEnd')exec #erase -------end of does not work12-There is the possibility of inserting the long text value into a table and then call that table froom the proc, but this is not what I want, I dont want to use any tables or temporary tables.-------------I was thinking on declaring some 20 varchar variables and then assembling them within the sproc but I done know if there is a better way, my final objective is to pass a string of id's to a sproc as a string such as:exec mySproc '''23'',''5434'',''765'',...,''453'''my sproc will then be able to do the following:create proc mySproc @myVariable sometypeasexec('select * from my table where id in ('+myVariable+')')gobut the problem is that I cant find "sometype"Does anyone has a suggestion ?thank you |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2007-03-30 : 11:49:28
|
| Sounds like you'd be better off throwing all those values in a temp table and then joining to the temp table rather than using IN any way. I know you said you don't want to use other tables but if using another table will be the best bet maybe you need to consider that. |
 |
|
|
heze
Posting Yak Master
192 Posts |
Posted - 2007-03-30 : 12:08:29
|
| X002548,snSQL,the problem with the table is the concurrent access and since this procedure is accessed via web it is a possiblity. I could use a ##myTable+uniqueIdentifier naming approach though.I can not use a query in the sproc and then parametrize it because the set of ids is arbitrary, it comes from a data grid column, the data grid may have obtained its data set via any kind of query, we dont know.Doing an inline sql query on the application is very slow when the number of ids is high and security people in the institution dont like inline sql.In response to the question this is not a transactinal operation (I dont do any inserts), I query specific data about the ids provided.So if tables is my only option then I would have to call a procedure that inserts the ids from the web grid one by one, which will introduce to the next problem which is what will happen if I want to pass 20,000 ids, would I have to call the insert section of the sproc 20K times? Or I could do inline sql just for theinsertion part so that all code is executed in sql server at once, as I see it I will have to go against some of the good programming practices more than once,but maybe you have an alternative...thank you again |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2007-03-30 : 12:37:12
|
| OK....First why so many id's?How can the application use 20k rows in the front6 end handle that many rows?But I might consider a permanent table, with an id column and a spid column...save the id's to a file then bcip it inThen clear it outBrett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam |
|
|
|
heze
Posting Yak Master
192 Posts |
Posted - 2007-03-30 : 16:41:10
|
| X002548,The actual grid in the application does not show mor than say 1000 rows at a time that can be obtained by clicking a specific row[] value in a very aggregated small grid. When you click this field another web page is opened with the details (like a drill down).The reason for which I need to pass ~20,000 ids is because in the session I save the "driller" table which contains all the joins, so oin order to obtain the individual data of the entire set of ids I need to pass those id's included in the full driller table to the sproc. I dont want to pre query the details in the first pass to sql server because the aggregation application and the drilldown are different webforms. I am doing all of this because I found it exremely difficult and non documented to query analysis services 2000 from a web application so Im basically doing it myself, but as always, if you have a suggestion...I will learnby the way, regarding the saving of the id's on a file I am wondering if you are considering the possible concurrent access, what will happen if 2 users acess the same file at the same time, we would also need to implement a naming convention to associate file name with the specific session wouldnt we?Thank you |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
|
|
|
|
passing a long string to a stored proc?