using exec

[Garima12]

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. BillGeneralTaxD etails_SC_07_01 ,dbname. BCollRateFile
WHERE (dbname. BillGeneralTaxD etails_SC_07_01 .id like '%' + id + '%' or id is NULL)
and (dbname. BillGeneralTaxD etails_SC_07_01 .TaxRollYr = dbname. BCollRateFile.T axRollYr) and (dbname. BillTaxDetails_ SC_07_01. PO = dbname. BCollRateFile. PO)



I am writing this query in sp-

exec('SELECT * FROM dbname.' + @tablename + ',dbname.BCollR ateFile WHERE (dbname.' + @tablename + '.id like " %' + @id + '% ") and (dbname.' + @tablename + '.TaxRollYr = dbname.BCollRat eFile.TaxRollYr ) and (dbname.' + @tablename + '.PO = dbname.BCollRat eFile.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.

[ck9663]

Use two quotes...

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)'')

-- CK

[Garima12]

Use two quotes...

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)'')

-- CK

throwing error-
Msg 156, Level 15, State 1, Procedure usp_ts, Line 24
Incorrect syntax near the keyword 'SELECT'.
Msg 102, Level 15, State 1, Procedure usp_ts, Line 24
Incorrect syntax near ' + @tablename + '.

[ck9663]

My bad...

try:

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)')

-- CK

[dbpros]

You can usually get around these problems by adding more ' (single quotes) until the parser is happy.

http://www.db-pros.com

[zachster17]

Whenever I have to quote a query (whether it is with OPENQUERY or EXEC), I simply type it regularly without the surrounding extra single quotes and then do a replace all on the query (replace ' to '')