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.
Dig deeper on Win Development Resources
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.