sql server 2008 - Passing preference type parameters to a stored procedure - best practice? -


i need provide solution problem of passing multiple parameters series of reporting stored procedures in sql server 2008. parameters going user preferences , may consist of lists user has selected more 1 type of value (e.g. multiple months). there may many 30 different parameters , maybe 60% of them contain multiple selections.

so far have 3 options.

  1. pass in values normal parameters set interface - seems fail quite need pass collections (multiple months example).

  2. pass in parameters single xml fragment. method used here although i'm not sure if can cope multiple elements of same type (e.g. in months above). code uses dynafilter parse xml - i've never heard of , can't find reference on internet. developer wrote code tomorrow i'll have more info then.

  3. use table value parameters - i'm beginning @ these promising , seem offer performance.

we're using sql server 2008 asp.net mvc front end. move 2012 if needed.

i've started , continue more research on best way deal problem value opinions on best way forward , if there other options available.

thanks in advance.

here's how can table-valued parameters. these introduced sql 2008, version fine solution, if choose go route. in test scenario i'm making primary table , related table foreign key pointing primary.

first, create table datatypes:

create type primary_tbltype table     (personkey int not null primary key,     firstname varchar(30),     lastname varchar(30))  create type related_tbltype table     (fk_personkey int not null,     accountnum varchar(30),     accountbalance money)

create stored procedure:

create procedure mysproc     @primarytable primary_tbltype readonly,     @relatedtable related_tbltype readonly begin declare @currentkey int declare @firstname varchar(30) declare @lastname varchar(30) declare @accounttotal money  declare personcursor cursor local fast_forward     select personkey, firstname, lastname @primarytable open personcursor fetch next personcursor @currentkey, @firstname, @lastname while @@fetch_status= 0 begin      select @accounttotal = sum(accountbalance) @relatedtable          fk_personkey = @currentkey      print @firstname + ' ' + @lastname + ' - account total: ' + convert(varchar(30), @accounttotal)      fetch next personcursor @currentkey, @firstname, @lastname end end;

and here's test data try out:

declare @primarytvp primary_tbltype declare @relatedtvp related_tbltype  insert @primarytvp values (1, 'john', 'cleese') insert @primarytvp values (2, 'eric', 'idle') insert @primarytvp values (3, 'graham', 'chapman')  insert @relatedtvp values (1, '29310918', 28934.33) insert @relatedtvp values (2, '123123', 3418.11) insert @relatedtvp values (2, '33333', 666.66) insert @relatedtvp values (3, '554433', 22.22) insert @relatedtvp values (3, '239482', 151515.15)   exec mysproc @primarytvp, @relatedtvp;

some of advantages of using tvps covered here, , capable of participating in set-based operations.


Comments

Post a Comment

Popular posts from this blog

c++ - CryptStringToBinary API behavior -

i seeing strange behavior cryptstringtobinary cryptography api. please see below code (config: x64 debug): #include "stdafx.h" #include <windows.h> #include <strsafe.h> #include <iostream> #include <exception> void main() { dword dwskip; dword dwflags; dword dwdatalen; //lpcwstr pszinput = l"jaaaaaecaaadzgaaakqaagdnnl1l56bwgfjdgr3rpxtqqqn6daw3usv2emkjym4t"; //this works fine lpcwstr pszinput = l"mytest"; //doesnt work, api returns false,error code 0x0000000d // determine size of byte array , allocate memory. if(! cryptstringtobinary( pszinput, _tcslen( pszinput ) + 1, crypt_string_base64, null, &dwdatalen, &dwskip, &dwflags ) ) { dword dw = getlasterror(); //0x0000000d: data invalid throw std::exception( "error computing byte length." ); } byte *pbytebyte = null; try { ...
Read more

c++ - Correct method for redrawing a layered window -

i have window created ws_ex_layered window style. drawing onto memory bitmap using gdi+, , using updatelayeredwindow update graphical content of layered window. intend use window main window of application, require redraw frequently. seeing layered windows not receive wm_paint windows message [?] , need come appropriate method re-drawing window. optimisation not essential , it's nice have cake , eat too. therefore, in search of "correct" method use. here thoughts far: i guess it's idea render onto off-screen bitmap before bitblt ing or similar. 60 frames rendered per second should (more than?) enough (but how compare other applications' frame rates?). possible solutions: use settimer send wm_timer message on regular basis. useful because through specifying time-out value, can achieve desired frames per second, without requirement measure duration "frame" takes rendered. would cause input or other lags due frequency , speed of m...
Read more

java.util.scanner - How to read and add only numbers to array from a text file -

i'm learning java , have question regarding reading file want read numbers file contains strings well. here example of file: 66.56 "3 java 3-43 5-42 2.1 1 and here coding: public class test { public static void main (string [] args){ if (0 < args.length) { file x = new file(args[0]); try{ scanner in = new scanner( new fileinputstream(x)); arraylist<double> test = new arraylist<>(); while(in.hasnext()){ if(in.hasnextdouble()){ double f=in.nextdouble(); test.add(f);} else {in.next();} } catch(ioexception e) { system.err.println("exception during reading: " + e); } } my problem add 66.56,2.1 , 1 doesn't add 3 after "3 or ignores 3-43 , 5-42 can tell me how skip strings , add doubles here? thanks all said 3 ie; "3, 3-43 , 4-42 strings either u read string , split , check number @ " , - or put in space between characters , integers. jvm after compilation tre...
Read more