I need to use exec command and it is working fine but there is some problem in query. Can you please help me in building query.
I am passing id and table name to stored procedure.
Following will be the form of query after merging id and table name-
SELECT *
FROM dbname. BillGeneralTaxDetails_SC_07_01,dbname. BCollRateFile
WHERE (dbname. BillGeneralTaxDetails_SC_07_01.id like '%' + id + '%' or id is NULL)
and (dbname. BillGeneralTaxDetails_SC_07_01.TaxRollYr = dbname. BCollRateFile.TaxRollYr) and (dbname. BillTaxDetails_SC_07_01. PO = dbname. BCollRateFile. PO)
I am writing this query in sp-
exec('SELECT * FROM dbname.' + @tablename + ',dbname.BCollRateFile WHERE (dbname.' + @tablename + '.id like " %' + @id + '% ") and (dbname.' + @tablename + '.TaxRollYr = dbname.BCollRateFile.TaxRollYr) and (dbname.' + @tablename + '.PO = dbname.BCollRateFile.PO)')
my problem is near " '.id like " %' + @id + '% ")". Can you please correct query? Problem is because of single quote as I need to close the command in exec(‘ ‘) in single quote as well as need to put single quote around id as it is string.
any help would be appreciated.