October 29, 2018 at 3:13 pm
Hello, I have one SP 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 @searchstring <> '''' or @searchstring <> null
BEGIN
SET @sSQL = @sSQL +
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)
and when I try to execute it, it doesn't bring any results, not displaying print statement and doesn't throw any error.exec usp_test '2017'
its a very silly thing that i m missing here but can u help? Thanks a lot.
October 29, 2018 at 3:25 pm
You are defaulting @criteria to null so null concatenated with any string is null.
October 29, 2018 at 3:26 pm
dallas13 - Monday, October 29, 2018 3:13 PMHello, I have one SPCREATE 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 @searchstring <> '''' or @searchstring <> null
BEGIN
SET @sSQL = @sSQL +
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)
and when I try to execute it, it doesn't bring any results, not displaying print statement and doesn't throw any error.exec usp_test '2017'
its a very silly thing that i m missing here but can u help? Thanks a lot.
Have you printed out the SQL that you get from this, and tried to then run that SQL ?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 29, 2018 at 3:28 pm
Thank you sir.
In my case @searchstring is also NULL and it should not go inside the IF condition and just execute @sSQL which is not happening here.
October 29, 2018 at 3:28 pm
[post deleted]
October 29, 2018 at 3:29 pm
dallas13 - Monday, October 29, 2018 3:13 PMHello, I have one SPCREATE 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 @searchstring <> '''' or @searchstring <> null
BEGIN
SET @sSQL = @sSQL +
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)
and when I try to execute it, it doesn't bring any results, not displaying print statement and doesn't throw any error.exec usp_test '2017'
its a very silly thing that i m missing here but can u help? Thanks a lot.
Well, when I modify the procedure to be a script and set @year to '2017' the following SQL is displayed in the Messages tab:
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 = '2017'
Looking at the code I see that the INNER JOIN between gtx_grantees and gtx_applicatioin becomes an INNER JOIN. I guess the first thing I would verify is that there is actually data for 2017 in gtx_application.
October 29, 2018 at 3:41 pm
Does this run for you in SSMS?
--CREATE PROCEDURE [dbo].[usp_test]
declare
@year char(4) = '2017',
@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 @searchstring <> '''' or @searchstring <> null
BEGIN
SET @sSQL = @sSQL +
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)
October 29, 2018 at 3:51 pm
I would change this line:
If @searchstring <> '''' or @searchstring <> null
to this:
--If @searchstring <> '''' or @searchstring <> null
if @searchstring > ''
October 29, 2018 at 4:38 pm
Lynn, 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!
October 29, 2018 at 4:43 pm
Hello Lynn Pettis
Thanks but I've already tried the test that you've suggested,--CREATE PROCEDURE [dbo].[usp_test]
And it works like that, gives me table result from that select statement.
But when I run it from SQL stored procedure, it doesn't give anything. Thats what is bothering me and strange.
October 29, 2018 at 4:45 pm
autoexcrement - 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.
October 29, 2018 at 4:47 pm
Also 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.
October 29, 2018 at 4:47 pm
dallas13 - Monday, October 29, 2018 4:43 PMHello Lynn Pettis
Thanks but I've already tried the test that you've suggested,--CREATE PROCEDURE [dbo].[usp_test]
And it works like that, gives me table result from that select statement.
But when I run it from SQL stored procedure, it doesn't give anything. Thats what is bothering me and strange.
Well, we don't have access to your system nor have you posted any DDL, sample data, and expected results based on the sample data that recreates the problem. Without either there really isn't much more we can do.
October 29, 2018 at 4:47 pm
Yes it's easy to test against '' manually and see the results. I was just curious if there was a function or something so I could see the "value" of any string, just out of curiosity to see what SQL Server is doing behind the scenes.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply