select * from table where column = @AllValues?

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 ....

re: select * from table where column = @AllValues?

Am 5 Sep 2005 14:01:33 -0700 schrieb saturnius@gmx.net:
[color=blue]
> 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 ....[/color]

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

re: select * from table where column = @AllValues?

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

;-)
Satunius

re: select * from table where column = @AllValues?

Pass a NULL as you flag for all rows. :

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

re: select * from table where column = @AllValues?

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

re: select * from table where column = @AllValues?

--CELKO-- wrote:[color=blue]
> Pass a NULL as you flag for all rows. :
>
> SELECT <list of columns>
> FROM Foobar
> WHERE some_col = COALESCE( @parm, some_col);[/color]

What if some_col is DATETIME and NULLable?

Thanks.

re: select * from table where column = @AllValues?

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 ***

re: select * from table where column = @AllValues?

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 ***

re: select * from table where column = @AllValues?

Misunderstood ur question


some_col = COALESCE( @parm, some_col)

or

@parm IS NULL OR some_col = @parm

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

re: select * from table where column = @AllValues?

Rakesh Makhijani (rakeshmakhijani77@yahoo.com) writes:[color=blue]
> 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)[/color]

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, esquel@sommarskog.se

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