Hello. I am having difficulty figuring out how to make this work as a stored procedure. (I don't want to write 32 different stored procedures to handle all these conditions.) Currently in the code I have this explicit SQL statement, which is conditional depending on the values in certain textboxes and dropdown lists:
If cboAsBuilt.Checked = True Then StrAsBuilt = 1 strAsBuiltQuery = " AND RIS_DATA.ASBUILT = 1" Else StrAsBuilt = 0 strAsBuiltQuery = " AND RIS_DATA.ASBUILT = 0" End If If strLocationCode <> "ALL" Then strLocQuery = " AND RIS_PLANT_LOCATIONS.LOCATION = '" & strLocationCode & "'" End If If strPrefix <> "ALL" Then strprefixquery = " AND UPPER(RIS_DATA.SHEET_NO) LIKE '" & UCase(strPrefix) & "%' " End If If strDesc <> "" Then strDescQuery = " AND UPPER(RIS_DATA.DESCRIPTION) LIKE '%" & UCase(strDesc) & "%'" End If If strWkNo &tl;> "" Then strWkNoQuery = " AND UPPER(RIS_STICKER_WORK.WORK_NO) LIKE '%" & UCase(strWkNo) & "%'" End If Dim SQLQuery As String = "SELECT RIS_DATA.STICKER_NO, RIS_DATA.DESCRIPTION," _ & " RIS_DATA.CHECKED_OUT, RIS_DATA.DOC_TYPE AS DOCTYPE, " _ & " RIS_DOC_TYPES.URL_LOCATION, RIS_STICKER_FILETYPE.FILE_EXTENSION, RIS_STICKER_FILETYPE.ALT_FILE_EXTENSION,"_ & " RIS_DOC_TYPES.DESCRIPTION AS DOCDESC, RIS_STICKER_WORK.WORK_NO, RIS_DATA.SHEET_NO " _ & " FROM RIS_DATA " _ & " INNER JOIN " _ & " RIS_DOC_TYPES ON RIS_DATA.Doc_Type = RIS_DOC_TYPES.DOC_TYPE" _ & " INNER JOIN " _ & " RIS_PLANT_LOCATIONS ON RIS_DATA.STICKER_NO = RIS_PLANT_LOCATIONS.STICKER_NO" _ & " LEFT OUTER JOIN " _ & " RIS_STICKER_WORK ON RIS_DATA.STICKER_NO = RIS_STICKER_WORK.Sticker_NO" _ & " LEFT OUTER JOIN" _ & " RIS_Sticker_filetype ON RIS_DATA.sticker_no = RIS_STICKER_FILETYPE.Sticker_no" _ & " WHERE RIS_DATA.DOC_TYPE = 'CDPLANT' " _ & strWkNoQuery & strprefixquery & strLocQuery & strDescQuery & strAsBuiltQuery & " ORDER BY RIS_DATA.DESCRIPTION, RIS_STICKER_WORK.WORK_NO"
Any help would be greatly appreciated!
You can pass all the variable values as parameters to the stored procedure, and inside perform the same processing you?re doing here. T-SQL supports IF..ENDIF, among other things. Once you have the SQL query built, you can use EXECUTE 'sqlstring' to execute it and get the results.
Related Q&A from Daniel Cazzulino
Here Daniel Cazzulino explains how to load a DSL (domain specific language) domain model instance file programmatically. This requires the .NET type ...continue reading
Here we offer a glimpse at 12 of .NET development expert Danny Cazzulino's top ASP.NET questions and answers.continue reading
C# developers should NOT be modifying InitializeComponent method in the code-behind (or any of the variable definitions) by hand.continue reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.