Here’s how I solved it: Working SQL Fiddle First I have create a function which splits the string value i.e. ‘1,2,4,5’ Split function: CREATE FUNCTION fn_Split(@text varchar(8000), @delimiter varchar(20) = ‘ ‘) RETURNS @Strings TABLE ( position int IDENTITY PRIMARY KEY, value varchar(8000) ) AS BEGIN DECLARE @index int SET @index = -1 WHILE (LEN(@text) … Read more
For part 2 of your question, see my answer to Stored procedure: pass XML as an argument and INSERT (key/value pairs) for an example of how to use XML within a stored procedure. EDIT: Sample code below is based on the specific example given in the comments. declare @MyXML xml set @MyXML = ‘<booksdetail> <isbn_13>700001048</isbn_13> … Read more
I needed something similar, but all DAYS instead of all MONTHS. Using the code from MatBailie as a starting point, here’s the SQL for creating a permanent table with all dates from 2000-01-01 to 2099-12-31: CREATE TABLE _Dates ( d DATE, PRIMARY KEY (d) ) DECLARE @dIncr DATE = ‘2000-01-01’ DECLARE @dEnd DATE = ‘2100-01-01’ … Read more
Try this DataSet ds = new DataSet(“TimeRanges”); using(SqlConnection conn = new SqlConnection(“ConnectionString”)) { SqlCommand sqlComm = new SqlCommand(“Procedure1”, conn); sqlComm.Parameters.AddWithValue(“@Start”, StartTime); sqlComm.Parameters.AddWithValue(“@Finish”, FinishTime); sqlComm.Parameters.AddWithValue(“@TimeRange”, TimeRange); sqlComm.CommandType = CommandType.StoredProcedure; SqlDataAdapter da = new SqlDataAdapter(); da.SelectCommand = sqlComm; da.Fill(ds); }
You could use the directive pragma autonomous_transaction. This will run the function into an independant transaction that will be able to perform DML without raising the ORA-14551. Be aware that since the autonomous transaction is independent, the results of the DML will be commited outside of the scope of the parent transaction. In most cases … Read more
Stored Procedures Pro: Good for short, simple queries (aka OLTP–i.e. add, update, delete, view records) Pro: Keeps database logic separate from business logic Pro: Easy to troubleshoot Pro: Easy to maintain Pro: Less bits transferred over network (i.e. only the proc name and params) Pro: Compiled in database Pro: Better security (users don’t need direct … Read more
It’s still not possible.
SQL Server basically goes through these steps to execute any query (stored procedure call or ad-hoc SQL statement): 1) syntactically check the query 2) if it’s okay – it checks the plan cache to see if it already has an execution plan for that query 3) if there is an execution plan – that plan … Read more
Two syntax errors: You need commas in between the conditions for your exit handler. Notice the syntax documentation shows commas. You need to terminate the END of the exit handler with a semicolon. The DECLARE statement itself (including its BEGIN…END block) is a statement like any other, and need to have a terminator. So you … Read more
You can’t concatenate an int to a string. Instead of: SET @sql = N’DECLARE @Rt int; SET @Rt=” + @RowTo; You need: SET @sql = N”DECLARE @Rt int; SET @Rt=” + CONVERT(VARCHAR(12), @RowTo); To help illustrate what”s happening here. Let’s say @RowTo = 5. DECLARE @RowTo int; SET @RowTo = 5; DECLARE @sql nvarchar(max); SET … Read more