Hi i have always used views in my code to group common functionality in my sql expressions and then i can simply call these views in my data access layer by saing:
SqlCommand cmd = new SqlCommand("SELECT * FROM vw_Documents WHERE CategoryID = @.CategoryID", cn);
However my view has become so complicated that i had to convert it to a stored procedure called sp_Documents. The problem now though is that is that i wish to do queries against the data returned but i can't simply say:
SqlCommand cmd = new SqlCommand("SELECT * FROM sp_Documents WHERE CategoryID = @.CategoryID", cn);
The only way i can see to do it is to create a stored procedure for every single senario i have passing in the appropriate values as parameters. This seems a pretty messy solution to me because i would have repeated logic in all my stored procedures. Therefore i was wondering if there's a simpler way for me to do this or am i just being lazy :).
Appreciate if someone could help,
Oops i found the solution straight after i posted. User defined functions. Never realized you could return more than one value with a function in sql server. If there is a better solution please let me know but this seems to tick all the boxes.
Edit: I have discovered that this is not going to work for me since my stored procedure produces different columns (based on values passed in) and it appears that the Multi-statement Table-Value User-Defined Function requires you to specify the structure you will be outputting.
|||>SqlCommand cmd = new SqlCommand("SELECT * FROM vw_Documents WHERE CategoryID = @.CategoryID", cn);
It is preferable to select just the columns you require.
>However my view has become so complicated that i had to convert itto a stored procedure called sp_Documents.
>The problem now though isthat is that i wish to do queries against the data returned but I can'tsimply say:
>SqlCommand cmd = new SqlCommand("SELECT * FROM sp_Documents WHERE CategoryID = @.CategoryID", cn);
>The only way i can see to do it is to create a stored procedure forevery single scenario i have passing in the appropriate values asparameters
It is tempting to code complicated IF ... SELECT ... ELSE SELECT ..., however it is generally best to a code one stored procedure for each permutation as then the query engine can optimise each variation. There are some situations where serial scanning of a table is an acceptable perfomance hit and it is possible to use the COALESCE trick to search any combination of 1 to N columns for specific value. For example if table FRED has non-null columns A through D and the sp has args &A to &D and for simplicity the allowed values are non-zero integer then:
IF &A = 0 SET &A = NULL
IF &B = 0 SET &B = NULL
IF &C = 0 SET &C = NULL
IF &D = 0 SET &D = NULL
SELECT A, B, C, D FROM FRED
WHERE COALESCE(&A, A) = A
AND COALESCE(&B, B) = B AND COALESCE(&C, C) = C AND COALESCE(&D, D) = D
If say &A is the only non-zero parameter then the effect select simplifies to SELECT A, B, C, D FROM FRED WHERE &A = A, as COALESCE selects the first non-null value.
