add new conditions to a query

Question

Post reply

add new conditions to a query

mathewspsimon

SSCommitted

Points: 1706
More actions
January 20, 2009 at 4:41 am

#383760

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.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 1

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ShuaThe2nd SSChasing Mays Points: 613 More actions · Answer 2

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

mathewspsimon SSCommitted Points: 1706 More actions · Answer 3

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

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 4

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

mathewspsimon SSCommitted Points: 1706 More actions · Answer 5

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.

mathewspsimon SSCommitted Points: 1706 More actions · Answer 6

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.

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 7

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

mathewspsimon SSCommitted Points: 1706 More actions · Answer 8

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.

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 9

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Tomm Carr SSCertifiable Points: 5426 More actions · Answer 10

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

mathewspsimon SSCommitted Points: 1706 More actions · Answer 11

Hi tomm,

thanks for the help but one question,@InsuranceCompany is and Integer so does it have to be modified?

Tomm Carr SSCertifiable Points: 5426 More actions · Answer 12

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