SQL Separate and lookup value - follow up

Posted on 2011-10-04
Medium Priority
Last Modified: 2012-05-12
I have a slight problem with this sp that was kindly provided by angelIII,

When I try with the following I can see nothing returned:


DECLARE      @return_value int

EXEC      @return_value = [dbo].[getAdminRiskAssessmentsLinks]
            @id = 105,
            @CompanyID = 50

SELECT      'Return Value' = @return_value


But when I try this:

DECLARE      @return_value int

EXEC      @return_value = [dbo].[getAdminRiskAssessmentsLinks]
            @id = 104,
            @CompanyID = 36
SELECT      'Return Value' = @return_value


It works, so I'm not sure what why this does not work?

However I noticed something strange, if I renamed 'Inspection of ladders and platforms' to 'AAAA' it works not sure why?
This is the stored proc.

@id int,
@CompanyID int



DECLARE @myid varchar

set @myid = @CompanyID

select a.id, a.CompanyID, v.value, b.DocumentReference, '<a href="uploads' + cast(@CompanyID AS varchar(3)) + '/' + b.filename + '">' + b.filename + '</a>' As LinkFile

from tbl_permits a
cross apply dbo.values2table(a.RiskAssessments, ',') v
inner join tbl_documents b on b.DocumentReference=v.value AND b.companyID = @CompanyID
where a.id=@id


Open in new window

Question by:sanjshah12
LVL 21

Expert Comment

by:Jason Yousef, MS
ID: 36911697
Are you sure you have   id  of  105 and CompanyID of 50 ???  could be a wrong combination.

Also I'm not sure what do you mean by 'Inspection of ladders and platforms' to 'AAAA'

where is that located in the SP?

Author Comment

ID: 36911781
Yes I'm sure there is 105 and 50

WhenI changed the name from  'Inspection of ladders and platforms'  to 'AAAAA' it worked in both tables.

Not sure why this would work?
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 36912368

DECLARE @myid varchar

please try:

DECLARE @myid varchar(100)
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.


Author Comment

ID: 36912406
Thanks angelIII for your response, I changed this as requested but this still does not work.
LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 2000 total points
ID: 36912574
function declaration of values2table presumable has a varchar(x) with x too small to contain "Inspection of ladders and platforms", but ok to contain "AAAAAA".

please modify that function to return the varchar data with a bigger (x)

Author Comment

ID: 36912618
Thanks angelIII, looks like that was it - I've chnaged it 200 should be more than enough.

Thanks again!
LVL 71

Expert Comment

ID: 37218521
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question