Need To Pull A Select Few Results From INSERT EXEC
Aug 17, 2006
I'd *like* to execute a sProc within another sProc, because this other one (let's call it dataProc) will provide me with results I need that are calculated based off execing other procs within that proc itself.
I'd like to use this data in a new sProc (we'll call it newProc), but I don't need NEARly all of the columns. The dataProc returns 1 row with 42 columns. I need about 4-5 of those for my newProc.
The dataProc does not have any output variables, and I do not want to change the signature, because several pages/apps use this existing dataProc.
My question:
Is there a way to INSERT INTO table EXEC dataProc
*Without* making a temp table that takes in every single column the dataProc puts out?
Any help is much appreciated.. thanks!
View 8 Replies
ADVERTISEMENT
May 18, 2006
Hello, I recently view a webcast of sql injection, and at this moment I created a user, and give dbo to this user, and this same user, is the one I have in the connection string of my web application, I want to create a user to prevent sql injection attacks, I mean that user wont be able to drop or create objects, only select views, tables, exec insert,update, deletes and exec stored procedures.
Is any easy way to do this?
A database role and then assing that role to the user?
View 4 Replies
View Related
May 20, 2006
Hi all,
I am having a weird problem occur when trying to insert missing records based on an INSERT INTO...SELECT statement.
Basically, I am populating a temporary table with data, then inserting into a main table those records from the temporary table that aren't in the main table. In addition, I am updating those records that are in the main table.
When I call the following SQL, all of the records from the temporary table are inserted even though those records already exist. Bizzarrely the update statement works perfectly.
INSERT INTO
AffiliateCategories (Identifier, AffiliateID, NetworkID, AffiliateCategoryName, NetworkCategoryName)
SELECT
Identifier,
AffiliateID,
1,
AffiliateCategoryName,
NetworkCategoryName
FROM
#TempTradeDoublerCategories
WHERE
#TempTradeDoublerCategories.AffiliateCategoryName NOT IN
(
SELECT
AffiliateCategoryName
FROM
AffiliateCategories
)
I am not sure why it is doing this and it's driving me crazy. If anyone has any ideas why this is happening, or knows of a better way to accomplish this then please let me know.
Are there some issues with string comparisons going on here?
The table that populates the temp table is defined as:
CREATE TABLE [dbo].[Staging_TradeDoubler_Categories](
[id] [int] NULL,
[name] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[merchantName] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[TDCategories_Id] [int] NULL,
[AffiliateID] [int] NULL
) ON [PRIMARY]
Full code listing:
IF OBJECT_ID('tempdb..#TempTradeDoublerCategories') IS NOT NULL
DROP TABLE #TempTradeDoublerCategories
CREATE TABLE #TempTradeDoublerCategories
(
Identifier INT,
AffiliateID INT,
AffiliateCategoryName VARCHAR(255),
NetworkCategoryName VARCHAR(255)
)
INSERT INTO
#TempTradeDoublerCategories (AffiliateCategoryName)
SELECT DISTINCT
CAST(merchantName AS VARCHAR(255)) AS MerchantName
FROM
Staging_TradeDoubler_Categories
ORDER BY
MerchantName
UPDATE
#TempTradeDoublerCategories
SET
#TempTradeDoublerCategories.Identifier = Staging_TradeDoubler_Categories.id,
#TempTradeDoublerCategories.AffiliateID = Staging_TradeDoubler_Categories.AffiliateID,
#TempTradeDoublerCategories.NetworkCategoryName = Staging_TradeDoubler_Categories.[name]
FROM
Staging_TradeDoubler_Categories
WHERE
Staging_TradeDoubler_Categories.merchantName = #TempTradeDoublerCategories.AffiliateCategoryName
UPDATE
#TempTradeDoublerCategories
SET
#TempTradeDoublerCategories.AffiliateID = Staging_TradeDoubler_Affiliates.AffiliateID
FROM
Staging_TradeDoubler_Affiliates
WHERE
#TempTradeDoublerCategories.AffiliateID = Staging_TradeDoubler_Affiliates.TradeDoublerAffiliateID
Print 'Inserting records'
INSERT INTO
AffiliateCategories (Identifier, AffiliateID, NetworkID, AffiliateCategoryName, NetworkCategoryName)
SELECT
Identifier,
AffiliateID,
1,
AffiliateCategoryName,
NetworkCategoryName
FROM
#TempTradeDoublerCategories
WHERE
#TempTradeDoublerCategories.AffiliateCategoryName NOT IN
(
SELECT
AffiliateCategoryName
FROM
AffiliateCategories
)
Print 'Updating records'
UPDATE
AffiliateCategories
SET
AffiliateCategories.AffiliateCategoryName = #TempTradeDoublerCategories.AffiliateCategoryName,
AffiliateCategories.NetworkCategoryName = #TempTradeDoublerCategories.NetworkCategoryName,
DateModified = GETDATE()
FROM
#TempTradeDoublerCategories
WHERE
#TempTradeDoublerCategories.AffiliateCategoryName IN
(
SELECT
AffiliateCategories.AffiliateCategoryName
FROM
AffiliateCategories
)
PRINT 'Dropping table'
IF OBJECT_ID('tempdb..#TempTradeDoublerCategories') IS NOT NULL
DROP TABLE #TempTradeDoublerCategories
View 1 Replies
View Related
Feb 11, 2015
I need to insert data into a table based on the results returned by a select statement. Basically, the select statement below gives me a list of all the work orders created in the last hour.
select worknumber from worksorderhdr where date_created > DATEADD(HOUR, -1, GETDATE())
This might return anywhere between 5 and 50 records each time. What I then need to do is use each of the work numbers returned to create a record in the spec_checklist_remind table. The other details in the insert statement will be the same for each insert, it's just the worknumber from the select statement that needs to be added to the insert where the ?? are below:
INSERT INTO spec_checklist_remind (form, record_type, linked_to_worknumber, spec_checklist_id) values (5, 0, '??',52)"
View 2 Replies
View Related
Dec 4, 2015
I'm trying to fill a temp table whose columns are the same as another table plus it has one more column. The temp table's contents are those rows in the other table that meet a particular condition plus another column that is the name of the table that is the source for the rows being added.
Example: 'permTable' has col1 and col2. The data in these two rows plus the name of the table from which it came ('permTable' in this example) are to be added to #temp.
Data in permTable
col1 col2
11, 12
21, 22
Data in #temp after permTable's filtered contents have been added
TableName, col1 col2
permTable, 11, 12
permTable, 21, 22
What is the syntax for an insert like this?
View 2 Replies
View Related
Feb 20, 2007
this might sound dumb or impossible, but is there a way to run exec commands forcing them to not return results
View 6 Replies
View Related
Jun 2, 2005
Hello, I have been working around this issue, but couldn't yet find any solution.I have a stored procedure that calls a method to do a certain repetitive work.In this function, I have a dynamic query, which means, that I am concatinating commands to the query depending on the input of the function.for example, there is an input for a function called "Id"Inside the function, if Id = 111I need to add " and ID <> 1" and if Id has another value I need to add " and ID = c.ID" something like that.Now, inside the function, I need to return a value by executing the above @SQLString as follows:EXEC @SQLStringWhen I need is something likeEXEC @SQLString, @Total OutputReturn (@Total)Are there any ideas ?regards
View 1 Replies
View Related
Sep 15, 2006
Hi All, we got a problem that I hope you guys can help us with.
We are trying to extract data from a SQL database in XML format to a variable (string) using an 'Execute SQL Task'
In the task we are using an ADO.NET connection to the database which is fine and via direct input we are using this query.
SELECT
title,
initials,
surname,
username,
gender,
emailaddress,
addressline1,
addressline2,
addressline3,
town,
postcode,
country,
emailcontact,
postalcontact,
telephone
FROM
CRMCustRecords
FOR XML path('customer'), root('CRMData'), ELEMENTS XSINIL
In SSMS this is fine and returns an ok (to my eyes anyway!) XML resultset.
<CRMData xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<customer>
<title>xxx</title>
<initials>xxxxxxx</initials>
<surname>xxxxxx</surname>
<username>xxxxxx</username>
<gender>x</gender>
<emailaddress>xxxx@xxxxxxx.xx.xx</emailaddress>
<addressline1>xxxxx</addressline1>
<addressline2 xsi:nil="true" />
<addressline3 xsi:nil="true" />
<town>xxxx</town>
<postcode>xxxxxxxxx</postcode>
<country>xx</country>
<emailcontact>x</emailcontact>
<postalcontact>x</postalcontact>
<telephone>99999999999</telephone>
</customer>
<customer>
</CRMData>
However the SSIS task (running exactly the same query) is returning
<ROOT>
<CRMData xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<customer>
<title>xxx</title>
<initials>xxxxxxx</initials>
<surname>xxxxx</surname>
<username>xxxxxxxx</username>
<gender>x</gender>
<emailaddress>xxxxx@xxxxxxxxxx.xx.xx</emailaddress>
<addressline1>xxxxxxxxxx</addressline1>
<addressline2 xsi:nil="true" />
<addressline3 xsi:nil="true" />
<town>xxxxx</town>
<postcode>xxxxxxxx</postcode>
<country>xx</country>
<emailcontact>x</emailcontact>
<postalcontact>x</postalcontact>
<telephone>9999999999</telephone>
</customer>
</CRMData>
</ROOT>
I setup the query as a stored procedure and changed the SSIS accordingly. Running the sp from within SSMS was fine but the task still returned the wierd results.
This is completely unexpected and giving us headaches. It doesn't even look like well-formed XML to my eyes and won't display in IE without showing problems! Where are the eroneous /'s and ROOT nodes coming from?
I can't imagine we are the only people to have run into this and I'm sure we aren't doing something quite right - just stuck as to what we're doing wrong.
Hopefully I've provided enough info. If not just ask.
Thanks for any and all input
Pat
View 3 Replies
View Related
Apr 21, 2006
Hi,
I'm writing a small query where I have a dynamic table name and dynamic condition for the criteria. In order to execute this, I need Exec command.
exec(select count(*) from @dynamictable where condition = @dynamiccond)
But here I want to capture the count from the select statement. Could any of you help me capture the results from exec command?
Thanks
View 6 Replies
View Related
Mar 1, 2015
We can use comparison operators with strings as well. Hence, I tried to use the following query on a SQL Server 2012 instance with the sample AdventureWorks2012 database (the collation of the database and of the column is the default:
SQL_Latin1_General_CP1_CI_AS):
USE AdventureWorks2012 ;
GO
--Returns 5 records
SELECT pp.Name
FROM Production.Product AS pp
WHERE pp.Name >= N'Short' AND pp.Name <= N'Sport' ;
GO
The query only returns 5 records. This despite the fact that the search is an inclusive search and the Production.Product table contains records that begin with "Sport".
Now, when I replace "Sport" with "Sporu" (just moving one character up in the alphabet to verify whether characters after the word have any impact on the search) gives me 8 records.
USE AdventureWorks2012 ;
GO
--Returns 8 records
SELECT pp.Name
FROM Production.Product AS pp
WHERE pp.Name >= N'Short' AND pp.Name <= N'Sporu' ;
GO
What's going on inside of SQL Server that allows it to fetch "Short-Sleeve Classic Jersey" for the starting word "Short" but prevents it from fetching "Sport-100 Helmet" for the ending word "Sport" despite the search being an inclusive search?
View 3 Replies
View Related
Apr 25, 2006
I would like to make inner join with results from different exec %procedureName% calls.
thanks.
View 3 Replies
View Related
Oct 31, 2007
I have two SQL Server 2000 (one is localhost, one is remote with VPN IP 192.168.5.4).
I can select * from [192.168.5.4].db.dbo.test but I can't exec [192.168.5.4].db..spAdd in localhost.
These select and sp is OK for 1 or 2 week without any problem,but it didn't work one day.
Can some one explain why?
View 5 Replies
View Related
Apr 1, 2007
hi, like, if i need to do delete some items with the id = 10000 then also need to update on the remaining items on the with the same idthen i will need to go through all the records to fetch the items with the same id right? so, is there something that i can use to hold those records so that i can do the delete and update just on those records and don't need to query twice? or is there a way to do that in one go ?thanks in advance!
View 1 Replies
View Related
Oct 20, 2006
Hello, in my scenerio i am pulling a table from msde to my ppc and then i want to insert a new record to my local table but when i try to do that i faced with an index paradox forexample:
if i pulled a table with record index 1 then i am triying to insert a new record at local but at local it tries to insert index 1 again so i can't finish my operation. My primariy index is seed 1 also. How can i achieve my goal?
View 1 Replies
View Related
Nov 3, 2005
I am using sql 2005 beta
View 23 Replies
View Related
Jan 21, 2004
I need to insert the results into a temp table and i recieve the "MSDTC on server 'servername' is unavailable error".
declare @thestringall varchar(1000)
set @thestringall = 'select statment here'
insert into #temptable exec (@thestringall)
Thanks
Wooanaz
View 2 Replies
View Related
Apr 19, 2007
I need get the o/p of a system sp into a table. I am doing the following,
insert #repl_monitor
exec [distribution].sys.sp_replmonitorhelpsubscription @publisher =
N'FGRWA0508', @publisher_db = N'DB_Name', @publication = N'publication'
Code is really not important. Any sys SP can replace the above code.
I am getting the following error
Msg 8164, Level 16, State 1, Procedure sp_MSload_tmp_replication_status, Line 80
An INSERT EXEC statement cannot be nested.
I have seen the following link which discuss this issue,
http://www.sommarskog.se/share_data.html
But there is no solution there.
I tried with sp_executesql and EXEC(), but unable to get the result. Can anyone put some light?
------------------------
I think, therefore I am - Rene Descartes
View 8 Replies
View Related
Oct 9, 2007
I'm wondering if there is a single statement I can write to pull my data. Let's say my Order table has one field for userId and one field for supervisorId (among other fields) both of which are foreign keys into the Users table where their name, address, etc. are stored. What I'd like to do is to pull all the rows from Order and have a join that pulls the user name and supervisor name from the User table all in one go. Right now I pull all the Orders with just user name joined, and then go back over the objects to add the supervisor name as a separate query.
The reason I'd like to do this is to simplify the objects I'm passing to the GridView by doing a single fetch instead of multiples. I'm using SQL Server, .NET 2.0 and VS.NET 2005.
Thanks
View 1 Replies
View Related
May 6, 2008
Sorry if this is rudimentary.
I need a select statement to retrieve what version of sql server is running and whether it is enterprise or standard.
I know I could just right click but I have hundreds of servers to go through so this should save alot of time.
Thanks,
Kraig
View 2 Replies
View Related
Mar 21, 2008
I have my database: "RequestTrack"
My table (with its columns): "Request"RequestKey (automatically generated)..and the Primary KeyEntryDate (datetime)Summary (nvarchar)RequestStatusCodeKey (bigint)EntryUserID (nvarchar)EntryUserEmail (nvarchar)I am wanting to create a basic web form where my user interface has 3 text boxes and a Submit button:
txtUserID.TexttxtEmailAddress.TexttxtRequestSummary.Text
**After I hit the submit button the information will then be inserted into the database. Also the RequestStatusCodeKey will be MANUALLY typed in so that will not require the user to add that. Please please please help ! I've been searching online for days and looking at various websites and still havent found anything. I've found somethings but they went into too much depth with too much information. I am just wanting to stay basic but w/o using SQLDataSource Controls. I would like to be able to store a lot of data. Thanks for your help!!!
View 4 Replies
View Related
Dec 27, 2007
I've got a case where I am bringing older data into a brand new DB. Unfortunately, there are several versions of the old DB and I won't know ahead of time which one I will be running against. The data's basicaly the same, the table names are the same, but the column schema is NOT.
Basiclly, I want to use a conditional on the Insert.
Code Block
CREATE TABLE TestTable
(Col1 int NULL,
Col2 varchar(50) NULL)
GO
IF EXISTS (SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_CATALOG= 'TestDB'
AND TABLE_NAME ='TestTable'
AND COLUMN_NAME= 'Fred')
INSERT TestTable
(Col1, Col2, Fred)
VALUES
(1,'test', 'fredvalue')
This results in an "Unknown column" error.
What am I missing here and how can I accomplish it.
I know that I COULD build a SQL string and Exec it, but that is such a PITA and not very elegant.
Any help would be appreciated
View 3 Replies
View Related
Oct 23, 2007
Hi,
I have a stored procedure that transfers data from one database to another. I do a lot of manipulation to the data in between because although both databases are for the same purpose, the database designs are different. The two databases exist on two different servers. Till now my transfer procedure used a linked server to pull the data from one server to the other. This process takes a ton of time with complex queries across linked servers. I was researching on a faster method to transfer data and the only other working method seemed to be the insert..exec statement with which I can call a remote stored procedure which would run and return the results of the complex queries which I can then use locally as I wish.
My main question is, can I rely on insert..exec to work consistently and/or is there another method to achieve what I need that I'm totally ignorant of? I already know of inline/multi table value functions (they wont work across servers); openquery/openrowset (I need to be able to pass parameters); creating permanent tables to be used and then destroyed (not very appealing). I read an excellent article about these online by Erland Sommarskog (http://www.sommarskog.se/share_data.html) but I'm hoping someone can explain a little further.
Thanks all,
Anand
View 11 Replies
View Related
Nov 9, 2007
There must be some workaround for this. I need to grab the data from a stored proc on another server and place it into a temp table. I do not have the option to change the proc.
help if you can.
View 7 Replies
View Related
Oct 30, 2007
I have the following table called BADSANTA:
varchar(30) datetime
NAME WHENBAD
OJ Simpson 2007-1-12 xx:xx:xx:xxx
OJ Simpson 2007-4-2 xx:xx:xx:xxx
Monica Lewinsky 2006-7-4 xx:xx:xx:xxx
Monica Lewinsky 2006-10-31 xx:xx:xx:xxx
Bill Clinton 2006-7-4 xx:xx:xx:xxx
Bill Clinton 2006-10-31 xx:xx:xx:xxx
Bart Simpson 2006-11-2 xx:xx:xx:xxx
Bart Simpson 2006-2-25 xx:xx:xx:xxx
Bart Simpson 2005-07-27 xx:xx:xx:xxx
I want the records of the persons latest WHENBAD date. It should return one recordset for each person.
Thanks in advanced.
View 4 Replies
View Related
Jun 6, 2008
I am writing a stored procedure to select some information from two tables and I would also like to Execute a function using the userid information from the processing in the where clause of the Select statement. Is the syntax below possible?? If yes, could you please help me understand exactly what I may be doing wrong here.. If no, can you please help with better syntax. Thanks in advance
SELECT M.UserID, M.FullName, (EXEC calcPoints M.UserID) as 'UserPoints'
FROM MissionUsers M LEFT OUTER JOIN MissionUserInfo MU ON M.UserID = MU.UserID
WHERE M.EMAIL = @UserEmail
View 5 Replies
View Related
Aug 5, 2004
Hi All...
if i had the following sp...
*******************************************************
create procedure my_insert (param1 int, param2 int, paramx int)
as
...
complicated insert routine
...
return
*******************************************************
and then i wanted to exec this sp in another procedure i would have
exec my_insert( 1_value, 2_value, 3_value )
My question is how could i exec this will the result set of a select.... something like this
exec my_insert (select 1_value, 2_value, 3_value from another_table).
I know i could have this in an insert result type statement ie...
insert into dest_table (select 1_value, 2_value, 3_value from another_table)
but my insert routine is quite complicated and carries out some other functions so I would like to call (exec) a sp rather than repeating the complication in the select statement
Many Thanks
Gary T
View 4 Replies
View Related
Jan 2, 2007
hi,
can somebody tell me how to call SP in another SP select statement..? i've seen ppl doing it something like this..
Select column1, coulumn2, (select EXEC SP_NAme), blabla
thanks in advance... :)
View 6 Replies
View Related
May 25, 1999
Hi,
has anyone come across deadlocks on sysindexes in tempdb where the insert/exec combination is used.
eg
create table #fred (IntColumn int)
insert into #fred exec ProcThatSelectsAnIntColumn
This is being done in a stored procedure, and is deadlocking with other procs which are doing vanilla #table work - creating, inserting into, updatind, selecting from, etc.
I have noticed similar deadlocks where a #table is created inside an explict transaction, and I wondered whether there is an implicit transaction created, but @@Nestlevel is not changing either before or after the insert/exec.
I can't find any references in knowledgebase.
Any pointers appreciated.
Cheers
Simon
________________________
Simon Davis
Bankers Trust Australia Limited
Asset Management Technology
Ph: 61 2 9259 9137
<mailto:Simon.Davis@Bankerstrust.com.au>
View 2 Replies
View Related
Jul 6, 2004
Hi all,
I have an sp that sends cdomail which requires 4 variables.
I want an after insert trigger that fills in the values for the sp from the record just submitted, how can i do that?
Sp code
CREATE PROCEDURE [dbo].[sp_send_cdosysmail]
@From varchar(100) ,
@To varchar(100) ,
@Subject varchar(100)=" ",
@Body varchar(4000) =" "
/************************************************** *******************
This stored procedure takes the parameters and sends an e-mail.
All the mail configurations are hard-coded in the stored procedure.
Comments are added to the stored procedure where necessary.
References to the CDOSYS objects are at the following MSDN Web site:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_messaging.asp
************************************************** *********************/
AS
Declare @iMsg int
Declare @hr int
Declare @source varchar(255)
Declare @description varchar(500)
Declare @output varchar(1000)
--************* Create the CDO.Message Object ************************
EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT
--***************Configuring the Message Object ******************
-- This is to configure a remote SMTP server.
-- http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_schema_configuration_sendusing.asp
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'
-- This is to configure the Server Name or IP address.
-- Replace MailServerName by the name or IP of your SMTP Server.
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', 'smtp.bbeyond.nl'
-- Save the configurations to the message object.
EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null
-- Set the e-mail parameters.
EXEC @hr = sp_OASetProperty @iMsg, 'To', @To
EXEC @hr = sp_OASetProperty @iMsg, 'From', @From
EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject
-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
EXEC @hr = sp_OASetProperty @iMsg, 'HTMLBody', @Body
EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL
-- Sample error handling.
IF @hr <>0
select @hr
BEGIN
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END
-- Do some error handling after each step if you have to.
-- Clean up the objects created.
EXEC @hr = sp_OADestroy @iMsg
GO
View 5 Replies
View Related
Jul 24, 2006
Hello,I want to share my experiences about using insert into exec which mayhelp others .Using SQL Server 2000, SP3 .Two Proceduers - Parent SP caliing a Child SP (nested ) . No Explicittransactions .I have defined a # table in Parent SP and calling a Child SP like thisinsert into #temp exec childsp ......Child SP has Select * from local # temp table ( local to child SP) as the last statement .When number of records are less ( around 1000 - 5000) Parent SPexecutes but slow .When the Child SP returns higher number of rows ( 1,00,000 or more )the SP will be running for hours with out completion .Although executing the child SP , with exec ChildSP .... with sameparameters it is completed in 2 mins for 3,00,000 rows .Resolution : - Define a temp table (say #tempChild ) in the Parent SP..In the Child SP instead of select * replace with insert into#tempChild select * from ...Also note that this problem is not noticed in SQL 2000 Server with SP4..This may be due to SP executing in implicit transactions .
View 1 Replies
View Related
Sep 19, 2007
I try to select a store procedure in SqlExpress2005 which inside store procedure execute another store procedure,
When I select it but it prompt error messages "An INSERT EXEC statement cannot be nested.".
In Fire bird /Interbase store procedure we can nested. Below are the code;
declare @dtReturnData Table(doccode nvarchar(20), docdate datetime, debtoraccount nvarchar(20))
Insert Into @dtReturnData
Exec GetPickingList 'DO', 0, 37256, 'N', 'N', 'YES'
Select doccode, docdate, debtoraccount
From @dtReturnData
Inside the GetPickList It will do like this, but most of the code I not included;
ALTER PROCEDURE GETPICKINGLIST
@doctype nvarchar(2),
@datefrom datetime,
@dateto datetime,
@includegrn char(1),
@includesa char(1),
@includedata nvarchar(5)
AS
BEGIN
declare @dtReturnData Table(doccode nvarchar(20),
docdate datetime,
debtoraccount nvarchar(20))
IF (@DOCTYPE = 'SI')
BEGIN
Insert Into @dtSALESINVOICEREGISTER
Exec SALESINVOICEREGISTER @DateFrom, @DateTo, @IncludeGRN, @IncludeSA, @IncludeData
END
ELSE
BEGIN
Insert Into @dtDELIVERYORDERREGISTER
Exec DELIVERYORDERREGISTER @DateFrom, @DateTo, @IncludeGRN, @IncludeSA, @IncludeData
END
Select doccode,docdate,debtoraccount From @dtReturnData
END
So how can I select a nested store procedure? can someone help me
View 1 Replies
View Related
Nov 9, 2006
Hi,
I have written a master proc which calls another proc (say proc1).
This proc1 has insert-exec statements, for eg insert into #temp exec proc1.
i.e. multiple times the proc would be nested.
This the err thrown :
An INSERT EXEC statement cannot be nested.
Is it possible to resolve it..
View 5 Replies
View Related
Jun 16, 2006
Hi All,
I am having a problem with nested insert exec. Say for example I have three stored procedure procA, procB and procC.
I am executing procedure procC in procB; I am storing the values returned by procC in a tempTable. The code is
INSERT INTO #Temp
EXEC procC
And in procA, I am executing procB, and the values returned by procB are stored in another temp table. The code is
INSERT INTO #TempOne
EXEC procC
When I execute the procA, I am getting error as
An INSERT EXEC statement cannot be nested.
My requirement is like this, please give me a solution.
Thanks
View 5 Replies
View Related
