Difference between Query Analyzer and Stored Procedure

[saturnius]

Hello,
The code below works fine in the Query Analyzer but from a Stored Prodedure I get the following error. Any Ideas?

*******************************
SP error:Line 1: Incorrect syntax near '.'.
-------------------------------
*******************************
Query Analyzer works correct
-------------------------------

declare @strDetail char (30)
declare @strOverview char (30)

set @strDetail = 'company1_detail'
set @strOverview = 'company1_overview'


EXEC ('
SELECT
'+@strDetail+'.id,
'+@strDetail+'.uid,
'+@strOverview+'.identifier as oident,
'+@strDetail+'.identifier,
'+@strDetail+'.demanddate,
'+@strDetail+'.contact_id,
'+@strDetail+'.company_id,
'+@strDetail+'.demandsupplier_id,
'+@strDetail+'.demandcompanyorig_id,
'+@strDetail+'.supplier_quotation_sum
FROM '+@strDetail+'
JOIN '+@strOverview+' ON ('+@strDetail+'.overview_id = '+@strOverview+'.id)
where (status_id = 4) and (workstage = 0)

union

select
common_detail.id,
common_detail.uid,
common_overview.identifier as oident,
common_detail.identifier,
common_detail.demanddate,
common_detail.contact_id,
common_detail.company_id,
common_detail.demandsupplier_id,
common_detail.demandcompanyorig_id,
common_detail.supplier_quotation_sum
from common_detail
JOIN common_overview ON (common_detail.overview_id = common_overview.id)
where (status_id = 4) and (workstage = 0)');

 

[MaffewW]

Try declaring the variables as varchar instead of char, poss the trailing blanks causing a problem

 

[saturnius]

Hello,
Thank you for you comments. I changed both and it did not help:
- I've declared the varibles as varchar like:
@strDetail varchar (30)
- There is only on line left in the SP
- I did the following as well:
declare @sql as varchar(2000)
set @sql = 'Select ..see above ..(workstage = 0)'
begin
exec(@sql)
end
go

Nothing was successful. I think there is an issue with ***'*** an ***"*** (single and double quote).
How does the SP process it?

 

[MaffewW]

Not sure, SQL looks ok to me. I wonder if its because id is a reserved word, could try qualifying it with []. Have to admit I'm stabbing in the dark a little, but as no one else has answered...

Does this work?



declare @strDetail varchar (30)
declare @strOverview varchar (30)

set @strDetail = 'company1_detail'
set @strOverview = 'company1_overview'


EXEC ('
SELECT
['+@strDetail+'.id],
'+@strDetail+'.uid,
'+@strOverview+'.identifier as oident,
'+@strDetail+'.identifier,
'+@strDetail+'.demanddate,
'+@strDetail+'.contact_id,
'+@strDetail+'.company_id,
'+@strDetail+'.demandsupplier_id,
'+@strDetail+'.demandcompanyorig_id,
'+@strDetail+'.supplier_quotation_sum
FROM '+@strDetail+'
JOIN '+@strOverview+' ON ('+@strDetail+'.overview_id = ['+ @strOverview+'.id)' + ']' + '
where (status_id = 4) and (workstage = 0)

union

select
common_detail.id,
common_detail.uid,
common_overview.identifier as oident,
common_detail.identifier,
common_detail.demanddate,
common_detail.contact_id,
common_detail.company_id,
common_detail.demandsupplier_id,
common_detail.demandcompanyorig_id,
common_detail.supplier_quotation_sum
from common_detail
JOIN common_overview ON (common_detail.overview_id = common_overview.id)
where (status_id = 4) and (workstage = 0)');

 

[SQLBill]

What does your stored procedure look like?

-SQLBill

 

[saturnius]

Hi,
Below is the SP -- so far I did not try to apply []

Thanks for your ideas.
Cheers Saturnius

***************************************

CREATE PROCEDURE newsquote
(
@strDetail char (30),
@strOverview char (30)
)
as
declare @sql as varchar(3000)
set @sql = ('SELECT "'+@strDetail+'".id,"'+@strDetail+'".uid,"'+@strOverview+'".identifier as oident,"'+@strDetail+'".identifier,"'+@strDetail+'".demanddate,"'+@strDetail+'".contact_id,"'+@strDetail+'".company_id,"'+@strDetail+'".supplier_id,"'+@strDetail+'".companyorig_id,"'+@strDetail+'".supplier_quotation_sum FROM '+@strDetail+' JOIN '+@strOverview+' ON ("'+@strDetail+'".overview_id ="'+@strOverview+'".id) where (status_id = 4) and (workstage = 0) union select common_detail.id,common_detail.uid,common_overview.identifier as oident,common_detail.identifier,common_detail.demanddate,common_detail.contact_id,common_detail.company_id,common_detail.supplier_id,common_detail.companyorig_id,common_detail.supplier_quotation_sum from common_detail JOIN common_overview ON (common_detail.overview_id = common_overview.id) where (status_id = 4) and (workstage = 0)')
begin
exec(@sql)
end

GO

 

[swampBoogie]

EXEC ('SELECT d.id,d.uid,o.identifier as oident,' +
' d.identifier,d.demanddate,d.contact_id,' +
' d.company_id,d.demandsupplier_id,d.demandcompanyorig_id,' +
' d.supplier_quotation_sum ' +
' FROM '+@strDetail+' as d JOIN '+@strOverview+' as o' +
' ON (d.overview_id = o.id) ' +
' where (status_id = 4) and (workstage = 0) ' +
' union select cd.id,cd.uid,co.identifier , cd.identifier,cd.demanddate,cd.contact_id, ' +
' cd.company_id,cd.demandsupplier_id,cd.demandcompanyorig_id,cd.supplier_quotation_sum ' +
' from common_detail cd JOIN common_overview co ' +
' ON (cd.overview_id = co.id) ' +
' where (status_id = 4) and (workstage = 0)')

You seem to be mixing up " and '. You shouldn't really use " as a string delimiter in SQL. " in SQL is used for delimited identifiers and Tsql is also adopting this behaviour in never versions.

If you need to put a ' in a string use ''

 

[saturnius]

Hi,
I will try this tomorrow : To qualify once "@strDetail" as "d" seems the trick (FROM '+@strDetail+' as d)
Thank you very much!!! Have a nice day - all of you
Cheers
Saturnius