SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 openquery with variables using varchar workaround
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

koenslootmans
Starting Member

Belgium
1 Posts

Posted - 04/05/2013 :  08:39:36  Show Profile  Reply with Quote
Hello,

I have an openquery which is being passed variables in a procedure
It compiles fine but When i run it i get the following error:

Msg 245, Level 16, State 1, Procedure GET_SLOWMOVERSINFO, Line 24
Conversion failed when converting the nvarchar value 'select count(1) as nrofreference

here is the procedure...

Create PROCEDURE [dbo].[GET_SLOWMOVERSINFO]
@PickLocation nvarchar(30),
@StorageLocationWarehouse nvarchar(30),
@StorageLocationAisle nvarchar(30),
@PickBinType int


AS
BEGIN

SET NOCOUNT ON;

-- Insert statements for procedure here
insert into dbo.ProcedureLog values(SYSDATETIME(),'GET_SLOWMOVERSINFO','started',' ');

delete from dbo.KPI_Inventory_Health where datediff(DAY,dateofloading, SYSDATETIME()) = 0 and PickBinType = @PickBinType
and PickLocation = @PickLocation and @StorageLocationAisle = StorageLocationAisle and @StorageLocationWarehouse = StorageLocationWarehouse;



Declare @TSQL nvarchar(4000);
set @TSQL = 'select count(1) as nrofreferences, sqdept, Product_Type, Product_Status, sum(qtystocked) as qtystocked, sum(boxesstored) as boxesstocked, sum(valuestocked) as salesvaluestocked
from (select
sqdept||''-''||sqstyl||''-''||sqcolr||''-''||sqsize as a_reference,
sqdept,
rmdesc,
MAS.RMFRCT,
sum(bstk.sqqtys) as qtystocked,
sum(bstk.sqqtys*rmsspr) as valuestocked,
sum(bstk.sqqtys)/rmpunu as boxesstored,
qty_out,
max_box_out,
avg_box_out,
boxes_out,
nof_weeks_picked as nbr_times_picked,
rmpunu,
rmsspr,
last_act_yy,
last_act_ww,
lastactivedate,
case
when (max_box_out <= 2 and max_box_out >1 and sysdate - rmfrct >28 ) then ''Slow mover''
when (max_box_out <= 1 and sysdate - rmfrct >28) then ''Very slow mover''
else ''Normal'' end as Product_type,
case
when (sysdate - rmfrct >28 and sysdate - lastactivedate > 28 ) then ''Sleeping''
when (sysdate - rmfrct >140 and sysdate - lastactivedate > 140 ) then ''Death''
When (sysdate - rmfrct <= 28) then ''NEW''
else ''Normal''
end as Product_status,
sqdept as dept
from
stkbstk bstk,
stkmas mas,
pick_aisle_loc ais,
(select qrrdept,qrrstyl, qrrsize,qrrcolr, max(lastactivedate) as lastactivedate from
(select qrdept as qrrdept, qrstyl as qrrstyl, qrsize as qrrsize, qrcolr as qrrcolr, max(qrcreated_dt) as lastactivedate from stkpick
group by qrdept, qrstyl,qrsize,qrcolr
union select qrdept as qrrdept, qrstyl as qrrstyl, qrsize as qrrsize, qrcolr as qrrcolr, max(qrcreated_dt) as lastactivedate from stkpick_hist
group by qrdept, qrstyl,qrsize,qrcolr
)
group by qrrdept,qrrstyl,qrrsize,qrrcolr
)
,
(
select

max(crea_yy) as last_act_yy,
max(crea_ww) as last_act_ww,
mas.rmdesc as description,
qrdept,
qrstyl,
qrcolr,
qrsize,
sum(quantityout) as qty_out,
sum(quantityout/rmpunu) as boxes_out ,
max(quantityout/rmpunu) as max_box_out,
avg(quantityout/rmpunu) as avg_box_out,
count(1) as nof_weeks_picked
from
(
select

crea_yy,
crea_ww,
qrdept,
qrstyl,
qrcolr,
qrsize,
sum(quantityOut) as quantityOUT
from
(
select

to_char(qrcreated_dt,''yy'') as crea_yy,
to_char(qrcreated_dt,''ww'') as crea_ww,
qrdept,
qrstyl,
qrcolr,
qrsize,
sum(qrrqty) as quantityOUT
from
Disport.stkpick pik,
disport.pick_aisle_loc ais
where
pik.qraisl = ais.AISLE and
aisle_com '''+ @PickLocation +''' and
qrcreated_dt > sysdate - 140
group by
to_char(qrcreated_dt,''yy''),
to_char(qrcreated_dt,''ww''),
qrdept,
qrstyl,
qrcolr,
qrsize
union
select
to_char(qrcreated_dt,''yy'') as crea_yy,
to_char(qrcreated_dt,''ww'') as crea_ww,
qrdept,
qrstyl,
qrcolr,
qrsize,
sum(qrrqty) as quantityOUT
from
Disport.stkpick_hist hist,
Disport.pick_aisle_loc ais
where
qraisl = ais.AISLE and
aisle_com '''+@PickLocation+''' and
qrcreated_dt > sysdate - 140
group by
to_char(qrcreated_dt,''yy''),
to_char(qrcreated_dt,''ww''),
qrdept,
qrstyl,
qrcolr,
qrsize
)
group by
crea_yy,
crea_ww,
qrdept,
qrstyl,
qrcolr,
qrsize
) p1,
stkmas mas
where
qrdept = rmdept and
qrstyl = rmstyl and
qrcolr = rmcolr and rmcomp = 1 and

qrsize = rmsize
group by
qrdept,
qrstyl,
qrcolr,
qrsize,
rmpunu,
rmdesc
) p2
where
qrdept(+) = sqdept and
qrstyl(+) = sqstyl and
qrcolr(+) = sqcolr and
qrsize(+) = sqsize and
qrrstyl = qrstyl and qrrsize = qrsize and qrrcolr = qrcolr and qrrdept = qrdept and
sqdept = rmdept and
sqstyl = rmstyl and
sqcolr = rmcolr and
sqsize = rmsize and
rmcomp = 1 and
sqaisl = ais.AISLE and
aisle_com '''+@StorageLocationWarehouse+''' and Sqaisle '''+@StorageLocationAisle+'''
and SQBTYP = '''+@PickBinType+'''
group by
sqdept,
sqstyl,
sqcolr,
sqsize,
qty_out,
boxes_out,
last_act_yy,
last_act_ww,
rmpunu,

rmfrct,

rmsspr,lastactivedate,
rmdesc,
avg_box_out,
max_box_out,
nof_weeks_picked)
group by sqdept, product_type,product_status'




set @TSQL = N'insert into dbo.KPI_Inventory_Health select SYSDATETIME() as dateofloading,
'''+@PickBinType+''' as PickBinType,
'''+@PickLocation+''' as PickLocation,
'''+@StorageLocationWarehouse+''' as StorageLocationWarehouse,
'''+@StorageLocationAisle+''' as StorageLocationAisle,
NROFREFERENCES,SQDEPT, PRODUCT_TYPE , PRODUCT_STATUS, qtystocked,boxesstocked,salesvaluestocked from openquery(Disport,''' + REPLACE(@TSQL, '''', '''''') + ''')'

print @TSQL;
exec (@TSQL);

insert into dbo.ProcedureLog values(SYSDATETIME(),'GET_SLOWMOVERSINFO','completed','ALL');



END
commit

Lamprey
Flowing Fount of Yak Knowledge

4613 Posts

Posted - 04/05/2013 :  11:44:21  Show Profile  Reply with Quote
Change:
and SQBTYP = '''+@PickBinType+'''

To:
and SQBTYP = '''+ CAST(@PickBinType AS VARCHAR(20)) +'''

Alwasy try to avoid impicit conversions.

Edited by - Lamprey on 04/05/2013 11:45:01
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000