Stored Procedures and asp.net programmability; variable or SQL

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

Edit: Getting SqlServer 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?

Passing One Stored Procedure’s Result as Another Stored Procedure’s Parameter

Procedure 1:

EXEC Parse
@Part = '0123,4567'
@Qty = '1,1';

returns the following:

Part        Qty
0123         1
4567         1

This procedure simply takes a part and quantity input and parses the strings at each instance of ",".

Procedure 2:

EXEC PA
@Part = '0123'
@Qty = '1';

returns the following:

Top-Level Assembly     TotalQty      MaterialPart     Qty
      0123                1             12A            2
      0123                1             13A           21
      0123                1             14A            5

My overall goal is to have a user enter an assembly part or list of assembly parts (delimited by a comma) and their appropriate quantities. The first procedure creates a result list of all the assembly parts. The second procedure should run off of the result set from the first procedure to get all of the pieces that make up the assembly part.

How can I run my second procedure based off of the result of the first procedure? Any help is greatly appreciated!!

How to show all database objects that operate on a given object

Is there a command to show all the jobs and functions and stored procedures etc. in a database where operations involving a specific table are executed? For example, all the jobs that INSERT into Tablex, all the jobs that CREATE Tablex, and any other operations on that table.

Thank you!

Stored Procedure in cursor firing only once

I am getting an output from a stored procedure but in my cursor it only returns the first value.

SP 1

ALTER PROCEDURE [dbo].[register_system_email_audits]
    @UserId int,
    @EmailFor varchar(500),
    @DateSent datetime,
    @UniqueKey varchar(20) output
AS
BEGIN
    INSERT INTO [SystemEmailsAudit]
           ([UserId]
           ,[EmailFor]
           ,[DateSent]
           ,[UniqueKey]
           )
     VALUES
           (@UserId 
           ,@EmailFor
           ,@DateSent
           ,(SELECT CAST( CAST(RAND() * 100000000 AS int) as varchar(20)))
           );
     SELECT @UniqueKey=s.UniqueKey FROM [SystemEmailsAudit] s 
        WHERE s.RecordId=SCOPE_IDENTITY();

END

SP2

ALTER PROCEDURE [SendNewsletterMails]
(
    @nLID int,
    @Category VARCHAR(50)
)
as
DECLARE
  @html varchar(max),
  @Description VARCHAR(100),
  @Subject varchar(50),
  @Email varchar(100),
  @listID   int,
  @DLC smalldatetime,
  @Date DATETIME = NULL
    set @html = (SELECT html from NewsLetter where nLID=@nLID)
    DECLARE crsEmailList CURSOR FOR
    SELECT email, ListID from lists where category=@Category AND (DLC < DATEADD(DAY, -1,GETDATE()) OR DLC IS NULL)
  OPEN crsEmailList
  FETCH NEXT FROM crsEmailList INTO @email, @ListID
    while @@FETCH_STATUS = 0 BEGIN
    --Add Beacon
    DECLARE @UniqueKey varchar(20)
    EXEC [register_system_email_audits] @ListID, @email, @Date, @UniqueKey output
    SET @html = Replace(@html,'[keyvalue]', @UniqueKey)
    EXEC msdb.dbo.sp_send_dbmail 
      @profile_Name ='Local Server',
       @recipients= @email ,
       @subject = @Subject,
       @body = @html,
       @body_format='HTML'
    FETCH NEXT FROM crsEmailList INTO @email, @ListID
    END
  CLOSE crsEmailList
  DEALLOCATE crsEmailList
GO

The stored procedure returns the proper @UniqueKey but only for the first record in the cursor. I have been contemplating a while loop or a temp table but settled on the cursor route for now.

Which SQL Query is the site running?

The websites (intranet sites or extranet sites - sometimes web portals) at my company return certain results (which is obtained via SQL queries/commands in the back-end systems). I"m trying to find out which queries are being run in the background and how I could track back the query results onto the tables where they come from. How can I achieve that? I tried looking at the "source" but found no queries there. Back-end uses SQL Server if that matters.

SQL Server Query Aid

I have a query in SQL Server to return a list of Reports, it has to return either a string representing a location, or a string representing the store it's referencing.

The issue is my query is only returning reports that references a store id, instead of returning all reports and the relevant location information. I'm convinced its a stupid syntax issue, but I haven't done database work for a while, and can't seem to pick it out. I've tried several different ways to get this to work, but it simply refuses.

SELECT rep.rep_id AS "RepId", ISNULL(rep.rep_status, 'C') AS "RepStatus", ISNULL((loc.location_street + ' ' + loc.location_city), store.Description) AS "Location", rep.date_reported AS "DateReported", rep.reported_by AS "ReportedBy"
FROM Report rep JOIN Report_Location reploc ON reploc.rep_id = rep.rep_id
JOIN Location loc ON loc.location_id = reploc.location_id
LEFT JOIN Store store ON store.StoreID = loc.store_id;

I've tried removing the left join and just adding a where loc.store_id = store.StoreID or loc.store_id IS NULL. Neither worked. Thanks in advance for your help.

delete millions records using partition tables?

We write daily about 1 million records into a sql server table. Records has a insertdate and status fields, among others of course. I need to delete records from time to time to free space on the volume but leaving the last 4 days records there. The problem is the deletion takes hours and lots of resources.

I have think about partition tables setting the partition field on the insertdate, but I never used that kind of tables.

How can I archieve the goal using the less cpu/disk resources and having the solution the less drawbacks possible? (I assume any solution has its own drawbacks, but please explain them if you know).

Thank you in advance