Function to check input parameter in guery

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Function to check input parameter in guery

Author

Topic

eden02
Starting Member

5 Posts

Posted - 2011-10-03 : 05:49:53

Hi,

What function can be used in a query to check the parameters with the form?
Parameters on the form can be input but does not necessarily, if input item to display price for that item, and if not to show prices for all items...

select NameItem, Price
from items
where IDitem=:var

Thank you!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-03 : 06:10:24

[code]select NameItem, Price
from items
where IDitem=@var
or @var is null
[/code]

make null as default value from form

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

eden02
Starting Member

5 Posts

Posted - 2011-10-03 : 07:23:42

Visakh16, thank you for help, again..

I don't have access to source code for the form, I can only change the sql query on reports, because that I can not set default value as NULL..
And this query work if I input item on form, if I don't input item, I do not get any result..
I tried

select NameItem, Price
from items
where IDitem=:var or IDitem= (select IDitem from items)

than does not work if I input item on form :(((

m_imran18
Starting Member

14 Posts

Posted - 2011-10-03 : 07:40:11

If you need to filter more than one item on the basis of value or null.



Drop table test
Go
Create table test
(ID int,
[Student Name] nvarchar(50),
[Class] nvarchar(50),
)
Go

Insert  into test values (1,'John','I')
Insert  into test values (2,'Robert','I')
Insert  into test values (3,'William','II')
GO

Declare @StudentName as nvarchar(50)
Declare @Class as nvarchar(50)
set @StudentName=NULL
Set @Class='I'
Select * from [test] Where 
(1=(CASE WHEN @StudentName IS NULL THEN 1 ELSE 0 END) Or  [Student Name] = @StudentName)
And (1=(CASE WHEN @Class IS NULL THEN 1 ELSE 0 END) Or  [Class] = @Class)

Try to make both the variables as null or provide values to one of them or both of them.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-03 : 08:04:51

quote:
Originally posted by eden02

Visakh16, thank you for help, again..

I don't have access to source code for the form, I can only change the sql query on reports, because that I can not set default value as NULL..
And this query work if I input item on form, if I don't input item, I do not get any result..
I tried

select NameItem, Price
from items
where IDitem=:var or IDitem= (select IDitem from items)

than does not work if I input item on form :(((

as siad before this has to be dealt at form end not at backend

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

eden02
Starting Member

5 Posts

Posted - 2011-10-04 : 19:43:16

Thank you for help visakh6 and m_imran 18!!!

I use code like this and it's ok..

select NameItem, Price
from items
where IDitem like '%'+(:var4) +'%'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-05 : 00:43:19

only problem is i guess it wont utilise even if there's an existing index on IDitem

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources