Stored Procedures and asp.net programmability; variable or SQL

Question

Trying to display a users Lastname, Firstname --- Website

And I need to insert a comma and space after Lastname to a GridView. I am trying to add a CASE statement in SQL and having trouble figuring it out.

Perhaps I need to use @parameter (scalar variable?) to abstract the memory read from CASE statement; or my syntax is wrong and I just don't understand.

SELECT 
    CASE
       WHEN IsNull(people_Table.firstName, '') = ''
         THEN CONCAT(people_Table.lastName, ', ', people_Table.firstName) 
         ELSE people_Table.lastName
    END as fullName, 
    people_Table.website
FROM 
    people_Table 
INNER JOIN 
    membership_Table on people_Table.ID = membership_Table.personID
WHERE 
    rectype = 'Master'
    AND membershipType = 'Business'
    AND expirationDate > GetDate()
ORDER BY 
    people_Table.lastName

Getting SQL Server error:

Msg 208, Level 16, State 1, Line 1
Invalid object name 'people_Table'.

Otherwise I suppose I should use an asp databoundevent in the template.

What is better for performance and security?

Are you getting an error? The syntax looks ok at first glance. — TTeeple, Aug 04 '15 at 15:42

score 1 · Answer 1 · answered Aug 04 '15 at 15:43

1

SELECT    ISNULL(people_Table.lastName + ', ', '') 
            + ISNULL(people_Table.firstName , '') as fullName
        , people_Table.website
FROM people_Table INNER JOIN membership_Table on people_Table.ID =
membership_Table.personID
WHERE rectype = 'Master'
AND membershipType = 'Business'
AND expirationDate > GetDate()
ORDER BY people_Table.lastName

OR

SELECT    COALESCE(people_Table.lastName + ', ', '') 
            + COALESCE(people_Table.firstName , '') as fullName
        , people_Table.website
FROM people_Table INNER JOIN membership_Table on people_Table.ID =
membership_Table.personID
WHERE rectype = 'Master'
AND membershipType = 'Business'
AND expirationDate > GetDate()
ORDER BY people_Table.lastName

answered Aug 04 '15 at 15:43

M.Ali

67,945
13
101
127

Thank you, unfortunately I still get an error: Invalid object name 'people_Table'. – user3285269 Aug 04 '15 at 15:53
1

Ok the error suggests that the SQL Server cannot find a table with name `people_Table`, First make sure the table exists (no typo errors) , then make sure you use two part name for the table i.e `schemaName.TableName` , finally make sure the calling user has permission to read data from the table. – M.Ali Aug 04 '15 at 15:58
Thanks now it is working! Always good to have help when learning multiple variables at once. How do I pull AS result to GridView DataField? www.padreguadalupe.com – user3285269 Aug 04 '15 at 17:12

Stored Procedures and asp.net programmability; variable or SQL

1 Answers1

OR