Sending file paths to the application with a SP

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Sending file paths to the application with a SP

Author

Topic

cesark
Posting Yak Master

215 Posts

Posted - 2004-08-16 : 07:59:01

Hi,
With the follow SP I want to send to the application the complete path to a two images placed in different directories. First the SP receives a region_id, and with that it returns two images path. But it doesn’ t work, the application doesn’ t receive the paths.
What is wrong? Here is the complete SP:


USE market
GO
ALTER PROCEDURE path_certificate_logo
@Region_id smallint, @Logo_certificate_A varchar(256) output, 
@Logo_certificate_B varchar(256) output
As
Declare @State_num smallint
 
Select @State_num = certA.State_num, @Logo_certificate_A = generalPath.Directory_path + certA.Logo_imageName, 
@Logo_certificate_B = generalPath.Directory_path + certB.Logo_imageName 

From Regions As rg 

JOIN Certificates_typeA As certA
On certA.State_num = rg.State_num

JOIN Certificates_typeB As certB
On certB.State_num = rg.State_num

JOIN Directories_path As generalPath
On generalPath.Directory_path_id = certA.Directory_path_num And
generalPath.Directory_path_id = certB.Directory_path_num

Where Region_id = @Region_id
GO

Suggestions?

Thank you,
Cesar

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-08-16 : 08:15:13

Need some sample data with a create table and INSERT statements.

Does your Directory_path end in '\'? Otherwise, you're going to just be conactenating the directory and file names into one big name. You ought to run the query in Query Analyzer and SELECT all the variables at the end. Look at the results.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

cesark
Posting Yak Master

215 Posts

Posted - 2004-08-16 : 10:49:45

I've created the table directly in SQL Server Enterprise Manager. But the tables are more or less:


Regions:
Region_id
Region_name
State_num

Directories_path:
Directory_path_id
Directory_path_description
Directory_path (for example: 'images/certificates_type_A/')

Certificates_typeA:
Certificate_id
Certificate_name
Logo_imageName (for example: 'logo01.jpg')
State_num
Directory_path_num (related with 'Directory_path_id'  from 'Directories_path' table)

Certificates_typeB:
Certificate_id
Certificate_name
Logo_imageName (for example: 'logo05.jpg')
State_num
Directory_path_num (related with 'Directory_path_id'  from 'Directories_path' table)

I don' t understand what you mean with: You ought to run the query in Query Analyzer and SELECT all the variables at the end. Look at the results.

Thanks

cesark
Posting Yak Master

215 Posts

Posted - 2004-08-16 : 12:03:39

I have seen that the variables value returned (@Logo_certificate_A and @Logo_certificate_B) are null. I don' t understand why..

cesark
Posting Yak Master

215 Posts

Posted - 2004-08-17 : 04:42:54

..Indeed, I want a different general path for each variable:


Select @State_num = certA.State_num, @Logo_certificate_A = generalPath.Directory_path + certA.Logo_imageName, 
@Logo_certificate_B = generalPath.Directory_path + certB.Logo_imageName

The general path retrieved from the Directories_path table should be 'images/certificates_A/' for the '@Logo_certificate_A' variable, and it should be 'images/certificates_B' for the '@Logo_certificate_B' variable. Because the tables content are:

Directories_path: (The first two rows)
Directory_path_id = 1, 2
Directory_path_description = Some description1, Some description2
Directory_path = images/certificates_A/, images/certificates_B/
...

Certificates_typeA:
Certificate_id = 1
Certificate_name = First Cert.
Logo_imageName = LogoFirst_c.jpg
Directory_path_num = 1
...

Certificates_typeB:
Certificate_id = 1
Certificate_name = Advanced Cert.
Logo_imageName = LogoAdv_c.jpg
Directory_path_num = 2
...

So, surely that the last ‘join’ is wrong:


JOIN Directories_path As generalPath
On generalPath.Directory_path_id = certA.Directory_path_num And
generalPath.Directory_path_id = certB.Directory_path_num

How can I do it?

cesark
Posting Yak Master

215 Posts

Posted - 2004-08-17 : 07:10:48

Well.. here is my solution, and it works. What do you think?


USE market
GO
ALTER PROCEDURE path_certificate_logo
@Region_id smallint, @Logo_certificate_A varchar(256) output, 
@Logo_certificate_B varchar(256) output
As
Declare @State_num smallint
 
Select @State_num = certA.State_num, @Logo_certificate_A = generalPath.Directory_path + certA.Logo_imageName 

From Regions As rg 

JOIN Certificates_typeA As certA
On certA.State_num = rg.State_num

JOIN Directories_path As generalPath
On generalPath.Directory_path_id = certA.Directory_path_num

Where Region_id = @Region_id



Select @State_num = certB.State_num, @Logo_certificate_B = generalPath.Directory_path + certB.Logo_imageName 

From Regions As rg 

JOIN Certificates_typeB As certB
On certB.State_num = rg.State_num

JOIN Directories_path As generalPath
On generalPath.Directory_path_id = certB.Directory_path_num

Where Region_id = @Region_id

GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources