stored procedure problem

[philh]

Originally posted by: philh


mvlistvar = "''myvar1'',''myvar2'',''myvar3''"
Pay very close attention to the number and type of quotes here.




Remember when I said that? There are consecutive single quotes in that
string. The only double quotes are at the start and finish. Copy and paste it
into Query Analyzer and you'll see the way this should look.

[Manweevil]

I did use the two single quotes inside a double quoted string, and here is what
I get from the query analyzer:


Insert into #tempreport (app,prev_app,cert,basin,file_dt,app_status,source ,
pod_qq,pod_q,pod_sec,pod_twn,pod_rng,div_balance,m ou,pou_acre_total,
duty_balance,duty_unit,county,owner_name)
SELECT dbo.main_src.app, dbo.prevapp.prev_app, dbo.main_src.cert,
dbo.main_src.basin, dbo.main_src.file_dt, dbo.main_src.app_status,
dbo.main_src.source, dbo.main_src.pod_qq,
dbo.main_src.pod_q, dbo.main_src.pod_sec, dbo.main_src.pod_twn,
dbo.main_src.pod_rng,
dbo.main_src.div_balance, dbo.main_src.mou,
dbo.main_src.pou_acre_total, dbo.main_src.duty_balance,
dbo.main_src.duty_unit, dbo.main_src.county,
MIN(dbo.owner.owner_name) AS owner_name
FROM dbo.main_src LEFT OUTER JOIN
dbo.owner ON dbo.main_src.app = dbo.owner.app LEFT OUTER
JOIN

dbo.prevapp ON dbo.main_src.app = dbo.prevapp.app
WHERE (dbo.owner.owner_type IN ("b","c")) AND
( f_basin IS NULL OR dbo.main_src.basin IN(''007'',''006''))
GROUP BY dbo.main_src.app, dbo.main_src.cert, dbo.main_src.app_status,
dbo.main_src.basin, dbo.main_src.county, dbo.main_src.source,
dbo.main_src.pod_qq, dbo.main_src.pod_q,
dbo.main_src.pod_sec, dbo.main_src.pod_twn, dbo.main_src.pod_rng,
dbo.main_src.duty_balance,
dbo.main_src.duty_unit, dbo.main_src.div_balance,
dbo.main_src.mou, dbo.main_src.pou_acre_total, dbo.main_src.file_dt,
dbo.prevapp.prev_app
Server: Msg 170, Level 15, State 1, Line 12
Line 12: Incorrect syntax near '007'.

[philh]

I don't understand how you're using double quote marks in T-SQL. You should
use single quote marks to delimit strings.

Try this sample code (cut and paste into Query Analyzer)

declare @mySQLstring varchar(8000)
declare @l_vmyPassedVariable varchar(128)
set @l_vmyPassedVariable = '''107'',''108'''
set @mySQLString =
'SELECT *
FROM some_table
WHERE (@some_var IS NULL OR dbo.some_table.some_var IN
('+@l_vmyPassedVariable+'))'
print @mySQLString
--exec(@mySQLString)

This is the model your code should follow to work correctly.

HTH,