--Set ESP On 

declare @tblSalesDoc table (Sales_Doc_Id int, OtherSalesInfo varchar(100))
Insert Into @tblSalesDoc
Select 1, 'This is sale #1, 1 non-promo item'
Union Select 2, 'This is sale #2, multiple non-promo items'
Union Select 3, 'This is sale #3, 1 promo item'
Union Select 4, 'This is sale #4, multiple promo items'
Union Select 5, 'This is sale #5, multiple non-promo items and multiple promo items'

declare @tblSalesDocItems table (Sales_Doc_Id int, LineItem int, PromoSerial varchar(100), itemSerial varchar(100))
Insert Into @tblSalesDocItems
Select 1, 1, null, 'abcd1234'
Union Select 2, 1, null, 'abcd1234'
Union Select 2, 2, null, 'zyxw9876'
Union Select 3, 1, 'promo1', 'abcd1234'
Union Select 3, 2, 'promo1', 'zyxw9876'
Union Select 4, 1, 'promo1', 'abcd1234'
Union Select 4, 2, 'promo1', 'zyxw9876'
Union Select 4, 3, 'promo2', 'abcd1234'
Union Select 4, 4, 'promo2', 'wjkf5542'
Union Select 4, 5, 'promo2', 'fkek5871'

Union Select 5, 1, null, 'abcd1234'
Union Select 5, 2, null, 'zyxw9876'
Union Select 5, 3, 'promo1', 'abcd1234'
Union Select 5, 4, 'promo1', 'zyxw9876'
Union Select 5, 5, 'promo2', 'abcd1234'
Union Select 5, 6, 'promo2', 'wjkf5542'
Union Select 5, 7, 'promo2', 'fkek5871'


--Select * From @tblSalesDoc

--Select * From @tblSalesDocItems


Select 
	Sales_Doc_Id, 
	OtherSalesInfo, 
	LineItem = min(LineItem),
	isPromo, 
	ItemSerial 
From 
	(
	Select 
		A.Sales_Doc_Id,
		A.OtherSalesInfo,
		B.LineItem,
		isPromo = case when PromoSerial is not null then 1 else 0 end,
		ItemSerial = isnull(PromoSerial,itemSerial)
	From @tblSalesDoc A
	Inner Join @tblSalesDocItems B
	On A.Sales_Doc_Id = B.Sales_Doc_Id
	) Z
Group By Sales_Doc_Id, OtherSalesInfo, isPromo, ItemSerial

--Set ESP Off

quote:

---

Originally posted by dragon_ballz96

Hi,

I am trying to pass the result of a executed dynamic query to a curosr. I have trying something like this but didn't work:

declare @sql nvarchar(300)

set @sql = 'select * from tblLocation'

declare location_cursor cursor for
exec @sql

Anyone got an idea on how to do it correctly? Let me know. Thks.

Rgds
Ryan

---