switching from an explicit sql statement to a SPROC (a tough SPROC)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

switching from an explicit sql statement to a SPROC (a tough SPROC)

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-03-10 : 07:22:03

Roxanne writes "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 text boxes and drop downlists:

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 <> "" 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!

Cheers,
rox"

skillile
Posting Yak Master

208 Posts

Posted - 2003-03-10 : 21:53:46

Dynamic SQL

DECLARE
@sql varchar(1000),
@where varchar(1000)

SET @sql = '
SELECT
blah,
blah

FROM blah a
'

IF --DO YOUR CHECKS HERE

SET @where = '
WHERE blah blah
'

SET @sql = @sql + @where

EXEC(@sql)

slow down to move faster...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources