troubleshooting Question

Help with using Dynamic SQL and ISNULL

Avatar of flg8tor96
flg8tor96 asked on
SQL
1 Comment1 Solution1072 ViewsLast Modified:
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
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 1 Comment.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 1 Comment.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros