You can use a numbers table: DECLARE @Date1 DATE, @Date2 DATE SET @Date1 = ‘20150528’ SET @Date2 = ‘20150531’ SELECT DATEADD(DAY,number+1,@Date1) [Date] FROM master..spt_values WHERE type=”P” AND DATEADD(DAY,number+1,@Date1) < @Date2 Results: ╔════════════╗ ║ Date ║ ╠════════════╣ ║ 2015-05-29 ║ ║ 2015-05-30 ║ ╚════════════╝
If going to SQL Server 2008 is an option for you, there’s a new feature called “Table-valued parameters” to solve this exact problem. Check out more details on TVP here and here or just ask Google for “SQL Server 2008 table-valued parameters” – you’ll find plenty of info and samples. Highly recommended – if you … Read more
Don’t use CREATE TYPE to return a polymorphic result. Use and abuse the RECORD type instead. Check it out: CREATE FUNCTION test_ret(a TEXT, b TEXT) RETURNS RECORD AS $$ DECLARE ret RECORD; BEGIN — Arbitrary expression to change the first parameter IF LENGTH(a) < LENGTH(b) THEN SELECT TRUE, a || b, ‘a shorter than b’ … Read more
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
EDIT: My original answer for EF4.1 (below) is now out of date. Please see the answer below from Diego Vega (who works on the EF team at Microsoft)! @gsharp and Shawn Mclean: Where are you getting this information? Don’t you still have access to the underlying ObjectContext? IEnumerable<Customer> customers = ((IObjectContextAdapter)this) .ObjectContext.ExecuteStoreQuery<Customer>(“select * from customers”); … Read more
This is one way to do it: SQL> set serveroutput on SQL> CREATE OR REPLACE TYPE MyType AS VARRAY(200) OF VARCHAR2(50); 2 / Type created SQL> CREATE OR REPLACE PROCEDURE testing (t_in MyType) IS 2 BEGIN 3 FOR i IN 1..t_in.count LOOP 4 dbms_output.put_line(t_in(i)); 5 END LOOP; 6 END; 7 / Procedure created SQL> DECLARE … Read more
QueryMultiple supports the ability to deal with multiple result sets. The only design restriction we added was totally disabling buffering for the grid reader. This means the whole API is streaming. In the simplest case you can use: var grid = connection.QueryMultiple(“select 1 select 2”); grid.Read<int>().First().IsEqualTo(1); grid.Read<int>().First().IsEqualTo(2); In the slightly more sophisticated case you can … Read more
SELECT … INTO … only works if the table specified in the INTO clause does not exist – otherwise, you have to use: INSERT INTO dbo.TABLETWO SELECT col1, col2 FROM dbo.TABLEONE WHERE col3 LIKE @search_key This assumes there’s only two columns in dbo.TABLETWO – you need to specify the columns otherwise: INSERT INTO dbo.TABLETWO (col1, … Read more
I think you’re missing something here. This is how you can get multiple results from stored procedure using mysqli prepared statements: $stmt = mysqli_prepare($db, ‘CALL multiples(?, ?)’); mysqli_stmt_bind_param($stmt, ‘ii’, $param1, $param2); mysqli_stmt_execute($stmt); // fetch the first result set $result1 = mysqli_stmt_get_result($stmt); // you have to read the result set here while ($row = $result1->fetch_assoc()) { … Read more
Since a stored procedure is created, altered and dropped using queries you actually CAN manage them using phpMyAdmin. To create a stored procedure, you can use the following (change as necessary) : CREATE PROCEDURE sp_test() BEGIN SELECT ‘Number of records: ‘, count(*) from test; END// And make sure you set the “Delimiter” field on the … Read more