Can any one please tell me where i am going wrong..
Code Snippet
create proc SP_PercentageRMU
as
SELECT cast (sum([Usage Qty]) as [decimal] (28,8))as 'TUsageQty'
,RAW_MATERIAL
into #TZMelt_Pound
FROM [LatrobeOCT].[dbo].[ZMelt_Pound]
group by RAW_MATERIAL
GO
select [Usage Qty],(case when r.raw_material = z.raw_material
then cast ((r.[usage QTY] / z.TUsageQty) as decimal (28,8))
else 0
end) as '%UsageQty'
,r.[PRODL]
,r.RAW_MATERIAL
,r.[GRADECODE]
into #PZMelt_Pound
FROM [LatrobeOCT].[dbo].[ZMelt_Pound]r
inner join #TZMelt_Pound z on r.raw_material = z.raw_material
I'm writing a search engine and I want to make a search and then, after I've recieved the results, put them into some sort of temporary(in mememory) table so that I can do another query on that temporary table.
I saw something about temp tables (in T-SQL) in the help guide, but I still want to know how to do a query on a regular table and then store those results in a temporary table to perform a query on that. Could anyone show me some example or something?
DECLARE @AssessBenefitID INT DECLARE @ActualQuantity INT DECLARE @ExpectedQuantity INT DECLARE @ActualQuality VARCHAR(2000) DECLARE @ExpectedQuality VARCHAR(2000) DECLARE @AssessFlag CHAR DECLARE CUR_BENEFIT CURSOR FOR SELECT AssessBenefitID,ProjectBenefitID,AssessFlag FROM PMPT_AssessBenefit WHERE ProjectBenefitID=@ProjectBenefitID
OPEN CUR_BENEFIT
FETCH NEXT FROM CUR_BENEFIT INTO @AssessBenefitID,@ProjectBenefitID,@AssessFlag
WHILE @@FETCH_STATUS = 0 BEGIN
SELECT @ActualQuantity=Quantity FROM dbo.PMPT_AssessBenefit WHERE AssessFlag='A' AND AssessBenefitID=@AssessBenefitID AND ProjectBenefitID=@ProjectBenefitID SELECT @ExpectedQuantity=Quantity FROM dbo.PMPT_AssessBenefit WHERE AssessFlag='E' AND AssessBenefitID=@AssessBenefitID AND ProjectBenefitID=@ProjectBenefitID SELECT @ActualQuality=Quality FROM dbo.PMPT_AssessBenefit WHERE AssessFlag='A' AND AssessBenefitID=@AssessBenefitID AND ProjectBenefitID=@ProjectBenefitID SELECT @ExpectedQuality=Quality FROM dbo.PMPT_AssessBenefit WHERE AssessFlag='E' AND AssessBenefitID=@AssessBenefitID AND ProjectBenefitID=@ProjectBenefitID
I've a requirement to store the output of the stored procedure into temp. tables/ table varibles. I've 4 select statements as my output of the stored procedure. How do I store the results of all the 4 select stmnts into 4 different temp tables.
Simplified SP is as...
Create procedure usp_test as begin
select c1,c2 from table1 select c3,4 from table2 select c9,c8 from table3 select c5,c7 from Table4 end
I'm expecting something like this...
declare @table1 table (c1, c2) insert into @table1 Exec <Sp_Name>
select * from @table1
I know the above stmnt works, if my SP has only 1 select stmnt as output. Please help me to acheive this for multiple select statements.
i am inserting something into the temp table even without creating it before. But this does not give any compilation error. Only when I want to execute the stored procedure I get the error message that there is an invalid temp table. Should this not result in a compilation error rather during the execution time.?
--create the procedure and insert into the temp table without creating it. --no compilation error. CREATE PROC testTemp AS BEGIN INSERT INTO #tmp(dt) SELECT GETDATE() END
only on calling the proc does this give an execution error
I am a starter of vb.net and trying to build a web application. Do anyone know how to create a temp table to store data from database? I need to extract data from 3 different tables (Profile,Family,Quali). Therefore, i need to use 3 different queries to extract from the 3 tables and then store it in the temp table. Then, i need to output the data from temp table to the screen. Do anyone can help me?
I wanted to insert the result-set of a Exec(@sqlcommand) into a temp table. I can do that by using:
Insert into #temp Exec(@sqlcommand)
For this to accomplish we need to define the table structure in advance. But am preparing a dynamic-sql command and storing that in variable @sqlcommand and the output changes for each query execution. So my question is how to insert/capture the result-set of Exec(@sqlcommand) into a temp table when we don't know the table structure.
We are migrating a SQL 6.5 application with 1900 stored procedures that use 100's of temp tables to SQL 2000.
A problem we have encountered was that we started out getting an "invalid column" errors on certain procedures. Investigation determined that the error was being generated in a nested procedure. The table that caused the error ended up being a temp table that was created using "select into". The following select statement from that temp table gave the invalid column error.
First thinking it was the "Select Into" we then discovered that the outer most procedure had created a temp table of the same name prior to executing the lower level procedure. After the select into, the next statement was a SELECT that went against what it thought was the inner temp table. However, it grabbed the outermost temp table and then couldn't find the appropriate columns and generated the error.
The solution, of course, was to rename the inner most temp table. We also remove the "select into" in the procedure by explicitly creating the temp table.
We tried creating some test procedures to attempt to reproduce this scenario without complete success.
Our test created 3 procedures (sp1 calling sp2 calling sp3) to mimic the current scenario. Sp1 created a temp table and executed sp2, which executed sp3. Sp3 created another temp table using the same name as the one created in sp1.
If we create all three procedures at the same time, it doesn't matter if we change the order in which they are created or whether we create the inner temp table explicitly or with a "select into", SQL Query Analyzer won't let us create the procedure because it finds that the temp table has been declared twice. If we create the procedures separately however, they compile and allow sp3 to create a temp table by the same name as sp1. After creating the procedures independently, they runs properly in all cases with everything in proper scope and no problems.
Admittedly, this is bad coding to start with, but what is happening with the scope of the temp tables within the stored procedures?
I have 3 jobs each consists of set of stored procedures.The stored procedures have lots of temp tables. And all the jobs run at the same time.
job1:
EXEc sp1 EXEC sp2 EXEC sp3
Job2
EXEC abc1 EXEC abc2 EXEC abc3 EXEC abc4 EXEC abc5
Job3
EXEC xyz1 EXEC xyz2 EXEC xyz3 EXEC xyz4
But the issue is that the stored procedures in the job1 has temp tables with the same name as stored procedures in the job 2 have.
Eg:
procedure abc1 and procedure sp2 have the temp table #temp1. procedure abc4 and procedure xyz2 have the temp table #temp3.
Like this i have some more common temp tables. So my question is that can I use the temp tables like that.If so does it degrade the perforamnce of the sps.
If you use a stored procedure that references one or more temp tables as data dource for a report, you get an error saying that the temp tables cannot be found when you click on the Layout tab. This happens even if you have executed the query in the Data tab before going to the Layout tab. The work around is to simply ignore the error but it is a distraction for the user. Is this a known big that is going to be fixed in a future release?
All, I have beating my head over this and have asked questions to clear this up in small bits.
In trying to get all of my business processes in stored procedures versus in the ASP pages. The ASP page is as follows:
--Normal ASP header <% myquery = "Exec MainSysLogQuery '6ED178C0-0202-4F8D-9C04-4C7B83A14190'
'Set Conn = Server.CreateObject("ADODB.Connection")'Set Connection Variable Set SysMainQuery=Server.CreateObject("ADODB.Recordset" ) SysMainQuery.open "Select * from tbl_Date", strDSNPath
howmanyfields=SysMainQuery.fields.count -1
response.write "<table border='1'><tr>"
'Put Headings On The Table of Field Names FOR i=0 to howmanyfields response.write "<td NOWRAP><Font Size=2><b>" & SysMainQuery(i).name & "</b></td>" NEXT response.write "</tr>"
' Now loop through the data DO WHILE NOT SysMainQuery.eof response.write "<tr>" FOR i = 0 to howmanyfields fieldvalue=SysMainQuery(i) If isnull(fieldvalue) THEN fieldvalue="n/a" END IF If trim(fieldvalue)="" THEN fieldvalue=" " END IF response.write "<td valign='top' NOWRAP><Font Size=2>" response.write fieldvalue response.write "</td>" next response.write "</tr>" SysMainQuery.movenext 'howmanyrecs=howmanyrecs+1 LOOP response.write "</table></font><p>"
' close, destroy SysMainQuery.close set SysMainQuery=nothing %> </body></html>
The stored procedure takes the input an returns the sql statement:
Alter Procedure MainSysLogQuery @myDates nvarchar(200) = '', As declare @oSql nvarchar(4000) declare @viewtmp nvarchar(3000) declare @querytmp nvarchar(3000) declare @wheretmp nvarchar(3000) declare @ordertmp nvarchar(3000) declare @tmplen int
Set @wheretmp = '' Set @tmplen = 0
if @myDates != '' Set @wheretmp = @wheretmp + 'tbl_All_SysDATA.dateid = ''' + @myDates +''''
If len(@wheretmp)>0 Set @wheretmp = ' WHERE ' + @wheretmp
print @wheretmp Set @viewtmp = 'SELECT *' Set @querytmp = ' FROM tbl_All_SysData' Set @ordertmp = ' ORDER BY LongDate DESC' Set @oSQL = @viewtmp + @querytmp + @wheretmp + @ordertmp PRINT @oSQL Exec(@oSQL) Go
The problem is I keep getting: Error Type: ADODB.Recordset (0x800A0E78) Operation is not allowed when the object is closed.
It returns the recordset when I call it from SQL Query Tool but not in the ADO object. If I use a regular SQL statement it works get but not with a Stored Procedure.... The Errors are on on the recordset.eof, recordset.movenext, etc....
How would you set up a group of developers-application programers in SQL 6.5 to let them have authority so that they all can store, update, delete, & execute each others stored procedures, within a particular database. They are not permitted to modify the table structures within a data base, but I can not seem to let them have authority so that they can work on and execute any of their sp's unless the DBO actually does the sp modifications? They do not want to modify any code by putting the sp owners name in front of the sp name (I don't blame them), otherwise Error 2812 results.
I am developing a complex database-driven application with SQL Server 2000. My database has dozens of stored procedures, and whenever I want to rename a database field, I have to go through my stored procedures, finding where that field is used. This is a laborious and error-prone process, even when I look up depenencies.
Is there an easier way to work stored procedure code – some tool to search/replace the text perhaps?
Hello, can anyone offer any advice on this problem related to store procedures.
The following 2 chunks of SQL illustrate the problem
--1 declare @lsFilt varchar(16) select @lsFilt = 'fil%ter' select * from sysobjects where name like @lsFilt
--2 declare @lsQuery varchar(128) select @lsQuery = 'select * from sysobjects where name like ''fil%ter''' exec (@lsQuery)
When I view the execution plan the cost % breakdown is approx 82%, 18%. The second query does a bookmark lookup and an index seek while the first slow query does a clustered index seek and takes approx 5 times longer to do.
Now my real question is suppose I have an store procedure to run a similar query. Should be writing my SPs along the lines of
create proc SP2Style @psFilter varchar(16) AS declare @lsQuery varchar(128) select @lsQuery = 'select * from sysobjects where name like ''' @psFilter + '''' exec (@lsQuery) GO
instead of
create proc SP1Style @psFilter varchar(16) AS select * from sysobjects where name like @psFilter GO
Is there another way to write similar store procedures without using dynamic query building or the exec but keep the faster execution speed?
i am one of the user of database and in that user i created store procedures.
now i want to create another user and give that user to permission of excute all my proceudres and i also give him privillages such a way that user can also modify my procedures...
I recently set up a new sql 2005 standard edition and planning to mirgrate our production sql 2000 data. but only the tables were migrated from the copy data or import / export task. I cannot find a way to recreate the store procedures and views on the new server without create one view and one store procedure at a time unless all hundreds of views and procedures were rescripted. can anyone help
I think this is a very simple question, however, I don't know the answer. What is the difference between a regular Temp table and a Global Temp table? I need to create a temp table within an sp that all users will use. I want the table recreated each time someone accesses the sp, though, because some of the same info may need to be inserted and I don't want any PK errors.
I am trying to call DB2 stroe procedure from within SQL server 2000 using DTS. I have the IBM odbc driver installed on the server. I have created an ACtiveX script to run in DTS and it fails staing it could not findor load the DB2 store procedure.
Has anyone come across doing this and how they did it?
I do have very old versions of duplicate store procedures on my databases. I know there is no "safe" way to do this using DMVs, so I am planning to combine that with a trace. But I would like to get others opinions about that.
Here's the DMV I am planning to use:
SELECT CASE WHEN database_id = 32767 then 'Resource' ELSE DB_NAME(database_id)END AS DBName ,OBJECT_SCHEMA_NAME(object_id,database_id) AS [SCHEMA_NAME] ,OBJECT_NAME(object_id,database_id)AS [OBJECT_NAME] ,cached_time ,last_execution_time ,execution_count
[Code] ....
I will save that on a local table and run it every 5 min maybe? Or at an interval equal or lower than PLE?
Hi all i need little help regarding stored procedure. i have some job say ".one." that runs daily on sql server agent But i have some holiday schedule as well in database suppose if a holiday come and i want to stop that job on sql server agent on that particular holiday. can anybody help me writing it .. how to do it by calling SQL server agent to stop and start with date in stored procedure. Regards
In a report called ICD_PrivateHospital, I have designed to show output to two table from two store procedures. 1st store proc: usp_RPT_Private and 2nd store proc: usp_RPT_Private2.
I have created both the store procs and it is executing successfully. I also have designed the Crystal Report in Visual Studio 2008.
I have added both the store procs in the crystal report with no error. I only wants to show top 20 records. It is running successfully when I add the fields and parameters from the first store procedure. But when I add the 2nd store procedures fields into the report, duplication occurs for both the results in store proc 1 and store proc 2. How to solve this issue?
I have several developers working on different systems but same database; these developers make Store Procedures and all utilize these SP€™s; at the moment our SP list grows and keeps about 200 + SP's, whereas I expect a lot more to come.
Now I want to organize the SP''s under certain folders; example by the System and underneath by User name so that traceability of the work can be easy as well as organization of the SP's will be achieved.
I cannot find any way how to customize the Store Procedure node; and create folder underneath.
I know the filter option helps for searching the SP's but we also have remote users who are working somewhere away from us; so I want to get their work only by filtering the folder of these user name and also sometimes I need to know who made these SP's so that that developer can fix or alter the procedures.
We may do this using security instead of dbo, the developer who is creating the procedure will carry his name; but this needs tight security and we are in the phase of testing and lot of changes occurs on daily basis which requires little loose security till we pass this phase.
Any Idea or suggestions to make it viewable and accessible and copy the work of the specific users right away; rather than searching his Sp's, with the coordination of the user or with the maintained documentation.
