Round Up To Next Whole Number TSQL

I was looking for a way to round up the results of a query to the next whole number and after
 fair bit of searching the net and experimenting to get this to work in SQL 2005
The result was


declare @totalpallets as int
declare @total as int
declare @palletsize as int


set total_pallets =  (select ceiling(cast(@total as float)/cast(@palletsize as float)))



This does the job admirably

Get List of Stored Procedures Tsql

To get a list of sprocs(Stored Procedures) from SQL run the following

select *
  from Database Name .information_schema.routines
 where routine_type like 'Procedure'

Replace Database Name  with you Database name.

How to return a single Value from a Stored Procedure

I need to return a single value from a stored procedure, after a lot of digging i came up with this all the other ways

 i found did not work.

Dim result as integer

  Dim sql As String
        Dim mycomman1 As New SqlCommand
        Try
            mycomman1.Connection = sqlCon  'Set your connection

            mycomman1.CommandType = CommandType.StoredProcedure
            mycomman1.CommandText = "Getproductid"  'Name of Stored Procedure
            With mycomman1
                .Parameters.Add("@product_code", SqlDbType.VarChar, ParameterDirection.Input).Value = productcode
                .Parameters.Add("@description", SqlDbType.VarChar, ParameterDirection.Input).Value = delimitedtext(25)
                .Parameters.Add("@Customer", SqlDbType.Int, ParameterDirection.Input).Value = 199
            End With
        Catch ex As Exception
 
        End Try 

'create a table to store result

         Dim dt As New DataTable
        Dim da = New SqlDataAdapter(mycomman1)
        Dim ds As New DataSet()
        da.Fill(dt)
 
        If dt.Rows.Count > 0 Then
            tstr = dt.Rows(0)("return status").ToString
            Result = CInt(tstr) --------  
        End If
 
        da.Dispose() 
        sqlCon1.Close()

Check if a temporary table exist in as stored procedure

To check if a temporary table exist  in as stored procedure use the following

 if object_id(N'tempdb..#temp') is not null

Where #temp is your table

Then do something

if object_id(N'tempdb..#temp') is not null
 drop table #temp