SQL: Samples

Last updated: 2016-06-02

Execute a stored procedure many times + using a temporary view

With this sample a list of id’s are extracted from a table.
That id is used as an input parameter to a stored procedure.
The sample prints a list of calls to that stored procedure and executes the list as the next thing.

Note: When using a temporary view: It can only be used once immediately following the with ().

--- Repeat a stored procedure many times
DECLARE @SQL varchar(max)='';
WITH view_tmp AS 
( select e.employeeid from [employee] e where e.employeestate = 0 ) -- this could be a very long query, so here we put it in a temporary view only for abbrevity on the next line
--- Do something wuith the first 100 rows from above query
Select top 100 @SQL = @SQL + 'exec [dbo].[SP_DoSomething] ' + convert(varchar(10),EmployeeID) + ';' from employee where EmployeeID in ( SELECT employeeid FROM view_tmp )
EXEC (@SQL) -- execute the stored procedure 100 times
Print len(@SQL)
GO

HowTo Retrieve Values in StoredProcedures from EF

Retrieve RAISERROR/PRINT/RETURN values from ‘multiple resultsets.

http://support.microsoft.com/kb/310070

http://support.microsoft.com/kb/194792

In this sample a StoredProcedure has been added manually to a EntityFramework edmx file.

The StoredProcedure returns a returnvalue (RETURN 0) and a resultset (SELECT something)

using System.Linq;
using System.Data.EntityClient; //EntityConnection
using System.Data.Common; //DbConnection
using System.Data; //CommandType
using System.Text;
using System.Data.SqlClient; //SqlParameter

        protected void Button1_Click(object sender, EventArgs e)
        {
            TextBoxOutput1.Text = EdmStoreJobs.ExecCleanUpJob().ToString();
        }
        public static string ExecCleanUpJob()
        {
            string result = "";

            using (MySvcEntities db = new (MySvcEntities())
            {
                //Execute SP
                EntityConnection entConn = (EntityConnection)db.Connection;
                using (DbConnection storeConn = entConn.StoreConnection)
                {
                    DbCommand cmd = storeConn.CreateCommand();
                    cmd.CommandText = "SP_CleanUpJob";
                    cmd.CommandType = CommandType.StoredProcedure;
                    SqlParameter retval = new SqlParameter("@RETURN_VALUE", System.Data.SqlDbType.Int);
                    retval.Direction = System.Data.ParameterDirection.ReturnValue;
                    cmd.Parameters.Add(retval);

                    StringBuilder sb = new StringBuilder(4000);

                    bool openConn = cmd.Connection.State == ConnectionState.Closed;
                    if (openConn)
                    {
                        cmd.Connection.Open();
                        var reader = cmd.ExecuteReader();
                        var response = "";
                        //There is only one resultset with one row and one column, which is a string
                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                response = reader.GetString(0);
                                //Console.WriteLine("{0}\t{1}", reader.GetInt32(0),reader.GetString(1));
                            }
                        }
                        else
                        {
                            sb.AppendLine("No rows in reader");
                            //Console.WriteLine("No rows found.");
                        }
                        reader.Close();

                        var ihaserrors = (int)retval.Value;
                        var haserrors = (ihaserrors != 0);
                        if (!haserrors)
                        {
                            sb.AppendLine("No errors.");
                        }
                        else
                        {
                            sb.AppendLine("Errors!");
                        }

                        //The responsestring is an array separated with ;
                        char[] splitters = { ';' };
                        var responsearr = response.Split(splitters);
                        foreach (var line in responsearr)
                        {
                            sb.AppendLine(line);
                        }

                    }
                    else {
                        sb.AppendLine("Wrong connection state");
                    }

                    result = sb.ToString();
                }
            }
            return result;
        }

Select date n-th month ago

I need a date some month ago to put in a WHERE clause:

	DECLARE @monthago int = 6
	DECLARE @monthagodate datetime
	-- Select date x month ago
	SET @monthagodate = DATEADD(month, -@monthago, GETDATE())
	-- Select midnight at x month ago
	SET @monthagodate = DATEADD(dd, 0, DATEDIFF(dd, 0, @monthagodate))
	Print @monthagodate

Output:

Jul 15 2012 12:00AM

Thanks to  Alex Bagnolini and Aku

Transaction sample – 2005+

	DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
	BEGIN TRY
		BEGIN TRAN one

		--//STP BODY
		PRINT 2 / 0 -- throw error

		COMMIT TRAN one
	END TRY
	BEGIN CATCH
		IF @@TRANCOUNT > 0
			ROLLBACK TRAN one

		SELECT @ErrMsg = ERROR_MESSAGE(),@ErrSeverity = ERROR_SEVERITY()
		PRINT @ErrMsg
		RAISERROR(@ErrMsg, @ErrSeverity, 1)
	END CATCH

Output:

Divide by zero error encountered.
Msg 50000, Level 16, State 1, Line 16
Divide by zero error encountered.

Thanks to Naor

Converting int to String

	DECLARE @counts as int
	SELECT @counts = count (*) from SomeTable; Print 'SomeRows: ' + Cast(@counts AS varchar)

Output:

SomeRows: 11

Implement list paging with SQL

Comparing performance for different SQL Server paging methods

The End

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: