Creating a datafile via UDF, Sproc?
-
Hoping some bright TSQL expert can help me out....
I work with automotive dealerships. I'm writing some jobs to batch data to different companies that sell vehicles online. The info in the data file is all about a vehicle for sale and includes where images for that vehicle can be found on our ftp site. I'm creating the file from a sproc using bcp.
The data in in an inventory table populated from a transactiona system. The db table only contains the number of images per stocknumber, not the actual names. The dealerships have an assumed naming convention based on the stocknumber. There's three variations so far, for naming conventions, stocknumber_<number>.JPG, stocknumber_<alpha>.JPG, or stocknumber(<number>
.JPG.In the past, I needed to create one record for each vehicle with the names of the images on our ftp site as a single quote delimited column. I just wrote a UDF for this. It is listed below and is name fn_GetImageNames.
>>12345,'http://www.server.com/Images/12345_A.JPG,http://www.server.com/Images/12345_B.JPG'
Now, I need the query result to be 1 row for each vehicle image. >>12345,http://www.server.com/Images/12345_A.JPG
>>12345,http://www.server.com/Images/12345_B.JPG
Originally, I thought I could just take the results from my UDF (comma seperated list) and use a UDF split I found here on sqlserver central, but I couldn't get that to work. Then, I thought I'd just rerwite my current UDF to output a row for each image. I ended up using dynamic sql, since the number of images can be dynamic, which is a no no in a UDF.
Any ideas on how I can accomplish this?
I'd really appreciate any comments or suggestions!!
This is what I want for output:
StockNumber image
12345 12345_A.JPG
12345 12345_B.JPG
12345 12345_C.JPG
12345 12345_D.JPG
12345 12345_E.JPG
Q2346 Q2346_A.JPG
Q2346 Q2346_B.JPG
Q2346 Q2346_C.JPG
Here's some things I tried (using some UDFs and sprocs from below):
--Working version of Comma delimited UDF
select stocknumber,
tempdb.dbo.fn_GetImageNames(stocknumber,2,1,1)
from tempdb.dbo.inventory i
--Results:
--Working version of the Split UDF (not quite what I need b/c the input comma delimited string needs to by dynamic
select * from tempdb.dbo.fn_Split('htp://www.interflowinc.com/Images/CHEV-0/60173_A.JPG,http://www.interflowinc.com/Images/CHEV-0/60173_B.JPG,http://www.interflowinc.com/Images/CHEV-0/60173_C.JPG,http://www.interflowinc.com/Images/CHEV-0/60173_D.JPG,http://www.interflowinc.com/Images/CHEV-0/60173_E.JPG,http://www.interflowinc.com/Images/CHEV-0/60173_F.JPG'
, ',')
--Results
--1 htp://www.interflowinc.com/Images/CHEV-0/60173_A.JPG
--2 http://www.interflowinc.com/Images/CHEV-0/60173_B.JPG
--3 http://www.interflowinc.com/Images/CHEV-0/60173_C.JPG
--4 http://www.interflowinc.com/Images/CHEV-0/60173_D.JPG
--5 http://www.interflowinc.com/Images/CHEV-0/60173_E.JPG
--6 http://www.interflowinc.com/Images/CHEV-0/60173_F.JPG
--Combine fn_GetImageNames and fn_Split?
select stocknumber,
tempdb.dbo.fn_Split(tempdb.dbo.fn_GetImageNames(stocknumber,2,1,1), ',')
from tempdb.dbo.inventory i
--Server: Msg 208, Level 16, State 1, Line 1
--Invalid object name 'tempdb.dbo.fn_Split'
--I assume I get this error because the UDF fn_Split return a table
--Try again Combine fn_GetImageNames and fn_Split?
select * from tempdb.dbo.fn_Split(
select stocknumber,
tempdb.dbo.fn_GetImageNames(stocknumber,2,1,1)
from tempdb.dbo.inventory i
, ',')
--Server: Msg 170, Level 15, State 1, Line 2
--Line 2: Incorrect syntax near '('.
--Server: Msg 170, Level 15, State 1, Line 5
--Line 5: Incorrect syntax near ','.
--This just doesn't work!
--
select * from fn_GetImageNamesRow('1234A',2,1,1)
--Server: Msg 557, Level 16, State 2, Procedure fn_GetImageNamesRow, Line 155
--Only functions and extended stored procedures can be executed from within a function.
Here's some of the table definitions,table population, UDFs for your testing purposes:
USE tempdb
create table inventory (
stocknumber nvarchar(50),
Year nvarchar(50),
VehicleMakeId int,
VehicleModelId int,
images int
)
insert into inventory (stocknumber,year,vehiclemakeid,vehiclemodelid,images) values ('1234A','2000',1,1,3)
insert into inventory (stocknumber,year,vehiclemakeid,vehiclemodelid,images) values ('1234A','1989',1,1,9)
insert into inventory (stocknumber,year,vehiclemakeid,vehiclemodelid,images) values ('1634A','2006',1,1,6)
insert into inventory (stocknumber,year,vehiclemakeid,vehiclemodelid,images) values ('1274A','2008',1,1,2)
insert into inventory (stocknumber,year,vehiclemakeid,vehiclemodelid,images) values ('1237A','2000',1,1,3)
/************************
--This is the UDF I created to return a comma delimited list of image urls, works fine
--output is 'http://www.server.com/Images/12345_A.JPG, http://www.server.com/Images/12345_B.JPG,http://www.server.com/Images/12345_B.JPG'
************************/
CREATE FUNCTION fn_GetImageNames
(
@stockNum varchar(15)
,@storeid int
,@fullPath int
,@alpha int
)
RETURNS varchar(2000)
AS
BEGIN
--For Testing
--declare @stockNum varchar(10)
--set @stockNum='70237A'
--declare @storeid int set @storeid = 2
--declare @fullPath int set @fullPath=0
--How many images for the stocknumber
declare @NumberOfImages int
select @NumberOfImages=images from tempdb.dbo.inventory where stocknumber=@stockNum
--some vendors require full path, some relative path
declare @path varchar(100)
--select @path=webimages from cif.dbo.stores where cif.dbo.stores.pkid=2 --@storeid
--print 'path:'+@path
set @path='http://www.interflowinc.com/Images/CHEV-0'
--images can be named alpha or numeric
declare @imageArray varchar(100)
if @alpha = 1
set @imagearray = 'A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z'
else
set @imagearray = '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24'
--needed variables
declare @imageList varchar(1000)
DeCLARE @val as varchar(10)
declare @counter int
set @counter = 0
--Loop thru the array concatenating the list for as many images as the db says
WHILE @NumberOfImages> @counter
BEGIN
Set @Counter=@Counter+1
IF CHARINDEX(',', @imageArray) > 0
BEGIN
SELECT @val = LEFT(@imageArray, CHARINDEX(',', @imageArray) - 1) ,
@imageArray = RIGHT(@imageArray, LEN(@imageArray) - CHARINDEX(',', @imageArray))
IF @fullPath=1
BEGIN
--print 'fullPath'
IF @alpha = 1
BEGIN
--print 'alpha count'
if @counter=1
set @imageList = @path+@stockNum+'_'+@val+'.JPG'
else
set @imageList = @imageList+','+@path+@stockNum+'_'+@val+'.JPG'
END
ELSE
BEGIN
--print 'numeric count'
if @counter=1
set @imageList = @path+@stockNum+'('+@val+').JPG'
else
set @imageList = @imageList+','+@path+@stockNum+'_'+@val+'.JPG'
END
--print @imageList
END
ELSE
BEGIN
--print 'not full Path'
IF @alpha = 1
BEGIN
--print 'alpha count'
if @counter=1
set @imageList = @stockNum+'_'+@val+'.JPG'
else
set @imageList = @imageList+','+@stockNum+'('+@val+').JPG'
END
ELSE
BEGIN
--print 'numeric count'
if @counter=1
set @imageList = @stockNum+'('+@val+').JPG'
else
set @imageList = @imageList+','+@stockNum+'('+@val+').JPG'
END
--print @imageList
END
END
ELSE
BEGIN
SELECT @val = @imageArray, @imageArray = SPACE(0)
IF @fullPath=1
BEGIN
--print 'full path'
IF @alpha = 1
BEGIN
--print 'alpha count'
if @counter=1
set @imageList = @path+@stockNum+'_'+@val+'.JPG'
else
set @imageList = @imageList+','+@path+@stockNum+'_'+@val+'.JPG'
END
ELSE
BEGIN
--print 'numeric count'
if @counter=1
set @imageList = @path+@stockNum+'('+@val+').JPG'
else
set @imageList = @imageList+','+@path+@stockNum+'('+@val+').JPG'
END
--print @imageList
END
ELSE
BEGIN
--print 'not full path'
IF @alpha = 1
BEGIN
--print 'alpha count'
if @counter=1
set @imageList = @stockNum+'_'+@val+'.JPG'
else
set @imageList = @imageList+','+@stockNum+'_'+@val+'.JPG'
END
ELSE
BEGIN
--print 'numeric count'
if @counter=1
set @imageList = @stockNum+'('+@val+').JPG'
else
set @imageList = @imageList+','+@stockNum+'('+@val+').JPG'
END
--print @imageList
END
END
END
--print @imageList
RETURN @imageList
END
/************************
--The split function I got from sqlservercentral.com, hoping to use along with fn_GetImageNames to get the
**************************/
CREATE Function fn_Split (
@List varchar(8000) ,
@Delimiter char(1) = ','
)
Returns @Temp1 Table (
ItemId int Identity(1, 1) NOT NULL PRIMARY KEY ,
Item varchar(8000) NULL )
As
BEGIN
Declare @item varchar(4000) ,
@iPos int
Set @Delimiter = ISNULL( @Delimiter, ',' )
Set @List = RTrim( LTrim( @List ) ) -- check for final delimiter
If Right( @List, 1 ) <> @Delimiter -- append final delimiter
Select @List = @List + @Delimiter -- get position of first element
Select @iPos = Charindex( @Delimiter, @List, 1 )
While @iPos > 0 Begin -- get item
Select @item = LTrim( RTrim( Substring( @List, 1, @iPos -1 ) ) )
If @@ERROR <> 0 Break -- remove item form list
Select @List = Substring( @List, @iPos + 1, Len(@List) - @iPos + 1 )
If @@ERROR <> 0 Break -- insert item
Insert @Temp1 Values( @item )
If @@ERROR <> 0 Break -- get position pf next item
Select @iPos = Charindex( @Delimiter, @List, 1 )
If @@ERROR <> 0 Break End Return End
/************************
--My failed attempt at a UDF
**************************/
ALTER FUNCTION fn_GetImageNamesRow
(
@stockNum varchar(15)
,@storeid int
,@fullPath int
,@alpha int
)
RETURNS @Temp1 Table (Item varchar(500) NULL)
AS
BEGIN
--declare @stockNum varchar(10)
--set @stockNum='70237A'
--declare @storeid int set @storeid = 2
--declare @fullPath int set @fullPath=1
--declare @alpha int set @alpha=0
--How many images for the stocknumber
declare @NumberOfImages int
select @NumberOfImages=images from tempdb.dbo.inventory where stocknumber=@stockNum
--some vendors require full path, some relative path
declare @path varchar(100)
select @path=webimages from cif.dbo.stores where cif.dbo.stores.pkid=2 --@storeid
----print 'path:'+@path
--images can be named alpha or numeric
declare @imageArray varchar(100)
if @alpha = 1
set @imagearray = 'A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z'
else
set @imagearray = '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24'
--needed variables
declare @imageListSQL varchar(1000)
DeCLARE @val as varchar(10)
declare @counter int
set @counter = 0
--Loop thru the array concatenating the list for as many images as the db says
WHILE @NumberOfImages> @counter
BEGIN
Set @Counter=@Counter+1
IF CHARINDEX(',', @imageArray) > 0
BEGIN
SELECT @val = LEFT(@imageArray, CHARINDEX(',', @imageArray) - 1) ,
@imageArray = RIGHT(@imageArray, LEN(@imageArray) - CHARINDEX(',', @imageArray))
IF @fullPath=1
BEGIN
--print 'fullPath'
IF @alpha = 1
BEGIN
--print 'alpha count'
if @counter=1
set @imageListSQL = 'select '''+@path+@stockNum+'_'+@val+'.JPG'''
else
set @imageListSQL = @imageListSQL+' union select '''+@path+@stockNum+'_'+@val+'.JPG'''
END
ELSE
BEGIN
--print 'numeric count'
if @counter=1
set @imageListSQL = 'select '''+@path+@stockNum+'('+@val+').JPG'''
else
set @imageListSQL = @imageListSQL+' union select '''+@path+@stockNum+'_'+@val+'.JPG'''
END
--print @imageListSQL
END
ELSE
BEGIN
--print 'not full Path'
IF @alpha = 1
BEGIN
--print 'alpha count'
if @counter=1
set @imageListSQL = 'select '''+@stockNum+'_'+@val+'.JPG'''
else
set @imageListSQL = @imageListSQL+' union select '''+@stockNum+'('+@val+').JPG'''
END
ELSE
BEGIN
--print 'numeric count'
if @counter=1
set @imageListSQL = 'select '''+@stockNum+'('+@val+').JPG'''
else
set @imageListSQL = @imageListSQL+' union select '''+@stockNum+'('+@val+').JPG'''
END
--print @imageListSQL
END
END
ELSE
BEGIN
SELECT @val = @imageArray, @imageArray = SPACE(0)
IF @fullPath=1
BEGIN
--print 'full path'
IF @alpha = 1
BEGIN
--print 'alpha count'
if @counter=1
set @imageListSQL = 'select '''+@path+@stockNum+'_'+@val+'.JPG'''
else
set @imageListSQL = @imageListSQL+' union select '''+@path+@stockNum+'_'+@val+'.JPG'''
END
ELSE
BEGIN
--print 'numeric count'
if @counter=1
set @imageListSQL = 'select '''+@path+@stockNum+'('+@val+').JPG'''
else
set @imageListSQL = @imageListSQL+' union select '''+@path+@stockNum+'('+@val+').JPG'''
END
--print @imageListSQL
END
ELSE
BEGIN
--print 'not full path'
IF @alpha = 1
BEGIN
--print 'alpha count'
if @counter=1
set @imageListSQL = 'select '''+@stockNum+'_'+@val+'.JPG'''
else
set @imageListSQL = @imageListSQL+' union select '''+@stockNum+'_'+@val+'.JPG'''
END
ELSE
BEGIN
--print 'numeric count'
if @counter=1
set @imageListSQL = 'select '''+@stockNum+'('+@val+').JPG'''
else
set @imageListSQL = @imageListSQL+' union select '''+@stockNum+'('+@val+').JPG'''
END
--print @imageListSQL
END
END
END
--EXEC @imageListSQL
--Server: Msg 443, Level 16, State 2, Procedure fn_GetImageNamesRow, Line 151
--Invalid use of 'EXECUTE' within a function.
EXECUTE sp_executesql @imageListSQL
RETURN
END
-
I'm surprised nobody has any suggestions...I thought I was just being dense
-
You shouldn't be too surprised.
All you did was post a bunch of code with a bunch of errors in it, without a clear explanation of what you are trying to do, or exactly what you need help with.
-
Geez, having a bad day Michael?
I can't seem to win sometimes, I give too little info and I get jumped on, I give too much I get jumped on.
I've found it makes it easier for people to respond when I include the table create statments, insert statments for the data, and create statements for the stored procedures. That way they can just run the statments in QA and test it in their own dev environment. I included my errors as I thought it might save someone else from wasting their time with the same mistakes I had made.
Perhaps I could try to simplify it if it was too verbose for your taste.
The table I have is inventory with data such as:
stocknumber make model numimages
12345 Chevrolet Aveo 5
Q2346 Ford F15 3
This is what I want for output b/c I'm using bcp command to create a batch ftp file:
StockNumber image
del 12345_A.JPG
del 12345_B.JPG
del 12345_C.JPG
del 12345_D.JPG
del 12345_E.JPG
del Q2346_A.JPG
del Q2346_B.JPG
del Q2346_C.JPG
-
I find it easier to provide a solution when people provide a clear explanation on what they are trying to do, instead of a bunch of code that doesn't work.
You didn't really explain this time either, but this code produces output identical to your sample output.
select command= 'del '+a.stocknumber+'_'+CHAR(65+b.number)+'.jpg' from -- Function F_TABLE_NUMBER_RANGE available here -- http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685 (select NUMBER from F_TABLE_NUMBER_RANGE(0,50) ) b join ( -- Your data select stocknumber = '12345', numimages= 5 union all select stocknumber = 'Q2346', numimages= 3 ) a on b.NUMBER < a.numimages ORDER BY a.stocknumber, a.numimages, b.NUMBER
Results:
command --------------- del 12345_A.jpg del 12345_B.jpg del 12345_C.jpg del 12345_D.jpg del 12345_E.jpg del Q2346_A.jpg del Q2346_B.jpg del Q2346_C.jpg
(8 row(s) affected)
-
KBrown,
You all set? Michael's solution seems to do what you asked but just making sure (pretty cool sequence generation function he's got there, huh?) because of the duplication of stock numbers across years in your original sample data.
As a side bar (teach a man to fish), I understand the frustration you spoke of... too little info brings on "What, you expect us to read your mind?" comments... too much of the wrong kind of information brings on comments like Michael's. The operative words are "kind of information"... I'll add "order of information" to that, as well, because you posted the right kind of information... we just couldn't find it...
Here's what you need to post, as well as the order, for the type of question you asked (you know most of this... wait for the punchline at the end of this thread)...
- I have a table with columns that look like this (list the tablename, relevant column names, and datatypes. Be sure to identify the primary key even if you don't think it's important. It would help folks a lot if this were in the form of a table creation statement.)
- Here's some sample data from that table (list some sample data for the relevent columns including the PK. It would be really cool of you to list them in the form of INSERT/SELECT UNION ALL statements that work with the table creation you posted in 1)
- Here' what I'd like to do (brief description of what you'd like to do... the hard part will be deciding how brief because folks certainly need some detail. My best suggestion is to keep it all relevant)
- Here's what the output should look like (perhaps with some small bit of amplifying information as to where the data in the output should come from)
- THEN, you can list what you've tried... but several pages of code may turn off even the staunchest of forum participants.
The really frustrating part for you is that you did ALL of that... the problem is we had to read about 35 pages of stuff to dig it out. Since we do this for free, we're a bit lazy... make the steps above easy to find if you're going to post that much information. BOLD lettering works real well... for example (mostly copied from your post)...
Problem Description:
I work with automotive dealerships. I'm writing some jobs to batch data to different companies that sell vehicles online. The info in the data file is all about a vehicle for sale and includes where images for that vehicle can be found on our ftp site. I'm creating the file from a sproc using bcp.
Here's a sample table definition and some sample data
Note that the "Images" column contains the number of images available for a given StockNumber and Year.
USE tempdb
create table inventory (
stocknumber nvarchar(50),
Year nvarchar(50),
VehicleMakeId int,
VehicleModelId int,
images int
)
insert into inventory (stocknumber,year,vehiclemakeid,vehiclemodelid,images) values ('1234A','2000',1,1,3)
insert into inventory (stocknumber,year,vehiclemakeid,vehiclemodelid,images) values ('1234A','1989',1,1,9)
insert into inventory (stocknumber,year,vehiclemakeid,vehiclemodelid,images) values ('1634A','2006',1,1,6)
insert into inventory (stocknumber,year,vehiclemakeid,vehiclemodelid,images) values ('1274A','2008',1,1,2)
insert into inventory (stocknumber,year,vehiclemakeid,vehiclemodelid,images) values ('1237A','2000',1,1,3)
This is what I'm trying to get for an output:
For each stock number (regardless of year), I'd like the output to contain a group of lines where the number of lines adds up to the total number of images. The image column should have the following format...
nnnnn_a.JPG
...where nnnnn is the stock number and "a" is a letter starting at "A" and going as high as "Z" depending on the total of the Images column in the sample table above. Like this....
StockNumber image
12345 12345_A.JPG
12345 12345_B.JPG
12345 12345_C.JPG
12345 12345_D.JPG
12345 12345_E.JPG
Q2346 Q2346_A.JPG
Q2346 Q2346_B.JPG
Q2346 Q2346_C.JPG
... and here's what I've tried, so far... notice that yada-yada-yada, etc, etc....
And, here's the punchline I promised...
Like I said, you posted ALL the info that anyone could ever need, but it was buried in rhetoric about the problems you had... we're lazy... even Michael missed the fact that you identified everything we needed because it was buried!. Make the really important stuff easy to find!
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thank You both for your comments. Your time is appreciated.
-
Nicely done, Michael and Jeff.
There is no "i" in team, but idiot has two. -
Jeff,
Michael's code worked perfectly. Many thanks to you both.
--Relevant Code
--delete from Mediawerks
SET @Cmd='BCP "select '''+'del '+'''+'+' imageName from inventory.dbo.vwInventoryImages i where datediff(dd,solddate,getdate()) between 7 and 14 and i.storeid='+cast(@storeid as varchar)+'" queryout "'+@path+'\ftpCmdDel1MW.txt" -c'
EXEC master.dbo.xp_cmdshell @Cmd
SET @Cmd='BCP "select '''+'del hr_'+'''+'+' imageName from inventory.dbo.vwInventoryImages i where datediff(dd,solddate,getdate()) between 7 and 14 and i.storeid='+cast(@storeid as varchar)+'" queryout "'+@path+'\ftpCmdDel2MW.txt" -c'
EXEC master.dbo.xp_cmdshell @Cmd
SET @Cmd='BCP "select '''+'del thumb_'+'''+'+' imageName from inventory.dbo.vwInventoryImages i where datediff(dd,solddate,getdate()) between 7 and 14 and i.storeid='+cast(@storeid as varchar)+'" queryout "'+@path+'\ftpCmdDel3MW.txt" -c'
EXEC master.dbo.xp_cmdshell @Cmd
--The View
CREATE view vwInventoryImages as
select a.stocknumber,solddate,storeid,
a.stocknumber+'_'+CHAR(65+b.number)+'.jpg' imageName
from
-- Function F_TABLE_NUMBER_RANGE available here
-- http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685
(select NUMBER from F_TABLE_NUMBER_RANGE(0,50) ) b
join (
select stocknumber,images,storeid,solddate
from cif.dbo.inventoryvehicles
 
a on b.NUMBER < a.images
-
Perfect... thank you for the feedback!
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thanks for the kudo, Dave.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
.JPG'){kind=link}
.JPG'''){kind=link}
.JPG'''){kind=link}
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply