Convert this inline SQL to stored procedure

Question

How can I convert this inline SQL to a stored procedure

SELECT     
       PM.ProjectName           
      ,[PurposeorReason]    
      ,Reg.Name    
      ,EA.Comment as Comment   
      ,[FromDate]    
      ,[VoucherID]    
      ,[TotalAmount]    
      ,ex.CreatedOn        
  FROM [TimesheetDB].[dbo].[Expense] ex    
  inner join Registration Reg on reg.RegistrationID = ex.UserID    
  inner join ProjectMaster PM on ex.ProjectID =PM.ProjectID 
  inner join AssignedRoles AR on reg.RegistrationID = AR.RegistrationID   
  inner join ExpenseAuditTB EA on ex.ExpenseID = EA.ExpenseID 
  where FromDate  between '2019-09-25' and '2019-09-29' 
  and ea.ProjectID IN (1,2,5)
  and EA.Status = 2

I have issues with the (1,2,5) part. I have it in C# as a string "1,2,5" and the ProjectID is integer.

I know I am doing something wrong. The query itself works well.

Possible duplicate of [C# SQL Server - Passing a list to a stored procedure](https://stackoverflow.com/questions/7097079/c-sharp-sql-server-passing-a-list-to-a-stored-procedure) — Eldho, Sep 28 '19 at 08:37
You have to define `User Valued Type` and use it in your stored procedure — Eldho, Sep 28 '19 at 08:39
@Eldho, please the value changes based on different users, so i cant use it in stored procedure. — Peter Godfrey Obike, Sep 28 '19 at 08:52
You have to pass the values based on the user input. Or you can query project id = `select someId from Table where user ='youruser'` in your stored procedure — Eldho, Sep 28 '19 at 08:54
Use "Create Procedure". See : https://learn.microsoft.com/en-us/sql/t-sql/statements/create-procedure-transact-sql?view=sql-server-2017 — jdweng, Sep 28 '19 at 09:01
@@Peter Godfrey Obike I putting my answer...just they way you want......... — THE LIFE-TIME LEARNER, Sep 28 '19 at 11:06
You should pass the list of Project ID values as an array - which in tsql is a TVP. Discussed by Erland in detail [here](http://www.sommarskog.se/arrays-in-sql-2008.html). A little internet searching will find MANY examples. — SMor, Sep 28 '19 at 12:02

score 0 · Answer 1 · answered Sep 28 '19 at 18:10

I suggest a table-valued parameter to pass lists/arrays to a stored procedure. In the C# code, specify parameter type SqlDbType.Structured as the parameter type. The parameter value can be a DataTable, IEnumerable<SqlDataRecord>, or DbDataReader. I suggest a DataTable with a single column for this use case.

CREATE TYPE dbo.TVPProjectIdList AS TABLE (
    ProjectId int NOT NULL PRIMARY KEY
);
GO

CREATE PROCEDURE dbo.Example
      @StartDate date
    , @EndDate date
    , @Status int
    , @ProjectIdList dbo.TVPProjectIdList READONLY
AS
SET NOCOUNT ON;
SELECT     
       PM.ProjectName           
      ,[PurposeorReason]    
      ,Reg.Name    
      ,EA.Comment as Comment   
      ,[FromDate]    
      ,[VoucherID]    
      ,[TotalAmount]    
      ,ex.CreatedOn        
  FROM [TimesheetDB].[dbo].[Expense] ex    
  inner join Registration Reg on reg.RegistrationID = ex.UserID    
  inner join ProjectMaster PM on ex.ProjectID =PM.ProjectID 
  inner join AssignedRoles AR on reg.RegistrationID = AR.RegistrationID   
  inner join ExpenseAuditTB EA on ex.ExpenseID = EA.ExpenseID 
  where FromDate  between @StartDate and @EndDate
  and ea.ProjectID IN (SELECT ProjectId FROM @ProjectIdList)
  and EA.Status = @Status;
GO

score -1 · Answer 2 · answered Sep 28 '19 at 11:05

You have to add commas at the beginning and at the end of @ProjectId string.

In c# How to Get comma value at the beginning and at the end

string str = "1,2,5";

string replaced=str.Replace(',',',');

string concatenatdvalue=","+replaced+',';

Result = ",1,2,5,"

Then result value you can Pass as @ProjectId into SQL Server......

CREATE PROCEDURE dbo.sp_ProjectReport
(
    @FromDate VARCHAR(20)=NULL,
    @ToDate VARCHAR(20)=NULL,
    @ProjectId VARCHAR(50)=NULL,
    @StatusId INT=NULL
)
AS
BEGIN


SELECT     
       PM.ProjectName           
      ,[PurposeorReason]    
      ,Reg.Name    
      ,EA.Comment as Comment   
      ,[FromDate]    
      ,[VoucherID]    
      ,[TotalAmount]    
      ,ex.CreatedOn        
  FROM [TimesheetDB].[dbo].[Expense] ex    
  inner join Registration Reg on reg.RegistrationID = ex.UserID    
  inner join ProjectMaster PM on ex.ProjectID =PM.ProjectID 
  inner join AssignedRoles AR on reg.RegistrationID = AR.RegistrationID   
  inner join ExpenseAuditTB EA on ex.ExpenseID = EA.ExpenseID 
  where 
  convert(DATETIME,FromDate)   BETWEEN Convert(DATETIME,CASE WHEN isnull(@FromDate,'')='' THEN FromDate ELSE isnull(@FromDate,'') END)                     
  AND Convert(DATETIME, CASE WHEN isnull(@ToDate,'')='' THEN FromDate ELSE isnull(@ToDate,'') END)    

  and CHARINDEX(','+cast(ea.ProjectID as varchar(100))+',', @ProjectId) > 0

  and EA.Status = @StatusId 

  END

Testing Script For Executing SP

 EXEC dbo.sp_ProjectReport
 @FromDate='2019-09-25',
 @ToDate='2019-09-29',
 @ProjectId=',1,2,5,',
 @StatusId='2'

Note:- Here i'm changing also logic of FromDate to ToDate .........null part is also Handle in Custom Date Range.......

For E.g.

If you are enter only FromDate as "07/06/2017" then it will give you from result("07/06/2017" to last Date)

Simply a terrible idea - passing everything as a string just increases the potential for abuse and error. — SMor, Sep 28 '19 at 12:04

Convert this inline SQL to stored procedure

2 Answers2