By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,949 Members | 2,038 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,949 IT Pros & Developers. It's quick & easy.

select * from table where column = @AllValues?

P: n/a
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 ....

Sep 5 '05 #1
Share this Question
Share on Google+
9 Replies


P: n/a
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
Sep 6 '05 #2

P: n/a
Dear Helmut,
thank you very much - job done....

;-)
Satunius

Sep 6 '05 #3

P: n/a
Pass a NULL as you flag for all rows. :

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

Sep 6 '05 #4

P: n/a
AK
if performance is importnatn in this case, I'd write 2 different select
statements

Sep 6 '05 #5

P: n/a

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

Sep 9 '05 #6

P: n/a


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 ***
Sep 9 '05 #7

P: n/a
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 ***
Sep 9 '05 #8

P: n/a


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

or

@parm IS NULL OR some_col = @parm

*** Sent via Developersdex http://www.developersdex.com ***
Sep 9 '05 #9

P: n/a
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

Sep 9 '05 #10

This discussion thread is closed

Replies have been disabled for this discussion.