SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 query with parameter
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

damegu
Starting Member

Czech Republic
5 Posts

Posted - 08/28/2014 :  18:17:32  Show Profile  Reply with Quote
Hello,
I am working with a Report Designer where I am using SQL to get data which user wants to see so I am using parameters.
In my SQL database, name and surname columns can not be NULL, but nick can be NULL.
The problem is with 3rd parameter ${Nick}. I need to do some condition like this: if user lefts 3rd parameter empty, it should select people where table.nick is NULL, otherwise it should select people by entered nick. But I do not know how to rework my query.


SELECT
table.name,
table.surname,
table.nick
FROM
table
WHERE
table.name = ${Name}
AND
table.surname = ${Surname}
AND
table.nick = ${Nick}


Edited by - damegu on 08/29/2014 05:36:29

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1754 Posts

Posted - 08/28/2014 :  18:35:58  Show Profile  Reply with Quote
coalesce(table.nick, '') = ${Nick}
I am assuming that the variable will be an empty string and that the variable name "${Nick}" gets translated into T-SQL at some point.



Too often we enjoy the comfort of opinion without the discomfort of thought. - John F. Kennedy
Go to Top of Page

damegu
Starting Member

Czech Republic
5 Posts

Posted - 08/28/2014 :  19:32:42  Show Profile  Reply with Quote
Thanks but it does not work. When the string is not empty then it works. When the string is empty it does not.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30276 Posts

Posted - 08/29/2014 :  06:43:57  Show Profile  Visit SwePeso's Homepage  Reply with Quote
SELECT table.name, table.surname, table.nick
FROM dbo.Table
WHERE table.name = ${Name} AND table.surname = ${Surname} AND (table.nick = ${Nick} OR ${Nick} IS NULL)


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1754 Posts

Posted - 08/29/2014 :  10:58:31  Show Profile  Reply with Quote
(So much for my assumptions...)



Too often we enjoy the comfort of opinion without the discomfort of thought. - John F. Kennedy
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000