I am looking to populate a Schedule table with information from two
other tables. I am able to populate it row by row, but I have created
tables that should provide all necessary information for me to be
able
to automatically populate a "generic" schedule for a few weeks or
more
at a time.
The schedule table contains:
(pk) schedule_id, start_datetime, end_datetime, shift_employee,
shift_position
A DaysOff table contains:
(pk) emp_id, dayoff_1, dayoff_2 <-- the days off are entered in day
of
week (1-7) form
A CalendarDays table contains:
(pk) date, calendar_dow <-- dow contains the day of week number (as
above) for each day until 2010.
My main question is how to put all of this information together and
have SQL populate the rows with data based on days off. Any
suggestions?
I am looking to populate a Schedule table with information from twoother tables. I am able to populate it row by row, but I have createdtables that should provide all necessary information for me to beableto automatically populate a "generic" schedule for a few weeks ormoreat a time.The schedule table contains:(pk) schedule_id, start_datetime, end_datetime, shift_employee,shift_positionA DaysOff table contains:(pk) emp_id, dayoff_1, dayoff_2 <-- the days off are entered in dayofweek (1-7) formA CalendarDays table contains:(pk) date, calendar_dow <-- dow contains the day of week number (asabove) for each day until 2010.My main question is how to put all of this information together andhave SQL populate the rows with data based on days off, for a fewweeks in advance. Anysuggestions?
Currently I have the following stored procedure which simply adds a new row in my SQL Express 2005. What I want is that -1). before inserting the record find out the new ID (primary key) value. (ID is automatically a sequential integer generated by SQL Server)2). and set COMPANY_ID = (new) ID Any thoughts? Thanks ALTER PROCEDURE usp_tbl_Company_Insert @Company_ID int, @Name varchar(200), AS<FIND THE NEW ID of the new row in the database> @Company_ID = (new ID) INSERT INTO tbl_Company (Company_ID, Name,)VALUES (@Company_ID, @Name)
I am working with a large application and am trying to track down a bug. I believe an error that occurs in the stored procedure isbubbling back up to the application and is causing the application not to run. Don't ask why, but we do not have some of the sourcecode that was used to build the application, so I am not able to trace into the code. So basically I want to examine the stored procedure. If I run the stored procedure through Query Analyzer, I get the following error message: Msg 2758, Level 16, State 1, Procedure GetPortalSettings, Line 74RAISERROR could not locate entry for error 60002 in sysmessages. (1 row(s) affected) (1 row(s) affected) I don't know if the error message is sufficient enough to cause the application from not running? Does anyone know? If the RAISERROR occursmdiway through the stored procedure, does the stored procedure terminate execution? Also, Is there a way to trace into a stored procedure through Query Analyzer? -------------------------------------------As a side note, below is a small portion of my stored proc where the error is being raised: SELECT @PortalPermissionValue = isnull(max(PermissionValue),0)FROM Permission, PermissionType, #GroupsWHERE Permission.ResourceId = @PortalIdAND Permission.PartyId = #Groups.PartyIdAND Permission.PermissionTypeId = PermissionType.PermissionTypeId IF @PortalPermissionValue = 0BEGIN RAISERROR (60002, 16, 1) return -3END
Need some suggestions on what to check or look for.My stored proc is not finding the table in the sql db. This is the error...Invalid object name 'tblServRec'I use the same function to pass in an arraylist and the sp name for another sp and that one works ok.The sp is the same as another one except for the table name and that one works ok.The sp works fine on my local machine. It finds the table ok. But when I try it on the server, it doesn't work.I have checked permissions, and they are the same for all tables, even the one that the other sp works ok on the server.Here is the sp if that will help.....CREATE PROCEDURE dbo.deleteServRec @fldNum INT ASBEGIN DECLARE @errCode INT DELETE FROM tblServRec WHERE fldNum = @fldNum SET @errCode = 0 RETURN @errCode HANDLE_APPERR: SET @errCode = 1 RETURN @errCodeENDGOThanks all,Zath
I take the information below in query analyzer and everything runs fine-returns 48 rows. I try to run it as a stored proc and I get no records. If I put a simple select statement into the stored proc, it runs, so it's not permissions. Can anyone help me with why this won't execute as a stored procedure? Articles seem to indicate you can do this with temp tables in a stored procedure. Thanks
declare @style as int, @disc as int, @key as varChar(500), @sdate as varChar(15), @edate as varChar(15), @ld as varChar(15)
set @style=0 set @disc=0 set @sdate='3/1/2006' set @ld='2'
insert into #ListAll SELECT top 100 percent wid, parentID, 0 as myFlag FROM myTable WHERE (@style=0 or styleID=@style) and (@edate is null or start_date < @edate) and ((start_date is null) or (datediff(day,start_date,@sdate) <1)) and (@ld='9' or charIndex(convert(varchar(1),datepart(dw,start_dat e)),@ld)>0) and wid in (select wid from myTable2 where (@disc=0 or discID=@disc)) and wid in (select wid from myTable where @key is null or ([title] like '%' + @key + '%' or [keywords] like '%' + @key + '%'))
update #ListAll set myFlag=1 where parentID<>0 insert into #ListAll select w.wid, w.parentID, 0 as myFlag from myTable w right join #ListAll on #ListAll.parentID=w.wid where #ListAll.parentID<>0 delete #ListAll where myFlag=1
SELECT top 100 percent srt, w.WID, w.parentID, w.[title], w.start_date, w.end_date, w.cancelled, w.url, styleID, w.[keywords], w.onlineID, w.httplocation, datepart(dw,w.start_date) as lddate FROM myTable w right join #ListAll on #ListAll.wid=w.wid ORDER BY srt, start_date, [title]
Recently someone told me that I could use a Parameter in a Stored Proc as a text placeholder in the SQL Statement. I needed to update a table by looping thru a set of source tables. I thought NOW IS MY TIME to try using a parameter as a table name. Check the following Stored Proc
CREATE PROCEDURE [dbo].[sp_Update] @DistributorID int, @TableName varchar(50) AS UPDATE C SET C.UnitCost = T.[Price] FROM (tbl_Catalog C INNER JOIN @TableName T ON C.Code = T.Code) GO
NEEDLESS TO SAY this didn't work. In reviewing my references this seems to be a no no.
Is it possible to use a parameter as a table name? OR is there another way to do this?
We have modified a column name for a table. Now we need to find out all the database objects (stored procedures, functions, views) which access this table so that we can modify them. This is a very big database and its not easy to open the code for each object and check if its access that table. To add to the complexity, its not just one table and column but a number of tables have been modified.
Hello,Does anyone know of a way to loop thru a SQL table using code in a storedprocedure?I need to go thru each record in a small table and build a string usingvalues from the fields associated with a part number, and I can't find anyway to process each record individually. The string needs to be initializedwith the data associated with the 1st record's part number, and I need tobuild the string until a new part number is incurred. Once a new part numberis found in the table, the string is written to a different table and resetfor this next part number in the table. Need to repeat until all records inthe table have been processed.I use ADO in access 2000 to work thru local recordsets, I just can't findanyway to do this in a stored SQL procedure.Thanks for any suggestions, Eric.
i'm trying to create a stored procedure that takes 2 input parameters (taken from a querystring): a table name, and a number that needs to be checked whether it exists within that table. yes, i have different tables, one for each forum, so they will have the same structures -- i'm figuring this approach will result in faster page-load times if the tables have less in them. if everyone here tells me that having one table for all the forums will work fine, i'll just go that route, but if not, here's my procedure:
Create Procedure VerifySubjectNum (@forum_ID VARCHAR(10), @subject_num INT) As If Exists (SELECT subject_num FROM @forum_ID WHERE subject_num = @subject_num) Return 1 Else Return 0
when i try to create this, i get an error saying @forum_ID must be declared... why doesn't it work within the select for the EXISTS?
Hi folks, I have a procedure that pefroms some action and creates the outputs to a temporary table #mytable. I want to call this procedure and take the results from #mytable within the procedure. Can i. If i call #mytable after executing the procedure; won't work. Means that the table gets dropped and doesn't prolong for the session?
I know there maybe something similar of what im asking for but i just cant find it.
I have 3 Stored procedure.
SPA - create a temporary table "sp_getListOfChildren" SPB - insert the data into the temp table "sp_InsertCategoriesFound" SPC - display the list of categories i found "sp_ListingAvailableCategories"
process: SPA call SPB and SPC call SPA
my problem is in the SPC. it seems that the table doesnt exist anymore when i do a select but in the message tab of my sql analyser i can see that the table have some data before executing that store proc..
Invalid object name '#TblTempCat'. for my SPC !! ??? why.. how do i detect a temp table in diffirent stored procedure per user and as to be temp table.. for multiple access.. "WEB"
============MY "SPC" CODE============= alter PROCEDURE sp_ListingAvailableCategories @CurrentCategoryID AS uniqueidentifier AS
exec sp_getListOfChildren @CurrentCategoryID
select * from #TblTempCat
select * from TblCategories where CatID not in (select CatID from #TblTempCat) and CatId <> @CurrentCategoryID
Is there a way to delete records from table passing parameter as tablename? I won't to delete all records from a table dependent on table selected. i'm trying to do this with stored procedure...Table to delete depends on the checkbox selected.
Current code(works) Public Function DelAll() MZKDB = MZKHRFin If sloption = "L" Then sqlConn.ConnectionString = "Server=" & MZKSrv & ";Initial Catalog=" & MZKDB & ";Integrated Security=SSPI;" ElseIf sloption = "S" Then sqlConn.ConnectionString = "Server=" & MZKSrv & ";User id=sa;Password=" & MZKPswd & "; Initial Catalog=" & MZKDB & ";" End If sqlConn.Open() sqlTrans = sqlConn.BeginTransaction() sqlCmd.Connection = sqlConn sqlCmd.Transaction = sqlTrans Try sqlCmd.CommandText = sqlStr sqlCmd.ExecuteNonQuery() sqlTrans.Commit() frm2.txtResult.Text = frm2.txtResult.Text & " " & TableName & " Prior records have been deleted from the database." & vbCrLf SetCursor() Catch e As Exception Try sqlTrans.Rollback() Catch ex As SqlException If Not sqlTrans.Connection Is Nothing Then frm2.txtResult.Text = frm2.txtResult.Text & " " & TableName & " An exception of type " & ex.GetType().ToString() & " was encountered while attempting to roll back the transaction." & vbCrLf SetCursor() End If End Try frm2.txtResult.Text = frm2.txtResult.Text & " " & TableName & " Records were NOT deleted from the database." & vbCrLf SetCursor() Finally sqlConn.Close() End Try ResetID() End Function If cbGenFY.Checked Then sqlStr = "DELETE FROM FIN_FiscalYear" TableName = "dbo.FIN_FiscalYear" DelAll() ClearCounts() timeStepStart = Date.Now GenFY() timeStepStop = Date.Now
DispOneCounts() End If If cbGenFund.Checked Then sqlStr = "DELETE FROM FIN_Fund" TableName = "dbo.FIN_Fund" DelAll() ClearCounts() timeStepStart = Date.Now GenFund() timeStepStop = Date.Now DispOneCounts() End If If cbGenFunc.Checked Then sqlStr = "DELETE FROM FIN_Function" TableName = "dbo.FIN_Function" DelAll() ClearCounts() timeStepStart = Date.Now GenFunc() timeStepStop = Date.Now DispOneCounts()
End If If cbGenObject.Checked Then sqlStr = "DELETE FROM FIN_Object" TableName = "dbo.FIN_Object" DelAll() ClearCounts() timeStepStart = Date.Now GenObject() timeStepStop = Date.Now DispOneCounts() End If If cbGenCenter.Checked Then sqlStr = "DELETE FROM FIN_Center" TableName = "dbo.FIN_Center" DelAll() ClearCounts() timeStepStart = Date.Now GenCenter() timeStepStop = Date.Now DispOneCounts() End If If cbGenProject.Checked Then sqlStr = "DELETE FROM FIN_CodeBook" TableName = "dbo.FIN_CodeBook" DelAll() sqlStr = "DELETE FROM FIN_BudgetAccnt" TableName = "dbo.FIN_BudgetAccnt" DelAll() sqlStr = "DELETE FROM FIN_Budget" TableName = "dbo.FIN_Budget" DelAll() sqlStr = "DELETE FROM FIN_Project" TableName = "dbo.FIN_Project" DelAll() ClearCounts() timeStepStart = Date.Now GenProject() timeStepStop = Date.Now TableName = "dbo.FIN_Project" DispOneCounts() End If If cbGenProgram.Checked Then sqlStr = "DELETE FROM FIN_Program" TableName = "dbo.FIN_Program" DelAll() ClearCounts() timeStepStart = Date.Now GenProgram() timeStepStop = Date.Now DispOneCounts() End If If cbGenGL.Checked Then sqlStr = "DELETE FROM FIN_gl" TableName = "FIN_gl" DelAll() ClearCounts() timeStepStart = Date.Now GenGL() timeStepStop = Date.Now DispOneCounts() End If If cbGenRevenue.Checked Then sqlStr = "DELETE FROM FIN_Revenue" TableName = "FIN_Revenue" DelAll() ClearCounts() timeStepStart = Date.Now GenRevenue() timeStepStop = Date.Now DispOneCounts() End If If cbGenBank.Checked Then sqlStr = "DELETE FROM FIN_VendorBankAccnt" TableName = "dbo.FIN_VendorBankAccnt" DelAll() sqlStr = "DELETE FROM FIN_VendorBank" TableName = "dbo.FIN_VendorBank" DelAll() sqlStr = "DELETE FROM FIN_bankAdd" TableName = "dbo.FIN_bankAdd" DelAll() sqlStr = "DELETE FROM FIN_bankTERMS" TableName = "dbo.FIN_bankTerms" DelAll() sqlStr = "DELETE FROM FIN_bank" TableName = "dbo.FIN_bank" DelAll() ClearCounts() timeStepStart = Date.Now GenBank() timeStepStop = Date.Now TableName2 = "dbo.FIN_bankTERMS" TableName3 = "dbo.FIN_BankAdd" TableName4 = "dbo.FIN_VendorBank" TableName5 = "dbo.FIN_VendorBankAccnt" DispTwoCounts() End If If cbFinAP.Checked Then sqlStr = "DELETE FROM FIN_Period" TableName = "FIN_Period" DelAll() ClearCounts() timeStepStart = Date.Now GenPeriod() timeStepStop = Date.Now DispOneCounts() End If
Hi I wanted to use the table variable in Stored proc , for that i have create the table variable in the main SP which will be used by again called sp(child SPs) now when i am trying to use the same table variable in the child SP, at the time of compliation it is showing error
Msg 1087, Level 15, State 2, Procedure fwd_price_cons, Line 149 Must declare the table variable "@tmp_get_imu_retn".
Can any body give me the idea how to complile the child SP with the same table variable used in the main SP.
Can anyone helpCREATE PROCEDURE PagedResults_New (@startRowIndex int, @maximumRows int ) AS
--Create a table variable DECLARE @TempItems TABLE (ID int IDENTITY, ShortListId int ) -- Insert the rows from tblItems into the temp. table INSERT INTO @TempItems (ShortListId) SELECT Id FROM shortlist SWHERE Publish = 'True' order by date DESC
-- Now, return the set of paged records SELECT S.*, C.CategoryTitleFROM @TempItems t INNER JOIN shortList S ON t.ShortListId = S.Id WHERE ID BETWEEN @startRowIndex AND (@startRowIndex + @maximumRows) - 1 GO
I have a stored proc that inserts into a table variable (@ReturnTable) and then ends with "select * from @ReturnTable."
It executes as expected in Query Analyzer but when I call it from an ADO connection the recordset returned is closed. All the documentation that I have found suggests that table variables can be used this way. Am I doing somthing wrong?
Hi,I am using data from multiple databases and/or queries. It would greatlysimplify and speed things up if I could use CONTAINS in processing theresults. However, "CONTAINS" requires the data to be indexed. Due to theamount of processing, I think it would be faster even if I had to re-indexevery time.For example, I would like to do something like this (simplified toillustrate the desired functionality... This should show all of the wordsfrom one table that are not contained in their current or inflectional formswithin another table):SELECT W1.ContentFROM(SELECT Word AS ContentFROM MyTable) W1LEFT OUTER JOIN(SELECT Phrase AS ContentFROM MyOtherTable) W2ON W2.Content CONTAINS(INFLECTIONAL, W1.Content)WHERE W2.Content IS NULLCan the results of a procedure be indexed? If not, can I drop the resultsinto a new table and trigger an automatic index of it, pausing the procedureuntil the indexing is done?Or, it there another way?Thanks!
I have a "source" table that is being populated by a DTS bulk importof a text file. I need to scrub the source table after the importstep by running appropriate stored proc(s) to copy the source data to2 normalized tables. The problem is that table "Companies" needs tobe populated first in order to generate the Identity ID and then usethat as the foreign key in the other table.Here is the DDL:CREATE TABLE [dbo].[OriginalList] ([FirstName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[LastName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[Company] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[Addr1] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[City] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[State] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[Zip] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[Phone] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[Companies] ([ID] [int] IDENTITY (1, 1) NOT NULL ,[Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[CompanyLocations] ([ID] [int] IDENTITY (1, 1) NOT NULL ,[CompanyID] [int] NOT NULL ,[Addr1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[City] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[State] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[Zip] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[Phone] [varchar] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]GOThis is the stored proc I have at this time that does NOT work. Ituses the last Company insert for all the CompanyLocations which is notcorrect.CREATE PROCEDURE DataScrubSP ASBegin Transactioninsert Companies (Name) select Company from OriginalListIF @@Error <> 0GOTO ErrorHandlerdeclare @COID intselect @COID=@@identityinsert CompanyLocations (CompanyID, Addr1, City, State, Zip) select@COID, Addr1, City, State, Zip from OriginalListIF @@Error <> 0GOTO ErrorHandlerCOMMIT TRANSACTIONErrorHandler:IF @@TRANCOUNT > 0ROLLBACK TRANSACTIONRETURNGOThanks for any help.Alex.
I need to write a storedproc that receives the name of a table (as a string) and inside the stored proc uses select count(*) from <tablename>. The problem is the passed in tablename is a string so it can't be used in the select statement. Any ideas how I can do what I want?
Hi, Summary: When my stored procedure uses temporary tables then the TableAdapter won't be able to work out the field names and so won't work. I get an error in the TableAdapter configure wizard saying: Invalid object name '#TempTable'. I'm not doing anything unusual so this must be a common problem. Let me explain: I'm using Visual Studio 2005 and SQL Server 2000. Detail: I've written a new stored procedure (SP) that uses a temporary table in calculating the resulting results set (several fields with several rows). I recon the temporary table bit is significant. I've created a new DataSet in VS2005 and dragged the stored proc onto the DataSet design surface. I right click on the TableAdapter and enter the 'configure'. The problem is that the wizard doesn't think any fields are being returned by the SP. If I try and do it another way I get the same problem: Right click on DataSet and add new TableAdapter (same thing happens, it won't recognise that there are fields being returned from the SP). FYI: If I do it for an SP that doesn't use any temporary tables it all works like a dream (problem is that I need to use temporary tables as its complex ). Thanks for any advise
Hi, I need to list out all the stored proc for all the DB in entire server. My inpute parameter will be table name and column1/column2/column3...etc. Please give me your input for my request. Thank, Ravi
I have access to a stored procedure that was written previously for a process that uses the output from the stored procedure to provide input to a BCP operation in a bat file that builds a flat text file for use in a different system.
To continue with the set up, here is the stored procedure in question: CREATE PROCEDURE [dbo].[HE_GetStks] AS
select top 15 Rating, rank, coname, PriceClose, pricechg, DailyVol, symbol from
(selectf.rating, f.rank, s.coname, cast ( f.priceclose as decimal(10,2)) as PriceClose, cast ( f.pricechg as decimal(10,2)) as pricechg, f.DailyVol, f.symbol from dailydata f, snames s where f.tendcash = 0 and f.status = 1 and f.typ = 1 and f.osid = s.osid) tt order by rating desc, rank desc
GO
The code in the calling bat file is: REM ************************* REM BCP .WRK FILE REM ************************* bcp "exec dailydb.[dbo].[HE_GetStks]" queryout "d:TABLESINPUTHE_GetStks.WRK" -S(local) -c -U<uname> -P<upass>
This works just peachy in the process for which it was designed, but I need to use the same stored procedure to grab the same data in order to store it in a historical table in the database. I know I could duplicate the code in a separate stored procedure that does the inserting into my database table, but I would like to avoid that and use this stored procedure in case the select statement is changed at some point in the future.
Am I missing something obvious in how to utilize this stored procedure from inside an insert statement in order to use the data it outputs? I know I cannot use an EXECUTE HE_GetStks as a subquery in my insert statement, but that is, in essence, what I am trying to accomplish.
I just wanted to bounce the issue of y'all before I go to The Boss and ask him to change the procedure to SET the data into a database table directly (change the select in the proc to an INSERT to a local table) then have the external BAT file use a GET procedure that just does the select from the local table. This is the method most of our similar jobs use when faced with this type of "intercept" task.
I have a situation where I need to call a stored procedure once per each row of table (with some of the columns of each row was its parameters). I was wondering how I can do this without having to use cursors.
Here are my simulated procs...
Main Stored Procedure: This will be called once per each row of some table.
-- All this proc does is, prints out the list of parameters that are passed to it.
CREATE PROCEDURE dbo.MyMainStoredProc ( @IDINT, @NameVARCHAR (200), @SessionIDINT ) AS BEGIN
[Code] ....
Here is a sample call to the out proc...
EXEC dbo.MyOuterStoredProc @SessionID = 123
In my code above for "MyOuterStoredProc", I managed to avoid using cursors and was able to frame a string that contains myltiple EXEC statements. At the end of the proc, I am using sp_executesql to run this string (of multipl sp calls). However, it has a limitation in terms of string length for NVARCHAR. Besides, I am not very sure if this is an efficient way...just managed to hack something to make it work.
All i want to do is add a parameter for Territory_code how do i do that, in a stored procedure that uses a memory table. the yellow shows the two places i thought i should be able to include it. Any help will be greatly appreciated!! thanks! I couldnt post the whole code, so i deleted alot , just need to know where to put the param!
Code Snippet
USE [RC_STAT] GO
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO
ALTER PROCEDURE [dbo].[PROC_RPT_Breeder_Sales] AS (@Territory varchar(20) BEGIN SET NOCOUNT ON;
Declare @ReportingTbl TABLE ( Source_Id int, Territory_Code varchar(20), Territory_Description varchar (30), Sort_Id int, Column_Text varchar(20), Subbrand_Key int, SubBrand_Description varchar (30), Period_1 Decimal(18,0), Period_2 Decimal(18,0), Period_3 Decimal(18,0), Period_4 Decimal(18,0), Period_5 Decimal(18,0), Period_6 Decimal(18,0), Period_7 Decimal(18,0), Period_8 Decimal(18,0), Period_9 Decimal(18,0), Period_10 Decimal(18,0), Period_11 Decimal(18,0), Period_12 Decimal(18,0), Period_13 Decimal(18,0), YTD Decimal (18,0), Total_Amount decimal (18,0)) ------------------------------------------------------------------------------------------------------------------------------------------ ----Distributor Load Section ------------------------------------------------------------------------------------------------------------------------------------------ INSERT INTO @ReportingTbl (Source_Id, Territory_Code, Territory_Description, Sort_Id, Column_Text, Subbrand_Key, SubBrand_Description, Period_1, Period_2, Period_3, Period_4, Period_5, Period_6, Period_7, Period_8, Period_9, Period_10, Period_11, Period_12, Period_13, YTD, Total_Amount) SELECT 1 as Source_Id, Tbv_Customer.Breeder_Territory_Code, RC_DWDB_INSTANCE_1.dbo.Qry_Sales_Group_Dimension.Territory_Name, CASE WHEN cusSales.Customer_Sales_Summary_Year = YEAR(GETDATE()) THEN 2 ELSE 1 END as Sort_Id, cusSales.Customer_Sales_Summary_Year Column_Text, cusSales.Sub_Brand_Id , Qry_Report_Level_Brand.Sub_Brand_Description, SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 1 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END)AS Period_1, SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 2 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_2, SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 3 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_3, SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 4 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_4, SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 5 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_5, SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 6 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_6, SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 7 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_7, SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 8 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_8, SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 9 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_9, SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 10 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END)AS Period_10, SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 11 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_11, SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 12 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_12, SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 13 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_13, 0 as YTD, SUM (cusSales.Customer_Sales_Summary_Amount) As Total FROM RC_DWDB_INSTANCE_1.dbo.Qry_Sales_Group_Dimension INNER JOIN Tbv_Customer ON RC_DWDB_INSTANCE_1.dbo.Qry_Sales_Group_Dimension.Territory_Code = Tbv_Customer.Breeder_Territory_Code INNER JOIN RC_DWDB_INSTANCE_1.dbo.Tbl_Customer_Sales_Summary_Fiscal AS cusSales ON Tbv_Customer.Customer_Code = cusSales.Customer_Code INNER JOIN Qry_Report_Level_Brand ON cusSales.Sub_Brand_Id = Qry_Report_Level_Brand.Sub_Brand_Id WHERE cusSales.Bill_Customer_Code NOT IN ('RNPROC','RNPROF') AND cusSales.Sub_Brand_Id <> 65 AND cusSales.Report_Level_Id = 85 AND cusSales.Report_Level_Id = 85 AND cusSales.Consolidated_Sales_Tables_Id = 6 AND cusSales.Customer_Sales_Summary_Year >= YEAR(GETDATE()) - 1 GROUP BY Tbv_Customer.Breeder_Territory_Code, RC_DWDB_INSTANCE_1.dbo.Qry_Sales_Group_Dimension.Territory_Name, cusSales.Customer_Sales_Summary_Year, cusSales.Sub_Brand_Id , Qry_Report_Level_Brand.Sub_Brand_Description
SELECT Source_Id, Territory_Code, Territory_Description, Sort_Id, Column_Text, Subbrand_Key, SubBrand_Description, Period_1, Period_2, Period_3, Period_4, Period_5, Period_6, Period_7, Period_8, Period_9, Period_10, Period_11, Period_12, Period_13, YTD, Total_Amount FROM @ReportingTbl -- ORDER BY Source_Id, Sort_Id where Territory_Code=@Territory END
I am writing a SQL 2000 stored procedure which uses an €˜EXEC @sqlString€™ statement. The @sqlString is generated at runtime. I want to give as few permissions as possible and currently allow users to access the database tables using only the stored procedures provided. However, with €˜Exec€™ I discover that I need to grant permissions on the actual tables to the users or groups. I would like to avoid this. I would also prefer not having to maintain a separate user with table level permissions and hardcoding the stored procedure with these details. Is there anyway for me to dynamically generate the required SQL statement within my stored procedure and let SQL know that this stored procedure is allowed to select whatever tables it wants to without having to define permissions on the tables?
I'm having problems with a stored procedure, that i'm hoping someone can help me with.
I have a table with 2 columns - Username (varchar), LastAllocation (datetime)
The Username column will always have values, LastAllocation may have NULL values. Example
Username | LastAllocation ------------------------ Greg | 02 October 2005 15:30 John | 02 October 2005 18:00 Mike | <NULL>
My stored procedure needs to pull back a user name with the following criteria:
If any <NULL> dates send username of first person where date is null, sorted alphabetically, otherwise send username of person with earliest date from LastAllocation
Then update the LastAllocation column with GETDate() for that username.
This SP will be called repeatedly, so all users will eventually have a date, then will be cycled through from earliest date. I wrote an SP to do this, but it seems to be killing my server - the sp works, but I then can't view the values in the table in Enterprise Manager. SP is below - can anyone see what could be causing the problem, or have a better soln? Thanks Greg ------------------------------------------------------------------------------ ------------------------------------------------------------------------------ CREATE PROCEDURE STP_GetNextSalesPerson AS DECLARE @NextSalesPerson varchar(100)
BEGIN TRAN
IF (SELECT COUNT(*) FROM REF_SalesTeam WHERE LeadLastAllocated IS NULL) > 0 BEGIN SELECT TOP 1 @NextSalesPerson = eUserName FROM REF_SalesTeam WHERE LeadLastAllocated IS NULL ORDER BY eUserName ASC END ELSE BEGIN SELECT TOP 1 @NextSalesPerson = eUserName FROM REF_SalesTeam ORDER BY LeadLastAllocated ASC END
SELECT @NextSalesPerson UPDATE REF_SalesTeam SET LeadLastAllocated = GETDATE() WHERE eUserName = @NextSalesPerson
