query with parameter - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

query with parameter

Author

Topic

damegu
Starting Member

5 Posts

Posted - 2014-08-28 : 18:17:32

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}

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-08-28 : 18:35:58

[CODE]coalesce(table.nick, '') = ${Nick}[/CODE]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

damegu
Starting Member

5 Posts

Posted - 2014-08-28 : 19:32:42

Thanks but it does not work. When the string is not empty then it works. When the string is empty it does not.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-08-29 : 06:43:57

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

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-08-29 : 10:58:31

(So much for my assumptions...)

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources