[dba-SQLServer]Creating Sproc over 8000 chars long

Plus there's also a whole other aspect of security problems that go 
along with dynamic sql.

First...
   You must issue users direct access to tables (a big no no).  Access 
to tables should be managed through Views and Sprocs, by using Dynamic 
SQL you must give direct SELECT, INSERT rights to those users.

2nd,
   SQL injections, by this I mean that a malicious users could 
theoretically call your sproc and inject into it's parameter some data 
along with ";" and something sinsiter such as Truncate Table, or detach 
db or something else.  It will be perfectly viable and depending on how 
much access is given to the tables, extreamly lethal.

Things to think about...

Stuart McLachlan wrote:
> On 23 Sep 2003 at 12:16, David Emerson wrote:
> 
> 
>>I have a sproc that accepts a string as a parameter which is used in a 
>>where clause to filter out customers.
>>
>>The string is created by looping through a list box and adding the 
>>customers that have been selected:
>>
>>strList = strList & " or ((CustomerID) = " & .ItemData(varItem) & ")"
>>
> 
> 
> 
> You will build a much smaller string if you use
> 
> 'initialise the string
> strList  = "CustomerID IN ("
> ......
> 'build the list
> strList = strList & ItemData(varItem) & ","
> ........
> 
> 'strip trailing comma and close the expression
> strList = left$(strList,Len(strList)-1) & ")"
> 
> 
>>This goes into a statement which starts like this:
>>
>>DECLARE @qs varchar (8000)
>>
>>SELECT @qs = 'INSERT INTO ttmpExportMerge (CustomerName, TradingName, ... etc
>>
>>However the problem is that there could be over 1600 customers and this 
>>means that strList is over 8000 character which exceeds the @qs length 
>>(without even allowing for the rest of the sproc).
>>
>>Is there a data type that can handle these numbers?  I tried 'text' but the 
>>sproc errors with "text is invalid for local variables"
>>
> 
> Nope, your stuck with data types which can have a maximum of 8000 
> characters.
> 
> If the IN construct still gives you more than 8000 characters you're SOOL 
> with that approach.
> 
> 
>>Is my only option to write the customer ID to a table and join it to the 
>>rest of the sproc?
>>
> 
> 
> That's one option. 
> Another may be to have a "Selected" field in the customer table which you 
> set somehow rather than building the where string.
> If this is likely to be multi user, I'd probably go with your option of 
> building a temp table of selected IDs and joining it.
> 
> 
> 


-- 
-Francisco