Ask the Expert

Moving from SQL statement in code to a SPROC

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.

This was first published in March 2003

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: