必须在SQL Server中使用BCP声明标量变量

I have the following section of my stored procedure code and for some reason, I keep getting the error messages:

我有我的存储过程代码的以下部分,由于某种原因,我不断收到错误消息:

Must declare scalar variable "@query"

必须声明标量变量“@query”

Statement(s) could not be prepared

声明无法准备

I am also attempting to use BCP to export to a file and the @query string is a complex string. Here's the code:

我也尝试使用BCP导出到文件,@ query字符串是一个复杂的字符串。这是代码:

@market varchar(20) = null, 

@date datetime = null


AS
BEGIN
    SET NOCOUNT ON;

 set @date =

 CASE datepart(dw,getdate())

 WHEN 1 THEN dateadd(day, -1, convert(varchar, getdate(), 101))

 WHEN 2 THEN dateadd(day, -2, convert(varchar, getdate(), 101))

 WHEN 3 THEN dateadd(day, -3, convert(varchar, getdate(), 101))

 WHEN 4 THEN dateadd(day, -4, convert(varchar, getdate(), 101))

 WHEN 5 THEN dateadd(day, -5, convert(varchar, getdate(), 101))

 WHEN 6 THEN dateadd(day, +0, convert(varchar, getdate(), 101))

 WHEN 7 THEN dateadd(day, -7, convert(varchar, getdate(), 101))

 END
  --Set @date = '2014-12-27 00:00:00.000'
  Select @date  -- Latest Saturday (Day Number)
  SELECT CONVERT(CHAR(8), @date, 112)
  declare @query VARCHAR(4000)

 SELECT @query = 'select ''"'' + CONVERT(varchar,fo.orderno) + ''"'' as [STOP_ID], 
  CASE WHEN (isnull(i.pickuptime,'''') <> '''') THEN convert(datetime,i.pickuptime,126) ELSE convert(datetime,fo.pickeduptime,126) END as [PICKUP_TIME], 
  CASE WHEN (isnull(i.deliveredtime,'''') <> '''') THEN convert(datetime,i.deliveredtime,126) ELSE convert(datetime,fo.deliveredtime,126) END as [DELIVERY_TIME], 
  CASE WHEN (isnull(i.pickupcontact,'''') <> '''') THEN ''"'' + i.pickupcontact + ''"'' ELSE ''"'' + og.marketsitenumber + ''"'' END as [SENDER_CODE], 
  CASE WHEN (isnull(i.pickupcompany,'''') <> '''') THEN ''"'' + i.pickupcompany + ''"'' ELSE ''"'' + fo.pickupcompanyname + ''"'' END as [SENDER_NAME], 
  ''"'' + (CONVERT(varchar, fo.PickupstreetNo) + '' '' + fo.PickupStreet + '' '' + fo.PickupUnit) + ''"'' as [SENDER_STREET], 
  ''"'' + fo.pickupcity + ''"'' as [SENDER_CITY], 
  ''"'' + fo.pickupprovince + ''"'' as [SENDER_STATE], 
  ''"'' + fo.pickuppostalcode + ''"'' as [SENDER_POSTAL_CODE], 
  ''"US"'' as [SENDER_COUNTRY], 
  CASE WHEN (isnull(i.driver,'''') <> '''') THEN ''"'' + i.driver + ''"'' ELSE ''"'' + CONVERT(varchar,fo.pickupdriver) + ''"'' END as [DRIVER_ID], 
  CASE WHEN (isnull(i.deliveryfacilitycode,'''') <> '''') THEN ''"'' + i.deliveryfacilitycode + ''"'' ELSE ''""'' END as [RECIPIENT_CODE], --Issue for left-joined "FO Only" records:  No safety net. 
  CASE WHEN (isnull(i.deliverycompanyname,'''') <> '''') THEN ''"'' + i.deliverycompanyname + ''"'' ELSE ''"'' + fo.deliverycompanyname + ''"'' END as [RECIPIENT_NAME], 
  ''"'' + (CONVERT(varchar, fo.deliverystreetNo) + '' '' + fo.DeliveryStreet + '' '' + fo.DeliveryUnit) + ''"'' as [RECIPIENT_STREET], 
  ''"'' + fo.deliverycity + ''"'' as [RECIPIENT_CITY], 
  ''"'' + fo.deliveryprovince + ''"'' as [RECIPIENT_STATE], 
  ''"'' + fo.deliverypostalcode + ''"'' as [RECIPIENT_POSTAL_CODE], 
  ''"US"'' as [RECIPIENT_COUNTRY_CODE], 
  CASE WHEN (og.accounttype = ''stat'' and st.servicetypeid in (''324'',''154'',''156'',''122'',''303'',''290'')) THEN ''"STAT"'' 
        WHEN ((og.accounttype = ''stat'' and st.servicetypeid in (''186'',''304'',''305'')) or (og.accounttype = ''scheduled'' and fo.[route] in (''B1'',''B2'',''B3'',''B4''))) THEN ''"MULTI-STAT"'' 
        WHEN (fo.route not like ''LH%'' and og.accounttype = ''scheduled'' and st.servicetypeid not in (''329'') and fo.[route] not in (''B1'',''B2'',''B3'',''B4'')) THEN ''"ROUTE"'' 
        WHEN ((og.accounttype = ''scheduled'' and st.servicetypeid in (''329'')) or (og.accounttype = ''stat'' and st.servicetypeid in (''372'',''373'',''374'',''377'',''386'',''329'',''356'',''387'',''388'',''389'',''390'',''391'',''392''))) THEN ''"SWEEP"'' 
        WHEN (og.accounttype = ''scheduled'' and fo.route like ''LH%'') THEN ''"LINE-HAUL"'' 
        ELSE ''"PLEASE CONTACT PDI IT WITH THIS STOP_ID"'' END as [DELIVERY_TYPE], 
  CASE WHEN (fo.route not like ''LH%'' and og.accounttype = ''scheduled'' and st.servicetypeid not in (''329'') and fo.[route] not in (''B1'',''B2'',''B3'',''B4'')) THEN ''"'' + fo.reference + ''"'' ELSE ''""'' END as [ROUTE_REFERENCE], 
  CASE WHEN (fo.route not like ''LH%'' and og.accounttype = ''scheduled'' and st.servicetypeid not in (''329'') and fo.[route] not in (''B1'',''B2'',''B3'',''B4'')) THEN ''"0"'' ELSE ''"'' + convert(varchar,fo.distance) + ''"'' END as [MILEAGE], 
  CASE WHEN ((og.accounttype = ''scheduled'') or (og.accounttype = ''stat'' and fo.servicetypeid in (329))) THEN ''"'' + convert(varchar,fo.waitingtimedriver1) + ''"'' ELSE ''"0"'' END as [WAIT_TIME], 
  ''""'' as [WAIT_START_TIME],    --"The time the courier began waiting after the grace period."  Not currently captured anywhere in system. 
  CASE WHEN (((og.accounttype = ''scheduled'') or (og.accounttype = ''stat'' and fo.servicetypeid in (329))) and fo.waitingtimedriver1 > 0) THEN ''"'' + fo.reference + ''"'' ELSE ''""'' END as [WAIT_TIME_ROUTE_REFERENCE], 
  ''""'' as [ARRIVAL_TIME], --"The time the courier arrived at the Pharmacy to pick up the shipment.  REQUIRED in the case of wait time."  Not currently captured anywhere in system. 
  ''""'' as [DEPARTURE_TIME], ----"The time the courier departed from the Pharmacy with the shipment.  REQUIRED in the case of wait time."  Not currently captured anywhere in system. 
  ''"USD"'' as [CURRENCY], 
  ''"0"'' as [FUEL_SURCHARGE], 
  ''"0"'' as [TAX], 
  ''"0"'' as [HOSPICE_CHARGE], 
  ''"0"'' as [MISC_CHARGE], 
  ''"'' + convert(varchar,fo.totalamount) + ''"'' as [TOTAL_CHARGE] 
  from finalizedorders fo 
inner join servicetypes st on fo.servicetypeid = st.servicetypeid 
inner join aropentransactions at on fo.invoicenumber = at.transactionnumber 
inner join OBR_Generator og on og.AccountNumber = fo.AccountNumber 
LEFT JOIN OrderSearchView osv ON fo.OrderNo = osv.[Order No] 
  LEFT JOIN [IRIS\SQLEXPRESS].IDSImport.dbo.IDSData i ON osv.[Cust. Field 2] = i.TripID 
where og.Market = ''Columbus'' 

and at.transactiondate = ''20150124'' --Previous Saturday (Always) 
    and fo.totalamount > 0 
    and og.accounttype <> ''invoice''


queryout "C:\bcptest.txt" -T -c -t,'

PRINT @query

EXEC xp_cmdshell 'bcp @query queryout "C:\bcptest.txt" -T -c -t,'



END

1 个解决方案

#1

@DBNull is correct. You need to change the actual execution of the bcp command to something like this....

@DBNull是正确的。你需要将bcp命令的实际执行更改为这样的....

SET @query = 'bcp "' + @query + '" queryout "C:\bcptest.txt" -T -c -t,';
EXEC xp_cmdshell @query;

You need to wrap the query in quotes, and there's a limit of varchar(8000) or nvarchar(4000) on the actual command you can execute with xp_cmdshell. I would suggest you consider wrapping your query in a view or a stored procedure. Not only would that greatly enhance the maintainability of the code, but you wouldn't need to worry about the size limit, not to mention all the single quote escaping you're having to do now.

您需要将引号括在引号中,并且对于可以使用xp_cmdshell执行的实际命令,存在varchar(8000)或nvarchar(4000)的限制。我建议你考虑在视图或存储过程中包装你的查询。这不仅会大大增强代码的可维护性,而且您不必担心大小限制,更不用说您现在必须要做的所有单引号。

Hope that helps.

希望有所帮助。

#1