Tag: stored-procedures

better way to assign stored procedures output values from a table select

Due to programming language restraints, my ERP system does not allow me to make advanced select queries, that´s why I need to rely on making a stored procedure on SQL Server, calling it from the ERP system and getting the result through an array. The code belows works ok, but I think it´s not the […]

Triggers / Stored Procs for Data Integrity

I have a table called Documents which stores filenames, notes etc. about documents. The relevant fields are: Document_ID – Autonumber Document_Type_ID – FK to a lookup table Document_Types Table_Unique_ID The Table_Unique_ID relates to any of the other IDs used in other tables and we know which table by the relevant Document_Type_ID. E.g. Document_Type_ID = 1 […]

Entity Framework6 Transaction vs Transaction handled in Stored Procedures

I have a stored procedure that executes a transaction, the code was written long time ago and the SP looks something like this: begin transaction begin try … commit transaction end try begin catch … rollback transaction end catch Now. I know that EF6 allows you to use transactions smarter: using (var db = myDbContext) […]

incorrect syntax in over and partition by with temporary tables

I have a partition-by function in my stored procedure which calls for a temporary table from a previous partition-by function. The table that I created with the previous partition by function is #tempVehicleManifestRow which in turn is what I call for the next partition by function. What happens is that it shoes the error “incorrect […]

ExecuteSqlCommand of EF always return -1

DbContext.Database.ExecuteSqlCommand(sql, parameters) I am using above method of Entity Framework to execute SP which takes table valued parameter and perform some operation. It is successfully doing its task and I can see the result in the database. But it always return -1, no matter the query is successfully executed or not. I think, it should […]

The formal parameter was not declared as an output parameter – SQL Server & PHP

I’m having a problem with a stored procedure from SQL Server with PHP. I’m working on a system that takes order lines, generates an order header and then places the order lines into the SQL Server database. Everything is going fine, until I try to add each order line to the database. I’ve implemented the […]

Stored procedure call error in SQL Server 2008 R2

I have a stored procedure in my SQL Server that starts with these lines code: CREATE PROCEDURE [dbo].[SP_Notify] — Add the parameters for the stored procedure here @UserName nvarchar(50), @lastDate datetime AS BEGIN — my code… I try to call the stored procedure using this code: DECLARE @data datetime DECLARE @Username nvarchar(50) SET @Username = […]

tsql – sp_executesql difference

I have a problem about the way of my orm tool execute procedures. This is how i execute: exec PostViewProc @Id=’18767F6A-FF7A-47E9-AF09-6DB8A2F3B20E’,@AuthorId=’5455D9B9-B25A-41BD-BD2C-C9CBAE87D629′ Returns 1 row as expected.. And this is how the orm tool generates it: exec sp_executesql N’EXEC [PostViewProc] @Id, @AuthorId’, N’@Id uniqueidentifier,@AuthorId uniqueidentifier’, @Id=’18767F6A-FF7A-47E9-AF09-6DB8A2F3B20E’,@AuthorId=’5455D9B9-B25A-41BD-BD2C-C9CBAE87D629′ Returns NOTHING! First one just works fine. But the orm-generated […]

SQL Server : Dynamic functions or reuse of stored procedures

I want to use some dynamic SELECT statements. The name of the table or column could be different, that’s why I use dynamic stored procedures like this: CREATE PROCEDURE [dbo].[test_sp]( @database nvarchar(70)= ” , @table_name nvarchar(70)= ” , @column nvarchar(70)= ” ) AS DECLARE @sql nvarchar(max)= ‘SELECT ‘ + @column + ‘FROM [‘ + @database […]

Dynamic generating columns in SQL Server procedure

I need help with my stored procedure. I have tables: Items (Id, ItemName), Suppliers, Quotes. Quotes table has following structure: Id, ItemId, SupplierId, Quote 1) Suppose I have N suppliers. How can I generate the table with the following columns: ItemName Supplier1Quote Supplier2Quote … Supplier-n-Quote To be exact, suppose I have 3 suppliers and the […]

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.