w3hello.com logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML videos Categories
Returning multiple values in stored procedure
Your query did not work for me; it contained an error. Add a comma after 'Amount' in your select: select a.amount as Amount, b.EmployeeName from salesTable a left outer join nameEmployees b on(b.id=a.employeeId) where ItemId=@ItemId By the way, here was my test, which worked fine. You may want to double check the data in your tables as well: CREATE TABLE #salesTable ( ItemId int, amount money, employeeId int ) INSERT INTO #salesTable ( ItemId, amount, employeeId ) VALUES ( 1001, 100.00, 1 ) INSERT INTO #salesTable ( ItemId, amount, employeeId ) VALUES ( 1001, 101.00, 1 ) INSERT INTO #salesTable ( ItemId, amount, employeeId ) VALUES ( 1001, 102.00, 1 ) INSERT INTO #salesTable ( ItemId, amount, employeeId ) VALUES ( 1001, 103.00, 1 ) INSERT INTO #salesTable ( ItemId, amoun

Categories : SQL

Returning Max with a stored procedure
Try with command5.Parameters.AddWithValue("@UniqueID", 0L) The AddWithValue determines the DataType of the parameter to pass to the underlying engine looking at the datatype of the second parameter. You pass a string and this is certainly wrong. In alternative you could define explicitly the parameter Dim parameter = new SqlParameter("@UniqueID", SqlDbType.BigInt) parameter.Direction = ParameterDirection.Output parameter.Size = 8 parameter.Value = 0 command5.Parameters.Add(parameter) And, as last but fundamental steps, don't forget to specify that this command executes a storedprocedure and then execute the command command5.CommandType = CommandType.StoredProcedure command5.ExecuteNonQuery() As an alterative, but this requires a change to your stored procedure, is to

Categories : SQL

Returning table from CLR stored procedure
To my knowledge, the only way to send results that did not originate from the context connection is to build a new result set metadata object and then send the rows up one at a time. Something like: private static void sendResultsetRecords(SqlDataReader dataReader) { SqlMetaData[] meta = createMetaDataForResultset(dataReader); SqlContext.Pipe.SendResultsStart(new SqlDataRecord(meta)); sendEachRecordOfData(dataReader, meta); SqlContext.Pipe.SendResultsEnd(); } You can find the complete implementation in the tSQLt.ResultsetFilter method that is part of the tSQLt database unit testing framework for SQL Server. The relevant code starts at line 56 of this file: http://sourceforge.net/p/tsqlt/code/HEAD/tree/tSQLtCLR/tSQLtCLR/ResultSetFilter.cs#l5

Categories : Sql Server

Returning result from stored procedure
Your finalexecution procedure contains error. You should use IS or AS instead of DECLARE. Check documentaion for more details. Also in case if any error, you can check all_errors view to find out what is causing the error.

Categories : SQL

Returning results from stored procedure as IEnumerable
Your SqlCommand does not have a corresponding connection. Either use: _command.Connection = conn; or create the SqlCommand by using conn.CreateCommand(...)

Categories : C#

Check if a field returning from stored procedure has value?
Change your stored procedure to: ALTER PROCEDURE [dbo].[o_By_RNum] @rNum varchar(50) ,@ServiceType varchar(50) ,@Direction varchar(1) = NULL ,@VendorID int AS BEGIN SELECT ISNULL(o_num,0) FROM RemoteOrder WHERE r_num LIKE @rNum AND r_ServiceType = @ServiceType AND (@Direction is Null OR Direction = @Direction) AND VendorID = @VendorID END this way you will have actual SELECT that will show results in Management Studio. Then instead of last 4 lines after command.Parameters.Add("@VendorID", SqlDbType.Int).Value = IIf(IsNothing(VENDORID), DBNull.Value, VENDORID) that add RETURN_VALUE parameter and ExecuteNonQuery just do RetVal = command.ExecuteScalar()

Categories : SQL

SQL Server stored procedure not returning a select result upon an error
When encountering an error, the statement execution is stopped and only the error is returned. Add a try/catch block in there, then hard-code the error that you want to return (since you want it to be "2").

Categories : SQL

Two SELECT statements in one stored procedure, one supplying input for another and the other returning more than one row
You can do something like this WITH first AS ( SELECT val1, val2, val3, val4 FROM Table1 WHERE 1 = 1 ), second AS ( SELECT val1, MIN(CASE WHEN rnum = 1 THEN val5 END) val51, MIN(CASE WHEN rnum = 1 THEN val6 END) val61, MIN(CASE WHEN rnum = 2 THEN val5 END) val52, MIN(CASE WHEN rnum = 2 THEN val6 END) val62, MIN(CASE WHEN rnum = 3 THEN val5 END) val53, MIN(CASE WHEN rnum = 3 THEN val6 END) val63 FROM ( SELECT t2.val1, val5, val6, ROW_NUMBER() OVER (PARTITION BY t2.val1 ORDER BY (SELECT 1)) rnum FROM Table2 t2 JOIN first f ON t2.val1 = f.val1 ) a GROUP BY val1 ) SELECT * FROM first f JOIN second s ON f.val1 = s.val1 Here is SQLFiddle demo

Categories : SQL

Stored Procedure has void type instead of returning my desired result
You are confused by the various t-sql function types. What you want is: create function [dbo].[ShowProjectByNameOfPerson] (@name as nvarchar) returns table as return ( select Personality.Id, Personality.FullName, COUNT(*) as cnt from Personality inner join Personal_project on Personality.Id = Personal_project.PersonalityId_FK where Personality.FullName like N'%' + @name + N'%' group by Personality.Id, Personality.FullName order by cnt desc ); See the MSDN reference to understand the syntax variations and their meaning: http://msdn.microsoft.com/en-us/library/ms186755.aspx Also, you should try to avoid using temporary tables needlessly (see how I changed your code).

Categories : Asp Net

Retaining existing table values using a Stored Procedure with Optional Parameter Values
One "better" (in terms of syntax simplicity) way to do that would be to use NULLIF() and ISNULL/COALESCE instead of the CASE expressions: UPDATE [Users] SET UserKey = COALESCE(NULLIF(@UserKey , ''), UserKey ), UserName = COALESCE(NULLIF(@UserName , ''), UserName ), CategoryID = COALESCE(NULLIF(@CategoryID, 0 ), CategoryID) WHERE UserID = @UserID ; To explain the absence of LTRIM() and RTRIM(), trailing spaces are ignored in string comparison in Transact-SQL. That means that a string of (any number of) spaces would match another string of (any other number of) spaces, as well as an empty string. (And, for that matter, the LEN() function also ignores trailing spaces when calculating the length of a string, and so, for instance, LEN(SPACE(10)) would r

Categories : Sql Server

get count and another values in stored procedure
Try following store procedure: ALTER procedure [dbo].[carcallvalidation] @carid nvarchar(100)=null as begin select sum(case when t.TBarcode is null then 0 else 1 end) as cnt, t.Paid,t.Status,t.DelDate from Transaction_tbl t where TBarcode=@carid group by t.Paid,t.Status,t.DelDate; declare @transid integer=null; select @transid= t1.transactID from Transaction_tbl t1 where TBarcode=@carid; if(select count(k.transactid) from KHanger_tbl k where k.transactid=@transid)>0 begin return 1 end end

Categories : Sql Server

Extracting xml tag and values with sql stored procedure
Your xml fragment is not a valid XML. Tag names cannot start with a number or punctuation character. Now if you were having an xml fragment like this DECLARE @xml XML = '<root> <_3018>1</_3018> <_3017>2</_3017> </root>' Then you can get names and values with a query SELECT CAST(REPLACE(N.value('local-name(.)', 'VARCHAR(12)'), '_', '') AS INT) tagname, N.value('(.)[1]', 'INT') tagvalue FROM @xml.nodes('/root/*') as T(N) Output: | TAGNAME | TAGVALUE | ---------------------- | 3018 | 1 | | 3017 | 2 | Here is SQLFiddle demo

Categories : SQL

While stored procedure how to take values from multiple tables
SELECT Ename FROM transaction INNER JOIN master ON master.Ecode = transaction.Ecode INNER JOIN Employeemaster ON Employeemaster.Ecode = master.Ecode WHERE transaction.status = 0

Categories : SQL

Can we return values in between when stored procedure is running?
if you're using SQL Server, it's possible short answer: CREATE PROCEDURE ProcedureName AS BEGIN EXEC storedProcedureName1 raiserror('Proc1 completed, 0, 0) with nowait EXEC storedProcedureName2 raiserror('Proc2 completed, 0, 0) with nowait EXEC storedProcedureName3 END long answer: see SQL Server stored procedure that returns processed records number

Categories : C#

Return values from stored procedure that contains a cursor
You can add data to temporary table and select from there CREATE TABLE #TEMPDATA(ID int,COUNTRY varchar(100)) BEGIN IF @NTCount > 0 BEGIN INSERT INTO #TEMPDATA(ID,OUNTRY) VALUES(@NTCount ,@Description) END FETCH NEXT FROM NewsCURSOR INTO @NewsTypeID,@Description END Then after the cursor SELECT ID,COUNTRY FROM #TEMPDATA

Categories : Sql Server

Getting a SqlException when I try to insert null values through a stored procedure
Set the default value for the parameter as null, try this: @CompanyID INT, @email VARCHAR(50) = null, @phone VARCHAR(50) = null, @fax VARCHAR(50) = null, @fName VARCHAR(50) = null, @lName VARCHAR(50), @sendVia VARCHAR(50), @default BIT, @repo TINYINT, @fail TINYINT, @borrow TINYINT, @loan TINYINT

Categories : C#

How call Table Values function from stored procedure
You are not specific in your question, there are different ways a table valued function can be called (depending on need): create procedure Test ( @someParam varchar(50), @someParam2 varchar(50) ) as begin set nocount on ... select tf.Col1, tf.Col2, tf.Col3, tf.Col4, tf.Col5 from tableValuedFunction0() tf select tf.Col1, tf.Col2 from tableValuedFunction1(@someParam) tf create table #temp (...) insert into #temp (...) select tf.Col1, tf.Col2, tf.Col3 from tableValuedFunction2(@someParam, @someParam2) tf create table #temp2 (...) insert into #temp2 (...) select t.SomeCol, tf.Col1, tf.Col2 from someTable t cross apply tableValuedFunction3(@someParam2, t.SomeOtherCol) tf update t set t.SomeCol = tf.Value

Categories : Sql Server

Stored procedure to not to retrieve null values from table
You should try this Alter PROCEDURE [dbo].[p_Get_HotelNamess] AS BEGIN SET NOCOUNT ON; SELECT Hotel_Info.Hotel_Name, Hotel_Info.HotelID FROM Hotel_Info WHERE Hotel_Info.Hotel_Name IS NOT NULL END

Categories : Sql Server

oracle how to write in stored procedure to get specific values
A procedure to do this would require two input parameters (PaymentType and bank) and two output parameters (param1 and param2). Note that Oracle parameters, unlike SQL Server and MySQL, are set off with a colon (:) rather than an "at" sign (@), so in your C# code you'll need to name them :PaymentType, :bank, :param1, and :param2. Also note that when you write the C# code you must name the parameters exactly as they're named in Oracle (except for the colon in front). If you don't, the C# code will compile fine but it will fail at runtime. If your first parameter is PaymentType in Oracle, it should be called :PaymentType in C#. One more note: I'm not sure how your Config and Config_Value tables are related. Based on the test data, it could be through Config_Value.Config_Value_Id or Config

Categories : Oracle

SSRS multi-value parameter using a stored procedure has too many values
When you make your @CompanyID parameter query. Add an option for 'Select All'. SELECT 'Select All' AS CompanyID, 1 as myOrder UNION Select CAST(CompanyID AS VARCHAR), 2 as myOrder FROM CompanyTable ORDER BY myOrder With this the user doesn't have to select all 4000 values they can just select the 'Select All' option and it will select everything for them. And update your proc_getCompanyRecord to handle the new 'Select All' value of CompanyID. CREATE PROCEDURE [dbo].[proc_GetCompanyRecord] ( @Year AS VARCHAR(MAX) = NULL, @CoreCompanyId AS VARCHAR(MAX) = NULL, @CompanyId AS VARCHAR(MAX) = NULL, @OwnerId VARCHAR(MAX) = NULL ) --THE REST OF MY QUERY HERE WHICH PULLS ALL OF THE NEEDED COLUMNS WHERE CoreCompany.CoreCompanyId in (SELECT * FROM ufnSplit (@CoreCom

Categories : Reporting Services

Inserting values to a table returned by a Stored Procedure in Oracle 11g
Assuming that the structure of your temporary table will be the same as a row returned by the ref-cursor (the same number of columns and the same types of corresponding columns), then the below code should work: declare my_row temp_table_name%rowtype; q_bio SYS_REFCURSOR; begin my_str_proc( 'x', 'y', q_bio ); LOOP FETCH q_bio INTO my_row; exit when q_bio%NOTFOUND; INSERT INTO temp_table_name VALUES my_row ; END LOOP; CLOSE q_bio; end; /

Categories : Oracle

How to get comma separated values inside Oracle stored procedure
As one of the approaches, the regexp_substr() and regexp_count() regular expression functions can be used to split the source string into table rows and then you can easily include that result in your IN clause: -- splitting a comma separated string select regexp_substr(FlightNumbers, '[^,]+', 1, level) as num from dual connect by level <= regexp_count(FlightNumbers, '[^,]+') So, for instance, if you pass in FlightNumbers as '0620,0712,0154', the above query will produce the following result: NUM -------- 0620 0712 0154 Having that at hand, your final query would be: with numbers(num) as( select regexp_substr(FlightNumbers, '[^,]+', 1, level) as num from dual connect by level <= regexp_count(FlightNumbers , '[^,]+') ) SELECT * -- do not forget INTO cla

Categories : Oracle

Passing array of values to SQL stored procedure in .net compact framework
If you need to pass only ID's then maybe this function will help you: ALTER FUNCTION [dbo].[fn_SplitIds] ( @text nvarchar(max) ) RETURNS @Ids TABLE ( Id int UNIQUE ) AS BEGIN declare @iStart int, @iPos int if substring( @text, 1, 1 ) = ',' begin set @iStart = 2 insert into @Ids values( null ) end else set @iStart = 1 while 1=1 begin set @iPos = charindex( ',', @text, @iStart ) if @iPos = 0 set @iPos = len( @text )+1 if @iPos - @iStart > 0 insert into @Ids values ( cast(substring( @text, @iStart, @iPos-@iStart ) as int)) else insert into @Ids values( null ) set @iStart = @iPos+1 if @iStart > len( @text ) br

Categories : C#

How to write a stored procedure to insert values in multiple tables
You cannot pass a variable number of variables to a procedure, nor a non-scalar type. A possible trick would be building a temporary table with the addresses before calling this procedure. Either the temporary table is pre-determined, or pass its name as a VARCHAR parameter(and use it to build dynamic SQL statements). Eg: CREATE PROCEDURE spAddPerson (tmp_table VARCHAR(10), ...) BEGIN ... PREPARE s AS CONCAT( 'INSERT INTO address (line1, ...) VALUES SELECT * FROM ', tmp_table ); EXECUTE s; ... END -- use it like this CREATE TEMPORARY TABLE tmp_addresses (line1 VARCHAR(255), ...); INSERT INTO tmp_addresses VALUES ('1 Cherry Lane'), ... ; CALL spAddPerson ('tmp_addresses', ...); However, I would rather split the action in two parts. Do you really want to pre

Categories : Mysql

pass checkedlistbox checked item values to stored procedure
checkedListBoxBids.CheckedItems will return collection of checked items. based on your need you can get each checked item or just one of item like below foreach(object itemChecked in checkedListBoxBids.CheckedItems) { DataRowView item = itemChecked as DataRowView; string listingID= item["ListingID"]; } assume you bind as below checkedListBoxBids.DataSource = dt; checkedListBoxBids.DisplayMember = "ListingName"; checkedListBoxBids.ValueMember = "ListingID";

Categories : C#

How to use values generated from insert statements inside a stored procedure?
There are several problems: variable declarations should come first using DECLARE you meant to use a local (stored procedure level) variable instead of user variable. For that don't use @ in front of a variable name. there is a typo in first INSERT. It should be VALUES instead of VALUE use LAST_INSERT_ID() function to retrieve a value of an auto_incremented column for a row that has been just inserted instead of Your code might look something like this DELIMITER $$ CREATE PROCEDURE test( IN a VARCHAR(255), IN b VARCHAR(255), IN c BIGINT(20) ) BEGIN DECLARE i BIGINT(20); // or BIGINT(20); INSERT INTO tableA (datetime_created) VALUES (NOW()); SET i = LAST_INSERT_ID(); INSERT INTO tableB(attr1, attr2, attr3, attr4) VALUES (a, b, c, i); END$$ DELIMITER ; In t

Categories : Mysql

Operation with values in query inside stored procedure (mysql)
Don't create a temporary table unless you need the data after the procedure call. Either way, in order to iterate over the results of a SELECT query, use a CURSOR. A simple example is provided in the linked manual page.

Categories : Mysql

mySQL create an INSERT stored procedure of unknown number of values
There is no simple way to pass and use multiple values to a MySql stored procedure. Your best bet is to pass a comma delimited string of values and then split it inside your SP and MySql doesn't provide means for that out of box. That being said I believe what you actually need is prepared statements. It's fast and it's safe. function addInvitedUsers($invitingUser, $invitedUsers) { //check if all the parameters are set if (isset($invitingUser) && isset($invitedUsers)) { // create a prepared statement for insert $query = "INSERT INTO Invited_Users (Inviting_id, Invited_Id) VALUES (?, ?)"; $stmt = $this->prepare($query); // you need to implement prepare method in your class // bind parameters $stmt->bind_param("ii", $invitin

Categories : PHP

How can I prevent MySQL from caching variable values between Stored Procedure calls?
@ are user variables, which are session specific. You want to use local variables which should not have the @ prefix. Remove it from the stored procedure declarations.

Categories : Mysql

How to insert PHP array values using an stored procedure SQL SERVER 2008 R2(mssql)
$sql = "INSERT INTO table column(c1,c2) VALUES('d1','d2'),('dd1','dd2'),('ddd1','ddd2')";// this is the basic sql command $data = array('data1','data2','data3'); $data = '('.implode(', ',$data).')'; $sql = "INSERT INTO table column(c1,c2) VALUES $data"; $data = array(array('1','2','3'),array('4','5','6'),array('7','8','9')); $xa = array(); $a = 0; foreach($data as $dt) { $xa[$a] = '('.implode(',',$dt).')'; ++$a; } $data = '('.implode(',',$xa).')'; $sql = "INSERT INTO table column(c1,c2) VALUES $data";

Categories : PHP

How to execute stored procedure multiple times to insert values in the table
Well assuming that you're using SSMS, then you can do this old school: Make the EXEC command into a single line, replace the arguments with "#", cut and paste that line 100 times. Then replace the "#"'s with the actual arguments from your list and execute. Here's an example with 10 lines to get you started: USE [Students] GO DECLARE @rt int EXEC @rt = [School].[Student_Entry] @StudentName=N'#', @StudentClass=N'#'; SELECT 'Return Value'=@rt EXEC @rt = [School].[Student_Entry] @StudentName=N'#', @StudentClass=N'#'; SELECT 'Return Value'=@rt EXEC @rt = [School].[Student_Entry] @StudentName=N'#', @StudentClass=N'#'; SELECT 'Return Value'=@rt EXEC @rt = [School].[Student_Entry] @StudentName=N'#', @StudentClass=N'#'; SELECT 'Return Value'=@rt EXEC @rt = [School].[Student_Entry] @StudentName

Categories : Sql Server

SQL Server Linked server calls Oracle stored procedure returning a result set
According to http://msdn.microsoft.com/en-us/library/ms188427.aspx it is not possible to call stored procedures using OPENQUERY, you must use the four-part name, such as... EXEC SeattleSales.master.dbo.xp_msver

Categories : Sql Server

How to assign multiple select query values into declared variables in a stored procedure
I don't know what you're doing - but selecting multiple values and assigning them to multiple variables in T-SQL works just fine - inside or outside of a stored procedure. If you have the AdventureWorks sample database - try this: CREATE PROCEDURE dbo.GetAddressTypeDetails (@AddressTypeID INT, @Name VARCHAR(50) OUTPUT, @Modified DATETIME OUTPUT) AS BEGIN SELECT @Name = Name, @Modified = ModifiedDate FROM [Person].[AddressType] WHERE [AddressTypeID] = @AddressTypeId END and then execute this stored procedure like this: DECLARE @AddressTypeID INT DECLARE @Name VARCHAR(50) DECLARE @Modified DATETIME -- TODO: Set parameter values here. SET @AddressTypeID = 5 EXECUTE [dbo].[GetAddressTypeDetails] @AddressTypeID, @Name OUTPUT, @Modified OUTPUT SELECT

Categories : Sql Server

Calling a stored procedure from an insert in a stored procedure
I ended up using a cursor to execute the SP for each row. There will only ever be one or two rows executed at a time - so this solution works (even if it isn't exactly beautiful). ALTER PROCEDURE Lead_CopySecuritiesToOppo -- Add the parameters for the stored procedure here @lead_leadID int, @oppo_opportunityID int, @user_userID int AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; declare @secu_SecurityID char( 11 ) declare @newsecu_SecurityID int set rowcount 0 select * into #mytemp from Security where secu_LeadId = @lead_leadID set rowcount 1 select @secu_SecurityID = secu_SecurityID from #mytemp --Cursor through all the returned security rows while @

Categories : Sql Server

write stored procedure inside the other stored procedure
You could cahnge your Procedure to a Function like this Create FUNCTION [dbo].[test] (@dec NUMERIC(18, 2)) RETURNS Varchar(50) AS BEGIN DECLARE @Mns DECIMAL DECLARE @Average Varchar(50) SELECT @Mns = @dec % 60; SELECT @Average = CONVERT(VARCHAR(10), (CONVERT(DECIMAL(10), @dec / 60))) + ':' + CONVERT(VARCHAR, @Mns) RETURN @Average END And use ist like .... SELECT l.LocName ,v.Vtype ,SUM(DATEDIFF(MI, t.Paydate, t.DelDate)) AS TotalDiff ,[dbo].[test]( CONVERT(DECIMAL(10, 1), AVG(CONVERT(NUMERIC(18, 2), DATEDIFF(MI, t.Paydate, t.DelDate)))) ) AS Average FROM Transaction_tbl t ....

Categories : Sql Server

Stored procedure inserting values into a table using Table-Valued Parameters
You can use the MERGE statement to capture the category IDs. DECLARE @changes TABLE (ChangeID VARCHAR(10), Id INTEGER); DECLARE @JustSomeRandomVariable INT; MERGE Category AS TARGET USING @data AS SOURCE ON TARGET.Category = SOURCE.s WHEN NOT MATCHED THEN INSERT ([Category]) VALUES (SOURCE.s) WHEN MATCHED THEN UPDATE SET @JustSomeRandomVariable = 1 OUTPUT $action, inserted.Id INTO @changes; The random variable in the merge statement makes sure that updates get logged into the @changes table variable. Now you can use the @changes to update your link table. INSERT INTO Link SELECT ProductID, ChangeID FROM @changes Just retrieve the required ProductID with a simple select query. EDIT: This could potentially result in double records in the Link table. You might need

Categories : Sql Server

Wrapping system procedure in View or Stored Procedure
To supply column metadata from the stored procedure consumable by Entity Framework you could do CREATE PROCEDURE dbo.GetHardDriveFreeSpaceInMegabytes AS BEGIN SET NOCOUNT ON; DECLARE @xp_fixeddrives TABLE ( drive nvarchar(1), [MB free] int ) INSERT INTO @xp_fixeddrives EXEC master..xp_fixeddrives SELECT * FROM @xp_fixeddrives END

Categories : Dotnet

Get mysql other stored procedure to new store procedure data
Unfortunately you still can't do that in Mysql. A possible solution is to insert into a temporary table from the first stored procedure and use it in the second. CREATE TEMPORARY TABLE tmp_result (`ID` int, `NAME` varchar(1), `SEX` varchar(1)); CREATE PROCEDURE sp_one() INSERT INTO tmp_result SELECT 1 id, 'A' name, 'M' sex UNION ALL SELECT 2, 'B', 'F' UNION ALL SELECT 3, 'C', 'M'; CREATE PROCEDURE sp_two() SELECT * FROM tmp_result; CALL sp_one(); CALL sp_two(); DROP TEMPORARY TABLE tmp_result;

Categories : Mysql

MySQL stored procedure return result before end of procedure
what kind of stuff are you wanting to keep doing after the select? Maybe you could insert the select query into a temp table, continue doing other stuff, then finally select at the very end? The temp table values would still be as if they were returned mid routine but you could still continue doing the "other stuff." Edit from below comment: To me this should be handled from the application doing the following -Break the current stored procedure up into 2 parts Call the 1st Insert Stored Procedure from the code that takes the first part of the string and inserts it into the database. Return the Id to the application The application takes the id it received back, and calls the second stored procedure(time consuming one) and passes 2 parameters. The id and the rest of the string. T

Categories : Mysql

use sql stored procedure to insert data which is returned from a query stored in a table
You may check this fiddle The code of the Stored Procedure is: CREATE PROCEDURE [dbo].[spInsertStuff] @cli_id int AS DECLARE @SQL AS VARCHAR(MAX) DECLARE @ADL_ID AS INT DECLARE MyCURSOR CURSOR FOR SELECT QUERY, ADL_ID FROM ASSESSMENT_DATALABEL_LIST OPEN MyCURSOR FETCH NEXT FROM MyCURSOR INTO @SQL, @ADL_ID WHILE @@FETCH_STATUS = 0 BEGIN SET @SQL = REPLACE(@SQL,'@KeyFieldValue',@cli_id) DECLARE @Temp AS TABLE ([Value] [nvarchar](50)) INSERT INTO @Temp EXEC (@SQL) INSERT INTO Item_Destination (ItemType, ItemCaption, ItemValue) SELECT ItemType, ItemCaption, (SELECT [Value] FROM @Temp) FROM Item_Source WHERE Item_Source.adl_ID = @ADL_ID DELETE FROM @Temp FETCH NEXT FROM

Categories : SQL



© Copyright 2017 w3hello.com Publishing Limited. All rights reserved.