The easy way is to right-click on the procedure in Sql Server Management Studio (SSMS), select ‘Execute stored procedure…” and add values for the input parameters as prompted. SSMS will then generate the code to run the procedure in a new query window, and execute it for you. You can study the generated code to … Read more
CHARINDEX() searches for a substring within a larger string, and returns the position of the match, or 0 if no match is found if CHARINDEX(‘ME’,@mainString) > 0 begin –do something end Edit or from daniels answer, if you’re wanting to find a word (and not subcomponents of words), your CHARINDEX call would look like: CHARINDEX(‘ … Read more
I am not a fan of stored procedures Stored Procedures are MORE maintainable because: * You don’t have to recompile your C# app whenever you want to change some SQL You’ll end up recompiling it anyway when datatypes change, or you want to return an extra column, or whatever. The number of times you can … Read more
You can call a stored procedure in your DbContext class as follows. this.Database.SqlQuery<YourEntityType>(“storedProcedureName”,params); But if your stored procedure returns multiple result sets as your sample code, then you can see this helpful article on MSDN Stored Procedures with Multiple Result Sets
using (var conn = new SqlConnection(connectionString)) using (var command = new SqlCommand(“ProcedureName”, conn) { CommandType = CommandType.StoredProcedure }) { conn.Open(); command.ExecuteNonQuery(); }
I had the same problem as the original poster but the quoted answer did not solve the problem for me. The query still ran really slow from a stored procedure. I found another answer here “Parameter Sniffing”, Thanks Omnibuzz. Boils down to using “local Variables” in your stored procedure queries, but read the original for … Read more
Stored procedures are a batch of SQL statements that can be executed in a couple of ways. Most major DBMs support stored procedures; however, not all do. You will need to verify with your particular DBMS help documentation for specifics. As I am most familiar with SQL Server I will use that as my samples. … Read more
I realize this has already been marked as answered, but we used to do it like this: IF NOT EXISTS (SELECT * FROM sys.objects WHERE type=”P” AND OBJECT_ID = OBJECT_ID(‘dbo.MyProc’)) exec(‘CREATE PROCEDURE [dbo].[MyProc] AS BEGIN SET NOCOUNT ON; END’) GO ALTER PROCEDURE [dbo].[MyProc] AS …. Just to avoid dropping the procedure.
SQL Server 2016 (or newer) You can pass in a delimited list or JSON and use STRING_SPLIT() or OPENJSON(). STRING_SPLIT(): CREATE PROCEDURE dbo.DoSomethingWithEmployees @List varchar(max) AS BEGIN SET NOCOUNT ON; SELECT value FROM STRING_SPLIT(@List, ‘,’); END GO EXEC dbo.DoSomethingWithEmployees @List=”1,2,3″; OPENJSON(): CREATE PROCEDURE dbo.DoSomethingWithEmployees @List varchar(max) AS BEGIN SET NOCOUNT ON; SELECT value FROM OPENJSON(CONCAT(‘[“‘, … Read more
instead of below Code BEGIN INSERT INTO EmailsRecebidos (De, Assunto, Data) VALUES (@_DE, @_ASSUNTO, @_DATA) WHERE NOT EXISTS ( SELECT * FROM EmailsRecebidos WHERE De = @_DE AND Assunto = @_ASSUNTO AND Data = @_DATA); END replace with BEGIN IF NOT EXISTS (SELECT * FROM EmailsRecebidos WHERE De = @_DE AND Assunto = @_ASSUNTO AND … Read more