flg8tor96
asked on
Help with using Dynamic SQL and ISNULL
The following SQL does not work. It is due to the addition of ISNULL. this must be dynamic SQL
I do not know how to break the quotes so the nulls are replaced by nothing.
CREATE procedure GetLabels
@Locations varchar(100)
as
Declare @SQL as Varchar(1000)
Select @SQL ='Select isnull(V.firstname,'') as FirstName, isnull(V.lastname,'') as LastName,
isnull(V.company,'') as Company, isnull(V.address1,'') as Address1,
isnull(V.address2,'') as Address2, isnull(V.city,'') as City,
isnull(S.state, '') as State, isnull(V.Zip, '') as Zip
FROM Vendordb.Vendor V
Join Vendordb.states S on S.Stateid = V.Stateid
Where V.VendorID in (Select Distinct B.VendorID
From Vendordb.Booth B
Join Vendordb.BoothSpace BS on BS.BoothID = B.BoothID
Join Vendordb.Spaces S on S.SpaceID = BS.SpaceID
Join Vendordb.Location L on L.LocationID = S.LocationID
Where L.LocationID in ('+@Locations+'))'
Exec(@SQL)
GO
I do not know how to break the quotes so the nulls are replaced by nothing.
CREATE procedure GetLabels
@Locations varchar(100)
as
Declare @SQL as Varchar(1000)
Select @SQL ='Select isnull(V.firstname,'') as FirstName, isnull(V.lastname,'') as LastName,
isnull(V.company,'') as Company, isnull(V.address1,'') as Address1,
isnull(V.address2,'') as Address2, isnull(V.city,'') as City,
isnull(S.state, '') as State, isnull(V.Zip, '') as Zip
FROM Vendordb.Vendor V
Join Vendordb.states S on S.Stateid = V.Stateid
Where V.VendorID in (Select Distinct B.VendorID
From Vendordb.Booth B
Join Vendordb.BoothSpace BS on BS.BoothID = B.BoothID
Join Vendordb.Spaces S on S.SpaceID = BS.SpaceID
Join Vendordb.Location L on L.LocationID = S.LocationID
Where L.LocationID in ('+@Locations+'))'
Exec(@SQL)
GO
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.