Join Stored Procedure And View
Jun 22, 2005Hi everybody
View 3 Replies
ADVERTISEMENT
Calling A Stored Procedure From A View OR Creating A #tempTable In A View
Aug 24, 2007Hi guys 'n gals,
I created a query, which makes use of a temp table, and I need the results to be displayed in a View. Unfortunately, Views do not support temp tables, as far as I know, so I put my code in a stored procedure, with the hope I could call it from a View....
I tried:
CREATE VIEW [qryMyView]
AS
EXEC pr_MyProc
and unfortunately, it does not let this run.
Anybody able to help me out please?
Cheers!
Should I Use A View Or A Stored Procedure
Apr 5, 2007I'm modifying a pretty big web application and the programmer who built it used all stored procedures and no views. Does anyone know why someone would do this? I realize that you can't pass parameters with views and insert/update/delete records with views, but he even used stored procedures for queries like: SELECT * FROM myTable WHERE myVal > 0 ORDER BY myVal Is it more efficient to put this in a stored procedure compared a view?
View 1 Replies View RelatedView Vs. Stored Procedure
Jul 7, 2000Are there performace benefits to using a select from a View instead of a stored procedure that returns the same dataset? I am concerned about when we ramp up to 100's of users.
View 3 Replies View RelatedStored Procedure Vs View
Jan 23, 2006I like the security of using stored procedures. It seems I am able to do anything with it that I can with a view. Why would I choose a view over a sproc?
View 3 Replies View RelatedStored Procedure Or View?
Mar 14, 2007Hello all I am not quite a beginner but not an expert at SQL. I'm kind of in a bind and need some help. I have a table that shows me statuses of tickets (open, pending, closed), some tickets could have as much as 25 rows/ticket. I want to try to avoid that but at the same time keep track of the time. Here's what I need to happen...
with the data example below I need to take the ((closed date - first open date) - total of Waiting time). This will give me total time duration of the ticket. I'd like to either write a stored procedure or create a view that would do this for me. Any one have ideas?
CallID DateStopTimeStopCallStatus
002161772006-01-2005:39:24Open
002161772006-01-2005:39:27Open
002161772006-01-2005:40:13Open
002161772006-01-2005:40:24Pending
002161772006-02-0716:05:47Pending
002161772006-02-2117:26:22Pending
002161772006-02-2117:29:06Pending
002161772006-02-2117:29:08Open
002161772006-03-0316:35:10Open
002161772006-04-0515:12:26Open
002161772006-04-0515:17:09Open
002161772006-04-1414:37:49Open
002161772006-04-1414:37:54Awaiting
002161772006-04-1911:20:30Awaiting
002161772006-04-1912:12:34Awaiting
002161772006-04-1912:12:37Awaiting
002161772006-04-1912:12:58Awaiting
002161772006-04-1912:13:00Closed[/b]
Stored Procedure From The View
Sep 25, 2007How can I create a stored procedure that combines the results from three views, and puts them in a temp table?
View 20 Replies View RelatedUsing A Stored Procedure In A View
Jan 1, 2008Hi all,
I want to know if there is a way to use a stored procedure in a view OR
a table value function OR
use the store procedure in table value function.
If any of these is a possibility, it would help. So far i have learnt that extended stored procedures can be accessed in table value functions.
Thanks.
Using A Stored Procedure In A View
Jan 1, 2008Hi all,
I want to know if there is a way to use a stored procedure in a view OR
a table value function OR
use the store procedure in table value function.
If any of these is a possibility, it would help. So far i have learnt that extended stored procedures can be accessed in table value functions.
Thanks.
Stored Procedure And INNER JOIN
Sep 26, 2007Will you please look at my stored procedure below? I originally had table "Servee" as Parent table and "Orders" talbe as child table, using relationships in VWD express. I am querying sql server 2005 express. When I run the page, I got an error saying a conflict relationship Orders_FK-Servee. So I deleted the table relationship and ran the page, but the procdure didn't do anything, but no error.
What I'm trying to do, is Insert a new record if the current (meaning displayed on the page) ServeeID is not present in the Orders table. If the current ServeeID is present, UPDATE the current record. Thanks in advance.
ALTER PROCEDURE dbo.newjob
@ServeeName varchar(500),@Identity int OUT
AS
IF NOT EXISTS(SELECT * FROM Servee INNER JOIN Orders ON Servee.ServeeID=Orders.ServeeID)
BEGININSERT Servee (ServeeName)
VALUES (@ServeeName)
END
ELSE
BEGIN
UPDATE Servee SET Servee.ServeeName=Servee.ServeeName FROM Servee INNER JOIN Orders ON Servee.ServeeID=Orders.ServeeID
ENDSET @Identity = SCOPE_IDENTITY()
Return
Self Join Or Stored Procedure
Aug 3, 2005hi gurushave a employee table which consist of the normal employee relatedfields which the fields empid and supervisor mainly related to thisquestion.have to create a single (the question is whether it is possible) or astored procedure to get the result as follow. the input parameter isempid.have to retrieve the subordinates records of the employee id passed asparameter and the drill down.for example from the below table. if empid 12 is been passed as theparameter value then it should retrieve the records with followingempid - 1, 26. the employee with empid 1 has subordinates under himwhich should also be pulled that is 16, 62 and so on. the output forthe above example will be a result with records in the following order- 12, 1,16,62, 26 and so on.hope i have explained the problem clearlywould appreciate if some one can guide in creating an efficient selfjoin or a stored procedureEmpId FirstName LastName Supervisor----- --------- -------- ----------1 Carl Hogans 1212 Fred Smith NULL16 Sue Bankers 126 Frank Green 1255 Karen Feeders NULL56 James Black 5557 Kirk Simmons 5658 Cliff Page 5659 Jimmy Plant 5660 Jack Cale 5961 Robert Santana NULL62 Jack Russell 1where clause or parameter passed is 'EmpId'thanx in advancebala
View 4 Replies View RelatedHelp Coverting A Stored Procedure To A View
Aug 13, 2007Can someone help me convert this stored procedure to a view? It is using two UDFs.
I appreciate this very much!@Start datetime,
@End datetime
AS
SELECT
C.Client_ID, (SUM(COALESCE(PR.AmountPaid,0))+ SUM(COALESCE(SC.SC_AMOUNT,0))) AS SumOfpmts, C.OrgName, C.FirstName, C.LastName, C.Sal1, C.Sal2, C.Sal3, A.Address, A.Address_Line2, A.City, A.State, A.Zip, A.Country, C.Title,
dbo
.getLevel(SUM(COALESCE(PR.AmountPaid,0))+ SUM(COALESCE(SC.SC_AMOUNT,0))) as pmtLevel,
dbo
.getLevelDesc(SUM(COALESCE(PR.AmountPaid,0))+ SUM(COALESCE(SC.SC_AMOUNT,0))) as Description
FROM
tblClients C INNER JOIN
tblPMTs P
ON C.Client_ID = P.Client_ID INNER JOIN
tblPMTReceipts PR
ON P.PMT_ID = PR.PMT_ID INNER JOIN
tblClientAddresses A
ON C.Client_ID = A.Client_ID LEFT OUTER JOIN
tblSoftCreditsPMTS SC
ON C.Client_ID = SC.SC_Client_ID
WHERE
(PR.PaymentDate BETWEEN @Start AND @End)
GROUP
BY C.Client_ID, C.OrgName, C.FirstName, C.LastName, C.Sal1, C.Sal2, C.Sal3, A.Address, A.Address_Line2, A.City, A.State, A.Zip, A.Country, C.Title
ORDER
BY pmtLevel
RETURN
Using Stored Procedure In View As A Table ?!
Aug 28, 2007Hi guys
I have a stored procedure that a make crosstab table , In this table the main column is "job titles" these jobs must be ordered in certain way , for example "1st managers then engineers … workers … " so In the table that job titles are defined there is also a column named "Ranking" so the" job titles" could be sorted appropriately by ranking order .
The problem is I cannot have the "Ranking" column with my crosstab table so I need to load it in a view or something like that.
Any Idea?
Updating A View Through A Stored Procedure.
Oct 14, 2007Hi i have a page in which a user fills out info on a page, the problem i am getting is that when the save button is clicked all text box values apart from one are saving to the database this field is the "constructor_ID" field. The save button performs a stored procedure, however there is a view which is doing something as well, would it be possible to write a stored procedure which would update the view at the same time?
CREATE PROCEDURE sp_SurveyMainDetails_Update
@Constructor_ID int,@SurveyorName_ID int,@Survey_Date char(10),@Survey_Time char (10),@AbortiveCall bit,@Notes text,@Survey_ID int,@User_ID int,@Tstamp timestamp out AS
DECLARE @CHANGED_Tstamp timestampDECLARE @ActionDone char(6)SET @ActionDone = 'Insert'
SET @CHANGED_Tstamp = (SELECT Tstamp FROM tblSurvey WHERE Survey_ID = @Survey_ID)IF @Tstamp <> @CHANGED_Tstamp --AND @@ROWCOUNT =0 BEGIN SET @Tstamp = @CHANGED_Tstamp RAISERROR('This survey has already been updated since you opened this record',16,1) RETURN 14 ENDELSE
BEGIN
SELECT * FROM tblSurvey WHERE Constructor_ID = @Constructor_ID AND --Contractor_ID = @Contractor_ID AND Survey_DateTime = Convert(DateTime,@Survey_Date + ' ' + LTRIM(RTRIM(@Survey_Time)), 103) AND IsAbortiveCall = @AbortiveCall IF @@ROWCOUNT>0 SET @ActionDone = 'Update'
UPDATE tblSurvey SET Constructor_ID = @Constructor_ID , SurveyorName_ID = @SurveyorName_ID , Survey_DateTime = Convert(DateTime,@Survey_Date + ' ' + LTRIM(RTRIM(@Survey_Time)), 103) , IsAbortiveCall = @AbortiveCall , Note = @Notes WHERE Survey_ID = @Survey_ID AND Tstamp = @Tstamp IF @@error = 0 begin exec dhoc_ChangeLog_Insert 'tblSurvey', @Survey_ID, @User_ID, @ActionDone, 'Main Details', @Survey_ID
end else BEGIN RAISERROR ('The request has not been proessed, it might have been modifieid since you last opened it, please try again',16,1) RETURN 10 END SELECT * FROM tblSurvey WHERE Survey_ID=@Survey_ID
END
--Make sure this has saved, if not return 10 as this is unexpected error
--SELECT * FROM tblSurvey
DECLARE @RETURN_VALUE tinyintIF @@error <>0 RETURN @@errorGO
This is the view;
CREATE VIEW dbo.vw_Property_FetchASSELECT dbo.tblPropertyPeriod.Property_Period, dbo.tblPropertyType.Property_Type, dbo.tblPropertyYear.Property_Year, dbo.tblProperty.Add1, dbo.tblProperty.Add2, dbo.tblProperty.Add3, dbo.tblProperty.Town, dbo.tblProperty.PostCode, dbo.tblProperty.Block_Code, dbo.tblProperty.Estate_Code, dbo.tblProperty.UPRN, dbo.tblProperty.Tstamp, dbo.tblProperty.Property_ID, dbo.tblProperty.PropertyStatus_ID, dbo.tblProperty.PropertyType_ID, dbo.tblProperty.Correspondence_Add4, dbo.tblProperty.Correspondence_Add3, dbo.tblProperty.Correspondence_Add2, dbo.tblProperty.Correspondence_Add1, dbo.tblProperty.Correspondence_Phone, dbo.tblProperty.Correspondence_Name, dbo.tblPropertyStatus.Property_Status, dbo.tblProperty.Floor_Num, dbo.tblProperty.Num_Beds, dbo.vw_LastSurveyDate.Last_Survey_Date, dbo.tblProperty_Year_Period.Constructor_ID, dbo.tblProperty_Year_Period.PropertyPeriod_ID, dbo.tblProperty_Year_Period.PropertyYear_ID, LTRIM(RTRIM(ISNULL(dbo.tblProperty.Add1, ''))) + ', ' + LTRIM(RTRIM(ISNULL(dbo.tblProperty.Add2, ''))) + ', ' + LTRIM(RTRIM(ISNULL(dbo.tblProperty.Add3, ''))) + ', ' + LTRIM(RTRIM(ISNULL(dbo.tblProperty.PostCode, ''))) AS Address, dbo.tblProperty.TenureFROM dbo.tblPropertyType RIGHT OUTER JOIN dbo.tblProperty LEFT OUTER JOIN dbo.tblProperty_Year_Period ON dbo.tblProperty.Property_ID = dbo.tblProperty_Year_Period.Property_ID LEFT OUTER JOIN dbo.vw_LastSurveyDate ON dbo.tblProperty.Property_ID = dbo.vw_LastSurveyDate.Property_ID LEFT OUTER JOIN dbo.tblPropertyStatus ON dbo.tblProperty.Status_ID = dbo.tblPropertyStatus.PropertyStatus_ID ON dbo.tblPropertyType.PropertyType_ID = dbo.tblProperty.PropertyType_ID LEFT OUTER JOIN dbo.tblPropertyPeriod ON dbo.tblProperty.PropertyPeriod_ID = dbo.tblPropertyPeriod.PropertyPeriod_ID LEFT OUTER JOIN dbo.tblPropertyYear ON dbo.tblProperty.PropertyYear_ID = dbo.tblPropertyYear.PropertyYear_ID
Is It Possible To Create A View From A Stored Procedure
Jan 3, 2008Is it possible to drop and then create a view from a stored procedure? Like the way you can drop and create a temp table.
I want to create a view of the fields in a table something like: But I cannot include the field names, they may be changed by an admin user.
If exists view 'custom_fields"
drop view 'custom_fields'
Create view custom_fields
Select * From tblCustomFields
And make this a view in the db named custom_fields.
And I want to call it from a button click in my UI.
When To Use ? Stored Procedure V/s View In SQL Server
Dec 6, 2005hi,
Can someone tell me when to use SQL Server View as oppose to Stored Porcedure?
Currently we do everything with SQL Server stored procedure. I mean, even if we have to display some report, we use Stored Procedure.
In what situations and senarios views are better and one should consider them over Stored Procedure?
View Creation Using Stored Procedure
Apr 21, 2001Hi
I need to create a view using a stored procedure .
The task is to Upload multiple sql server tables sourcing data from flat files as well as SQL server tables .It is the process of Data migration.
After loading few tables,I need to create a view on thoes tables which can be used (queried )to load furthe tables.
I need to AUTOMATE THIS PROCESS .Means Once I schedule the job .It should take fire the stored procedures one after another .
I am thinking to create a view though a stored procedure .
You can suggest me alternate ways to do same .
Sujit
Stored Procedure - Can I Use 4-part Name With A "VIEW"?
Aug 3, 2001Joe, thank you for the answer to my post.
Can I also use a
SELECT field1 FROM DBName.dbo.TableName with a "VIEW" in this other database (that's on the same server)?
Also, in my sp, I have the following:
SELECT DISTINCT Store.[DemoID#], Progstats.ProgramName, Progstats.[Program#], ZCHAIN.STR_NAME, ZCHAIN.[STR#], ZCHAIN.ADDRESS,
ZCHAIN.City, ZCHAIN.ST, ZCHAIN.ZIP, ZCHAIN.[PHONE#], Store.D1, Store.Status, Store.AgencyCompleted,
Store.Reason, Store.LeadName, Store.DemonstratorName, Store.UpdatedOnline
FROM (Store INNER JOIN Progstats ON Store.[Program#] = Progstats.[Program#]) INNER JOIN ZCHAIN ON Store.[TD#] = ZCHAIN.[ID#]
WHERE (((Store.[DemoID#])=@DemoID)) AND Progstats.Status=1;
GO
ZCHAIN has now become this "VIEWZCHAIN" in this other database. So, could I simply relace "ZCHAIN" with "DB2.dbo.VIEWZCHAIN.STR_NAME" which is actually now a 4-part name?
Thanks,
John Wilson
Accessing A 'View' From Within A Stored Procedure
Jun 7, 2004Hiya folks,
I'n need to access a view from within a SProc, to see if the view returns a recordset and if it does assign one the of the fields that the view returns into a variable.
The syntax I'm using is as follows :
SELECT TOP 1 @MyJobN = IJobN FROM MyView
I keep getting an object unknown error (MyView). I've also tried calling it with the 'owner' tags.
SELECT TOP 1 @MyJobN = IJobN FROM LimsLive.dbo.MyView
But alas to no avail!
Any offers kind people??
Call Stored Procedure From View
Sep 7, 2006Hello all,
does anyone know if it's possible to call a stored procedure from a view.
Thnx,
Patrick
Creating A View In A Stored Procedure
Apr 2, 2004Hi Everyone
Im trying to create a view from within a stored procedure and are having problems. Is it possible to do this? And if so, how? I've been trying with the code below.
CREATE PROC upProcName AS
DECLARE @Variable varchar(50)
CREATE VIEW vwName AS
SELECT DISTINCT Table1.*, Table2.*
FROM dbo.Table1
INNER JOIN dbo.Table2 AS BUG
ON Table1.Col1 = Table2.Col1
WHERE LI.accname = @Variable
GO
Any Thoughts ideas would be great
Cheers
Need Help To Create A View/Stored Procedure
Jun 24, 2008Experts
I am trying to create a view or Stored Procedure between different table
Table1 consist of the follwing Fields:
Ref_No: String hold the reference number, Unique
Details: String
Table2:
MasterRefNum : String, not Unique
SubscriberRefNum : String, not Unique
What I am trying to do is that when the user enter a refernece number the system should return back
1- the details where Ref_No = the required refernece number
2- get all the SubscriberRefNum from Table2 where MasterRefNum = the required refernece number and from the Table1 get the details for those SubscriberRefNum numbers
Any advice?
Create View Within A Stored Procedure
May 21, 2007hi,
Im am wandering if it is possible to create two views in two different tables from within the same stored proc:ex
create proc myProc
as
use [myDb1]
go
create view myV1
as
select * from mytable
go
use [myDb2]
go
create view myV2
as
select * from mytable
go
go
---
of course the go's are not allowed in a sproc, the create statement must be the first of a query batch and a vew can not have the databaase name preapended like when creating a table plus one can not use the "use" word in a proc, I tried using exec to bypass the "first statement in a batch" and go restrictions but have not been able to overcome the "use [myDb]" restriction, is there a way to solve this problem?
thank you
Creating A View From A Stored Procedure
Sep 19, 2007HelloNewbie here.Is there a way of creating a VIEW...using a stored procedure. I ambasically trying to create a view to return some data that I amgetting using a stored procedure.I have created the procedure and when I execute this its working ok.The stored procedure uses a datefrom and dateTo which I have set up bytweaking the getdate() and getdate()-2.In other words can you create a view like thisCREATE VIEW view_testASexec proc_testGOAny help will be greatly appreciated.Remmy
View 1 Replies View RelatedCretae A View In One DB With A Stored Procedure From Another DB
Jul 20, 2005Hi,Ik created an application with visuals basic.NET. This has aconnection string to one database, let's say 'A'. In this database astored procedure is called which should execute a string (which ispassed by the) VB tool. This string is a CREATE VIEW statement en thisshould be executed in another database let's say 'B'.I tried this in Transact - SQLEXEC('USE B;' + Query)An error occurs : CREATE VIEW should be the first in a batchedstatement.Could anyone help me with this one?Greetz,Hennie
View 1 Replies View RelatedView Stored Procedure Permissions
Jul 20, 2005We are running SQL Server 2000 Developer Edition. I don't want tomake the developers the sysadmin or even the dbo in the userdatabases. Is there a way to give them access to only view thepermissions for the stored procedures in the user database withoutmaking them dbo?When I take them out of the db_owner role, when they open a storedprocedure they no longer see the permissions tab. I would like forthem to see the permissions tab and be able to view the permissionsbut not change the permissions.Is that doable?
View 1 Replies View RelatedCan I Add Alter View In The Stored Procedure?
May 13, 2008Hi,
I am using SQL 2000( Version: 8.00.000)
I want to ask can I add View in the stored procedure like the following:
Create Procedure sp_createTblLogReasonColorView
@m int,
@y int
AS
BEGIN
ALTER View vw_tblLogReasonColor
AS
Select * From tblLogReasonColor where Year(n_Delivery_Date) = @y AND Month(n_Delivery_Date) = @m
END
Thanks for you reply.
Can I Call A Stored Procedure In View?.
May 5, 2008
Is it possible to call a soted procedure in view?.
If so, how to call?.
Please post syntax.
Thanks
Best Practice For View Or Stored Procedure
Oct 31, 2007
I have a view setup on db2 that I am accessing from our sql server. I'm trying to figure out how I could setup a view or stored procedure on the sql server to access the db2 view so the developers don't have to use the openquery statement, but still could query it like they had access to a table. In other words, I want them to be able to specify whatever columns they need to from the sql server stored procedure or view without sacrificing performance on the db2 side of things. I don't want to select every record from the db2 view or stored procedure and then filter based on that criteria.
I think I need to use a stored procedure to accomplish what I am trying to do. I would also like to prevent any type of sql injection attacks. Therefore, I didn't want to just create a long varchar variable and store the entire where clause in the varchar variable. In addition, I want the developers to be able to specify only the columns needed to be returned.
select * from openquery ([linkedServer], 'select * from catalog.schema.table where column1=''A''')
select * from openquery ([linkedServer], 'select column4 from catalog.schema.table where column1=''A'' or column2=''A'' or column3=''A''')
Any help would be greatly appreciated.
Is It Possible To Create A View Within A Stored Procedure
May 2, 2007Is it possible to dynamically create an sql create view statement then execute that sql statement? Or because create views must be the first statement in a query batch, it's not possible?
View 6 Replies View RelatedStored Procedure And Join Statement.
Mar 2, 2007I'm very new to SQL and have been assigned to modify an existing Store Procedure. The existing is pasted below:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE RPT_ACTIVITY_LEDGER
(
@StartDate datetime = NULL,
@EndDate datetime = NULL,
@PrjName varchar(50) = '%',
@TaskName varchar(50) = '%',
@EmpName varchar(50) = '%'
)
AS
SELECT ActivityCode AS 'Activity Code',
ActivityDate AS 'Activity Date',
ActDescription AS 'Activity Desc',
ActDetail AS 'Activity Detail',
ActSource AS 'Activity Source',
ActPrjSource AS 'Activity Type',
ActBillable AS 'Billable',
ActBillRate AS 'Bill Rate',
ActBilltoCCName AS 'Bill to Cost Center',
(CASE WHEN ActARClosed = 1 Then 'Billed' Else 'Not Billed' END) AS 'Billed Status',
ActBillingType AS 'Billing Type',
ActBudType AS 'Budget Type',
ActBudCat AS 'Budget Cat',
ActBudSubCat AS 'Budget Sub Cat',
ActCompMonth AS 'Company Month',
ActCompWeek AS 'Company Week',
ActCompYear AS 'Company Year',
ActCostRate AS 'Cost Rate',
ActCustName AS 'Customer',
ActCustMngr AS 'Customer Manager',
ActCustomerPct AS 'Customer Percentage',
ActCustPO AS 'Customer PO',
ActDataSplash AS 'Data Splash',
ActDocId AS 'Document ID',
ActEmpName AS 'Employee',
ActEmpCCCode AS 'Employee CC Code',
ActEmpCCGroup AS 'Employee CC Group',
ActEmpCCName AS 'Employee Cost Center',
ActEmpCCGrp3 AS 'Employee Department',
ActEmpCCGrp2 AS 'Employee Division',
ActEmpFTE AS 'Employee FTE',
ActEmpMgr AS 'Employee Manager',
ActEmpPos AS 'Employee Position',
ActEMpTaskName AS 'Employee Task',
(CASE WHEN ActFromType = 'Expense Report' THEN ActMoneyValue
WHEN ActFromType = 'Purchase Request' THEN ActMoneyValue
ELSE 0 END) AS 'Expenses' ,
ActFromType AS 'Form Type',
ActGroups AS 'Groups',
(CASE WHEN ActARClosed = 1 THEN ActBillTotal ELSE 0 END) AS 'Income',
ActInvoiceNum AS 'Invoice Number',
(CASE WHEN ActAPClosed = 1 Then 'Paid' Else 'Not Paid' END) AS 'Paid Status',
ActPrjCCName AS 'Project Cost Center',
ActPrjCCCode AS 'Project CC Code',
ActPrjCode AS 'Project Code',
ActPrjCCGrp3 AS 'Project Department',
ActPrjSource as 'Project Source',
ActPrjIndustry AS 'Project System',
ActPrjMgr AS 'Project Manager',
ActPrjName AS 'Project Name',
ActPrjPhase AS 'Project Phase',
ActPrjSponsor AS 'Project Sponsor',
ActPrjType AS 'Project Type',
ActTaskPhase AS 'Task Phase',
(CASE WHEN ActFromType = 'Time Card' THEN ActTimeValue ELSE 0 END) AS 'Time',
ActWeekEndDate AS 'Week Ending Date',
ActPersFlowTxt1 AS 'Person Custom Text 1',
ActPersFlowTxt2 AS 'Person Custom Text 2',
ActPersFlowTxt3 AS 'Person Custom Text 3',
ActProjFlowTxt1 AS 'Project Custom Text 1',
ActProjFlowTxt2 AS 'Project Custom Text 2',
ActProjFlowTxt3 AS 'Project Custom Text 3',
ActHeaderCustom1 as 'Header Custom Text 1',
ActHeaderCustom2 as 'Header Custom Text 2',
ActHeaderCustom3 as 'Header Custom Text 3',
ActHeaderCustom4 as 'Header Custom Text 4',
ActDetailCustom1 as 'Detail Custom Text 1',
ActDetailCustom2 as 'Detail Custom Text 2',
ActDetailCustom3 as 'Detail Custom Text 3',
ActDetailCustom4 as 'Detail Custom Text 4'
FROM ACTIVITIES
WHERE ActivityDate BETWEEN @StartDate AND @EndDate
AND (ISNULL(ActPrjName,'') LIKE @PrjName )
AND (ISNULL(ActEMpTaskName,'') LIKE @TaskName )
AND (ISNULL(ActEmpName,'') LIKE @EmpName )
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
*************
The goal is to add another table called EMPLOYEES with fields E1 and E2 as part of the returned data set. I need a join statement but I can't figure out the syntax that will actually pull the data. Any help would be appreciated. Thanks.
SQL Stored Procedure Join With Parameters
Oct 31, 2006I was just curious if there was anyway possible to accomplish something simliar to:
SELECT * FROM [dbo].[PRODUCT]
JOIN [dbo].[GetRegionProductPrice](@RegionClassID,[dbo].[PRODUCT].[ID]) Pricing ON Pricing.[ProductID] = [Product].[ID]
WHERE [CategoryID] = @ProductNavigationID
Basically what i am trying to do is Select all my products and obtain a product price for reach, which uses a complex stored procedure that obtains a price for a single product. I understand that i could create a cursor to go through it, but i was just curious if there was a better method... Thanks for your time..
-Devin
Stored Procedure - How To Make View Name Dynamic
May 8, 2012Basically, I'm working on a stored procedure which will retrieve data based on study parameter passed. The datasource is 'Views'. The name of the view is same for every study except that there is corresponding study name included. For example the views names are something like this for study abc 'v_abc_form' and for study def 'v_def_form'.
Below is the select statement I'm trying to use by declaring @study variable but not able to succeed. I'm not sure how to make the table name dynamic.
Select C1, C2, C3
From v_@study_form