Will
asked on
Trying to convert the following sql query (msssql) into a Parameter for execution. I am completely lost how to place the apostrophes. Can someone get me started? Thanks
set @SQL=
SELECT
@DP= Y.times.query('DateDispatched').value('.', 'varchar(19)'),
@ER=Y.times.query('DateEnroute').value('.', 'varchar(19)')
FROM (
SELECT CAST(y AS XML)
FROM OPENROWSET(
BULK @FILENAME,
SINGLE_BLOB) AS T(y)
) AS T(y)
CROSS APPLY y.nodes('/PCRDATA/Times') AS Y(Times)
if exists(select* from [dbo].[MICU] where PRID=@PRID)
UPDATE [dbo].[MICU]
SET dbo.MICU.Dispatched= @DP,dbo.MICU.Enroute= @er
where dbo.MICU.PRID=@PRID
ELSE
INSERT INTO [dbo].[MICU] (PRID,Dispatched,Enroute) values(@PRID,@DP,@er)
ASKER
Thank you Pawan. I get the following error when executing. I am thinking I may still be missing an '
Msg 245, Level 16, State 1, Procedure MICU_XML3, Line 33
Conversion failed when converting the varchar value '
SELECT
@DP= Y.times.query('DateDispatched').value('.', 'varchar(19)'),
@ER=Y.times.query('DateEnroute').value('.', 'varchar(19)')
FROM (
SELECT CAST(y AS XML)
FROM OPENROWSET(
BULK c:\pcr\xml\48633430.XML ,
SINGLE_BLOB) AS T(y)
) AS T(y)
CROSS APPLY y.nodes('/PCRDATA/Times') AS Y(Times)
if exists(select* from [dbo].[MICU] where PRID= ' to data type int.
ASKER
I looked a little more carefully, looks like the filename is not wrapped in ' tried a couple of variants didn't work.
ASKER
I was able to get the ' wrapped around the filename still get same error
ASKER
Msg 245, Level 16, State 1, Procedure MICU_XML3, Line 33
Conversion failed when converting the varchar value '
SELECT
@DP= Y.times.query('DateDispatched').value('.', 'varchar(19)'),
@ER=Y.times.query('DateEnroute').value('.', 'varchar(19)')
FROM (
SELECT CAST(y AS XML)
FROM OPENROWSET(
BULK 'c:\pcr\xml\48633430.XML' ,
SINGLE_BLOB) AS T(y)
) AS T(y)
CROSS APPLY y.nodes('/PCRDATA/Times') AS Y(Times)
if exists(select* from [dbo].[MICU] where PRID=' to data type int.
ASKER
My primary key is an int and my @prid is an int do I have to construct the query differently with the apostrophes?
could you please give me data types of
@PRID , @DP , @er
@PRID , @DP , @er
Please use this.. Updated ..
DECLARE @FILENAME AS VARCHAR(100) = 'Pawan.txt'
DECLARE @DP AS VARCHAR(100) = 'Package'
DECLARE @PRID INT = 10
DECLARE @er AS VARCHAR(12) = 'Err'
DECLARE @SQL AS VARCHAR(MAX) = ''
set @SQL= '
SELECT
@DP= Y.times.query(''DateDispatched'').value(''.'', ''varchar(19)''),
@ER=Y.times.query(''DateEnroute'').value(''.'', ''varchar(19)'')
FROM (
SELECT CAST(y AS XML)
FROM OPENROWSET(
BULK ' + '''' + @FILENAME + '''' + ' ,
SINGLE_BLOB) AS T(y)
) AS T(y)
CROSS APPLY y.nodes(''/PCRDATA/Times'') AS Y(Times)
if exists(select* from [dbo].[MICU] where PRID= ' + '''' + CAST(@PRID AS VARCHAR(200)) + '''' + ' )
UPDATE [dbo].[MICU]
SET dbo.MICU.Dispatched= ' + ''''+ @DP + '''' + ' ,dbo.MICU.Enroute= ' + '''' + @er + '''' + '
where dbo.MICU.PRID= ' + '''' + CAST(@PRID AS VARCHAR(200)) + '''' + '
ELSE
INSERT INTO [dbo].[MICU] (PRID,Dispatched,Enroute) values( ' + '''' + CAST(@PRID AS VARCHAR(200)) + ''''
+ ',' + '''' + @DP + '''' + ' , ' + '''' + @er + '''' + ')'
EXEC (@SQL)
ASKER
Good morning. Thanks again
@PRID , @DP , @er are all varchar(20). The changes didn't work. for some reason the query returns a null for @sql (have it updated a separate data table
@PRID , @DP , @er are all varchar(20). The changes didn't work. for some reason the query returns a null for @sql (have it updated a separate data table
can you run below and give me output of the below print statement.
Pass your actual values.
Pass your actual values.
DECLARE @FILENAME AS VARCHAR(100) = 'Pawan.txt'
DECLARE @DP AS VARCHAR(100) = 'Package'
DECLARE @PRID INT = 10
DECLARE @er AS VARCHAR(12) = 'Err'
DECLARE @SQL AS VARCHAR(MAX) = ''
set @SQL= '
SELECT
@DP= Y.times.query(''DateDispatched'').value(''.'', ''varchar(19)''),
@ER=Y.times.query(''DateEnroute'').value(''.'', ''varchar(19)'')
FROM (
SELECT CAST(y AS XML)
FROM OPENROWSET(
BULK ' + '''' + @FILENAME + '''' + ' ,
SINGLE_BLOB) AS T(y)
) AS T(y)
CROSS APPLY y.nodes(''/PCRDATA/Times'') AS Y(Times)
if exists(select* from [dbo].[MICU] where PRID= ' + '''' + CAST(@PRID AS VARCHAR(200)) + '''' + ' )
UPDATE [dbo].[MICU]
SET dbo.MICU.Dispatched= ' + ''''+ @DP + '''' + ' ,dbo.MICU.Enroute= ' + '''' + @er + '''' + '
where dbo.MICU.PRID= ' + '''' + CAST(@PRID AS VARCHAR(200)) + '''' + '
ELSE
INSERT INTO [dbo].[MICU] (PRID,Dispatched,Enroute) values( ' + '''' + CAST(@PRID AS VARCHAR(200)) + ''''
+ ',' + '''' + @DP + '''' + ' , ' + '''' + @er + '''' + ')'
PRINT (@SQL)
ASKER
SELECT
@DP= Y.times.query('DateDispatc hed').valu e('.', 'varchar(19)'),
@ER=Y.times.query('DateEnr oute').val ue('.', 'varchar(19)')
FROM (
SELECT CAST(y AS XML)
FROM OPENROWSET(
BULK 'Pawan.txt' ,
SINGLE_BLOB) AS T(y)
) AS T(y)
CROSS APPLY y.nodes('/PCRDATA/Times') AS Y(Times)
if exists(select* from [dbo].[MICU] where PRID= '10' )
UPDATE [dbo].[MICU]
SET dbo.MICU.Dispatched= 'Package' ,dbo.MICU.Enroute= 'Err'
where dbo.MICU.PRID= '10'
ELSE
INSERT INTO [dbo].[MICU] (PRID,Dispatched,Enroute) values( '10','Package' , 'Err')
@DP= Y.times.query('DateDispatc
@ER=Y.times.query('DateEnr
FROM (
SELECT CAST(y AS XML)
FROM OPENROWSET(
BULK 'Pawan.txt' ,
SINGLE_BLOB) AS T(y)
) AS T(y)
CROSS APPLY y.nodes('/PCRDATA/Times') AS Y(Times)
if exists(select* from [dbo].[MICU] where PRID= '10' )
UPDATE [dbo].[MICU]
SET dbo.MICU.Dispatched= 'Package' ,dbo.MICU.Enroute= 'Err'
where dbo.MICU.PRID= '10'
ELSE
INSERT INTO [dbo].[MICU] (PRID,Dispatched,Enroute) values( '10','Package' , 'Err')
Now please execute your last statement with actual parameters and give me the errors you are getting.
ASKER
I tried a print of it and go no re
declare @DP varchar(20)
declare @ER varchar(20)
declare @ATS varchar(20)
declare @ATP varchar(20)
declare @TODEST varchar(20)
declare @TRANSFER varchar(20)
declare @AVAIL varchar(20)
declare @DISPATCHLATLON varchar(25)
declare @INCIDENTLOC varchar(30)
DECLARE @SCENELONLAT VARCHAR(30)
declare @PRID int set @PRID=48633430
declare @PRID_S varchar(10) set @PRID_S='48633430'
declare @FILENAME VARCHAR(30) SET @FILENAME='c:\pcr\xml\'+@PRID_S+'.XML'
DECLARE @SQL AS VARCHAR(MAX) = '';
-- @PRID=47697063;
set @SQL= '
SELECT
@DP= Y.times.query(''DateDispatched'').value(''.'', ''varchar(19)''),
@ER=Y.times.query(''DateEnroute'').value(''.'', ''varchar(19)'')
FROM (
SELECT CAST(y AS XML)
FROM OPENROWSET(
BULK ' + '''' + @FILENAME + '''' + ' ,
SINGLE_BLOB) AS T(y)
) AS T(y)
CROSS APPLY y.nodes(''/PCRDATA/Times'') AS Y(Times)
if exists(select* from [dbo].[MICU] where PRID= ' + '''' + CAST(@PRID AS VARCHAR(200)) + '''' + ' )
UPDATE [dbo].[MICU]
SET dbo.MICU.Dispatched= ' + ''''+ @DP + '''' + ' ,dbo.MICU.Enroute= ' + '''' + @er + '''' + '
where dbo.MICU.PRID= ' + '''' + CAST(@PRID AS VARCHAR(200)) + '''' + '
ELSE
INSERT INTO [dbo].[MICU] (PRID,Dispatched,Enroute) values( ' + '''' + CAST(@PRID AS VARCHAR(200)) + ''''
+ ',' + '''' + @DP + '''' + ' , ' + '''' + @er + '''' + ')'
print (@SQL)
sponse
Thanks, checking.
ASKER
Thank you!
Update code for you .
OUTPUT I am getting..
declare @DP varchar(20) = 'DP'
declare @ER varchar(20) = 'Enroute'
declare @ATS varchar(20)
declare @ATP varchar(20)
declare @TODEST varchar(20)
declare @TRANSFER varchar(20)
declare @AVAIL varchar(20)
declare @DISPATCHLATLON varchar(25)
declare @INCIDENTLOC varchar(30)
DECLARE @SCENELONLAT VARCHAR(30)
declare @PRID int set @PRID=48633430
declare @PRID_S varchar(10) set @PRID_S='48633430'
declare @FILENAME VARCHAR(30) SET @FILENAME='c:\pcr\xml\'+@PRID_S+'.XML'
DECLARE @SQL AS VARCHAR(MAX) = '';
-- @PRID=47697063;
set @SQL=
'SELECT
@DP= Y.times.query(''DateDispatched'').value(''.'', ''varchar(19)''),
@ER=Y.times.query(''DateEnroute'').value(''.'', ''varchar(19)'')
FROM (
SELECT CAST(y AS XML)
FROM OPENROWSET(
BULK ' + '''' + @FILENAME + '''' + ' ,
SINGLE_BLOB) AS T(y)
) AS T(y)
CROSS APPLY y.nodes(''/PCRDATA/Times'') AS Y(Times)
if exists( select TOP 1 1 from [dbo].[MICU] where PRID= ' + '''' + @PRID_S + '''' + ' )
UPDATE [dbo].[MICU]
SET dbo.MICU.Dispatched= ' +'''' + @DP + '''' + ',dbo.MICU.Enroute= ' + '''' + @ER + '''' +
' WHERE dbo.MICU.PRID = ' + '''' + @PRID_S + '''' + '
ELSE
INSERT INTO [dbo].[MICU] (PRID,Dispatched,Enroute) values(' + '''' + @PRID_S + '''' + ',' + '''' + @DP + '''' + ',' + '''' + @ER + '''' + ')'
PRINT (@SQL)
OUTPUT I am getting..
/*------------------------
OUTPUT
------------------------*/
SELECT
@DP= Y.times.query('DateDispatched').value('.', 'varchar(19)'),
@ER=Y.times.query('DateEnroute').value('.', 'varchar(19)')
FROM (
SELECT CAST(y AS XML)
FROM OPENROWSET(
BULK 'c:\pcr\xml\48633430.XML' ,
SINGLE_BLOB) AS T(y)
) AS T(y)
CROSS APPLY y.nodes('/PCRDATA/Times') AS Y(Times)
if exists( select TOP 1 1 from [dbo].[MICU] where PRID= '48633430' )
UPDATE [dbo].[MICU]
SET dbo.MICU.Dispatched= 'DP',dbo.MICU.Enroute= 'Enroute' WHERE dbo.MICU.PRID = '48633430'
ELSE
INSERT INTO [dbo].[MICU] (PRID,Dispatched,Enroute) values('48633430','DP','Enroute')
ASKER
Looks like my bulk import is not working. set ER and DP to 'err' and query prints
SELECT
@DP= Y.times.query('DateDispatched').value('.', 'varchar(19)'),
@ER=Y.times.query('DateEnroute').value('.', 'varchar(19)')
FROM (
SELECT CAST(y AS XML)
FROM OPENROWSET(
BULK 'c:\pcr\xml\48633430.XML' ,
SINGLE_BLOB) AS T(y)
) AS T(y)
CROSS APPLY y.nodes('/PCRDATA/Times') AS Y(Times)
if exists(select* from [dbo].[MICU] where PRID= '48633430' )
UPDATE [dbo].[MICU]
SET dbo.MICU.Dispatched= 'ERR_DP' ,dbo.MICU.Enroute= 'ERR_ER'
where dbo.MICU.PRID= '48633430'
ELSE
INSERT INTO [dbo].[MICU] (PRID,Dispatched,Enroute) values( '48633430','ERR_DP' , 'ERR_ER')
Are you getting data from this ?
SELECT
@DP= Y.times.query('DateDispatched').value('.', 'varchar(19)'),
@ER=Y.times.query('DateEnroute').value('.', 'varchar(19)')
FROM
(
SELECT CAST(y AS XML)
FROM OPENROWSET(
BULK 'c:\pcr\xml\48633430.XML' ,
SINGLE_BLOB) AS T(y)
) AS T(y)
CROSS APPLY y.nodes('/PCRDATA/Times') AS Y(Times)
ASKER
Works!! what was the error?
Great ...
We missed this ..assignment.
declare @DP varchar(20) = ''
declare @ER varchar(20) = ''
if we use this declare @DP varchar(20) then the dynamic string does not work as it is NULL so we have set it ''.
We missed this ..assignment.
declare @DP varchar(20) = ''
declare @ER varchar(20) = ''
if we use this declare @DP varchar(20) then the dynamic string does not work as it is NULL so we have set it ''.
ASKER
Msg 137, Level 15, State 1, Line 2
Must declare the scalar variable "@DP".
Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'AS'.
SELECT
@DP= Y.times.query('DateDispatched').value('.', 'varchar(19)'),
@ER=Y.times.query('DateEnroute').value('.', 'varchar(19)')
FROM (
SELECT CAST(y AS XML)
FROM OPENROWSET(
BULK 'c:\pcr\xml\48633430.XML' ,
SINGLE_BLOB) AS T(y)
) AS T(y)
CROSS APPLY y.nodes('/PCRDATA/Times') AS Y(Times)
if exists( select TOP 1 1 from [dbo].[MICU] where PRID= '48633430' )
UPDATE [dbo].[MICU]
SET dbo.MICU.Dispatched= 'DP',dbo.MICU.Enroute= 'Enroute' WHERE dbo.MICU.PRID = '48633430'
ELSE
INSERT INTO [dbo].[MICU] (PRID,Dispatched,Enroute) values('48633430','DP','Enroute')
(1 row(s) affected)
Please use this complete code.
DECLARE @DP varchar(20) = ''
DECLARE @ER varchar(20) = ''
DECLARE @PRID int set @PRID=48633430
DECLARE @PRID_S varchar(10) set @PRID_S='48633430'
DECLARE @FILENAME VARCHAR(30) SET @FILENAME='c:\pcr\xml\'+@PRID_S+'.XML'
DECLARE @SQL AS VARCHAR(MAX) = '';
SET @SQL=
'SELECT
@DP= Y.times.query(''DateDispatched'').value(''.'', ''varchar(19)''),
@ER=Y.times.query(''DateEnroute'').value(''.'', ''varchar(19)'')
FROM (
SELECT CAST(y AS XML)
FROM OPENROWSET(
BULK ' + '''' + @FILENAME + '''' + ' ,
SINGLE_BLOB) AS T(y)
) AS T(y)
CROSS APPLY y.nodes(''/PCRDATA/Times'') AS Y(Times)
if exists( select TOP 1 1 from [dbo].[MICU] where PRID= ' + '''' + @PRID_S + '''' + ' )
UPDATE [dbo].[MICU]
SET dbo.MICU.Dispatched= ' +'''' + @DP + '''' + ',dbo.MICU.Enroute= ' + '''' + @ER + '''' +
' WHERE dbo.MICU.PRID = ' + '''' + @PRID_S + '''' + '
ELSE
INSERT INTO [dbo].[MICU] (PRID,Dispatched,Enroute) values(' + '''' + @PRID_S + '''' + ',' + '''' + @DP + '''' + ',' + '''' + @ER + '''' + ')'
EXEC(@SQL)
ASKER
looks like its failing here:
@DP= Y.times.query(''DateDispat ched'').va lue(''.'', ''varchar(19)''),
@ER=Y.times.query(''DateEn route'').v alue(''.'' , ''varchar(19)'')
@DP= Y.times.query(''DateDispat
@ER=Y.times.query(''DateEn
Please use this --complete code-
DECLARE @DP varchar(20) = ''
DECLARE @ER varchar(20) = ''
DECLARE @PRID int set @PRID=48633430
DECLARE @PRID_S varchar(10) set @PRID_S='48633430'
DECLARE @FILENAME VARCHAR(30) SET @FILENAME='c:\pcr\xml\'+@PRID_S+'.XML'
DECLARE @SQL AS VARCHAR(MAX) = '';
SET @SQL=
'SELECT
@DP= Y.times.query(''DateDispatched'').value(''.'', ''varchar(19)''),
@ER=Y.times.query(''DateEnroute'').value(''.'', ''varchar(19)'')
FROM (
SELECT CAST(y AS XML)
FROM OPENROWSET(
BULK ' + '''' + @FILENAME + '''' + ' ,
SINGLE_BLOB) AS T(y)
) AS T(y)
CROSS APPLY y.nodes(''/PCRDATA/Times'') AS Y(Times)
if exists( select TOP 1 1 from [dbo].[MICU] where PRID= ' + '''' + @PRID_S + '''' + ' )
UPDATE [dbo].[MICU]
SET dbo.MICU.Dispatched= ' +'''' + @DP + '''' + ',dbo.MICU.Enroute= ' + '''' + @ER + '''' +
' WHERE dbo.MICU.PRID = ' + '''' + @PRID_S + '''' + '
ELSE
INSERT INTO [dbo].[MICU] (PRID,Dispatched,Enroute) values(' + '''' + @PRID_S + '''' + ',' + '''' + @DP + '''' + ',' + '''' + @ER + '''' + ')'
EXEC(@SQL)
ASKER
same problem :
Msg 137, Level 15, State 1, Line 2
Must declare the scalar variable "@DP".
Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'AS'.
SELECT
@DP= Y.times.query('DateDispatc hed').valu e('.', 'varchar(19)'),
@ER=Y.times.query('DateEnr oute').val ue('.', 'varchar(19)')
FROM (
SELECT CAST(y AS XML)
FROM OPENROWSET(
BULK 'c:\pcr\xml\48633430.XML' ,
SINGLE_BLOB) AS T(y)
) AS T(y)
CROSS APPLY y.nodes('/PCRDATA/Times') AS Y(Times)
if exists( select TOP 1 1 from [dbo].[MICU] where PRID= '48633430' )
UPDATE [dbo].[MICU]
SET dbo.MICU.Dispatched= '',dbo.MICU.Enroute= '' WHERE dbo.MICU.PRID = '48633430'
ELSE
INSERT INTO [dbo].[MICU] (PRID,Dispatched,Enroute) values('48633430','','')
Msg 137, Level 15, State 1, Line 2
Must declare the scalar variable "@DP".
Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'AS'.
SELECT
@DP= Y.times.query('DateDispatc
@ER=Y.times.query('DateEnr
FROM (
SELECT CAST(y AS XML)
FROM OPENROWSET(
BULK 'c:\pcr\xml\48633430.XML' ,
SINGLE_BLOB) AS T(y)
) AS T(y)
CROSS APPLY y.nodes('/PCRDATA/Times') AS Y(Times)
if exists( select TOP 1 1 from [dbo].[MICU] where PRID= '48633430' )
UPDATE [dbo].[MICU]
SET dbo.MICU.Dispatched= '',dbo.MICU.Enroute= '' WHERE dbo.MICU.PRID = '48633430'
ELSE
INSERT INTO [dbo].[MICU] (PRID,Dispatched,Enroute) values('48633430','','')
Got the error. Checking.
Please use this ..
DECLARE @DP varchar(20) = ''
DECLARE @ER varchar(20) = ''
DECLARE @PRID int set @PRID=48633430
DECLARE @PRID_S varchar(10) set @PRID_S='48633430'
DECLARE @FILENAME VARCHAR(30) SET @FILENAME='c:\pcr\xml\'+@PRID_S+'.XML'
DECLARE @SQL AS VARCHAR(MAX) = '';
IF OBJECT_ID('tempdb..#tempDATA') IS NOT NULL DROP TABLE #tempDATA
CREATE TABLE #tempDATA ( DP VARCHAR(20) , ER VARCHAR(20) )
INSERT INTO #tempDATA
EXEC ( '
SELECT
Y.times.query(''DateDispatched'').value(''.'', ''varchar(19)''),
Y.times.query(''DateEnroute'').value(''.'', ''varchar(19)'')
FROM
(
SELECT CAST(y AS XML)
FROM OPENROWSET(
BULK ' + '''' + @FILENAME + '''' + ' ,
SINGLE_BLOB) AS T(y)
) AS T(y)
CROSS APPLY y.nodes(''/PCRDATA/Times'') AS Y(Times) '
)
SELECT TOP 1 @DP = DP , @ER = ER FROM #tempDATA
IF EXISTS( select TOP 1 1 from [dbo].[MICU] where PRID = '''' + @PRID_S + '''' )
UPDATE [dbo].[MICU]
SET dbo.MICU.Dispatched= '''' + @DP + '''' + ',dbo.MICU.Enroute= ' + '''' + @ER + ''''
WHERE dbo.MICU.PRID = '''' + @PRID_S + ''''
ELSE
INSERT INTO [dbo].[MICU] (PRID,Dispatched,Enroute) values('''' + @PRID_S + '''' + ',' + '''' + @DP + '''' + ',' + '''' + @ER + '''' )
ASKER
doesn't make sense:
Msg 109, Level 15, State 1, Line 36
There are more columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.
Msg 109, Level 15, State 1, Line 36
There are more columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.
ASKER
I ran the sql up to before the if "exists" and I am getting valid data to print:
(1 row(s) affected)
2017-11-26T09:37:00
2017-11-26T09:39:00
48620039
(1 row(s) affected)
2017-11-26T09:37:00
2017-11-26T09:39:00
48620039
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I fixed I removed the quotes around the @PRD_S, @DP and @ER. Thank you so much. Have a great day!!
ASKER
Thanks so much. Pawan!!
Great ! Glad to help as always:)
Open in new window