sql server programming: return string -- sql server programming

return string

j
10/16/2007 11:07:18 PM

sql server programming:

Hello,

I am trying to query a database and return the information in a zpl
(zebra printer language) string. I know the query and I know what the
string needs to be but and having problems.

Here's the goods (or in my case the bads):

DECLARE @vchBrand varchar(20)
DECLARE @vchFlav varchar(20)
DECLARE @vchCodeDT varchar(7)
DECLARE @vchPlant varchar(7)
DECLARE @intPalletID int
DECLARE @today AS datetime
DECLARE @vchPalletLabelString as varchar(462)

set @today = '10/14/07'

Select @vchBrand = Min(ib_desc_short)
, @vchFlav = min(if_desc_short)
, @vchCodeDT = Cast(MIN(pd_code_date) as varchar(7))
, @vchPlant = min(Replace(substring(pi_plantNumber,9,6),'-','') +
substring(Cast(datepart(yyyy, getdate())as varchar(4)),3,2))
from pallet_header
Join pallet_detail
On ph_pallet_id = pd_pallet_header_id
Join Item_master
On pd_lineno = im_lineno
join item_flavor
On if_desc = im_flavor
join Item_Brand
on ib_desc = im_brand
, plant_info
where ph_sched_date = @today

Set @vchPalletLabelString = '^XA
^PW1200
^BY12,3,272^FT890,150^B3R,N,,Y,N
^FD' + Cast (@intPalletID as varchar(9)) + '^FS
^FT132,420^A0R,292,290^FH\^FD' + @vchCodeDT +'^FS
^FT492,183^A0R,292,290^FH\^FD' + @vchBrand + ' ' + @vchFlav + '^FS
^FT40,840^A0R,42,40^FH\^FD' + @vchPlant + '^FS
^PQ1,0,1,Y^XZ'

So next I add :
print @vchPalletLabelString

And there nothing on the screen, no error messages to work from.

I am not sure what I am doing wrong. I need to be able to put this
into a stored procedure and call it from vbscript so I can add a
button on a HMI screen to automatically print a pallet tag.

Thanks, j

Re: return string

Tom Cooper
10/17/2007 2:43:45 AM

It looks like you are not putting a value in @intPalletID, so it will be
null, so when you cast it as a varchar, that will still be null and when you
concatenate strings together the result is null if any of the strings you
are concatening are null. When you print a null, you won't see anything.

I would recommend two things while you are testing this. First, just before
you put the value into @vchPalletLabelString, do a SELECT of all the
variables that go into making up @vchPalletLabelString to make sure they
have correct values. Then, after you pute the value into
@vchPalletLabelString, do a SELECT on it and it will show you the value in
@vchPalletLabelString (even if it is null, which print will not do).

Tom

[quoted text, click to view]