What Are Temporary Stored Procedure?
Jan 4, 2008hi
what are temporary store procedure and where they are used,can u give me an example where the temporary stored procedures are used.
can we create a procedur with in a procedure?
ADVERTISEMENT
VB5 Create Temporary Stored Procedure
Sep 3, 1998When I use comands insert and update with VB5 and ODBC, one temporary stored procedure is created in database tempdb to each command executed.
These stored procedures are deleted only when the connection is closed.
My program use comands insert and update inside a loop, and a lot of temporary stored procedure are generated and full the database tempdb. When it occur, others systems are afecteds.
My questions:
Why it occur ?
Wich have created this stored procedure ?
How to avoid it occur ?
The versions are:
SQL Server 6.5
Visual Basic 5.0
SQL Server ODBC Driver 2.65.0240
Using Temporary Tables Within The Stored Procedure
Nov 15, 2006Hi,If one uses a temporary table/ table variable within a storedprocedure, will it use the compiled plan each time the stored procedureis executed or will it recompile for each execution?Thanks in advance,Thyagu
View 1 Replies View RelatedQuerying Temporary Tables From A Stored Procedure
Jun 7, 2005What I am trying to do now is combine multiple complex queries into one table
and query it showing the results on an ASP.net page.
I am currently using SQL Server 2000 backend. Each one of these queries are
pretty complex so I created each query as a Stored Procedure. These queries are
dynamic by each user, so the results will never be the same globally.
What I have done so far was created a master stored procedure passing the
current user's username as a parameter. Within the master SP (Stored Procedure)
it creates a temporary table inserts and executes multiple stored procedures and
inserts into the temporary directory. Each of the sub stored procedures all
have the same columns. After the insert to the temp tables, I then query the
temp table and return it to the function it was executed in code and fill it as
a System.Data.DataTable. I then bind the DataTable to a
Repeater.DataSource.
Problem: When the page is rendered, it returns nothing. I
tested the master SP in the data environment and it works fine.
Suspect: ASP.net when the SP is executed, it sees that the
data is a disconnected datasource? Perhaps the session in the SQL Server when
the temp table is created isn't in SYSOBJECTS system table?
Here is an example of the code from the master SP:
CREATE PROCEDURE dbo.TM_getAlerts @Username
varchar(32)ASCREATE TABLE #MyAlerts ([DATE] datetime, [TEXT]
varchar(200), [LINK] varchar(200) )
INSERT INTO #MyAlertsEXEC
TM_getAlertsNewTasks @Username
INSERT INTO #MyAlertsEXEC
TM_getAlertsLateTasks @Username
SELECT [DATE] AS 'DATE', [TEXT]
AS 'TEXT', [LINK] AS LINK FROM #MyAlerts
GO
It is a fairly simple call... but why doesn't ASP.net doesn't see it when the
the DataTable is filled. I tried just executing one of the sub SP without
creating temporary tables and it works flawlessly. But the query in one of the
sub SP is a normal but complex select.
Insert Stored Procedure Result Into Temporary Table ?
Mar 21, 2006I'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
Stored Procedure Issue - Problem With Temporary Tables
Jun 14, 2006I would like to create a stored procedure which creates a temp table tostore some XML. The # of fields of XML is dependent upon the contentsof another table in the application, so the first part of my procedureidentifies the # of fields necessary. That is working correctly. Thesecond part of the procedure actually attempts to create the table.This is where my issue is.If I attempt to create the table as a non-temporary table, theprocedure executes correctly. As soon as I add the hash marks in frontof the table name to indicate that it is a temporary table, it isfailing. Is this a known bug? Or is my code just uncharacteristicallybad? ;)I'm getting an error that says "Invalid object name '#temp'."The section of code that has an issue is (the value of @max is 25 in mytest):SET @xq = 'CREATE TABLE #temp ( respid int, 'SET @i = 0WHILE( @i <= @max ) BEGINSET @xq = @xq + 'response' + CAST( @i AS VARCHAR( 4 ) ) + 'xml'IF ( @i < @max ) BEGINSET @xq = @xq + ', 'ENDSET @i = @i + 1ENDSET @xq = @xq + ' )'SELECT @nxq = CAST( @xq AS NVARCHAR( 40000 ) )EXECUTE sp_executesql @nxq......DROP TABLE #temp
View 3 Replies View RelatedHow To Find Query Plan For A Stored Procedure Using Temporary Tables
Oct 25, 2006This post is related to SQL server 2000 and SQL Server 2005 alleditions.Many of my stored procedures create temporary tables in the code. Iwant to find a way to find the query plan for these procsRepro--***********************************use pubsgoCREATE PROCEDURE Test @percentage intASSET Nocount on--Create and load a temporary tableselect * into #Temp1 from titleauthor--Create second temporary tablecreate table #Temp2 ( au_id varchar(20), title_id varchar (20), au_ordint, rolaylityper int)--load the second temporary table from the first oneinsert into #Temp2 select * from #Temp1goset showplan_Text ONgoEXEC Test @percentage = 100GOset showplan_Text OFFgo**************************************I get the following errorServer: Msg 208, Level 16, State 1, Procedure Test, Line 10Invalid object name '#Temp2'.Server: Msg 208, Level 16, State 1, Procedure Test, Line 10Invalid object name '#Temp1'.I do understand what the error message means. I just want to know abetter way of finding the query plan when using temp objects.My real production procs are hundreds of lines with many temp tablesused in join with other temp tables and/or real tables.Regards
View 3 Replies View RelatedTemporary Table In Stored Procedure Doesn't Work From SQLDataSource
Feb 20, 2008I 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.
What Is The Difference Between: A Table Create Using Table Variable And Using # Temporary Table In Stored Procedure
Aug 29, 2007which is more efficient...which takes less memory...how is the memory allocation done for both the types.
View 1 Replies View RelatedCan We Have Temporary Table In Store Procedure When Using BCP ?
Jul 21, 2006hi, good day,
when i run query in query analyzer
exec sp_test '2006-07-21'
it show result , however when i run it using BCP tools
exec master..xp_cmdshell 'bcp "exec sp_test '2006-07-21' " queryout c: est.txt -c '
it give me error which is something like "invalid object #temp_tbl",
i do using temporary table in store procedure
how to we solve it ? seem that bcp not support temporary table ? really need guidance from expert here ,
thank you :eek:
SqlDataSource And Stored Procedures With Temporary Tables
Dec 20, 2007Can a SqlDataSource or a TableAdapter be attached to a stored procedure that returns a temporary table? I am using Sql Server 2000.
The SqlDataSource is created with the wizard and tests okay but 2.0 controls will not bind to it.
The TableAdapter wizard says 'Invalid object name' and displayes the name of the temporary table.ALTER PROCEDURE dbo.QualityControl_Audit_Item_InfractionPercentageReport
@AuditTypeName varchar(50), @PlantId int = NULL,
@BuildingId int = NULL, @AreaId int = NULL,
@WorkCellId int = NULL, @WorkShift int = NULL,
@StartDate datetime = NULL, @EndDate datetime = NULL,
@debug bit = 0 AS
CREATE TABLE #QualityControl_Audit_Item_SelectDistinctByFilters_TempTable (ItemHeader varchar(100), Item varchar(250), HeaderSequence int, ItemSequence int, MajorInfractionPercent money, MinorInfractionPercent money)
DECLARE @sql nvarchar(4000), @paramlist nvarchar(4000)
SELECT @sql = '
INSERT INTO #QualityControl_Audit_Item_SelectDistinctByFilters_TempTable
(ItemHeader, Item, HeaderSequence, ItemSequence, MajorInfractionPercent, MinorInfractionPercent)
SELECT DISTINCT QualityControl_Audit_Item.Header,
QualityControl_Audit_Item.AuditItem,
QualityControl_Audit_Item.HeaderSequence,
QualityControl_Audit_Item.AuditItemSequence,
NULL, NULL
FROM QualityControl_Audit INNER JOIN
QualityControl_Audit_Item ON QualityControl_Audit.QualityControl_Audit_Id = QualityControl_Audit_Item.QualityControl_Audit_Id
WHERE (QualityControl_Audit.AuditType = @xAuditTypeName)'
IF @PlantId IS NOT NULL SELECT @sql = @sql + ' AND QualityControl_Audit.PlantId = @xPlantId'
IF @BuildingId IS NOT NULL SELECT @sql = @sql + ' AND QualityControl_Audit.BuildingId = @xBuildingId'
IF @AreaId IS NOT NULL SELECT @sql = @sql + ' AND QualityControl_Audit.AreaId = @xAreaId'
IF @WorkCellId IS NOT NULL SELECT @sql = @sql + ' AND QualityControl_Audit.WorkCellId = @xWorkCellId'
IF @WorkShift IS NOT NULL SELECT @sql = @sql + ' AND QualityControl_Audit.WorkShift = @xWorkShift'
IF @StartDate IS NOT NULL SELECT @sql = @sql + ' AND QualityControl_Audit.DateAuditPerformed >= @xStartDate'
IF @EndDate IS NOT NULL SELECT @sql = @sql + ' AND QualityControl_Audit.DateAuditPerformed <= @xEndDate'SELECT @sql = @sql + '
ORDER BY QualityControl_Audit_Item.HeaderSequence, QualityControl_Audit_Item.AuditItemSequence'
IF @debug = 1 PRINT @sqlSELECT @paramlist = '@xAuditTypeName varchar(50), @xPlantId int, @xBuildingId int, @xAreaId int, @xWorkCellId int,
@xWorkShift int, @xStartDate datetime, @xEndDate datetime'
EXEC sp_executesql @sql, @paramlist, @AuditTypeName, @PlantId, @BuildingId, @AreaId, @WorkCellId, @WorkShift, @StartDate, @EndDateDECLARE my_cursor CURSOR FOR
SELECT ItemHeader, Item, MajorInfractionPercent, MinorInfractionPercent FROM #QualityControl_Audit_Item_SelectDistinctByFilters_TempTable
OPEN my_cursor
--Perform the first fetchDECLARE @ItemHeader varchar(100), @Item varchar(250), @MajorInfractionPercent money, @MinorInfractionPercent money
FETCH NEXT FROM my_cursor INTO @ItemHeader, @Item, @MajorInfractionPercent, @MinorInfractionPercent
--Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
--Major
EXEC dbo.QualityControl_Audit_Item_InfractionPercentageReport_CalculateForMajorOrMinor @AuditTypeName, @PlantId, @BuildingId, @AreaId, @WorkCellId, @WorkShift, @StartDate, @EndDate, @ItemHeader, @Item, 'Major', @debug, @InfractionPercent = @MajorInfractionPercent OUTPUTUPDATE #QualityControl_Audit_Item_SelectDistinctByFilters_TempTable
SET MajorInfractionPercent = @MajorInfractionPercentWHERE (((ItemHeader IS NULL) AND (@ItemHeader IS NULL) OR (ItemHeader = @ItemHeader)) AND (Item = @Item))
--Minor
EXEC dbo.QualityControl_Audit_Item_InfractionPercentageReport_CalculateForMajorOrMinor @AuditTypeName, @PlantId, @BuildingId, @AreaId, @WorkCellId, @WorkShift, @StartDate, @EndDate, @ItemHeader, @Item, 'Minor', @debug, @InfractionPercent = @MinorInfractionPercent OUTPUTUPDATE #QualityControl_Audit_Item_SelectDistinctByFilters_TempTable
SET MinorInfractionPercent = @MinorInfractionPercentWHERE (((ItemHeader IS NULL) AND (@ItemHeader IS NULL) OR (ItemHeader = @ItemHeader)) AND (Item = @Item))
FETCH NEXT FROM my_cursor INTO @ItemHeader, @Item, @MajorInfractionPercent, @MinorInfractionPercent
END
CLOSE my_cursor
DEALLOCATE my_cursor
SELECT * FROM #QualityControl_Audit_Item_SelectDistinctByFilters_TempTable
(re)using A Temporary Table In A Stored Proc (was Confusion)
Feb 15, 2005Hi 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!
Temporary Table In 3 Diffirent Stored Proc
Mar 30, 2004I 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
SQL SERVER TEMPORARY TABLES In STORED PROCEDURES
Jun 3, 2006There are two ways to create a temporary tables in stored procedures
1: Using Create Table <Table Name> & then Drop table
ex. Create Table emp (empno int, empname varchar(20))
at last : drop table emp
2. Using Create table #tempemp
( empno int, empname varchar(20))
at last : delete #tempemp
---which one is preferrable & why.
what are the advantages & disadvantages of the above two types.
$100 USD To The Winner... Stored Procs, Temporary Tables And Cursors... Oh My!
Oct 14, 2001I'm so desperate, I'll pay $100 to the proper solution to this problem. I'm sure it's an easy fix, but I'm wasting more money every day trying to figure it out...
I have a table with hierarchial data in it (see the bottom tabledef) and I need to query an "indented outline" of the records in it for a tree control on a website. To do that I have to perform some sort of recursive or nested query or I can do all that manipulation in a temporary table/cursor... However, even though the resultset will display when I check the query, when I try to open it using ADO, I get a recordcount of -1.... it's very frustrating and extremely important.
I'd rather pay an expert here than try to navigate a tech help line.
ConnIS is defined in an earlier include file...
Set oCmd = Server.CreateObject("ADODB.Command")
Set oCmd.ActiveConnection = ConnIS
oCmd.CommandText = "dbo.Expandset" 'Name of SP
oCmd.CommandType = adCmdStoredProc 'ADO constant for 4
Set oTmp = oCmd.CreateParameter("@current", adInteger, adParamInput,, 892)
oCmd.Parameters.Append oTmp
Set oRs = Server.CreateObject("ADODB.Recordset")
oRs.Open oCmd
Response.Write oRs.RecordCount & "<hr>"
oRs.Close
Set oRs=Nothing
This code generates the following result when run from an active server page:
-1<hr>
When I execute the raw SQL code ("exec Expandset 892") against the stored proc in the query analyzer, I get:
item tier
----------- -----------
892 1
948 2
895 2
946 2
945 2
893 2
894 3
944 2
943 2
904 2
896 3
897 4
901 2
903 3
902 3
900 2
947 2
899 2
The source for the stored proc is:
------------------------------------------------------
CREATE PROCEDURE Expandset (@current int) as
SET NOCOUNT ON
DECLARE @level int, @line char(20)
CREATE TABLE #stack (item int, tier int)
CREATE TABLE #output (item int, tier int)
INSERT INTO #stack VALUES (@current, 1)
SELECT @level = 1
WHILE @level > 0
BEGIN
IF EXISTS (SELECT * FROM #stack WHERE tier = @level)
BEGIN
SELECT @current = item
FROM #stack
WHERE tier = @level
SELECT @line = space(@level - 1) + convert(varchar,@current)
INSERT INTO #output (item, tier) values (@current, @level)
DELETE FROM #stack
WHERE tier = @level
AND item = @current
INSERT #stack
SELECT ID, @level + 1
FROM SITE_Container
WHERE parent = @current
IF @@ROWCOUNT > 0
SELECT @level = @level + 1
END
ELSE
SELECT @level = @level - 1
END
SELECT O.item, O.tier FROM #output O
-------------------------------------------------------------------
The relevant portions of the Table definitions for SITE_Container are:
CREATE TABLE [dbo].[SITE_Container] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Parent] [int] NULL)
-------------------------------------------------------------------
My contact information is:
Jared Nielsen
PO Box 600271
Jacksonville, FL 32260
904-230-1688
888-316-2357 vm / fax
Nested Queries, Stored Procedures, Temporary Table
Jul 23, 2005Hi,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 RelatedCalling A Stored Procedure Inside Another Stored Procedure (or Nested Stored Procedures)
Nov 1, 2007Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly. For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie
exec dbo.DeriveStatusID 'Created'
returns an int value as 1
(performed by "SELECT statusID FROM statusList WHERE statusName= 'Created')
but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie:
exec dbo.AddProduct_Insert 'widget1'
which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
I want to simply the insert to perform (in one sproc):
SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example).
My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert).
Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
Calling A Stored Procedure From ADO.NET 2.0-VB 2005 Express: Working With SELECT Statements In The Stored Procedure-4 Errors?
Mar 3, 2008Hi all,
I have 2 sets of sql code in my SQL Server Management Stidio Express (SSMSE):
(1) /////--spTopSixAnalytes.sql--///
USE ssmsExpressDB
GO
CREATE Procedure [dbo].[spTopSixAnalytes]
AS
SET ROWCOUNT 6
SELECT Labtests.Result AS TopSixAnalytes, LabTests.Unit, LabTests.AnalyteName
FROM LabTests
ORDER BY LabTests.Result DESC
GO
(2) /////--spTopSixAnalytesEXEC.sql--//////////////
USE ssmsExpressDB
GO
EXEC spTopSixAnalytes
GO
I executed them and got the following results in SSMSE:
TopSixAnalytes Unit AnalyteName
1 222.10 ug/Kg Acetone
2 220.30 ug/Kg Acetone
3 211.90 ug/Kg Acetone
4 140.30 ug/L Acetone
5 120.70 ug/L Acetone
6 90.70 ug/L Acetone
/////////////////////////////////////////////////////////////////////////////////////////////
Now, I try to use this Stored Procedure in my ADO.NET-VB 2005 Express programming:
//////////////////--spTopSixAnalytes.vb--///////////
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim sqlConnection As SqlConnection = New SqlConnection("Data Source = .SQLEXPRESS; Integrated Security = SSPI; Initial Catalog = ssmsExpressDB;")
Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdaptor("[spTopSixAnalytes]", sqlConnection)
sqlDataAdapter.SelectCommand.Command.Type = CommandType.StoredProcedure
'Pass the name of the DataSet through the overloaded contructor
'of the DataSet class.
Dim dataSet As DataSet ("ssmsExpressDB")
sqlConnection.Open()
sqlDataAdapter.Fill(DataSet)
sqlConnection.Close()
End Sub
End Class
///////////////////////////////////////////////////////////////////////////////////////////
I executed the above code and I got the following 4 errors:
Error #1: Type 'SqlConnection' is not defined (in Form1.vb)
Error #2: Type 'SqlDataAdapter' is not defined (in Form1.vb)
Error #3: Array bounds cannot appear in type specifiers (in Form1.vb)
Error #4: 'DataSet' is not a type and cannot be used as an expression (in Form1)
Please help and advise.
Thanks in advance,
Scott Chang
More Information for you to know:
I have the "ssmsExpressDB" database in the Database Expolorer of VB 2005 Express. But I do not know how to get the SqlConnection and the SqlDataAdapter into the Form1. I do not know how to get the Fill Method implemented properly.
I try to learn "Working with SELECT Statement in a Stored Procedure" for printing the 6 rows that are selected - they are not parameterized.
T-SQL (SS2K8) :: One Stored Procedure Return Data (select Statement) Into Another Stored Procedure
Nov 14, 2014I am new to work on Sql server,
I have One Stored procedure Sp_Process1, it's returns no of columns dynamically.
Now the Question is i wanted to get the "Sp_Process1" procedure return data into Temporary table in another procedure or some thing.
SQL Server 2014 :: Embed Parameter In Name Of Stored Procedure Called From Within Another Stored Procedure?
Jan 29, 2015I have some code that I need to run every quarter. I have many that are similar to this one so I wanted to input two parameters rather than searching and replacing the values. I have another stored procedure that's executed from this one that I will also parameter-ize. The problem I'm having is in embedding a parameter in the name of the called procedure (exec statement at the end of the code). I tried it as I'm showing and it errored. I tried googling but I couldn't find anything related to this. Maybe I just don't have the right keywords. what is the syntax?
CREATE PROCEDURE [dbo].[runDMQ3_2014LDLComplete]
@QQ_YYYY char(7),
@YYYYQQ char(8)
AS
begin
SET NOCOUNT ON;
select [provider group],provider, NPI, [01-Total Patients with DM], [02-Total DM Patients with LDL],
[Code] ....
Connect To Oracle Stored Procedure From SQL Server Stored Procedure...and Vice Versa.
Sep 19, 2006I have a requirement to execute an Oracle procedure from within an SQL Server procedure and vice versa.
How do I do that? Articles, code samples, etc???
Grab IDENTITY From Called Stored Procedure For Use In Second Stored Procedure In ASP.NET Page
Dec 28, 2005I have a sub that passes values from my form to my stored procedure. The stored procedure passes back an @@IDENTITY but I'm not sure how to grab that in my asp page and then pass that to my next called procedure from my aspx page. Here's where I'm stuck: Public Sub InsertOrder() Conn.Open() cmd = New SqlCommand("Add_NewOrder", Conn) cmd.CommandType = CommandType.StoredProcedure ' pass customer info to stored proc cmd.Parameters.Add("@FirstName", txtFName.Text) cmd.Parameters.Add("@LastName", txtLName.Text) cmd.Parameters.Add("@AddressLine1", txtStreet.Text) cmd.Parameters.Add("@CityID", dropdown_city.SelectedValue) cmd.Parameters.Add("@Zip", intZip.Text) cmd.Parameters.Add("@EmailPrefix", txtEmailPre.Text) cmd.Parameters.Add("@EmailSuffix", txtEmailSuf.Text) cmd.Parameters.Add("@PhoneAreaCode", txtPhoneArea.Text) cmd.Parameters.Add("@PhonePrefix", txtPhonePre.Text) cmd.Parameters.Add("@PhoneSuffix", txtPhoneSuf.Text) ' pass order info to stored proc cmd.Parameters.Add("@NumberOfPeopleID", dropdown_people.SelectedValue) cmd.Parameters.Add("@BeanOptionID", dropdown_beans.SelectedValue) cmd.Parameters.Add("@TortillaOptionID", dropdown_tortilla.SelectedValue) 'Session.Add("FirstName", txtFName.Text) cmd.ExecuteNonQuery() cmd = New SqlCommand("Add_EntreeItems", Conn) cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.Add("@CateringOrderID", get identity from previous stored proc) <------------------------- Dim li As ListItem Dim p As SqlParameter = cmd.Parameters.Add("@EntreeID", Data.SqlDbType.VarChar) For Each li In chbxl_entrees.Items If li.Selected Then p.Value = li.Value cmd.ExecuteNonQuery() End If Next Conn.Close()I want to somehow grab the @CateringOrderID that was created as an end product of my first called stored procedure (Add_NewOrder) and pass that to my second stored procedure (Add_EntreeItems)
View 9 Replies View RelatedSQL Server 2012 :: Executing Dynamic Stored Procedure From A Stored Procedure?
Sep 26, 2014I have a stored procedure and in that I will be calling a stored procedure. Now, based on the parameter value I will get stored procedure name to be executed. how to execute dynamic sp in a stored rocedure
at present it is like EXECUTE usp_print_list_full @ID, @TNumber, @ErrMsg OUTPUT
I want to do like EXECUTE @SpName @ID, @TNumber, @ErrMsg OUTPUT
System Stored Procedure Call From Within My Database Stored Procedure
Mar 28, 2007I have a stored procedure that calls a msdb stored procedure internally. I granted the login execute rights on the outer sproc but it still vomits when it tries to execute the inner. Says I don't have the privileges, which makes sense.
How can I grant permissions to a login to execute msdb.dbo.sp_update_schedule()? Or is there a way I can impersonate the sysadmin user for the call by using Execute As sysadmin some how?
Thanks in advance
Ad Hoc Query Vs Stored Procedure Performance Vs DTS Execution Of Stored Procedure
Jan 23, 2008
Has anyone encountered cases in which a proc executed by DTS has the following behavior:
1) underperforms the same proc when executed in DTS as opposed to SQL Server Managemet Studio
2) underperforms an ad-hoc version of the same query (UPDATE) executed in SQL Server Managemet Studio
What could explain this?
Obviously,
All three scenarios are executed against the same database and hit the exact same tables and indices.
Query plans show that one step, a Clustered Index Seek, consumes most of the resources (57%) and for that the estimated rows = 1 and actual rows is 10 of 1000's time higher. (~ 23000).
The DTS execution effectively never finishes even after many hours (10+)
The Stored procedure execution will finish in 6 minutes (executed after the update ad-hoc query)
The Update ad-hoc query will finish in 2 minutes
Table Names In Stored Procedures As String Variables And Temporary Table Question
Apr 10, 2008How 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.
User 'Unknown User' Could Not Execute Stored Procedure - Debugging Stored Procedure Using Visual Studio .net
Sep 13, 2007Hi all,
I am trying to debug stored procedure using visual studio. I right click on connection and checked 'Allow SQL/CLR debugging' .. the store procedure is not local and is on sql server.
Whenever I tried to right click stored procedure and select step into store procedure> i get following error
"User 'Unknown user' could not execute stored procedure 'master.dbo.sp_enable_sql_debug' on SQL server XXXXX. Click Help for more information"
I am not sure what needs to be done on sql server side
We tried to search for sp_enable_sql_debug but I could not find this stored procedure under master.
Some web page I came accross says that "I must have an administratorial rights to debug" but I am not sure what does that mean?
Please advise..
Thank You
Is The Transaction Context Available Within A 'called' Stored Procedure For A Transaction That Was Started In Parent Stored Procedure?
Mar 31, 2008I have a stored procedure 'ChangeUser' in which there is a call to another stored procedure 'LogChange'. The transaction is started in 'ChangeUser'. and the last statement in the transaction is 'EXEC LogChange @p1, @p2'. My questions is if it would be correct to check in 'LogChange' the following about this transaction: 'IF @@trancount >0 BEGIN Rollback tran' END Else BEGIN Commit END.
Any help on this would be appreciated.
Calling Stored Procedure Fromanother Stored Procedure
Oct 10, 2006Hi,I am getting error when I try to call a stored procedure from another. I would appreciate if someone could give some example.My first Stored Procedure has the following input output parameters:ALTER PROCEDURE dbo.FixedCharges @InvoiceNo int,@InvoiceDate smalldatetime,@TotalOut decimal(8,2) outputAS .... I have tried using the following statement to call it from another stored procedure within the same SQLExpress database. It is giving me error near CALL.CALL FixedCharges (@InvoiceNo,@InvoiceDate,@TotalOut )Many thanks in advanceJames
View 16 Replies View RelatedUse Resultset Returned From A Stored Procedure In Another Stored Procedure
Nov 15, 2006I have a store procedure (e.g. sp_FetchOpenItems) in which I would like to call an existing stored procedure (e.g. sp_FetchAnalysts). The stored proc, sp_FetchAnalysts returns a resultset of all analysts in the system.
I would like to call sp_FetchAnalysts from within sp_FetchOpenItems and insert the resultset from sp_FetchAnalysts into a local temporary table. Is this possible?
Thanks,
Kevin
SQL Stored Procedure Issue - Search Stored Procedure
May 18, 2007This is the Stored Procedure below ->
SET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GO
/****** Object: Stored Procedure dbo.BPI_SearchArchivedBatches Script Date: 5/18/2007 11:28:41 AM ******/if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BPI_SearchArchivedBatches]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)drop procedure [dbo].[BPI_SearchArchivedBatches]GO
/****** Object: Stored Procedure dbo.BPI_SearchArchivedBatches Script Date: 4/3/2007 4:50:23 PM ******/
/****** Object: Stored Procedure dbo.BPI_SearchArchivedBatches Script Date: 4/2/2007 4:52:19 PM ******/
CREATE PROCEDURE BPI_SearchArchivedBatches( @V_BatchStatus Varchar(30)= NULL, @V_BatchType VARCHAR(50) = NULL, @V_BatchID NUMERIC(9) = NULL, @V_UserID CHAR(8) = NULL, @V_FromDateTime DATETIME = '01/01/1900', @V_ToDateTime DATETIME = '01/01/3000', @SSS varchar(500) = null, @i_WildCardFlag INT)
AS
DECLARE @SQLString NVARCHAR(4000)DECLARE @ParmDefinition NVARCHAR (4000)
IF (@i_WildCardFlag=0)BEGIN
SET @SQLString='SELECT Batch.BatchID, Batch.Created_By, Batch.RequestSuccessfulRecord_Count, Batch.ResponseFailedRecord_Count, Batch.RequestTotalRecord_Count, Batch.Request_Filename, Batch.Response_Filename, Batch.LastUpdated_By, Batch.LastUpdated, Batch.Submitted_By, Batch.Submitted_On, Batch.CheckedOut_By, Batch.Checked_Out_Status, Batch.Batch_Description, Batch.Status_Code, Batch.Created_On, Batch.Source, Batch.Archived_Status, Batch.Archived_By, Batch.Archived_On, Batch.Processing_Mode, Batch.Batch_TemplateID, Batch.WindowID,Batch.WindowDetails, BatchTemplate.Batch_Type, BatchTemplate.Batch_SubType FROM Batch INNER JOIN BatchTemplate ON Batch.Batch_TemplateID = BatchTemplate.Batch_TemplateID WHERE ((@V_BatchID IS NULL) OR (Batch.BatchID = @V_BatchID )) AND ((@V_UserID IS NULL) OR (Batch.Created_By = @V_UserID )) AND ((Batch.Created_On >= @V_FromDateTime ) AND (Batch.Created_On <= @V_ToDateTime )) AND Batch.Archived_Status = 1 '
if (@V_BatchStatus IS not null) begin set @SQLString=@SQLString + ' AND (Batch.Status_Code in ('+@V_BatchStatus+'))' end
if (@V_BatchType IS not null) begin set @SQLString=@SQLString + ' AND (BatchTemplate.Batch_Type in ('+@V_BatchType+'))' end END
ELSEBEGIN SET @SQLString='SELECT Batch.BatchID, Batch.Created_By, Batch.RequestSuccessfulRecord_Count, Batch.ResponseFailedRecord_Count, Batch.RequestTotalRecord_Count, Batch.Request_Filename, Batch.Response_Filename, Batch.LastUpdated_By, Batch.LastUpdated, Batch.Submitted_By, Batch.Submitted_On, Batch.CheckedOut_By, Batch.Checked_Out_Status, Batch.Batch_Description, Batch.Status_Code, Batch.Created_On, Batch.Source, Batch.Archived_Status, Batch.Archived_By, Batch.Archived_On, Batch.Processing_Mode, Batch.Batch_TemplateID, Batch.WindowID,Batch.WindowDetails, BatchTemplate.Batch_Type, BatchTemplate.Batch_SubType FROM Batch INNER JOIN BatchTemplate ON Batch.Batch_TemplateID = BatchTemplate.Batch_TemplateID WHERE ((@V_BatchID IS NULL) OR (isnull (Batch.BatchID, '''') LIKE @SSS )) AND ((@V_UserID IS NULL) OR (isnull (Batch.Created_By , '''') LIKE @V_UserID )) AND ((Batch.Created_On >= @V_FromDateTime ) AND (Batch.Created_On <= @V_ToDateTime )) AND Batch.Archived_Status = 1 '
if (@V_BatchStatus IS not null) begin set @SQLString=@SQLString + ' AND (Batch.Status_Code in ('+@V_BatchStatus+'))' end
if (@V_BatchType IS not null) begin set @SQLString=@SQLString + ' AND (BatchTemplate.Batch_Type in ('+@V_BatchType+'))' end
END
PRINT @SQLString
SET @ParmDefinition = N' @V_BatchStatus Varchar(30), @V_BatchType VARCHAR(50), @V_BatchID NUMERIC(9), @V_UserID CHAR(8), @V_FromDateTime DATETIME , @V_ToDateTime DATETIME, @SSS varchar(500)'
EXECUTE sp_executesql @SQLString, @ParmDefinition, @V_BatchStatus , @V_BatchType , @V_BatchID, @V_UserID , @V_FromDateTime , @V_ToDateTime , @SSS
GO
SET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GO
The above stored procedure is related to a search screen where in User is able to search from a variety of fields that include userID (corresponding column Batch.Created_By) and batchID (corresponding column Batch.BatchID). The column UserID is a varchar whereas batchID is a numeric.
REQUIREMENT:
The stored procedure should cater to a typical search where any of the fields can be entered. meanwhile it also should be able to do a partial search on BatchID and UserID.
Please help me regarding the same.
Thanks in advance.
Sandeep Kumar
Sql Count Using Stored Procedure Withing Stored Procedure
Apr 29, 2008I have a stored procedure that among other things needs to get a total of hours worked. These hours are totaled by another stored procedure already. I would like to call the totaling stored procedure once for each user which required a loop sort of thing
for each user name in a temporary table (already done)
total = result from execute totaling stored procedure
Can you help with this
Thanks
Exec Twp Stored Procedure In A Main Stored Procedure
Apr 29, 2004Hi there
i have a stored procedure like this:
CREATE PROCEDURE SP_Main
AS
SET NOCOUNT ON
BEGIN TRANSACTION
exec SP_Sub_One output
exec SP_Sub_Two output
IF @@ERROR = 0
BEGIN
-- Success. Commit the transaction.
Commit Tran
END
ELSE
Rollback Tran
return
GO
now the problem is, when i execute the stored procedure's inside the main stored procedure, and these sub sp's has an error on it, the main stored procedure doesnt rollback the transation.
now i can put the "begin transation" in the two sub stored procedure's. The problem is
what if the first "SP_Sub_One" executed successfully, and there was error in "SP_Sub_Two"
now the "SP_Sub_One" has been executed and i cant rollback it... the error occured in the
"SP_Sub_Two" stored procedure ... so it wont run ... so there will be error in the data
how can i make a mian "BEGIN TRANSACTION" , that even it include the execution of stored procedure in it.
Thanks in advance
Mahmoud Manasrah