CODEWITHSUNDEEP
c#sql-serverexec
Mohamad Mahmoud Darwish
Mohamad Mahmoud Darwish

Reputation: 4175

How To Send WhereClause To EXEC in SQL Server

ALTER PROCEDURE [dbo].[GetTimeSheetsAttendance]

@WhereClause varchar
AS

EXEC  ('Select vwEmployeeList.ID,vwEmployeeList.Code,
        tbGNClient.FName + '' '' +  tbGNClient.MName + '' ''+ tbGNClient.LName as Name,
        EmployeeAttendanceTb.EmpAttID, EmployeeAttendanceTb.EmpAttSetupID,
        EmployeeAttendanceTb.GNClientID, EmployeeAttendanceTb.SheetID,
        EmployeeAttendanceTb.Date, EmployeeAttendanceTb.TimeIn,
        EmployeeAttendanceTb.TimeOut, EmployeeAttendanceTb.HasExtraTime,
        EmployeeAttendanceTb.ExtraTime, EmployeeAttendanceTb.TotalOfHours,
        EmployeeAttendanceTb.TotalOfCost, EmployeeAttendanceTb.Description,
        vwEmployeeList.Department,
        EmployeeAttendSetup.NoMaximumUnits,
        EmployeeAttendSetup.NoMinimumUnits
        From EmployeeAttendanceTb
        LEFT JOIN vwEmployeeList on EmployeeAttendanceTb.GNClientID = vwEmployeeList.ID
        LEFT JOIN tbGNClient on vwEmployeeList.ID = tbGNClient.GNClientID
        LEFT JOIN EmployeeAttendSetup on EmployeeAttendanceTb.GNClientID = EmployeeAttendSetup.EmpID And EmployeeAttendanceTb.EmpAttSetupID  = EmployeeAttendSetup.AttndUnitsID
        ' + ' ' + @WhereClause)

Note : @WhereClause is sent from a C# application As parameters.

DBType=nvarchar.. 
@WhereClause.Value= "Where EmployeeAttendanceTb.GNClientID=17"

Exception :

There are some problems while trying to use the Data Access Application block, please check the following error messages:Incorrect syntax near 'W'.

Upvotes: 0

Views: 102

Answers (1)

MartW
MartW

Reputation: 12538

The problem is primarily caused by the fact that you've not declared the length of your @WhereClause parameter, only stating it is varchar. This means it gets the default length, which is 1 in variable declarations, and consequently only the 'W' of your WHERE clause is actually being passed through to the EXEC.

When n is not specified in a data definition or variable declaration statement, the default length is 1.

Upvotes: 5

Related Questions