September 17, 2007 at 6:34 pm
Thanks for the suggestion & help.
I have given an sp that is successfully installed but I get the error when executing, please scroll down to see the error -
anything missing in the code or the code is correct? any ideas? Thanks.
CREATE PROCEDURE [dbo].[sp_VendorDatabase_Filter_Vendors]
@WhereClause nvarchar(2000)
AS
DECLARE @SQL1 as nvarchar(4000)
BEGIN
SET @SQL1='
SELECT
distinct
Vendor.VendorId,
Vendor.VendorType,
Vendor.Name,
Vendor.City,
Vendor.State,
Vendor.Zipcode,
Vendor.Address1,
Vendor.Address2,
Vendor.Country,
Vendor.Phone,
Vendor.EmailAddress,
Vendor.TherapeuticCategoryPrimary,
Vendor.TherapeuticCategorySecondary,
Vendor.Qualification,
'''' as PreferredCommonHealthVendor,
(SELECT CASE AVG(WorkQuality)
WHEN 1 THEN ''*'' WHEN 2 THEN ''**'' WHEN 3 THEN ''***'' WHEN 4 THEN ''****'' WHEN 5 THEN ''*****'' ELSE '' '' END AS Expr1
FROM VE_VENDOR_JOB_EXT
WHERE (VendorId = Vendor.VendorId)) AS Rating,
(CASE WHEN (VE_VENDOR_EXT.TypesOfMaterial IS NOT NULL)
THEN VE_VENDOR_EXT.TypesOfMaterial ELSE '' '' END) AS TypesOfMaterial,
VE_VENDOR_EXT.Reference,
VE_VENDOR_EXT.WorkSample,
VE_VENDOR_EXT.InterviewConducted,
VE_VENDOR_EXT.InterviewComments,
VE_VENDOR_EXT.Comments,
VE_VENDOR_EXT.Recommend,
(CASE WHEN (VE_VENDOR_EXT.Degree IS NOT NULL) THEN VE_VENDOR_EXT.Degree ELSE '' '' END) AS Degree,
(CASE WHEN (VE_VENDOR_EXT.ProductsOffered IS NOT NULL) THEN VE_VENDOR_EXT.ProductsOffered ELSE '' '' END) AS ProductsOffered,
(CASE WHEN (VE_VENDOR_EXT.AudienceTypes IS NOT NULL) THEN VE_VENDOR_EXT.AudienceTypes ELSE '' '' END) AS AudienceTypes, VE_VENDOR_EXT.SourceOfContact,
(SELECT Name
FROM VE_CONTACT_SOURCE
WHERE (Id = VE_VENDOR_EXT.SourceOfContact)) AS SourceOfContactName,
(CASE WHEN (VE_VENDOR_EXT.TherapeuticCategories IS NOT NULL)
THEN VE_VENDOR_EXT.TherapeuticCategories ELSE '' '' END) AS TherapeuticCategories,
(CASE WHEN (VE_VENDOR_EXT.PreferredByClients IS NOT NULL) THEN VE_VENDOR_EXT.PreferredByClients ELSE '' '' END) AS PreferredByClients,
(CASE WHEN (VE_VENDOR_EXT.YearsOfExperience IS NOT NULL) THEN VE_VENDOR_EXT.YearsOfExperience ELSE '' '' END) AS YearsOfExperience,
CASE WHEN
(SELECT COUNT(*) FROM VendorJob
WHERE VendorJob.VendorId = Vendor.VendorId) > 0 THEN ''Yes'' ELSE ''No'' END AS PriorCHExperience
FROM VE_VENDOR Vendor LEFT OUTER JOIN
VE_VENDOR_EXT ON Vendor.VendorId = VE_VENDOR_EXT.VendorId'
--SET @SQL1 = @SQL1 --+ @WhereClause
END
GO
**************************************************************************************************************************************************
The name 'SELECT distinct
Vendor.VendorId,
Vendor.VendorType,
Vendor.Name,
Vendor.City,
Vendor.State,
Vendor.Zipcode,
Vendor.Address1,
Vendor.Address2,
Vendor.Country,
Vendor.Phone,
Vendor.EmailAddress,
Vendor.TherapeuticCategoryPrimary,
Vendor.TherapeuticCategorySecondary,
Vendor.Qualification,
'' as PreferredCommonHealthVendor,
(SELECT CASE AVG(WorkQuali...
September 17, 2007 at 7:01 pm
September 18, 2007 at 7:41 am
Thanks a lot!!!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply