July 20, 2016 at 1:47 pm
I have a stored procedure building dynamic SQL based on input parameters, with a simple PHP web screen for users to input values.
When it's run, it should build 4 scripts into variable @SQL_CMD, and then EXEC (@SQL_CMD) each script each time.
It executes the first one, but the next one doesn't execute and the SP stops without doing any more steps.
I write @SQL_CMD value into a log file before each step to check the syntax, and it works perfectly if I copy paste to a query window, with the same permission.
Also, if I run the Stored procedure manually from a query window, passing parameters, it does all the steps.
Seems strange that it only stops half way when invoked from the PHP window
Puzzling
July 21, 2016 at 12:20 am
Are the all 4 scripts in Variable "@SQL_CMD" are Select statements ? If they are then that means your SPs is generating multiple result set.
you need to handle it in your PHP.
OR you can use UNION ALL, to club all the select statements generating a single select statement.
July 21, 2016 at 8:56 am
All the "@SQL_CMD" are INSERT statements.
The process is for a simple utility for users. They enter some parameters such as Job#, State, and search string. The Stored Procedure does a lookup of records that match, and then inserts into another table.
Everything works from Query window, but very puzzled why it works half-way, then stops when called from the PHP application.
July 21, 2016 at 9:26 am
What's the best way to trap for possible error on the dynamic INSERT statement ?
An earlier dynamic INSERT to a different table in the same database does work, so it's not permissions.
I added this to the SP, but get no record in my JobStepStatus table inside the CATCH, so I assume that means the SQL_CMD is not failing.
BEGIN TRY
EXEC (@SQL_CMD) --'Run it'
END TRY
BEGIN CATCH
SELECT @ErrorNumber = ERROR_NUMBER()
,@ErrorSeverity = ERROR_SEVERITY()
,@ErrorState = ERROR_STATE()
-- ,@ErrorProcedure = ERROR_PROCEDURE()
,@ErrorLine = ERROR_LINE()
,@ErrorMessage = ERROR_MESSAGE()
INSERT INTO [job_monitor].[dbo].[JobStepStatus]-- LOG
([JobStep#]
,[JobName]
,[DatabaseName]
,[RunTime]
,[StepDescription]
,[ServerName])
select
2
,'ListMember insert Error'
,'client'
,getdate()
,'Error#: ' + cast(@ErrorNumber as varchar(10)) + ' Severity: ' + cast(@ErrorSeverity as varchar(10))
+ ' State: ' + cast(@ErrorState as varchar(10)) -- + ' Procedure: ' + @ErrorProcedure
+ ' Line: ' + cast(@ErrorLine as varchar(10)) + ' ErrorMessage: ' + @ErrorMessage
,'SQL Stage Server'
END CATCH
July 21, 2016 at 12:08 pm
In case it helps, here the whole SP, with a comment where @SQL_CMD WORKS and @SQL_CMD DOES NOT WORK.
Must be some dumb mistake somewhere.
ALTER procedure dbo.usp_JobMatch_ListTransfer
@Job_ID int = 0
,@Recruiter_MemID int = NULL-- Recruiter's MEM_ID for the new list being created in ListContacts
,@List_Name_New varchar(200) = NULL-- Name for the new list being created in ListContacts
,@CheckFUNC char(1) = NULL-- Match on FUNCTION ? Y,N
,@CheckIND char(1) = NULL-- Match on INDUSTRY ? Y,N
,@CheckLOC char(1) = NULL-- Match on LOCATION ? Y,N
,@SearchString varchar(2000) = NULL-- Search Word lookup
,@State varchar(300) = NULL-- State Codes
AS
DECLARE @Status_OUT varchar(200),
@JOB_funcCode1 varchar(5),
@JOB_funcCode2 varchar(5),
@JOB_indCode1 varchar(5),
@JOB_indCode2 varchar(5),
@JOB_locCode1 varchar(6),
@JOB_locCode2 varchar(6),
@SQL_CMD varchar(5000),
@List_ID_New int
set @SearchString = replace(@SearchString, ' %', ' ''%') -- Put quote ' before leading %
set @SearchString = replace(@SearchString, '% ', '%'' ') -- Put quote ' after trailing %
set @SearchString = replace(@SearchString, '%)', '%'')') -- Put closing quote ' before )
--/*** debugging
select @Job_ID as '@Job_ID',
@Recruiter_MemID as '@Recruiter_MemID',
@CheckFUNC as '@CheckFUNC' ,-- Match on FUNCTION ? Y,N
@CheckIND as '@CheckIND' ,-- Match on INDUSTRY ? Y,N
@CheckLOC as '@CheckLOC',-- Match on LOCATION ? Y,N
@SearchString as '@SearchString' ,-- Search Word lookup
@State as '@State'
--****/
-- State comes in like: AL,FL,GA,LA,MS convert to: 'AL','FL','GA','LA','MS'
set @State = '''' + @State + '''' -- First leading & trailing quotes
set @State = replace(@State, ',', ''',''') -- then quotes between the states
set @JOB_funcCode1 = (select JOB_funcCode1 from Client.dbo.job where job_id = @Job_ID)
set @JOB_funcCode2 = (select JOB_funcCode2 from Client.dbo.job where job_id = @Job_ID)
set @JOB_indCode1 = (select JOB_indCode1 from Client.dbo.job where job_id = @Job_ID)
set @JOB_indCode2 = (select JOB_indCode2 from Client.dbo.job where job_id = @Job_ID)
set @JOB_locCode1 = (select JOB_locCode1 from Client.dbo.job where job_id = @Job_ID)
set @JOB_locCode2 = (select JOB_locCode2 from Client.dbo.job where job_id = @Job_ID)
truncate table Client.dbo.Job_Match
IF (@SearchString > ' ')
BEGIN
set @SQL_CMD = 'INSERT into Client.dbo.Job_Match
select ' +
cast(@Job_ID as varchar(10)) + ' , j.job_id, JOB_funcCode1 ,JOB_funcCode2 ,JOB_indCode1 ,JOB_indCode2 ,JOB_locCode1 ,JOB_locName1 ,JOB_locCode2 ,JOB_locName2,
''' + REPLACE(@SearchString,'''','') + ''' as ''Searchword1'', '''' as ''Searchword2'', '''' as And_Or, job_title, JOB_PI_posDesc
FROM Client.dbo.job j
where 1=1 '
IF @CheckFUNC = 'Y'
SET @SQL_CMD = @SQL_CMD + ' AND ((JOB_funcCode1 > '' '' and JOB_funcCode1 = ''' + @JOB_funcCode1 + ''') OR (JOB_funcCode2 > '' '' and JOB_funcCode2 = ''' + @JOB_funcCode2 + ''' ))'
IF @CheckIND = 'Y'
SET @SQL_CMD = @SQL_CMD + ' AND ((JOB_indCode1 > '' '' and JOB_indCode1 = ''' + @JOB_indCode1 + ''' ) OR (JOB_indCode2 > '' '' and JOB_indCode2 = ''' + @JOB_indCode2 + ''' ))'
IF @CheckLOC = 'Y'
SET @SQL_CMD = @SQL_CMD + ' AND ((JOB_locCode1 > '' '' and JOB_locCode1 = ''' + @JOB_locCode1 + ''' ) OR (JOB_locCode2 > '' '' and JOB_locCode2 = ''' + @JOB_locCode2 + ''' ))'
SET @SQL_CMD = @SQL_CMD + ' AND ' + @SearchString + ' '
SET @SQL_CMD = @SQL_CMD + ' AND Job_ReleaseDate > dateadd(yy, -5, getdate()) ' -- ALL check if Within last 5 years
INSERT INTO job_monitor.dbo.JobStepStatus-- LOG
(JobStep#
,JobName
,DatabaseName
,RunTime
,StepDescription
,ServerName)
select
1
,'usp_JobMatch_ListTransfer- Job_Match Insert, with searchword'
,'Client'
,getdate()
,@SQL_CMD
,'SQL Stage'
--======================
--'THIS @SQL_CMD WORKS'
--======================
EXEC (@SQL_CMD)
END
--===========================================================
-- Now insert MEMBERS into ListMember from VIEWED and APPLIED
--===========================================================
-- Create a temp table to capture the new list ID created
DECLARE @TempList_Id table( TempList_Id int)
-- Create new ListContacts Record: Populate Recruiter MemID, List Name. ListID will auto populate
INSERT INTO Client.dbo.ListContacts
(List_MemID
,List_Name
,Archive)
OUTPUT INSERTED.List_Id INTO @TempList_Id -- Writes new List_ID into Temp table for later use
select @Recruiter_MemID, @List_Name_New , NULL
-- set variable to new List_id for use in ListMember
set @List_ID_New = (select TempList_Id from @TempList_Id)
-- APPLIES from MemberJobsApplied_History into
set @SQL_CMD = 'INSERT INTO Client.dbo.ListMember
select distinct ' + cast(@List_ID_New as varchar(10)) + ' , mj_applied_mem_id, NULL
from Client.dbo.MemberJobsApplied_History MJH
join Client.dbo.Member M on m.mem_id = mj_applied_mem_id
join Client.dbo.membereprofile mep on mep.mem_id = M.mem_id
join Client.dbo.Job_Match JM on JM.Job_ID = MJH.mj_applied_job_id
where mj_applied_mem_id > 0
AND NOT EXISTS (select * from Client.dbo.ListMember where ListM_contact_Id = mj_applied_mem_id and ListM_List_Id = ' + cast(@List_ID_New as varchar(10)) + ' )
and M.Memst_id in (1,4) ' -- Active, Inactive
IF @State > ' ' SET @SQL_CMD = @SQL_CMD + ' AND MEP.STA_ID in (' + @STATE + ')'
INSERT INTO job_monitor.dbo.JobStepStatus-- LOG
(JobStep#
,JobName
,DatabaseName
,RunTime
,StepDescription
,ServerName)
select
1
,'usp_JobMatch_ListTransfer- ListMember insert applies before'
,'Client'
,getdate()
,@SQL_CMD
,'SQL Stage'
DECLARE @ErrorNumber int
DECLARE @ErrorSeverity int
DECLARE @ErrorState int
DECLARE @ErrorProcedure varchar(128)
DECLARE @ErrorLine int
DECLARE @ErrorMessage varchar(4000)
BEGIN TRY
--======================
--'THIS @SQL_CMD DOES NOT WORK'
--======================
EXEC (@SQL_CMD) --'Run it'
END TRY
BEGIN CATCH
SELECT @ErrorNumber = ERROR_NUMBER()
,@ErrorSeverity = ERROR_SEVERITY()
,@ErrorState = ERROR_STATE()
-- ,@ErrorProcedure = ERROR_PROCEDURE()
,@ErrorLine = ERROR_LINE()
,@ErrorMessage = ERROR_MESSAGE()
INSERT INTO job_monitor.dbo.JobStepStatus-- LOG
(JobStep#
,JobName
,DatabaseName
,RunTime
,StepDescription
,ServerName)
select
2
,'usp_JobMatch_ListTransfer- ListMember insert applies ERROR'
,'Client'
,getdate()
,'Error#: ' + cast(@ErrorNumber as varchar(10)) + ' Severity: ' + cast(@ErrorSeverity as varchar(10))
+ ' State: ' + cast(@ErrorState as varchar(10)) -- + ' Procedure: ' + @ErrorProcedure
+ ' Line: ' + cast(@ErrorLine as varchar(10)) + ' ErrorMessage: ' + @ErrorMessage
,'SQL Stage'
END CATCH
-- Log AFTER running SQL
INSERT INTO job_monitor.dbo.JobStepStatus-- LOG
(JobStep#
,JobName
,DatabaseName
,RunTime
,StepDescription
,ServerName)
select
2
,'usp_JobMatch_ListTransfer- ListMember insert applies AFTER'
,'Client'
,getdate()
,@SQL_CMD
,'SQL Stage'
July 21, 2016 at 1:24 pm
Should @SQL_CMD be NVARCHAR?
July 21, 2016 at 1:30 pm
djj (7/21/2016)
Should @SQL_CMD be NVARCHAR?
Do you think that would make a difference in my case ?
All our similar SP code uses VARCHAR.
July 21, 2016 at 1:41 pm
homebrew01 (7/21/2016)
djj (7/21/2016)
Should @SQL_CMD be NVARCHAR?Do you think that would make a difference in my case ?
All our similar SP code uses VARCHAR.
I just remember that one of the execute things needed Unicode. This may not.
July 21, 2016 at 2:13 pm
SET NOCOUNT ON ?
July 21, 2016 at 3:05 pm
djj (7/21/2016)
Should @SQL_CMD be NVARCHAR?
I tried it, no improvement
July 21, 2016 at 3:21 pm
djj (7/21/2016)
homebrew01 (7/21/2016)
djj (7/21/2016)
Should @SQL_CMD be NVARCHAR?Do you think that would make a difference in my case ?
All our similar SP code uses VARCHAR.
I just remember that one of the execute things needed Unicode. This may not.
No, it doesn't make a significant difference. When using sp_executesql, there is an implicit conversion from varchar to nvarchar if the input isn't nvarchar. This implicit conversion is negligible.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
July 21, 2016 at 11:51 pm
homebrew01 (7/21/2016)
All the "@SQL_CMD" are INSERT statements.The process is for a simple utility for users. They enter some parameters such as Job#, State, and search string. The Stored Procedure does a lookup of records that match, and then inserts into another table.
Everything works from Query window, but very puzzled why it works half-way, then stops when called from the PHP application.
Please check the following:
1. Make sure when you are connecting with PHP, its using the same Server and same DB which you are currently working. (You never know :-))
2. As you already have the job monitor table in your SP. i would recommend to store your Calling Parameters in that monitor table.
3. Have you can compare the result of 'job_monitor.dbo.JobStepStatus' when called from window query and when called from PHP.
what you have found so far?
Few recommendation regarding working with dynamic SQL:
1. Passing a Comma-separated string in a @Param of you SP, it should be either 8000 or set it MAX, either varchar/nvarchar.
2. Local variable which is being used to store the complete dynamic query, SHOULD BE set to MAX, either varchar/nvarchar to avoid running out of character while generating dynamic query.
3. (My opinion people may disagree) Instead of using EXEC, you should be using sp_executesql. because EXEC statement is depreciated and will be removed in a future version of Microsoft SQL Server.
July 22, 2016 at 7:23 am
Have you tried printing the statements before executing them? Maybe a null is giving you problems.
I would suggest that you follow twin devil's recommendations. I disagree on the reason for number 3. You should use sp_executesql to use parameters in your dynamic queries. Right now, you're wide open to SQL injection.
July 22, 2016 at 7:33 am
Thanks for the replies. I know the SP is hitting the correct database, because it completes 1 of the inserts correctly, but just "stops" at the next one.
All I can think is there's some condition, like a timeout setting or something in PHP causing it to fail. But when I run it from SQL Mgt studio, it only takes 30 seconds, nothing extreme. (I am a PHP dummy)
I don't think there are any problems with data or parameters. I trap the SQL syntax for the step that it should run, and if I run it manually it works.
Also, I have a trace set up to capture the call of the stored procedure from PHP. If I copy-paste the trace data that calls the SP into a QRY window and run it, with the same login as PHP is using, it works perfectly. Here's the command found in the trace file, and it works perfectly.
-- From SQL Trace. Works perfectly in a QRY window
EXEC usp_JobMatch_ListTransfer
@Job_ID=454185,@Recruiter_MemID=2829026,
@List_Name_New='GD 721 454',
@CheckFUNC='Y',@CheckIND='N',@CheckLOC='N',
@SearchString=' (JOB_Title_posDesc_Comb like %regional%) ',
@State='CA,FL,IL,IN'
July 22, 2016 at 8:22 am
All I can think is there's some condition, like a timeout setting or something in PHP causing it to fail. But when I run it from SQL Mgt studio, it only takes 30 seconds, nothing extreme.
Well if you want to check that you can use Transaction. i-e try to execute all the insert statement under a single Transaction so that if there is any failure/timeout then the complete transaction either fails or completes.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply