here is a portion of a procedure I use on my system to find text…. DECLARE @Search varchar(255) SET @Search=”[10.10.100.50]” SELECT DISTINCT o.name AS Object_Name,o.type_desc FROM sys.sql_modules m INNER JOIN sys.objects o ON m.object_id=o.object_id WHERE m.definition Like ‘%’+@Search+’%’ ORDER BY 2,1
Some advantages of LINQ over sprocs: Type safety: I think we all understand this. Abstraction: This is especially true with LINQ-to-Entities. This abstraction also allows the framework to add additional improvements that you can easily take advantage of. PLINQ is an example of adding multi-threading support to LINQ. Code changes are minimal to add this … Read more
Generally speaking I always look for a set based approach (sometimes at the expense of changing the schema). However, this snippet does have its place.. — Declare & init (2008 syntax) DECLARE @CustomerID INT = 0 — Iterate over all customers WHILE (1 = 1) BEGIN — Get next customerId SELECT TOP 1 @CustomerID = … Read more
A view represents a virtual table. You can join multiple tables in a view and use the view to present the data as if the data were coming from a single table. A stored procedure uses parameters to do a function… whether it is updating and inserting data, or returning single values or data sets. … Read more
The default collation for stored procedure parameters is utf8_general_ci and you can’t mix collations, so you have four options: Option 1: add COLLATE to your input variable: SET @rUsername = ‘aname’ COLLATE utf8_unicode_ci; — COLLATE added CALL updateProductUsers(@rUsername, @rProductID, @rPerm); Option 2: add COLLATE to the WHERE clause: CREATE PROCEDURE updateProductUsers( IN rUsername VARCHAR(24), IN … Read more
The most general difference between procedures and functions is that they are invoked differently and for different purposes: A procedure does not return a value. Instead, it is invoked with a CALL statement to perform an operation such as modifying a table or processing retrieved records. A function is invoked within an expression and returns … Read more
SELECT name, create_date, modify_date FROM sys.objects WHERE type=”P” ORDER BY modify_date DESC The type for a function is FN rather than P for procedure. Or you can filter on the name column.
SQLite has had to sacrifice other characteristics that some people find useful, such as high concurrency, fine-grained access control, a rich set of built-in functions, stored procedures, esoteric SQL language features, XML and/or Java extensions, tera- or peta-byte scalability, and so forth Source : Appropriate Uses For SQLite
use a cursor ADDENDUM: [MS SQL cursor example] declare @field1 int declare @field2 int declare cur CURSOR LOCAL for select field1, field2 from sometable where someotherfield is null open cur fetch next from cur into @field1, @field2 while @@FETCH_STATUS = 0 BEGIN –execute your sproc on each row exec uspYourSproc @field1, @field2 fetch next from … Read more
In the simple case you can do: var user = cnn.Query<User>(“spGetUser”, new {Id = 1}, commandType: CommandType.StoredProcedure).First(); If you want something more fancy, you can do: var p = new DynamicParameters(); p.Add(“@a”, 11); p.Add(“@b”, dbType: DbType.Int32, direction: ParameterDirection.Output); p.Add(“@c”, dbType: DbType.Int32, direction: ParameterDirection.ReturnValue); cnn.Execute(“spMagicProc”, p, commandType: CommandType.StoredProcedure); int b = p.Get<int>(“@b”); int c = p.Get<int>(“@c”); … Read more