Help using cloumn name as parameters!!
-
HELP!!! Urgent!!!!This is the case, and all help will be very much appreciated!!! One of the developers handed me the following alter stament for a procedure alreay created (exactly as the alter) He wants to filter a record set with some parameters. One is a known value, the second could be any of the colums in a view.
Here is the alter statement, at the end what they need:
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
ALTER procedure usp_GetJobLog
(@branch udt_code, @Name int, @Value varchar(2000))
as
select cast(WorkOrder as varchar(100)) as workorder,
Date as recordcreationdate,
JobID as jobID,
JobNumber as jobnumber,
JobDescription as jobdescription,
CompanyCode as companycode,
LocationName as locationname,
LocationAddress as locationaddress,
LocationCity as locationcity,
LocationStateProvinceCode as locationstateprovincecode,
Rtrim(Ltrim(LocationPostalCode)) as locationpostalcode,
PositionCode as position,
OrderType as ordertype,
OrderStartDate as orderstartdate,
EstimatedAssignmentLengthComments as estimatedassignmentlengthcomments,
cast(StaffingAssociatePersonGUID as varchar(100)) as staffingassociatepersonGUID,
StaffingAssociateFirstName as staffingassociatefirstname,
StaffingAssociateLastName as staffingassociatelastname,
OrderOpenBy as orderopenby,
PayRate as payrate,
BillRate as billrate,
MarkUp as markup,
HourlyGM as hourlyGM,
convert(smalldatetime, OrderFillDate) as orderfilldate,
InterviewComments as interviewcomments,
cast(SalesAssociatePersonGUID as varchar(100)) as salesassociateGUID,
WorkOrderStatus as workorderstatus
from vJobLog
where col_name(object_id('vJobLog'), @Name) like @Value
and branchid like @branchid
GO
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
This the statement, now he wants to run the styored proc using:
BRANCH and any of the values in any of the columns in the view. For example:
10 (for the branch) and Philadelpia (for the Location Name)
or
10 and 9/20/2005 (as order start date)
How can I achieve this one. As always, they need this done by two days ago!!! PLEASE HELP URGENT!!!!
THANK YOU!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
-
You shouldn't mess with this on the server. Let the developer do the filtering on the client instead, you just return all columns without any fuzz.
Since the dev's code knows the content of the variable holding the columnname, he can pick out that column in his client code for display as well.
..just my .02 though
/Kenneth
-
It can be done but I'd strongly suggest adding a few limitations so that this doesn't slow down too much. You'd either have to go with dynamic sql or one of the dyn search options. I'd strongly advise against dynamic sql in this case.
-
Hi,
If the purpose of the input parameters are to specify which column to filter against, then rather change the stored procedure to take all the available columns as input parameters. This way you can also filter against more than one column are a time.
I have a stored proc that does exactly that on a table, you would have to rework yours to fit onto your table. Below see the script to create the stored proc:
CREATE PROCEDURE dbo.WODIssue_GetWhere
(
@WOID int = null,
@CFStatusID int = null,
@CFSeverityID int = null,
@CFModuleID int = null,
@CFProjectID int = null,
@CFTypeOfProblemID int = null,
@Reassigned_CFTypeOfProblemID int = null,
@ReportedBy_CFUserID int = null,
@AssignedTo_CFUserID int = null,
@Notes text = null,
@EstimatedTimeRequired decimal(19, 2) = null,
@Priority int = null,
@ActualTimeSpent decimal(19, 2) = null,
@DescriptionOfFix text = null,
@StoryTrackerID int = null,
@RolloutVersion varchar(100) = null,
@UserAcceptanceTestingPassDate datetime = null,
@CFUserID_AcceptedBy int = null,
@DateCreated datetime = null )
AS
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
DECLARE @selectWhere NVARCHAR(2100)
SET @selectWhere ='SELECT WODIssueID,
WOID,
CFStatusID,
CFSeverityID,
CFModuleID,
CFProjectID,
CFTypeOfProblemID,
Reassigned_CFTypeOfProblemID,
ReportedBy_CFUserID,
AssignedTo_CFUserID,
Notes,
EstimatedTimeRequired,
Priority,
ActualTimeSpent,
DescriptionOfFix,
StoryTrackerID,
RolloutVersion,
UserAcceptanceTestingPassDate,
CFUserID_AcceptedBy,
DateCreated FROM dbo.WODIssue '
SET @selectWhere = @selectWhere + ' WHERE WOID = CASE WHEN @WOID IS NOT NULL THEN @WOID ELSE WOID END,
CFStatusID = CASE WHEN @CFStatusID IS NOT NULL THEN @CFStatusID ELSE CFStatusID END,
CFSeverityID = CASE WHEN @CFSeverityID IS NOT NULL THEN @CFSeverityID ELSE CFSeverityID END,
CFModuleID = CASE WHEN @CFModuleID IS NOT NULL THEN @CFModuleID ELSE CFModuleID END,
CFProjectID = CASE WHEN @CFProjectID IS NOT NULL THEN @CFProjectID ELSE CFProjectID END,
CFTypeOfProblemID = CASE WHEN @CFTypeOfProblemID IS NOT NULL THEN @CFTypeOfProblemID ELSE CFTypeOfProblemID END,
Reassigned_CFTypeOfProblemID = CASE WHEN @Reassigned_CFTypeOfProblemID IS NOT NULL THEN @Reassigned_CFTypeOfProblemID ELSE Reassigned_CFTypeOfProblemID END,
ReportedBy_CFUserID = CASE WHEN @ReportedBy_CFUserID IS NOT NULL THEN @ReportedBy_CFUserID ELSE ReportedBy_CFUserID END,
AssignedTo_CFUserID = CASE WHEN @AssignedTo_CFUserID IS NOT NULL THEN @AssignedTo_CFUserID ELSE AssignedTo_CFUserID END,
Notes = CASE WHEN @Notes IS NOT NULL THEN @Notes ELSE Notes END,
EstimatedTimeRequired = CASE WHEN @EstimatedTimeRequired IS NOT NULL THEN @EstimatedTimeRequired ELSE EstimatedTimeRequired END,
Priority = CASE WHEN @Priority IS NOT NULL THEN @Priority ELSE Priority END,
ActualTimeSpent = CASE WHEN @ActualTimeSpent IS NOT NULL THEN @ActualTimeSpent ELSE ActualTimeSpent END,
DescriptionOfFix = CASE WHEN @DescriptionOfFix IS NOT NULL THEN @DescriptionOfFix ELSE DescriptionOfFix END,
StoryTrackerID = CASE WHEN @StoryTrackerID IS NOT NULL THEN @StoryTrackerID ELSE StoryTrackerID END,
RolloutVersion = CASE WHEN @RolloutVersion IS NOT NULL THEN @RolloutVersion ELSE RolloutVersion END,
UserAcceptanceTestingPassDate = CASE WHEN @UserAcceptanceTestingPassDate IS NOT NULL THEN @UserAcceptanceTestingPassDate ELSE UserAcceptanceTestingPassDate END,
CFUserID_AcceptedBy = CASE WHEN @CFUserID_AcceptedBy IS NOT NULL THEN @CFUserID_AcceptedBy ELSE CFUserID_AcceptedBy END,
DateCreated = CASE WHEN @DateCreated IS NOT NULL THEN @DateCreated ELSE DateCreated END'
EXEC (@selectWhere)
RETURN ISNULL(@@ROWCOUNT, -1)
GO
As you can see, the procedure takes in all the available columns - although they are all nullable. If you for exec this proc and pass it values for certain columns to filter against, it applies only those filters.
-
Does this work? I would imagine you would get an error when you run this proc, because the variables are not known in the dynamically executed part. On the other hand, I don't see the reason for using dynamic execution...
-
Hi,
You are right - I was busy changing this proc into dynamic SQL when I copied & pasted it into this - so this one doesn't work, just use straightforward SQL instead of dynamic SQL, then it works
-
or you could set up the where clause to be a big case statement and not use dynamic sql at all.
where @value = case @name
when 1 then col1value
when 2 then col2value
when 3 then col3value
when 4 then col4value
else 'no matching data'
end
Ad Id probably change @name from an int to a Varchar(20) or something. Is a lot easier to work with names than column numbers, what if somewhere down the line you need to chage the structure of the table?
Bill
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply