Stored Procedures SQL Server

Question

I have created a stored procedure which lists all customers that have 7 days left on their membership.

CREATE PROC spGetMemReminder
AS
    SELECT users.fullname,
           membership.expiryDate
    FROM   membership
           INNER JOIN users
             ON membership.uid = users.uid
    WHERE  CONVERT(VARCHAR(10), expiryDate, 105) =
           CONVERT(VARCHAR(10), ( getdate() + 7 ), 105)

I would like to insert this list into another table automatically. How do I achieve this? any suggestions appreciated. Thanks

By automatically you mean on a schedule? If so what edition of SQL Server are you on? Also what is the need to materialize this resultset into another table at all? — Martin Smith, May 21 '13 at 12:16
I see no reason to use a stored procedure here, use a view instead. — fancyPants, May 21 '13 at 12:18
I agree with tombom, I don't see why a stored procedure is needed here. A view would be much easier to deal with. — , May 21 '13 at 12:37
don't use convert() to compare dates like that. DATEDIFF() will be much faster. — Joel Coehoorn, May 21 '13 at 14:01

score 0 · Answer 1 · edited May 23 '17 at 11:58

0

Snipped this:

SELECT * INTO #MyTempTable FROM OPENROWSET('SQLNCLI', 'Server=    (local)\SQL2008;Trusted_Connection=yes;',
 'EXEC getBusinessLineHistory')

From this: Insert results of a stored procedure into a temporary table

edited May 23 '17 at 11:58

Community

1
1

answered May 21 '13 at 12:16

Bill Gregg

7,067
2
22
39

1

Or you can just `CREATE TABLE #T` then `INSERT ... EXEC` with no need for `OPENROWSET` – Martin Smith May 21 '13 at 12:18

score 0 · Answer 2 · answered May 21 '13 at 12:21

0

Insert into YOUR_TABLE exec("spGetMemReminder")

answered May 21 '13 at 12:21

Azadeh Radkianpour

961
9
18

I am using sql server 2005. I have created a job which runs the stored procedure every day. This list would then be inserted into another table, which is displayed when I login. This enables me to see users that will expire in 7 days.. How would I accomplish this using a view? I am relatively new to SQL. Thanks! – Damien Knox May 21 '13 at 12:55

granadaCoder · Accepted Answer · 2013-05-21T13:58:53.187

0

What the other suggestion is... don't write a stored procedure to insert into a temp table, because the data will always be changing.

Just write a view....... and have your "report" use/consume the view.

if exists (select * from sysobjects 
    where id = object_id('dbo.vwExpiringMemberships') and sysstat & 0xf = 2)
    drop VIEW dbo.vwExpiringMemberships

GO


/*
select * from dbo.vwExpiringMemberships
*/

CREATE VIEW dbo.vwExpiringMemberships AS  

    SELECT usr.fullname,
           mem.expiryDate
    FROM   dbo.Membership mem
           INNER JOIN dbo.Users usr
             ON mem.uid = usr.uid
    WHERE  CONVERT(VARCHAR(10), expiryDate, 105) =
           CONVERT(VARCHAR(10), ( getdate() + 7 ), 105) 


GO



GRANT  SELECT ,  UPDATE ,  INSERT ,  DELETE  ON  [dbo].[vwExpiringMemberships] TO public
GO

edited May 21 '13 at 13:58

answered May 21 '13 at 13:47

granadaCoder

26,328
10
113
146

Remember, there are "Yes this was helpful" arrows....and there is a "Mark as Answer". Not sure if you're new to stackoverflow, but this is proper etiquette. Esp the "mark as answer" when your answer is answered, so it doesn't keep showing up in the "Filter by Unanswered" screens (for people responding to posted questions). – granadaCoder May 21 '13 at 13:56
Since your new, I think a best practice is to preface your tables with the schema name. Don't use "Select * from Employee", use "Select * from dbo.Employee". I'm updating the view code in my answer. – granadaCoder May 21 '13 at 13:58
A view is a "virtual table".....after you create the view, you can treat it as a real-table for Selecting. Updating or Deleting from a view is a much longer topic. But for selecting, "Select * from dbo.MyView" works like selecting from a table for most intents and purposes. – granadaCoder May 21 '13 at 14:00
I was giving you the syntax of a View. You should implement the DATEDIFF() suggestion suggested in the comments. – granadaCoder May 23 '13 at 15:23

Stored Procedures SQL Server

3 Answers3