June 26, 2003 at 1:34 pm
Hi guys,
I need to include % sign in my existing stored procedure in Case_no% in @param1 so that I can get the LIKE results.This is the statement where i need to do it.
CASE WHEN @param1 IS NOT NULL THEN @return+' AND Case_no# LIKE '''+@param1+'''' ELSE '' END+
If any body can help then i would be really helpful. My sproc looks something like this :
CREATE PROCEDURE proc_test99
@param1 nvarchar(20) = null,
@param2 nvarchar(20) = null,
@param3 nvarchar(30) = null,
@param4 nvarchar(20) = null,
@param5 nvarchar(20) = null,
@param6 nvarchar(20) = null,
@param7 SMALLDATETIME = null,
@param8 SMALLDATETIME = null,
@param9 SMALLDATETIME = null,
@param10 SMALLDATETIME = null,
@param11 SMALLDATETIME = null,
@param12 nvarchar(20) = null
AS
BEGIN
DECLARE @where_clause varchar(8000),
@sql varchar(8000),
@return varchar(2)
SELECT @where_clause = 'WHERE 1=1',
@return = char(13)+char(10)
SELECT @where_clause = @where_clause +
CASE WHEN @param1 IS NOT NULL THEN @return+' AND Case_no# LIKE '''+@param1+'''' ELSE '' END+
CASE WHEN @param2 IS NOT NULL THEN @return+' AND last_name = '''+@param2+'''' ELSE '' END+
CASE WHEN @param3 IS NOT NULL THEN @return+' AND [First name] = '''+@param3+'''' ELSE '' END+
CASE WHEN @param4 IS NOT NULL THEN @return+' AND Determination = '''+@param4+'''' ELSE '' END+
CASE WHEN @param5 IS NOT NULL THEN @return+' AND sender = '''+@param5+'''' ELSE '' END+
CASE WHEN @param6 IS NOT NULL THEN @return+' AND [Case Type] = '''+@param6+'''' ELSE '' END+
CASE WHEN @param7 IS NOT NULL THEN @return+' AND Date = '''+CAST(@param7 AS nvarchar(20))+'''' ELSE '' END+
CASE WHEN @param8 IS NOT NULL THEN @return+' AND [Qwik Due] = '''+CAST(@param8 AS nvarchar(20))+'''' ELSE '' END+
CASE WHEN @param9 IS NOT NULL THEN @return+' AND [Qwik sent date] = '''+CAST(@param9 AS nvarchar(20))+'''' ELSE '' END+
CASE WHEN @param10 IS NOT NULL THEN @return+' AND [Final due] = '''+CAST(@param10 AS nvarchar(20))+'''' ELSE '' END+
CASE WHEN @param11 IS NOT NULL THEN @return+' AND [Final sent date] = '''+CAST(@param11 AS nvarchar(20))+'''' ELSE '' END+
CASE WHEN @param12 IS NOT NULL THEN @return+' AND [on hold code] = '''+@param12+'''' ELSE '' END
SELECT @sql = 'SELECT DISTINCT Case30.Case_no#,Case30.Last_name,Case30.[First Name],Case30.Determination,Case30.Sender,Case30.[Case Type],Case30.Date,Case30.[Qwik Due],Case30.[Qwik sent date],Case30.[Final due],Case30.[Final sent date],Case30.[on hold code],tblFileNAmes.reportid,userviewed.viewid FROM tblFileNames Left Outer Join Case30 ON Case30.Case_no# = tblFileNames.case_id Left Outer join userviewed ON tblFileNames.reportid = userviewed.reportid '+@return+@where_clause+@return+
'UNION '+@return+
'SELECT DISTINCT CASEOLDR.Case_no#,CASEOLDR.Last_name,CASEOLDR.[First Name],CASEOLDR.Determination,CASEOLDR.Sender,CASEOLDR.[Case Type],CASEOLDR.Date,CASEOLDR.[Qwik Due],CASEOLDR.[Qwik sent date],CASEOLDR.[Final due],CASEOLDR.[Final sent date],CASEOLDR.[on hold code],tblFileNAmes.reportid,userviewed.viewid FROM tblFileNames Left Outer Join CASEOLDR ON CASEOLDR.Case_no# = tblFileNames.case_id Left Outer join userviewed ON tblFileNames.reportid = userviewed.reportid'+@return+@where_clause+'order by Case_no#'
EXEC (@sql)
--SELECT @sql
END
June 26, 2003 at 1:45 pm
I solved it. So dont worry.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply