For SQL Server 2005+ — sample table with data declare @t table(UserID int, StartDate datetime, EndDate datetime) insert @t select 1, ‘20110101’, ‘20110102’ union all select 1, ‘20110101’, ‘20110110’ union all select 1, ‘20110108’, ‘20110215’ union all select 1, ‘20110220’, ‘20110310’ union all select 2, ‘20110101’, ‘20110120’ union all select 2, ‘20110115’, ‘20110125’ — your … Read more
you can try this code : bool result=false; SqlCommand scCommand = new SqlCommand(“usp_CheckEmailMobile”, sqlCon); scCommand.CommandType = CommandType.StoredProcedure; scCommand.Parameters.Add(“@Name”, SqlDbType.VarChar, 50).Value = txtName.Text; scCommand.Parameters.Add(“@Email”, SqlDbType.NVarChar, 50).Value = txtEmailAddress.Text; scCommand.Parameters.Add(“@Password “, SqlDbType.NVarChar, 50).Value = txtPassword.Text; scCommand.Parameters.Add(“@CountryCode”, SqlDbType.VarChar.50).Value =ddlCountryCode.SelectedText; scCommand.Parameters.Add(“@Mobile”, SqlDbType.NVarChar, 50).Value = txtMobileNumber.Text; scCommand.Parameters.Add(“@Result “, SqlDbType.Bit).Direction = ParameterDirection.Output; try { if (scCommand.Connection.State == ConnectionState.Closed) { scCommand.Connection.Open(); } … Read more
This can’t be done, directly, because the output of an unbounded select in a stored procedure is a result set sent to the client, but not technically a table. The workaround is to let the proc put the data in a temporary table after creating the table for you. This table will be available only … Read more
We (musicpictures.com / eviscape.com) wrote that django snippet but its not the whole story (actually that code was only tested on Oracle at that time). Stored procedures make sense when you want to reuse tried and tested SP code or where one SP call will be faster than multiple calls to the database – or … Read more
Expanding a bit on the answers from @Guru and @Ronnis, you can hide the sequence and make it look more like an auto-increment using a trigger, and have a procedure that does the insert for you and returns the generated ID as an out parameter. create table batch(batchid number, batchname varchar2(30), batchtype char(1), source char(1), … Read more
One way is to use the Database property off the DbContext: SqlParameter param1 = new SqlParameter(“@firstName”, “Frank”); SqlParameter param2 = new SqlParameter(“@lastName”, “Borland”); context.Database.ExecuteSqlCommand(“sp_MyStoredProc @firstName, @lastName”, param1, param2); EF5 definitely supports that.
I guess you just forgot to include the following line in your post: DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; Your code is correct, but bug/strange behaviour of mysql causes the warning to appear even if it was handled. You can avoid that if you add a “dummy” statement to the end … Read more
While understanding scoping addresses the direct need, thought it might be useful to add a few more options to the mix to elaborate on the suggestions from the comments. Pass XML into the stored procedure Pass a table-valued parameter into the stored procedure 1. Pass XML into the stored procedure With XML passed into a … Read more
Dynamic SQL is merely where the query has been built on the fly – with some vendors, you can build up the text of the dynamic query within one stored procedure, and then execute the generated SQL. In other cases, the term merely refers to a decision made by code on the client (this is … Read more
You’d have to use a temp table like this. UNION is for SELECTs, not stored procs CREATE TABLE #foo (bar int …) INSERT #foo exec MyStoredProcedure 1 INSERT #foo exec MyStoredProcedure 2 INSERT #foo exec MyStoredProcedure 3 … And hope the stored procs don’t have INSERT..EXEC.. already which can not be nested. Or multiple resultsets. … Read more