June 19, 2015 at 6:26 am
I am totally new to writing Stored Procedure.
I am trying to build a stored procedure the builds up the select statement depending on the input parameters passed, i.e. IF input parameter A is not null than that value is added into the SQLQuery as another predicate.
The problem I have, is that the statement I think is to be run syntax wise looks ok and if you take that statement and run it directly as a query it runs fine.
When it is attempted to be executed from the SP it gives
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'VOLSG0000764'.
The SP looks as follows:-
alter PROCEDURE [dbo].[Data_Line_Mapping_Miles]
@Sched_id NvarCHAR(12),
@Sched_Miles NVARCHAR(6) = NULL
AS
SET NOCOUNT ON
Declare @sqlquery AS NVarchar(4000)
Declare @ParamDefinition AS Varchar(2000)
DECLARE@return_value AS INT
SET @sqlquery =
'select DISTINCT
.[sched_id] as [service_schedule_id] ,.[sched_seq] as [service_interval_id]
,.[sort_seq] as [sort_sequence] ,.[first_mls] as [service_miles]
,.[first_kms] as [service_kms] ,.[first_months] as [service_months]
,.[first_hours] as [service_hours] ,.[sched_mls] as [repeat_miles]
,.[sched_kms] as [repeat_kms] ,.[sched_months] as [repeat_months]
,.[sched_hours] as [repeat_hours] ,.[std_time] as [standard_service_time]
,.[at_std_time] as [automatic_transmission_time] ,.[is_add_serv] as [additional_interval]
-- ,ISNULL(WebSvc.GetIntervalDescription(i.sched_id, i.sched_seq, i.sort_seq, 0, 4), ''''' + ') AS [interval_description_for_miles]
-- ,ISNULL(WebSvc.GetIntervalDescription(i.sched_id, i.sched_seq, i.sort_seq, 0, 3), ''''' + ') AS [interval_description_for_kms]
,ISNULL(ads.string_desc, ''''' + ') AS extra_time_description
,ISNULL(ie.ext_time, 0) AS extra_time
,ISNULL(ads2.string_desc, ''''' + ') AS service_advisory_message
,[ie].[opt_id] ,[op].[text_strip] ,[op].[text_right]
from [Intervals] as
left join [interval_ext] as [ie] on .[sched_id] = [ie].[sched_id] and .[sched_seq] = [ie].[sched_seq]
left join [interval_opts] as [iop] on [ie].[opt_id ] = [iop].[opt_id]
left join [ad_strings] as [ads] on [iop].[opt_desc_id] = [ads].[item_id] and [ads].[lang_num] = 0
left join [ad_strings] as [ads2] on .[var_id] = [ads2].[item_id] and [ads2].[lang_num] = 0
left join [sg_operations_view] as [op] on .[sched_id] = [op].[sched_id] and [op].[lang_num] = 0
where '
IF @Sched_id is NOT NULL
BEGIN
SET @sqlquery = @sqlquery + '.[sched_id] = ''' + convert(nvarchar(12),@Sched_id) + ''' '
END
else
BEGIN
GoTo ErrorHandler
END
IF @Sched_Miles > 0
BEGIN
SET @sqlquery = @sqlquery + ' AND .[sched_mls] = ''' + convert(nvarchar(6),@Sched_Miles) + ''' '
END
SET @sqlquery = @sqlquery + ' AND SUBSTRING([op].[text_right],1,6) IN (''Renew'', ''Check'')'
SET @sqlquery = @sqlquery + ' AND SUBSTRING([op].[text_left],1,6) IN
(''' + '2.0730' + ''',''' + '2.0732' + ''',''' + '3.0370' + ''',''' + '4.0240' + ''',''' + '4.0473' + ''','''
+ '4.0570' + ''',''' + '4.0830' + ''',''' + '5.0031' + ''',''' + '5.0033' + ''',''' + '5.0035' + ''')'
SET @sqlquery = @sqlquery + ' order by .[sort_seq] asc'
Set @ParamDefinition = '
@Sched_id NvarCHAR(12),
@Sched_Miles NVARCHAR(6) '
PRINT @sqlquery
Execute sp_Executesql @sqlquery,
@Sched_id,
@Sched_Miles
If @@ERROR <> 0 GoTo ErrorHandler
Set NoCount OFF
Return(0)
ErrorHandler:
Return(@@ERROR)
GO
And the output from the Print statement (used here to aid debugging only)..
select DISTINCT
.[sched_id] as [service_schedule_id] ,.[sched_seq] as [service_interval_id]
,.[sort_seq] as [sort_sequence] ,.[first_mls] as [service_miles]
,.[first_kms] as [service_kms] ,.[first_months] as [service_months]
,.[first_hours] as [service_hours] ,.[sched_mls] as [repeat_miles]
,.[sched_kms] as [repeat_kms] ,.[sched_months] as [repeat_months]
,.[sched_hours] as [repeat_hours] ,.[std_time] as [standard_service_time]
,.[at_std_time] as [automatic_transmission_time] ,.[is_add_serv] as [additional_interval]
-- ,ISNULL(WebSvc.GetIntervalDescription(i.sched_id, i.sched_seq, i.sort_seq, 0, 4), '') AS [interval_description_for_miles]
-- ,ISNULL(WebSvc.GetIntervalDescription(i.sched_id, i.sched_seq, i.sort_seq, 0, 3), '') AS [interval_description_for_kms]
,ISNULL(ads.string_desc, '') AS extra_time_description
,ISNULL(ie.ext_time, 0) AS extra_time
,ISNULL(ads2.string_desc, '') AS service_advisory_message
,[ie].[opt_id] ,[op].[text_strip] ,[op].[text_right]
from [Intervals] as
left join [interval_ext] as [ie] on .[sched_id] = [ie].[sched_id] and .[sched_seq] = [ie].[sched_seq]
left join [interval_opts] as [iop] on [ie].[opt_id ] = [iop].[opt_id]
left join [ad_strings] as [ads] on [iop].[opt_desc_id] = [ads].[item_id] and [ads].[lang_num] = 0
left join [ad_strings] as [ads2] on .[var_id] = [ads2].[item_id] and [ads2].[lang_num] = 0
left join [sg_operations_view] as [op] on .[sched_id] = [op].[sched_id] and [op].[lang_num] = 0
where .[sched_id] = 'VOLSG0000764' AND SUBSTRING([op].[text_right],1,6) IN ('Renew', 'Check') AND SUBSTRING([op].[text_left],1,6) IN
('2.0730','2.0732','3.0370','4.0240','4.0473','4.0570','4.0830','5.0031','5.0033','5.0035') order by .[sort_seq] asc
Which as stated runs fine when run as is in a query.
The command used to run the SP...
DECLARE@return_value int
EXEC@return_value = [dbo].[Data_Line_Mapping_Miles]
@Sched_id = 'VOLSG0000764',
@Sched_Miles = null
SELECT'Return Value' = @return_value
GO
Note same happens if @Sched_Miles set to a value as well (e.g. @Sched_Miles passed a value of 12000 say).
June 23, 2015 at 2:32 am
After a bit of digging, I think I have found the answer. (at least to what was causing me problems!)
Three of the input parameters are numbers (INT) but trying to pull them in to the building query, causes the @sqlquery to try to have an INT pulled into the NVARCHAR. So by CASTing the inputs into the query as NVARCHAR marries the @sqlquery datatype. Possibly could have done same by defined them as NVARCHAR in the first place.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply