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?
Howdy!
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
I've created a stored procedure that creates and uses a temporary table with a computed column. I want the computed column to call a user-defined function(UDF) that I've created. Here's an example:
CREATE PROCEDURE [dbo].[usp_Proc]
(
@Date datetime
)
AS
BEGIN
--Drop the temp table if it still exists so reports come out accurate
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[#temp]') AND type in (N'U'))
DROP TABLE #temp;
--Create the temp table for use in this stored procedure
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[#temp]') AND type in (N'U'))
CREATE TABLE #temp (
[ID] INT PRIMARY KEY IDENTITY(1,1),
[Column1] NVARCHAR (30) DEFAULT ('XXXX-XXXXX'),
[Column2] INT DEFAULT (0),
[Column3] INT DEFAULT (0),
[Column4] INT DEFAULT (0),
[Column5] as ([Column2] + [Column3] + [Column4]),
[Column6] as (dbo.FunctionName('5381', [Column1]))
)
--Insert data
INSERT INTO #temp([Column1], [Column2], [Column3], [Column4])
SELECT 'String', 1, 2, 3
--Perform more calculations
<snipped...>
SELECT * FROM #temp
DROP TABLE #temp
END
This is an example of the function:
CREATE FUNCTION [dbo].[FunctionName]
(
-- Add the parameters for the function here
@Type nvarchar(4),
@Quantity int
)
RETURNS Money
AS
BEGIN
RETURN (cast((SELECT ([Value] * cast(@Quantity as int)) FROM tblTable WHERE [ID] = @Type) as Money))
END
The error message I'm getting after I've created both the stored procedure and the UDF is when calling the stored procedure. Here it is:
Msg 4121, Level 16, State 1, Procedure usp_Proc, Line 13
Cannot find either column "dbo" or the user-defined function or aggregate "dbo.FunctionName", or the name is ambiguous.
There's no way the function name is ambiguous. Is it even possible to do what I'm trying to do or am I just calling it the wrong way?
Hosmerica
which is more efficient...which takes less memory...how is the memory allocation done for both the types.
View 1 Replies View RelatedHow do I use table names stored in variables in stored procedures?
Code Snippetif (select count(*) from @tablename) = 0 or (select count(*) from @tablename) = 1000000
I receive the error 'must declare table variable '@tablename''
I've looked into table variables and they are not what I would require to accomplish what is needed.
After browsing through the forums I believe I need to use dynamic sql particuarly involving sp_executesql. However, I am pretty new at sql and do not really understand how to use this and receive an output parameter from it(msdn kind of confuses me too). I am tryin got receive an integer count of the records from a certain table which can change to anything depending on what the user requires.
Code Snippet
if exists(Select * from sysobjects where name = @temptablename)
drop table @temptablename
It does not like the 'drop table @temptablename' part here. This probably wouldn't be an issue if I could get temporary tables to work, however when I use temporary tables i get invalid object '#temptable'.
Heres what the stored procedure does.
I duplicate a table that is going to be modified by using 'select into temptable'
I add the records required using 'Insert into temptable(Columns) Select(Columns)f rom TableA'
then I truncate the original table that is being modified and insert the temporary table into the original.
Heres the actual SQL query that produces the temporary table error.
Code Snippet
Select * into #temptableabcd from TableA
Insert into #temptableabcd(ColumnA, ColumnB,Field_01, Field_02)
SELECT ColumnA, ColumnB, Sum(ABC_01) as 'Field_01', Sum(ABC_02) as 'Field_02',
FROM TableB
where ColumnB = 003860
Group By ColumnA, ColumnB
TRUNCATE TABLE TableA
Insert into TableA(ColumnA, ColumnB,Field_01, Field_02)
Select ColumnA, ColumnB, Sum(Field_01) as 'Field_01', Sum('Field_02) as 'Field_02',
From #temptableabcd
Group by ColumnA, ColumnB
The above coding produces
Msg 208, Level 16, State 0, Line 1
Invalid object name '#temptableabcd'.
Why does this seem to work when I use an actual table? With an actual table the SQL runs smoothly, however that creates the table names as a variable problem from above. Is there certain limitation with temporary tables in stored procedures? How would I get the temporary table to work in this case if possible?
Thanks for the help.
I'm trying to insert the results of a stored procedure call into a temporary table, which is not working. It does work if I use a non-temporary table. Can anyone tell me if this is supported or what I am doing wrong.
Here is an example:
-- DROP PROCEDURE testProc
CREATE PROCEDURE testProc AS
BEGIN
SELECT '1111' as col1, '2222' as col2
END
-- this call will fail with message Invalid object name '#tmpTable'.
INSERT INTO #tmpTable EXEC testProc
--- DROP TABLE testTable
CREATE TABLE testTable (col1 varchar(5), col2 varchar(5))
-- this call will succeed
INSERT INTO testTable EXEC testProc
Hi,I'm adapting access queries to sql server and I have difficulties withthe following pattern :query1 : SELECT * FROM Query2 WHERE A=@param1query 2: SELECT * FROM Table2 WHERE B=@param2The queries are nested, and they both use parameters.In MS Acccess the management of nested queries with parameters is soeasy (implicit declaration of parameters, transmission of parametersfrom main query to nested query)that I don't know what the syntax should be for stored procedures.The corresponding stored procedure would be something likeCREATE TABLE #TempTable (...table definition...)INSERT INTO #TempTable ExecProc spQuery2 @Param2SELECT * FROM #TempTable WHERE A=@Param1And spQuery2 would be : SELECT * FROM Table2 WHERE B=@ParamI was wondering if this syntax would work and if I can skip theexplicit declaration of #TempTable definition.Thanks for your suggestions.
View 5 Replies View RelatedI have a stored procedure with the following:
CREATE TABLE #t1 (... ...);
WITH temp AS (....)
INSERT INTO #t1
SELECT .... FROM temp LEFT OUTER JOIN anothertable ON ...
This stored procedure works fine in Management Studio.
I then use this stored procedure in an ASP.NET page via a SQLDataSource object. The ASP.NET code compiles without error, but the result returned is empty (my bounded GridView object has zero rows). From Web Developer, if I try to Refresh Schema on the SQLDATASource object, I get an error: "Invalid object name '#t1'. The error is at the red #1 as I tried putting something else at that location to confirm it.
What does the error message mean and what have I done wrong?
Thanks.
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'
create table #ListAll (wid int, parentID int, myFlag int)
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]
drop table #ListAll
GO
Want to obtain the outpur of a xp_cmdshell (or any other procedure call) command to a table. Is it possible ?
View 6 Replies View RelatedRecently 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?
Thanks in advance for your help!
Hi All,
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.
Any pointers is highly appreciated
Thanks,
Krishna.
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?
View 4 Replies View RelatedI 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. Anysuggestions?
View 2 Replies View RelatedHello,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.
View 1 Replies View Relatedi'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?
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
If cbFinVM.Checked Then
sqlStr = "DELETE FROM FIN_vendorClass"
TableName = "FIN_vendorClass"
DelAll()
sqlStr = "DELETE FROM FIN_vendorAdd"
TableName = "FIN_vendorAdd"
DelAll()
sqlStr = "DELETE FROM FIN_vendor"
TableName = "FIN_vendor"
DelAll()
sqlStr = "DELETE FROM FIN_AddressType"
TableName = "FIN_AddressType"
DelAll()
sqlStr = "DELETE FROM FIN_VendorStatus"
TableName = "FIN_VendorStatus"
DelAll()
sqlStr = "DELETE FROM States"
TableName = "States"
DelAll()
sqlStr = "DELETE FROM Country"
TableName = "Country"
sqlStr = "DELETE FROM FIN_IndustrialCodes"
TableName = "FIN_IndustrialCodes"
DelAll()
ClearCounts()
timeStepStart = Date.Now
GenIndCodes()
timeStepStop = Date.Now
DispOneCounts()
DelAll()
ClearCounts()
timeStepStart = Date.Now
FinVendStat()
timeStepStop = Date.Now
TableName = "FIN_VendorStatus"
DispOneCounts()
ClearCounts()
timeStepStart = Date.Now
FinAddrType()
timeStepStop = Date.Now
TableName = "FIN_AddressType"
DispOneCounts()
ClearCounts()
timeStepStart = Date.Now
GenCountry()
timeStepStop = Date.Now
TableName = "Country"
DispOneCounts()
ClearCounts()
timeStepStart = Date.Now
GenState()
timeStepStop = Date.Now
TableName = "States"
DispOneCounts()
ClearCounts()
timeStepStart = Date.Now
FinVM()
timeStepStop = Date.Now
TableName = "FIN_Vendor"
TableName2 = "FIN_VendorAdd"
DispTwoCounts()
End If
If cbFinbudget.Checked Then
sqlStr = "DELETE FROM FIN_BudgetAccnt"
TableName = "FIN_BudgetAccnt"
DelAll()
sqlStr = "DELETE FROM FIN_Budget"
TableName = "FIN_Budget"
DelAll()
sqlStr = "DELETE FROM FIN_CodeBook"
TableName = "FIN_CodeBook"
DelAll()
ClearCounts()
TableName = "FIN_Budget"
timeStepStart = Date.Now
FinBudget()
timeStepStop = Date.Now
DispOneCounts()
ClearCounts()
TableName = "FIN_Codebook"
TableName2 = "FIN_budgetAccnt"
timeStepStart = Date.Now
FinCodeBook()
timeStepStop = Date.Now
DispTwoCounts()
ClearCounts()
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.
Thanks,
BPG
I am having some trouble populating a table with values from other tables:
I am creating the stored proc as follows:
CREATE PROCEDURE make_temp_stat (@from datetime, @to datetime)
AS
DROP TABLE tempTable
Create tempTable
(
NumApplications (int),
NumStudents (int),
NumTeachers (int)
)
//Then I insert the values into the table as follows
INSERT INTO tempTable (NumApplications) SELECT Count(*) FROM [dbo].[CASE_APPLICATION] WHERE (OPEN_DT>= @from AND OPEN_DT <= @to)
INSERT INTO tempTable (NumStudents) SELECT Count(*) FROM [dbo].[CASE_STUDENTS] WHERE (APP_DT>= @from_dt AND APP_DT<= @to_dt)
INSERT INTO tempTable (NumTeachers) SELECT Count(*) FROM [dbo].[CASE_TEACHER] WHERE (JOIN_DT>=@from_dt AND JOIN_DT<= @to_dt)
GO
Nothing happens when I run this stored proc. Can sombody point out what exactly is wrong over here?
Hi All,
I want to insert data using a stored proc. Can anyone tell me the correct syntax for inserting data into a table using a stored proc?
Thanks
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
Hi all,
Is it possible to pass a table variable to a Stored proc or a function?
If it is can you give me the sentax.
TIA,
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?
I'm trying to write a SQL that stores a result from a stored-procedureinto a temporary table.Is there any way of doing this?
View 3 Replies View RelatedHi,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!
View 2 Replies View RelatedI 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.
View 3 Replies View RelatedI 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?
TIA,
barkingdog
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
Hi all,
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.
Any thoughts?
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.