Link to home
Start Free TrialLog in
Avatar of flg8tor96
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
ASKER CERTIFIED SOLUTION
Avatar of derekkromm
derekkromm
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial