Conditional Where Clause

Hi - I am writing a C# program using SQL Server. The form I have is
collecting search criteria for a database. The main 3 fields are Category,
Type, Author.
Any combination of the 3 fields can be used. That is, All 3 fields can be
used to search on, or just 2 or just 1. If the user selects 1 or 2 fields,
I
can't use the 3rd field in the where clause of the query.
How can I create a generic query and pass a string for the "where" clause
instead of creating 7 specific queries for each possible combination of
search criteria.
Thanks,
SarahCREATE PROCEDURE getbook
@.category VARCHAR(10) = NULL,
@.bookType VARCHAR(10) = NULL,
@.author VARCHAR(10) = NULL
AS
SELECT category,
booktype,
author
FROM book
WHERE (category = @.category OR @.category IS NULL )
AND (booktype = @.bookType OR @.bookType IS NULL )
AND (author = @.author OR @.author IS NULL )|||Sarah,
From what you say is the following correct:
You want a Stored Procedure that takes 3 parameters and returns a recordset
based on the passed parameters.
The SELECT statement itself will be static, and both the first two params
will be used if present, and the third [arameter will only be used if neithe
r
of the first two params are present.
Does that sum it up?
Tony
"Sarah Sarah" wrote:

> Hi - I am writing a C# program using SQL Server. The form I have is
> collecting search criteria for a database. The main 3 fields are Category
,
> Type, Author.
> Any combination of the 3 fields can be used. That is, All 3 fields can b
e
> used to search on, or just 2 or just 1. If the user selects 1 or 2 fields
, I
> can't use the 3rd field in the where clause of the query.
> How can I create a generic query and pass a string for the "where" clause
> instead of creating 7 specific queries for each possible combination of
> search criteria.
> Thanks,
> Sarah|||Sarah,
Bearing in mind the mutual exclusivity between params 1,2 and param 3, the
following code will work:::
CREATE STORED PROCEDURE [dbo].[usp_GetSearchResults]
@.Category varchar(100)='',
@.Type varchar(100)='',
@.Author varchar(100)
AS
DECLARE @.sSQL varchar(2000)
SET @.sSQL = ''
IF @.Category ='' AND @.Type =''
BEGIN
SET @.sSQL = @.sSQL + ' SELECT Category, Type, Author '
SET @.sSQL = @.sSQL + ' FROM tblMYTABLE '
SET @.sSQL = @.sSQL + ' WHERE (@.Category='' OR Category=' + CHAR(39) +
@.Category + CHAR(39) + ') '
SET @.sSQL = @.sSQL + ' AND (@.Author ='' OR Author=' + CHAR(39) + @.Author +
CHAR(39) + ') '
END
ELSE
BEGIN
SET @.sSQL = @.sSQL + ' SELECT Category, Type, Author '
SET @.sSQL = @.sSQL + ' FROM tblMYTABLE '
SET @.sSQL = @.sSQL + ' WHERE (@.Category='' OR Category=' + CHAR(39) +
@.Category + CHAR(39) + ') '
SET @.sSQL = @.sSQL + ' AND (@.Type ='' OR Type=' + CHAR(39) + @.Type +
CHAR(39) + ') '
END
EXEC (@.sSQL)
You do not necessarily need the character string to create the select
statement, it does help with debugging though.
Hope it helps,
Tony
"Sarah Sarah" wrote:

> Hi - I am writing a C# program using SQL Server. The form I have is
> collecting search criteria for a database. The main 3 fields are Category
,
> Type, Author.
> Any combination of the 3 fields can be used. That is, All 3 fields can b
e
> used to search on, or just 2 or just 1. If the user selects 1 or 2 fields
, I
> can't use the 3rd field in the where clause of the query.
> How can I create a generic query and pass a string for the "where" clause
> instead of creating 7 specific queries for each possible combination of
> search criteria.
> Thanks,
> Sarah|||KenJ - thanks - this logic will work, but I am getting a syntax error:
"Duplicated parameter names are not allowed"
when I try to do this in Query builder. Any idea what would cause this erro
r.
Thanks,
Sarah
"KenJ" wrote:

> CREATE PROCEDURE getbook
> @.category VARCHAR(10) = NULL,
> @.bookType VARCHAR(10) = NULL,
> @.author VARCHAR(10) = NULL
> AS
> SELECT category,
> booktype,
> author
> FROM book
> WHERE (category = @.category OR @.category IS NULL )
> AND (booktype = @.bookType OR @.bookType IS NULL )
> AND (author = @.author OR @.author IS NULL )
>|||I'm not familiar with query builder. Can you run it in query analyzer?
Here is a sample script that creates a table, loads some dummy data,
runs the procedure with several variations then drops the table and
procedure. I've run it in query analyzer to be sure it works...
USE tempdb
GO
SET nocount ON
GO
CREATE TABLE book (
bookid INT IDENTITY( 1 , 1 ) NOT NULL PRIMARY KEY
, category VARCHAR(10) NULL
, booktype VARCHAR(10) NULL
, author VARCHAR(10) NULL)
GO
INSERT book
VALUES('fiction'
, 'paperback'
, 'twain')
INSERT book
VALUES('fiction'
, 'hardbound'
, 'asimov')
INSERT book
VALUES('fiction'
, 'paperback'
, 'rand')
GO
CREATE PROCEDURE getbook
@.category VARCHAR(10) = NULL
, @.bookType VARCHAR(10) = NULL
, @.author VARCHAR(10) = NULL
AS
SELECT category
, booktype
, author
FROM book
WHERE (category = @.category
OR @.category IS NULL )
AND (booktype = @.bookType
OR @.bookType IS NULL )
AND (author = @.author
OR @.author IS NULL )
GO
-- get all fiction books
EXEC getbook @.category = 'fiction'
-- all fiction books by rand
EXEC getbook @.category = 'fiction' ,
@.author = 'rand'
-- all paperbacks
EXEC getbook @.bookType = 'paperback'
-- returns all books since we don't supply any filter
EXEC getbook
GO
DROP TABLE book
GO
DROP PROCEDURE getbook
GO|||On Wed, 1 Feb 2006 16:50:27 -0800, Sarah Sarah wrote:

>Hi - I am writing a C# program using SQL Server. The form I have is
>collecting search criteria for a database. The main 3 fields are Category,
>Type, Author.
>Any combination of the 3 fields can be used. That is, All 3 fields can be
>used to search on, or just 2 or just 1. If the user selects 1 or 2 fields,
I
>can't use the 3rd field in the where clause of the query.
>How can I create a generic query and pass a string for the "where" clause
>instead of creating 7 specific queries for each possible combination of
>search criteria.
Hi Sarah,
Many ways to skin this cat can be found i Erland Sommarskog's article:
http://www.sommarskog.se/dyn-search.html
Hugo Kornelis, SQL Server MVP|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
It would also help if you would learn that a field and a column nothing
whatsoever alike and that names like "type", "category", etc. are too
vague to be data element names/. Try something like this:
CREATE PROCEDURE GetBook
(@.my_book_category VARCHAR(10) = NULL, -- wild guess
@.my_book_type INTEGER = NULL, -- Dewey Decimal ?
@.my_author_name VARCHAR(25) = NULL)
AS
SELECT book_category, book_type, author_name
FROM Library
WHERE book_category = COALESCE (@.my_book_category, book_category)
AND book_type = COALESCE (@.my_book_type, book_type)
AND author_name = COALESCE (@.my_author_name, author_)name) ;|||> WHERE book_category = COALESCE (@.my_book_category, book_category)
> AND book_type = COALESCE (@.my_book_type, book_type)
> AND author_name = COALESCE (@.my_author_name, author_)name) ;
That would give a tablescan.
Can you imagine how badly that will perform on a table with a few million
rows perhaps 1GB in size.
To do the tablescan everytime a user ran the query SQL Server would have to
read 1GB of data.
Now, multiply that by 10 users, thats 10GB of data SQL Server now needs to
read in order to process all 10 queries.
You are going to need one hell of a big box!
The correct way to do this is to either use IF..ELSE to make the query more
specific depending on which parameters are specified, ie. only put the
parameters specified on the WHERE clause.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1138944895.900338.14850@.g47g2000cwa.googlegroups.com...
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, data types, etc. in
> your schema are. Sample data is also a good idea, along with clear
> specifications. It is very hard to debug code when you do not let us
> see it.
> It would also help if you would learn that a field and a column nothing
> whatsoever alike and that names like "type", "category", etc. are too
> vague to be data element names/. Try something like this:
> CREATE PROCEDURE GetBook
> (@.my_book_category VARCHAR(10) = NULL, -- wild guess
> @.my_book_type INTEGER = NULL, -- Dewey Decimal ?
> @.my_author_name VARCHAR(25) = NULL)
> AS
> SELECT book_category, book_type, author_name
> FROM Library
> WHERE book_category = COALESCE (@.my_book_category, book_category)
> AND book_type = COALESCE (@.my_book_type, book_type)
> AND author_name = COALESCE (@.my_author_name, author_)name) ;
>|||Don't forget Ken that the query below will give you a very general plan so
you'll probably end up doing a table scan.
Check the plan before you decided on the solution.
Much better to use IF ELSE or dynamic SQL and taylor your query to the
parameters passed.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"KenJ" <kenjohnson@.hotmail.com> wrote in message
news:1138899994.839100.3010@.o13g2000cwo.googlegroups.com...
> I'm not familiar with query builder. Can you run it in query analyzer?
> Here is a sample script that creates a table, loads some dummy data,
> runs the procedure with several variations then drops the table and
> procedure. I've run it in query analyzer to be sure it works...
> USE tempdb
> GO
> SET nocount ON
> GO
> CREATE TABLE book (
> bookid INT IDENTITY( 1 , 1 ) NOT NULL PRIMARY KEY
> , category VARCHAR(10) NULL
> , booktype VARCHAR(10) NULL
> , author VARCHAR(10) NULL)
> GO
> INSERT book
> VALUES('fiction'
> , 'paperback'
> , 'twain')
> INSERT book
> VALUES('fiction'
> , 'hardbound'
> , 'asimov')
> INSERT book
> VALUES('fiction'
> , 'paperback'
> , 'rand')
> GO
> CREATE PROCEDURE getbook
> @.category VARCHAR(10) = NULL
> , @.bookType VARCHAR(10) = NULL
> , @.author VARCHAR(10) = NULL
> AS
> SELECT category
> , booktype
> , author
> FROM book
> WHERE (category = @.category
> OR @.category IS NULL )
> AND (booktype = @.bookType
> OR @.bookType IS NULL )
> AND (author = @.author
> OR @.author IS NULL )
> GO
> -- get all fiction books
> EXEC getbook @.category = 'fiction'
> -- all fiction books by rand
> EXEC getbook @.category = 'fiction' ,
> @.author = 'rand'
> -- all paperbacks
> EXEC getbook @.bookType = 'paperback'
> -- returns all books since we don't supply any filter
> EXEC getbook
> GO
> DROP TABLE book
> GO
> DROP PROCEDURE getbook
> GO
>

Conditional SQL Statements?

Hi There

I am writing a shopping cart page and I need some help in 'doing the math' for an SQL statement.

I have a main cart table that adds all the products.prices together from the 'price' field in the database to give the total amount payable.

SELECT SUM(TotPric) AS TheTotal FROM ( SELECT cart.cart_quantity * product_options.price AS TotPric FROM product_options INNER JOIN (products INNER JOIN (cart INNER JOIN main ON cart.main_id = main.main_id) ON products.product_id = main.product_id) ON product_options.product_options_id = main.product_options_id WHERE cart.session_id = "&cookiesesh&")AS TTT;"

I have however introduced a 'sale_price' that I would like the statement to select over the 'price' if the sale_price isn't 0.

Do I need to have another select statement in the mix to specifically select the sale_price if not zero, something like

SELECT SUM(TotPric) AS TheTotal FROM ( SELECT cart.cart_quantity * (SELECT product_options.price AS sp WHERE po.sale_price <> 0) AS TotPric FROM product_options INNER JOIN (products INNER JOIN (cart INNER JOIN main ON cart.main_id = main.main_id) ON products.product_id = main.product_id) ON product_options.product_options_id = main.product_options_id WHERE cart.session_id = "&cookiesesh&")AS TTT;"

I am a bit confused. Can you point me in the right direction, maybe a link to a tutorial or something.

I appreciate any help.

CheersRewritten for viewing

SELECT SUM(TotPric) AS TheTotal,
FROM (
SELECT (c.cart_quantity * po.price) AS TotPric
FROM product_options po
INNER JOIN main m ON po.product_options_id = m.product_options_id
INNER JOIN products p ON p.product_id = m.product_id
INNER JOIN cart c ON c.main_id=m.main_id
WHERE c.session_id = "&cookiesesh&"
) AS TTT"

Potential answer

SELECT SUM(TotPric) AS TheTotal,
FROM (
SELECT (c.cart_quantity * IF(po.sale_price>0,sale_price,price) AS TotPric
FROM product_options po
INNER JOIN main m ON po.product_options_id = m.product_options_id
INNER JOIN products p ON p.product_id = m.product_id
INNER JOIN cart c ON c.main_id=m.main_id
WHERE c.session_id = "&cookiesesh&"
) AS TTT"|||thanks aschk.|||Does it work? What DBMS are you using? Can you provide a sample table layout? Do you have some sample data for a test? If you could provide this information I'm sure I could give you a better answer.|||Hi Aschk

Its not working. Thanks for the effort though.

I am using MySQL.

Tables

tblCart
cart_id,session_id,main_id,cart_quantity,timestamp

tblProduct_Options
product_options_id, product_options_text,price,sale_price,stock

tblMain
main_id,product_id,product_options_id

tblProducts
product_id,product_name,description

(some fields removed for clarity)

I am basically trying to do is mutliply cart_quantity with the sale_price if the sale_price is not zero, if it is zero then multiply by the price.

I have included the sql_dump with all the data.

Thanks for your time and effort|||SELECT SUM(TotPric) as TheTotal
FROM (
SELECT (c.cart_quantity * IF(po.sale_price>0,sale_price,price)) AS TotPric
FROM product_options po
JOIN main m ON po.product_options_id = m.product_options_id
JOIN products p ON p.product_id = m.product_id
JOIN cart c ON c.main_id=m.main_id
WHERE c.session_id = '@.sessionid'
) ttt|||The original answer worked. Sorry aschk, I can't even copy and paste. time to hang up my coding hat and apply for that Burger King vacancy.

Thanks for your help and your time
:beer:
:)|||i was just looking through your table structure and had a question regarding a few columns you are using.

In your products table you are using a `text` column for your product name. I was wondering why? I suspect your names of products won't be larger than 255 variable characters so use a VARCHAR(255).

Also the same for your product_code and cart_finish columns in your cart and main tables. I can't see a reason why these need to be text columns either.

Maybe a rethink on those or explanation would be good ;) ?|||Thanks for pointing this out to me.

Definately a rethink.

I have recently moved from access to mysql and haven't really studied the benefits of choosing the right field type or best practises for mysql.

I am looking into it now.

Do you have any advice or know of any sites I can look at before I google it myself.

I am guessing that VARCHAR keeps the database size down & makes it quicker?|||In a word yes. I'm not 100% keyed up on the data types especially with regards to Text, however what I do is that it's a self altering column meaning that data could continually be placed in it and it could differ per row of the database. Unless you're dealing with text of an unknown size it's best to restrict fields to sensible values. e.g. usernames will probably be no longer than 30 characters and thus you column shouldn't need to be anything bigger than varchar(30).|||You know, using a subquery here is superfluous. This will return the same result with less obfuscation:
SELECT Sum((c.cart_quantity * IF(po.sale_price>0,sale_price,price)) AS TheTotal
FROM product_options po
JOIN main m ON po.product_options_id = m.product_options_id
JOIN products p ON p.product_id = m.product_id
JOIN cart c ON c.main_id=m.main_id
WHERE c.session_id = '@.sessionid'|||The IF thing is not SQL. A case expression has to be used:

SELECT SUM(c.cart_quantity * CASE po.sale_price > 0 THEN sale_price ELSE price END) AS theTotal
FROM ...|||The IF thing is not SQL.The poster does not indicate what platform he is using, but he did say that the code worked as written. You are correct that the statement is not in the correct syntax for MSSQL (or Oracle, I think), but it may execute for Access which is bastardized with VB.|||First of all: thanks Blindman and Stolze for the time you took in posting to this thread.

My Db is MySQL and I am using classic ASP to write the 'other bits'

I am a newb to this so I am a little lost when it comes to 'The IF thing is not SQL'

What I am trying to do is write fast, correct, standard code and I appreciate your input.

As Blindman pointed out the original query supplied by Aschk works fine, what I would like to know is: Are there performance gains to be made by losing the subquery, is it more 'standard and correct' to use, as Stolze said, the CASE expression instead.

I can see myself coming across this situation again and again and it would be good for me to get it right inthe future.

Thanks again for your input guys. enlightening and educating.

:)|||There are certainly no performance GAINS to be had by using unnecessary subqueries. If you are a consultant being paid to code by the line, then that method may work out well for you, but otherwise it just makes it more difficult to debug.|||The poster does not indicate what platform he is using, but he did say that the code worked as written. You are correct that the statement is not in the correct syntax for MSSQL (or Oracle, I think), but it may execute for Access which is bastardized with VB.

Here we discuss standardized SQL as defined in ISO/IEC 9075 (aka SQL:2003). That's why there is this sticky "Read This First" article: http://www.dbforums.com/announcement.php?f=11 So platform questions are usually irrelevant (except to state if a DBMS is conforming to SQL:2003 or not).

As for the question whether CASE expression or "the IF thing" is "more standard and correct", this is easy to answer: CASE expressions are defined in the SQL standard; so it is conforming to use that. "The IF thing" is not standard SQL and only a product-specific extension. If you want to write mostly portable SQL code (very hard btw), you shouldn't use it.|||Heh, if you're being paid line by line then space your work out, don't write subqueries ;)

Conditional query results

Hello everybody,

After several attempts of writing the query, I had to post my requirement in the forum.

Here is what I have, what I need and what I did.

Table A
Col1Col2
1Nm1
2Nm2
3Nm3

Table B
Col1Col2
10100
20200

Table C
Col1 (A.Col1)Col2 (B.Col1)
110
210

Table D
Col1 (A.Col1)Col2
1Value1
2Value2

I need results based on below criteria,

1.
Criteria - B.Col2 = 100
Resultset
A.Col1D.Col1
1Value1
2Value2

2.
Criteria - B.Col2 =""
A.Col1D.Col1
1Value1
2Value2
3NULL

3.
Criteria - B.Col2 =200
Empty resultset

Here is the query I tried, but looks its not working. Probably there is a better way to do this.

DDL and DML statements:
create table #tab1 (a1 int, a2 nvarchar(20))
create table #tab2 (b1 int, b2 int)
create table #tab3 (c1 int, c2 int)
create table #tab4 (d1 int, d2 nvarchar(20))

insert into #tab1 values (1, 'nm1')
insert into #tab1 values (2, 'nm2')
insert into #tab1 values (3, 'nm3')

insert into #tab2 values (10, 100)
insert into #tab2 values (20, 200)

insert into #tab3 values (1, 10)
insert into #tab3 values (2, 10)

insert into #tab4 values (1, 'value1')
insert into #tab4 values (2, 'value2')

select
a.a1
, d.d2
from #tab1 a
left join #tab3 b
on a.a1 = b.c1
left join #tab2 c
on b.c2 = c.b1
left join #tab4 d
on a.a1 = d.d1
where
c.b2 = [100 or 200 or ''] or exists (select 1 from #tab4 d
where a.a1 = d.d1
and c.b2 = [100 or 200 or ''] )

The

above query works well to give results for Criteria 1 and Criteria 2,

but doesn't return for ''. I couldn't manage cracking the solution. I

shall try once again, but meanwhile if anyone could help me in this,

that would be great.

Thanks.

Change the select query as follow as...

1. You should convert your Integer Column into Varchar to compare with '',

2. You should apply the Isnull function to match the ''

I hope it will work for you...

select
a.a1, d.d2
from #tab1 a
left join #tab3 b on a.a1 = b.c1
left join #tab2 c on b.c2 = c.b1
left join #tab4 d on a.a1 = d.d1
where
Isnull(Convert(varchar,c.b2),'') in (100 ,200,'')
or exists
(select 1 from #tab4 d where a.a1 = d.d1 and Isnull( Convert(varchar,c.b2),'') in (100, 200 , ''))

Conditional query results

Hello everybody,

After several attempts of writing the query, I had to post my
requirement in the forum.

Here is what I have, what I need and what I did.

Table A
Col1 Col2
1 Nm1
2 Nm2
3 Nm3

Table B
Col1 Col2
10 100
20 200

Table C
Col1 (A.Col1) Col2 (B.Col1)
1 10
2 10

Table D
Col1 (A.Col1) Col2
1 Value1
2 Value2

I need results based on below criteria,

1.
Criteria - B.Col2 = 100
Resultset
A.Col1 D.Col1
1 Value1
2 Value2

2.
Criteria - B.Col2 =""
A.Col1 D.Col1
1 Value1
2 Value2
3 NULL

3.
Criteria - B.Col2 =200
Empty resultset

Here is the query I tried, but looks its not working. Probably there is
a better way to do this.

DDL and DML statements:
create table #tab1 (a1 int, a2 nvarchar(20))
create table #tab2 (b1 int, b2 int)
create table #tab3 (c1 int, c2 int)
create table #tab4 (d1 int, d2 nvarchar(20))

insert into #tab1 values (1, 'nm1')
insert into #tab1 values (2, 'nm2')
insert into #tab1 values (3, 'nm3')

insert into #tab2 values (10, 100)
insert into #tab2 values (20, 200)

insert into #tab3 values (1, 10)
insert into #tab3 values (2, 10)

insert into #tab4 values (1, 'value1')
insert into #tab4 values (2, 'value2')

select
a.a1
, d.d2
from #tab1 a
left join #tab3 b
on a.a1 = b.c1
left join #tab2 c
on b.c2 = c.b1
left join #tab4 d
on a.a1 = d.d1
where
c.b2 = [100 or 200 or ''] or exists (select 1 from #tab4 d
where a.a1 = d.d1
and c.b2 = [100 or 200 or ''] )

The above query works well to give results for Criteria 1 and Criteria
3, but doesn't return for '' (criteria 2). I couldn't manage cracking
the solution. I shall try once again, but meanwhile if anyone could
help me in this, that would be great.

Thanks.msrviking@.gmail.com wrote:

Quote:

Originally Posted by

Hello everybody,
>
After several attempts of writing the query, I had to post my
requirement in the forum.
>
Here is what I have, what I need and what I did.
>
Table A
Col1 Col2
1 Nm1
2 Nm2
3 Nm3
>
Table B
Col1 Col2
10 100
20 200
>
Table C
Col1 (A.Col1) Col2 (B.Col1)
1 10
2 10
>
Table D
Col1 (A.Col1) Col2
1 Value1
2 Value2
>
>
I need results based on below criteria,
>
1.
Criteria - B.Col2 = 100
Resultset
A.Col1 D.Col1
1 Value1
2 Value2
>
2.
Criteria - B.Col2 =""
A.Col1 D.Col1
1 Value1
2 Value2
3 NULL
>
3.
Criteria - B.Col2 =200
Empty resultset
>
Here is the query I tried, but looks its not working. Probably there is
a better way to do this.

see http://www.sqlhacks.com/index.php/R...itional-columns|||(msrviking@.gmail.com) writes:

Quote:

Originally Posted by

I need results based on below criteria,
>
1.
Criteria - B.Col2 = 100
Resultset
A.Col1 D.Col1
1 Value1
2 Value2
>
2.
Criteria - B.Col2 =""
A.Col1 D.Col1
1 Value1
2 Value2
3 NULL
>
3.
Criteria - B.Col2 =200
Empty resultset
>
Here is the query I tried, but looks its not working. Probably there is
a better way to do this.

Thanks for posting the CREATE TABLE and INSERT statements. That makes
it easy to test. Here is a solution that gives the desired result. Since
B.Col2 is numeric, it cannot be a string value, so I am assuming NULL
for this case.

create table #tab1 (a1 int, a2 nvarchar(20))
create table #tab2 (b1 int, b2 int)
create table #tab3 (c1 int, c2 int)
create table #tab4 (d1 int, d2 nvarchar(20))

insert into #tab1 values (1, 'nm1')
insert into #tab1 values (2, 'nm2')
insert into #tab1 values (3, 'nm3')

insert into #tab2 values (10, 100)
insert into #tab2 values (20, 200)

insert into #tab3 values (1, 10)
insert into #tab3 values (2, 10)

insert into #tab4 values (1, 'value1')
insert into #tab4 values (2, 'value2')
go
create procedure #testie @.val int AS
select a.a1, d.d2
from #tab1 a
left join #tab4 d ON a.a1 = d.d1
WHERE @.val IS NULL OR
EXISTS (SELECT *
FROM #tab3 c
JOIN #tab2 b ON c.c2 = b.b1
WHERE c.c1 = a.a1
AND b.b2 = @.val)
go
EXEC #testie 100
EXEC #testie NULL
EXEC #testie 200
go
drop table #tab1, #tab2, #tab3, #tab4
drop proc #testie

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Can you fix up tyhis DDL? You seem to tell us that in Table C, we have
a VIEW of A.col1 and B.col1, but not rule for building this VIEW. You
also have no DDL delcaring keys and all columns are NULL-able so these
are not really tables at all! Is Table D a PK-FK relationship? In
which direction? Where did all those temp tables come from? Why do so
many of the data elements have the same names?

Just trying to make the abstrations human readable and put in questions
on the lack of specs:

CREATE TABLE Alpha
(alpha_id INTEGER NOT NULL PRIMARY KEY. - wild guess!!
alpha_name CHAR(5) NOT NULL);

CREATE TABLE Beta
(beta_id INTEGER NOT NULL PRIMARY KEY. -- wild guess!!
col2 INTEGER NOT NULL);

CREATE VIEW Gamma (alpha_id, beta_id)
AS
SELECT alpha_id, beta_id
FROM Alpha, Beta
WHERE << unknown search condiition>>; -- not possible to guess

CREATE TABLE Delta
(alpha_id INTEGER NOT NULL PRIMARY KEY
REFERENCES Alpha(alpha_id), -- or is this refernced by Alpha?
delta_name CHAR(6) NOT NULL);

Your first criteria wants four columns back, your second criteria wants
five columns back.
But tables do not have a variable number of columns, so this makes no
sense. Oh, even empty result sets have columns, which you did not show
in your vague personal narrative.

The correct syntax is "x IN (<list of expressions>)" and not "x = [exp1
OR exp2 OR ..]

Whenteh specs are this bad and vague, the usual answer is that the DDL
is a nightmare.

Conditional group by

Hi,

Can anyone help me in writing this sql query, i want to group my select statement depending on the parameter user is passing.

Say when @.group='Cell' I want to group by CellID otherwise different conditions, something like below query but it is not working. I know we can't use case directly in where but please let me know if there is any other work around.

I don't want to use dynamic query and also this is big SP so i dont want to break sp in four conditions.

declare @.group varchar(10)

set @.group='Cell'

select cellid,sum(count)

FROM CellImpressionFact

WHERE ImpressionTypeLevelId = 2

AND ImpressionTypeId = 4

group by

case when group='Cell' then GROUP BY CellId

else group by activityID

end

This is not a good idea really. I would use dynamic SQL to provide this kind of capability if you really need to. It is possible (see code) but I would be very concerned about performance.

create table test
(
grouper int,
grouper2 int,
value decimal(10,5)
)
go
insert into test
select 1,1,10
union all
select 1,2,10
union all
select 1,3,10
union all
select 2,1,10
go
declare @.groupby varchar(10)
set @.groupBy = 'grouper2'

select max(grouper) as grouper,
max(grouper2) as grouper2,
sum(value) as valueSum
from test
group by case when @.groupBy = 'grouper' then grouper else grouper2 end

Note that the grouper2 column is of any value when you group by grouper, and vice versa (say it five times fast.)

Concatenation problem: two integers and a char

Hello to all,

I am writing a query that is attempting to take three fields in a table, and create a new field called "MyKey." I'm doing this using concatenation. The problem: two of these fields, Storage_Facility and Storage_Receipt_Number, are integer fields. The third of these fields, Receipt_Suffix, is a char field. It appears that SQL server will not allow this. I can do it in Microsoft Access, why not in SQL Server? Is there any way around this?

Here's the relevant part of my query:

SELECT MyTable.STORAGE_FACILITY,
MyTable.STORAGE_RECEIPT_NUMBER,
MyTable.STORAGE_RECEIPT_SUFFIX,
(MyTable.STORAGE_FACILITY+MyTable.STORAGE_RECEIPT_ NUMBER+MyTable.STORAGE_RECEIPT_SUFFIX)
AS MyKey,

Etc

If you can help, great!

Thanks.

Quote:

Originally Posted by mikeDA

Hello to all,

I am writing a query that is attempting to take three fields in a table, and create a new field called "MyKey." I'm doing this using concatenation. The problem: two of these fields, Storage_Facility and Storage_Receipt_Number, are integer fields. The third of these fields, Receipt_Suffix, is a char field. It appears that SQL server will not allow this. I can do it in Microsoft Access, why not in SQL Server? Is there any way around this?

Here's the relevant part of my query:

SELECT MyTable.STORAGE_FACILITY,
MyTable.STORAGE_RECEIPT_NUMBER,
MyTable.STORAGE_RECEIPT_SUFFIX,
(MyTable.STORAGE_FACILITY+MyTable.STORAGE_RECEIPT_ NUMBER+MyTable.STORAGE_RECEIPT_SUFFIX)
AS MyKey,

Etc

If you can help, great!

Thanks.

Try any of these...

SELECT MyTable.STORAGE_FACILITY,
MyTable.STORAGE_RECEIPT_NUMBER,
MyTable.STORAGE_RECEIPT_SUFFIX,
(CAST(MyTable.STORAGE_FACILITY AS VARCHAR(10))
+ CAST(MyTable.STORAGE_RECEIPT_ NUMBER AS VARCHAR(10))+
MyTable.STORAGE_RECEIPT_SUFFIX)
AS MyKey,

SELECT MyTable.STORAGE_FACILITY,
MyTable.STORAGE_RECEIPT_NUMBER,
MyTable.STORAGE_RECEIPT_SUFFIX,
(CONVERT(VARCHAR(10),MyTable.STORAGE_FACILITY) +
CONVERT(VARCHAR(10),MyTable.STORAGE_RECEIPT_ NUMBER)+
MyTable.STORAGE_RECEIPT_SUFFIX)
AS MyKey ,

Concatenation in stored procedure

hi everyone
i am writing a stored procedure and i am new in this field
i want to concatenate a string, i got an error
hope someone could help.
my stored procedure is:
CREATE PROCEDURE dbo.ProdCatComp
(
@.Product nvarchar(40),
)
AS
DECLARE @.str nvarchar(100)
SET @.str='Products.ProductName like '%' + @.Product + '%''

SELECT Products.ProductName, Products.UnitPrice, Categories.CategoryName, Suppliers.CompanyName, Suppliers.ContactName, Suppliers.HomePage
FROM Products INNER JOIN
Suppliers ON Products.SupplierID = Suppliers.SupplierID INNER JOIN
Categories ON Products.CategoryID = Categories.CategoryID
WHERE Products.ProductName<>'' AND @.str
GO

The error is :
Error 403: Invalid operator for data type. Operator equals modulo, type equals varchar.you have to make the whole query dynamic
and then use exec/sp_executesql|||luber is correct in most cases you will need to build a sql string and then execute it, but in this particular case the code below should work - not tested.

CREATE PROCEDURE dbo.ProdCatComp
 @.Product nvarchar(40)
AS
 DECLARE @.str nvarchar(100)
 SET @.str = '%' + @.Product + '%'
 SELECT Products.ProductName,
 Products.UnitPrice,
 Categories.CategoryName,
 Suppliers.CompanyName,
 Suppliers.ContactName,
 Suppliers.HomePage
 FROM Products
 INNER JOIN Suppliers
 ON Products.SupplierID = Suppliers.SupplierID
 INNER JOIN Categories
 ON Products.CategoryID = Categories.CategoryID
 WHERE Products.ProductName <> ''
 AND Products.ProductName LIKE @.str
GO