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
flg8tor96Asked:
Who is Participating?
 
derekkrommCommented:
Double up the ''s.

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+'))'
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.