Build Cursor using sp_executesql/exec

[ahartong]

I am trying to build statements using a variable for my database name.(Ex. @DB.dbo.tablename) This is working fine using sp_executesql, but I am having trouble building a cursor from a large select statement with joins. It seems to let me run the statement, but it does not seem to keep the cursor or create it for that matter in memory. Can I even do this? Is there an easier way to build the small statements using a variable as the db name, and is this the best/only method?

Here is what I am doing: I have also tried the @ParmDefinition, but that doesn't seem to apply. Any ideas?

SET @SQLString = 'declare A_Cursor for SELECT
[Shipments].[orderid] as [SOPNUM],[Shipments].[PRONumber] as [PRONUM],[Shipments].[ShipViaID] as [SHPMTHD],
[Carrier Services].[TransitDays] AS [TransitDays],
[Shipping Containers].[Type] AS [CtnrType],[Shipping Containers].UCC128Number AS [UCC128],[Shipping Containers].CarrierTrackingNo AS [TrackingNUM],[Shipping Containers].Weight as [Weight],[Shipping Containers].ContainerNumber,
[Items].ITEMNMBR AS [ItemNUM],[ItemS].ITEMSHWT AS [ITEMWEIGHT],
[Shipment Items].QuantityFilled AS [QtyFilled], [Shipment Items].UOMID as [UOFM],[Shipment Items].ParentOrderDetailID as [LineSeq],
[SOPHDR].[PRBTADCD],[SOPHDR].[PRSTADCD],[SOPHDR].[SOPTYPE],[SOPHDR].[LOCNCODE],
[LOCXREF].CUSTNMBR,
[ITEMXREF].ESIPKCID,[ITEMXREF].ESICPCID,
DatabaseSources.Catalog as [Company]

FROM
OrderDetail as [Shipment Items]
INNER JOIN Containers AS [Shipping Containers] ON [Shipment Items].ContainerID = [Shipping Containers].ContainerID
INNER JOIN ShipRecv AS [Shipments] ON [Shipping Containers].ShipRecvID = [Shipments].ShipRecvID
INNER JOIN ShipMethods AS [Ship Methods] ON [Shipments].ShipViaID = rtrim([Ship Methods].MasterID)
AND [Shipments].CompanyID = [Ship Methods].CompanyID
INNER JOIN CarrierServices AS [Carrier Services] ON [Ship Methods].CarrierServiceID = [Carrier Services].ShipViaID
INNER JOIN Carriers AS [Carriers] ON [Carrier Services].CarrierID = Carriers.CarrierID
INNER JOIN ' + @INTERID + '.dbo.RM00101 AS [Customers] ON [Shipments].CVID = [Customers].CUSTNMBR
INNER JOIN ' + @INTERID + '.dbo.IV00101 AS [Items] ON [Shipment Items].ItemID = Items.ITEMNMBR

--INNER join ' + @INTERID + '.dbo.SOP10100 as [SOPHDR] on [Shipments].[orderid] = SOPHDR.SOPNUMBE
INNER join ' + @INTERID + '.dbo.SOP30200 as [SOPHDR] on [Shipments].[orderid] = SOPHDR.SOPNUMBE

inner join ' + @INTERID + '.dbo.ESI40300 as [LOCXREF] ON [SOPHDR].CUSTNMBR = LOCXREF.ESIACUNM
AND [SOPHDR].PRSTADCD = LOCXREF.PRSTADCD
inner join ' + @INTERID + '.dbo.ESI41000 as [ASNXREF] ON [LOCXREF].CUSTNMBR = ASNXREF.CUSTNMBR
inner join ' + @INTERID + '.dbo.ESI40200 as [ITEMXREF] ON [LOCXREF].CUSTNMBR = ITEMXREF.CUSTNMBR
AND [Items].ITEMNMBR =ITEMXREF.ITEMNMBR
inner join DatabaseSources on [Shipments].[CompanyID] = DatabaseSources.LocationID

WHERE [Shipment Items].RecordType = 64
and [Shipment Items].Deleted = 0
and [ASNXREF].ESIPRDUC <> ' + '''' + '' + '''' +
' and [Shipments].companyid = ' + @VSHIPCOID +
' and [Shipments].ShipRecvNumber = ' + @SHIPID +
' open A_Cursor'


EXECUTE sp_executesql @SQLString

FETCH NEXT FROM A_Cursor into @SOPNUM, @PRONUM, @SHIPMTHD,
@TransitDays, @CtnrType, @UCC128, @TrackingNum, @Weight, @ContainerNum, @ItemNum,
@ItemWeight, @QtyFilled, @UOFM, @LineSeq, @PRBTADCD, @PRSTADCD, @SOPTYPE, @LOCNCODE,
@CUSTNMBR, @ESIPKCID, @ESICPCID, @COMPANY

 

[nigelrivett]

Try it with something simpler first then build on that
You are missing 'cursor' in the declare statement

declare @id int
declare @SQLString nvarchar(1000)
select @SQLString = 'declare A_Cursor cursor for select id from sysobjects open A_Cursor'
EXECUTE sp_executesql @SQLString
FETCH NEXT FROM A_Cursor into @id
select @id
FETCH NEXT FROM A_Cursor into @id
select @id
FETCH NEXT FROM A_Cursor into @id
select @id
close A_Cursor
deallocate A_Cursor


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.