January 20, 2009 at 4:41 am
I have a query which fetches the price list for me and the query is below.
SET @SSQL = '
SELECT
Pricelist as [ID],
[Group],
Item,
Value as Price
FROM
[Procedure Pricelist]
WHERE ' + @Criteria + ' and isnull([active], ''Yes'') = ''Yes'' order by [item], [group] asc'
Print @SSQL
Exec(@SSQL)
I have a new requirement where I have to include the Insurance company price list also.What I tried is given below
SET @SSQL = '
SELECT
A.Pricelist as [ID],
[Group],
Item,
B.Value as Price
FROM
[Procedure Pricelist] A INNER JOIN [Insurance pricelist] B
ON A.Pricelist =B. Pricelist
WHERE ' + @Criteria + ' and isnull([active], ''Yes'') = ''Yes''
AND B. [Insurance Company] '+@InsuranceCompany+' order by [item], [group] asc'
Print @SSQL
Exec(@SSQL)
But this gives error.I
Can somebody help me to crack this please.I am not sure of dynamic SQL or whatever you call this.
January 20, 2009 at 4:58 am
mathewspsimon (1/20/2009)
But this gives error.
And the error it gives would be ... ?
If it's a syntax error, print the string before you try to execute it. It's usually easy to spot where the syntax error is if you do that.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 20, 2009 at 5:24 am
Hi,
As stated previously the error message always helps, but i'm guessing it is to do with a space after the full stop in the on clause of the inner join...
B. Pricelist
should be...
B.Pricelist
Perhaps?
Mao Says RTFM
January 20, 2009 at 5:25 am
Hi Gail,
I am not too sure how I can do that.Only thing I see is it give the first part of the sql which shows in the broweser after I run the procedure
January 20, 2009 at 5:28 am
mathewspsimon (1/20/2009)
Hi Gail,I am not too sure how I can do that.
How you can do what?
Only thing I see is it give the first part of the sql which shows in the broweser after I run the procedure
Where are you running this from? Not management studio by the sound of things
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 20, 2009 at 5:29 am
Server Error in '/WAS' Application.
--------------------------------------------------------------------------------
Syntax error converting the nvarchar value '
SELECT
A.Pricelist as [ID],
Code,
Item,
B.Value as Price
F...
this is the error on the browser I get.
January 20, 2009 at 5:32 am
I am running from an application which runs this s.proc to fetch the queries.to be honest this was done by somelse who in not with us now.I just need to add an additional condition.
January 20, 2009 at 5:35 am
Load it up in management studio or query analyser. You can't debug SQL properly from a web application
My guess, and this is just a guess, is that the variable you're trying to concatenate in is an int. The result of that is SQL attempting to convert the rest of the string to an int to do addition, and of course, 'SELECT ...' does not convert well to an int.
You've also got a space between the table and the column (which shouldn't be there) and you're missing an equals
AND B.[Insurance Company] = '+CAST(@InsuranceCompany AS VARCHAR(10))+' order by [item], [group] asc'
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 20, 2009 at 7:37 am
looks like data type conversion error but when i did as Gila told I get a new error as shown below.
Can some one help me with this please.
Server Error in '/WAS' Application.
--------------------------------------------------------------------------------
Cannot find table 0.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
January 20, 2009 at 1:12 pm
Can you please load up management studio or query analyser and test the query out there.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 21, 2009 at 11:58 am
One thing you can do is simply print out @SSQL to see what the string looks like. A quick glance at what you supplied shows two possible problems:
AND B. [Insurance Company] '+@InsuranceCompany+' order by
There is an equals sign missing. It should be
AND B. [Insurance Company] = '+@InsuranceCompany+' order by
You could also structure the text a bit better. It doesn't make any difference to the compiler but it makes it a lot more readable to us mere humans.
SET @SSQL = '
SELECT
A.Pricelist as [ID],
[Group],
Item,
B.Value as Price
FROM
[Procedure Pricelist] A
INNER JOIN
[Insurance pricelist] B
ON A.Pricelist = B. Pricelist
WHERE
' + @Criteria + '
and isnull ([active], ''Yes'') = ''Yes''
and B.[Insurance Company] = ' + @InsuranceCompany + '
order by [item], [group] asc'
This would help highlight the second possible problem. If @InsuranceCompany contains a text string, such as 'Acme Insurance', then the resulting substring is going to be this:
and B.[Insurance Company] = Acme Insurance
when you need it to be this:
and B.[Insurance Company] = 'Acme Insurance'
So you have to make the quotes part of the string, just like you did with ''Yes'' in the line before it.
AND B.[Insurance Company] = ''' + @InsuranceCompany + '''
(Though I have removed the extra space after the period in 'B. [Insurance Company]', that is not your problem. Spaces are allowed there.)
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
January 21, 2009 at 8:31 pm
Hi tomm,
thanks for the help but one question,@InsuranceCompany is and Integer so does it have to be modified?
January 22, 2009 at 12:43 pm
mathewspsimon (1/21/2009)
thanks for the help but one question,@InsuranceCompany is and Integer so does it have to be modified?
No, but you would have to convert it to text in order to concatenate:
'and B.[Insurance Company] = ' + Convert( varchar, @InsuranceCompany ) + '
(or use Cast if you prefer)
And you still need the "="
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply