October 29, 2018 at 4:54 pm
dallas13 - Monday, October 29, 2018 4:47 PMAlso Thanks again Lynn.
When I tried,If @searchStr > ''
After that I can successfully run it from SP.
Can you also please give me some explanation about that > and how it works? Thanks.
This, or @searchstring <> null, is wrong. I didn't catch it before. If you are testing for the existence on non existence of null you need to use one of the following: @searchstring is null, or @searchstring is not null.
If @searchstring is null it isn't greater than '' (the empty string).
October 30, 2018 at 6:53 am
I get the feeling that the problem is not enough NULL handling. Try the following:CREATE PROCEDURE [dbo].[usp_test]
@year char(4),
@criteria varchar(50) = NULL,
@Flag char(1) = NULL,
@searchstring varchar(100) = NULL
AS
DECLARE @sSQL varchar(max)
SET @sSQL =
'SELECT g.id
,g.grantee_id
,g.sub_account_number
,g.ssn
,g.grantee_type
,a.appl_id
,a.appl_year
,CASE
WHEN a.appl_status = ''C'' THEN ''Complete''
WHEN a.appl_status = ''D'' THEN ''Pending Documentation''
WHEN a.appl_status = ''F'' THEN ''Final''
ELSE ''Pending''
END as status
,i.person_id
,ISNULL(p.first_name,'''')+'' ''+ISNULL(p.last_name,'''') as name
,dbo.f_gtx_getElectrAddress(a.appl_id,''CURRENT_EMAIL'') as email
,'''' as granteeStatus
,g.participant_id
FROM gtx_grantees g
LEFT JOIN gtx_application a ON a.grantee_id = g.id and g.grantee_status=''A''
LEFT JOIN gtx_personal_info i ON a.appl_id = i.appl_id
LEFT JOIN gtx_person p ON i.person_id = p.person_id
WHERE a.year = '''+@year+''' '
IF ISNULL(@searchstring, '') <> ''
BEGIN
SET @sSQL = @sSQL + ISNULL(
CASE
WHEN @criteria = 'grantee_id' THEN 'AND g.grantee_id like '''
WHEN @criteria = 'sub_account_number' THEN 'AND g.sub_account_number like '''
WHEN @criteria = 'ssn' THEN 'AND g.ssn like '''
WHEN @criteria = 'last_name' THEN 'AND p.last_name like '''
WHEN @criteria = 'appl_status' THEN 'AND a.appl_status like '''
WHEN @criteria = 'participant_id' THEN 'AND g.participant_id like '''
END, '');
SET @sSQL = @sSQL + REPLACE(@searchstring,'''','''''') + '%'''
END;
PRINT @sSQL;
EXECUTE (@sSQL);
GO
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 30, 2018 at 7:35 am
Thanks a lot everyone for helping.
October 30, 2018 at 7:58 am
Lynn Pettis - Monday, October 29, 2018 4:45 PMautoexcrement - Monday, October 29, 2018 4:38 PMLynn, just curious...is there a function we can use to see the "value" that SQL Server is assigning to these text strings to determine if they are "greater than" the blank space? How specifically does it determine that 'blablabla' > '' ? Thanks!Is there a function, not that I am aware of. I have found this work just as well as @String <> '' and @String is not null. All I can suggest is to test for yourself and see.
There are cases where @String > '' will produce different results than @String <> ''. The most common one will be if @String begins with a tab, carriage return, or line feed, because most of the others are non-printing characters and are not likely to accidentally appear in a string.
There is not a function, because it depends on the sort order associated with the specific collation that you are using, but you can get a general idea by first right-padding the string with spaces and then converting to binary.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply