select * from table where column = @AllValues?

[saturnius]

Hello,
I am passing a value to a stored procedure in vb.net to get normally a
selection of the table. How could I get the complete table without
writing the code twice? Many thanks in advance ....

[helmut woess]

Am 5 Sep 2005 14:01:33 -0700 schrieb sa*******@gmx.net:

Hello,
I am passing a value to a stored procedure in vb.net to get normally a
selection of the table. How could I get the complete table without
writing the code twice? Many thanks in advance ....

You need a second parameter to enable/disable the where criteria. Your
select would look like so:

select * from table where column = @param1 or @param2 = 1

if you use @param2 = 0 then the column is the criteria, if you use @param2
= 1 then you will always get all rows, no matter what's in param1.

bye,
Helmut

[saturnius]

Dear Helmut,
thank you very much - job done....

;-)
Satunius

[--CELKO--]

Pass a NULL as you flag for all rows. :

SELECT <list of columns>
FROM Foobar
WHERE some_col = COALESCE( @parm, some_col);

[AK]

if performance is importnatn in this case, I'd write 2 different select
statements

[decland]

--CELKO-- wrote:

Pass a NULL as you flag for all rows. :

SELECT <list of columns>
FROM Foobar
WHERE some_col = COALESCE( @parm, some_col);

What if some_col is DATETIME and NULLable?

Thanks.

[Rakesh Makhijani]

declare @Sql varchar(8000),
@AllValues varchar(1000)

SELECT @Sql='select * from table where column=' + @AllValues

exec (@Sql)
Rakesh

*** Sent via Developersdex http://www.developersdex.com ***

[Rakesh Makhijani]

Sorry, I forgot to complete my code.

In SP u can use

declare @Sql varchar(8000),
@AllValues varchar(1000)

SELECT @Sql='select * from table where column IN (' + @AllValues + ') '

exec (@Sql)
Rakesh

*** Sent via Developersdex http://www.developersdex.com ***

[Rakesh Makhijani]

Misunderstood ur question
some_col = COALESCE( @parm, some_col)

or

@parm IS NULL OR some_col = @parm

*** Sent via Developersdex http://www.developersdex.com ***

[Erland Sommarskog]

Rakesh Makhijani (ra***************@yahoo.com) writes:

Sorry, I forgot to complete my code.

In SP u can use

declare @Sql varchar(8000),
@AllValues varchar(1000)

SELECT @Sql='select * from table where column IN (' + @AllValues + ') '

exec (@Sql)

Not only this was appearenly the answer to the wrong question, it was
the wrong answer to that question. Do *not* use dynamic SQL for
for testing for a list of values. There are a number of ways to unpack
such a list into table, and they are both without security issues, and
have better performance. Please see
http://www.sommarskog.se/arrays-in-sql.html.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp