By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,378 Members | 1,373 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,378 IT Pros & Developers. It's quick & easy.

using exec

P: 58
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.
Jun 19 '08 #1
Share this Question
Share on Google+
5 Replies


ck9663
Expert 2.5K+
P: 2,878
Use two quotes...

Expand|Select|Wrap|Line Numbers
  1. 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
Jun 19 '08 #2

P: 58
Use two quotes...

Expand|Select|Wrap|Line Numbers
  1. 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 + '.
Jun 19 '08 #3

ck9663
Expert 2.5K+
P: 2,878
My bad...

try:

Expand|Select|Wrap|Line Numbers
  1. exec
  2. ('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
Jun 20 '08 #4

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

http://www.db-pros.com
Jun 23 '08 #5

zachster17
P: 30
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 '')
Jun 24 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.