Let's say I have to implement a piece of T-SQL code that must return a table as result. I can implement a table-valued function or else a stored procedure that returns a set of rows. What should I use? In short, what I want to know is: Which are the main differences between functions and stored procedures? What considerations do I hav...
If I create a Stored Procedure in SQL and call EXEC spStoredProcedure within the BEGIN/END TRANSACTION, does this other stored procedure also fall into the transaction? I didn't know if it worked like try/catches in C#... ...
I'm making a Stored Procedure that basically concatenates a long string with data from many records. I want to do: set @output = @output + 'aaa' + LINEBREAK How do I specify that line break? ...
Is it possible to send a list of IDs to a stored procedure from c# without dirty hacks? UPDATE Germs SET Mutated = ~Mutated WHERE (GermID IN (ids)) ...
How do you manage revisions of stored procedures? We have a BI solution on SQL Server 2005 with hundreds of stored procedures. What would be a good way to get these into subversion? recommended tools to script SP to files? ...
Hi, my goal is to write a stored proc that can collect all field values from multiple rows into one single output variable (maybe varchar(some_length)). It may seem strange solution but i've quite positive its the only one i can use at that situation im in. I have not used Firebird before and stored procs look way different than in other...
I have this script: select name,create_date,modify_date from sys.procedures order by modify_date desc I can see what procedures were modified lately. I will add a "where modify_date >= " And I'd like to use some system stored procedure, that will generate me : drop + create scripts for the (let's say 5 matching) stored procedures Ca...
I'm logged into a SQL Server 2005 database as a non-sa user, 'bhk', that is a member of the 'public' server role only. The following code tries to execute within a stored procedure called by user 'bhk'. This line of code... TRUNCATE TABLE #Table1 DBCC CHECKIDENT('#Table1', RESEED, @SequenceNumber) WITH NO_INFOMSGS causes this error......
I believe there is a discussion on this very topic somewhere on the net but I lost the url and I am unable to find it via googling. What I might try right now would be: ISessionFactoryHolder factoryHolder = ActiveRecordMediator<EntityClass>.GetSessionFactoryHolder(); ISession session = factoryHolder.CreateSession(typeof(EntityClass)); ...
I have created the following stored procedure.. CREATE PROCEDURE [dbo].[UDSPRBHPRIMBUSTYPESTARTUP] ( @CODE CHAR(5) , @DESC VARCHAR(255) OUTPUT ) AS DECLARE @SERVERNAME nvarchar(30) DECLARE @DBASE nvarchar(30) DECLARE @SQL nvarchar(2000) SET @SERVERNAME = Convert(nvarchar, (SELECT spData FROM dbSpecificData WHERE spLookup = 'CM...
I've googled around a bit, but maybe I didn't put the correct magik incantation into the search box. Does anyone know how to get output parameters from a stored procedure in Python? I'm using pymssql to call a stored procedure, and I'm not sure of the correct syntax to get the output parameter back. I don't think I can use any other db...
What is the correct way to do this? For example, how would I change a stored procedure with this signature: CREATE PROCEDURE dbo.MyProcedure @Param BIT = NULL AS SELECT * FROM dbo.SomeTable T WHERE T.SomeColumn = @Param So that giving @Param with a value of 1 or 0 performs the filter, but not specifying it or passing NULL perf...
If you are using php5 and mysql5, is there a substantial advantage to using stored procs over prepared statements? ( i read somewhere you may not get substantial performance gains from mysql5 stored proc) ...
I have a Stored procedure which schedules a job. This Job takes a lot of time to get completed (approx 30 to 40 min). I need to get to know the status of this Job. Below details would help me 1) How to see the list of all jobs that have got scheduled for a future time and are yet to start 2) How to see the the list of jobs running and ...
Edit: My problem is not a problem anymore: I have redo my performances tests and I have do a fatal stupid error: I had forget a x1000 to get seconds from milliseconds :/ Sorry for that guys. For info: - I do some 1900 updates per second from my PC to the DataBase server on local network. - 3.200 updates per second if the programs ...
I am trying to write a stored procedure which selects columns from a table and adds 2 extra columns to the ResultSet. These 2 extra columns are the result of conversions on a field in the table which is a Datetime field. The Datetime format field has the following format 'YYYY-MM-DD HH:MM:SS.S' The 2 additional fields which should be i...
I've created a stored procedure similar to the one below (I'm using this cut down version to try and figure our the problem). CREATE PROCEDURE bsp_testStoredProc AS BEGIN CREATE TABLE #tmpFiles ( AuthorName NVARCHAR(50), PercentageHigh INT ) -- Insert data into temp table SELECT AuthorName, PercentageHigh FROM #tmpFiles ORDER ...
I'm working on an application for work that is going to query our employee database. The end users want the ability to search based on the standard name/department criteria, but they also want the flexibility to query for all people with the first name of "James" that works in the Health Department. The one thing I want to avoid is to si...
If I am executing a stored procedure for basic queries, such as: SELECT ColA, ColB FROM MyTable WHERE ID = 123; SELECT * FROM MyTable,OtherTable WHERE MyTable.ID = OtherTable.ID ORDER BY CreatedAt desc Is there any benefit to converting those to a stored procedure if that happen frequently? When is it better to use a stored proc? Wh...
I have a stored procedure that returns 80 columns, and 300 rows. I want to write a select that gets 2 of those columns. Something like SELECT col1, col2 FROM EXEC MyStoredProc 'param1', 'param2' When I used the above syntax I get the error "Invalid Column Name". I know the easiest solution would be to change the stored procedure,...