CODEWITHSUNDEEP
sql-serverstored-procedures
user3825831
user3825831

Reputation: 109

Create stored procedure for retrieving data

I am very new to creating stored procedures and I need to know if this is even possible. The query returns the results from a start date to say a week before(7 days) and returns a random sample of a percent(in this case .4). I have this query that runs and get the desired results:

    select top .4 percent *
    from
    (
      SELECT DISTINCT T.date,T.job,C.creditType
      FROM [dbo].[Trip_Credits]  as T 

      INNER JOIN [dbo].[Credits] as C 
      ON  T.credit=C.code

      INNER JOIN [dbo].[Credit_Types] CT
      ON CT.Code = C.creditType
      AND C.creditType =2 
      where T.postdate >= DateADD(day, -7, getDate())
      )  pop order by newID()

What I would like to know is if I can create a stored procedure where the user can Input the percentage value(where .4 is), data(where T.postdate is), and length(where -7 is). Keep in mind the only one that is in the table is T.postdate. I saw some examples that read like this:

    EXECUTE HumanResources.uspGetEmployeesTest2 @FirstName = N'Pilar', @LastName = N'Ackerman';

And this is where I would want the user to be able to enter the values they want. I know I can do this in C# but I was just wondering if this is possible in sql server Management Studio

Upvotes: 0

Views: 262

Answers (1)

How 'bout a Fresca
How 'bout a Fresca

Reputation: 2317

Yes you can. It would look something like this:

   CREATE PROCEDURE dbo.your_sp_name
   (
      @percent DECIMAL (1,1),
      @date DATETIME,
      @DaysToLookBack INT
   )
   AS
   BEGIN
      select top(@percent) percent *
      from
      (
        SELECT DISTINCT T.date,T.job,C.creditType
        FROM [dbo].[Trip_Credits]  as T 

        INNER JOIN [dbo].[Credits] as C 
        ON  T.credit=C.code

        INNER JOIN [dbo].[Credit_Types] CT
        ON CT.Code = C.creditType
        AND C.creditType =2 
        where @date >= DateADD(day, @DaysToLookBack, getDate())
        )  pop order by newID()
   END

Upvotes: 1

Related Questions