Solved

Variable in SQL Select Statement

Posted on 2014-10-13
6
111 Views
Last Modified: 2014-10-15
I am trying to show a list of teams and individuals. What I want to do is show a Person's Name that has been entered in a textbox when the Person's Name is null in the database. I do not want to use the Update Statement because it will only be temporary.  
string varPerson = Request.Form["PersonName"];
SELECT NumGames, PersonTeam, CASE WHEN PersonName IS NULL THEN varPerson END AS PersonName
FROM
(
Select NumGames, PersonTeam, PersonName FROM TeamData
)
TABLE1
Order by PersonTeam

Open in new window

0
Comment
Question by:RecipeDan
  • 3
  • 3
6 Comments
 
LVL 26

Expert Comment

by:Shaun Kline
ID: 40377546
Try using the ISNULL function:
ISNULL(PersonName, varPerson)
0
 
LVL 1

Author Comment

by:RecipeDan
ID: 40377634
I tried this and nothing happens:
SELECT NumGames, PersonTeam, ISNULL(PersonName, varPerson) as PersonName
FROM
(
Select NumGames, PersonTeam, PersonName FROM TeamData
)
TABLE1
Order by PersonTeam

Open in new window

0
 
LVL 1

Author Comment

by:RecipeDan
ID: 40377639
I get an invalid column name varPerson
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 26

Accepted Solution

by:
Shaun Kline earned 500 total points
ID: 40377663
Are you using a stored procedure for you SQL, or are you building your query in your .NET code?
If it is a stored procedure, you need to pass your .NET variable into it. If you are building the query in .NET, you need to "add" it to your query.

...PersonTeam, ISNULL(PersonName, '" + varPerson + "') as PersonName...

Open in new window

0
 
LVL 1

Author Comment

by:RecipeDan
ID: 40378417
Thank you that works. Will Not Null be the same way?
0
 
LVL 26

Expert Comment

by:Shaun Kline
ID: 40381957
When you use NOT NULL in a query, it is used for comparison. (Does this field have a value?) The ISNULL function uses the first parameter in the list that is not null. The CASE statement in your original query would have worked if you had included an ELSE condition. The ISNULL function is a little cleaner to use.
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question