Sqlserver007

There are cases when SQL developers want to filter the results of Procedures Output. This requires inserting the results of the procedure in the table. Here is how it can be done

Filter Procedure Output

In the above example the temp table is created with the same columns as that of the procedure output, the Procedure is executed and then stored in the table #temp. The table is later filtered on the users criteria.

Getting the list of tables and Columns that are embedded in a view ,function or a procedure is simple

sp_depends ‘viewName or ProcedureName‘

Documented in MSDN

http://msdn.microsoft.com/en-us/library/ms189487.aspx

Sql Server 2008′s new TOP statement could help update the Table in batches

Consider the following Example

Update

The above code illustrates how the batchUpdate can be done using Top().

The top 100 records are updated depending on the criteria where fullname is null so that the same records dont get updated again

I constantly keep querying the Database sometimes for checking the metadata in the DatawareHouse. Lately I realised that have been executing the same long sql statements everyday. So is there a better way of Organising all my Favourite SQL statments or procedures ?

This is what I ended up with.

Create a Table

Table DataSource

The table contains the DatabaseName and the SQL that needs to be executed on that Database

Create the Procedure FavList

FavList Procedure

The above procedure is for listing the SQL statements and the Databases from the Table.

I have inserted my favourite SQL statements in the DataSource table as shown below

Table DataSource

Create the Procedure Fav

Fav Procedure

Run the Procedure with the parameter as the datasourceid to execute your favourite SQL statements.

like Fav 1,Fav 2 etc

The Procedure can be be modified to use the Search Tags

Happy SQL!!!

SELECT name, create_date, datepart(dy,create_date) as CreatedDayOfYear,
modify_date, datepart(dy,modify_date) as ModificationDayOfYear
FROM sys.sql_modules
JOIN sys.objects
ON sys.sql_modules.object_id = sys.objects.object_id
AND TYPE = ‘P’
order by datepart(yyyy,modify_date) desc,
datepart(dy,modify_date) desc, name;

Here is the Sql function that will Proper Case any column in a table

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
create function [dbo].[ProperCase](@Text as varchar(8000))
returns varchar(8000)
as
begin
declare @Reset bit;
declare @Ret varchar(8000);
declare @i int;
declare @c char(1);
select @Reset = 1, @i=1, @Ret = ”
while (@i <= len(@Text))
select @c= substring(@Text,@i,1),
@Ret = @Ret + case when @Reset=1 then UPPER(@c) else LOWER(@c) end,
@Reset = case when @c like ‘[a-zA-Z]‘ or @c in (””) then 0 else 1 end,
@i = @i +1
return @Ret
end

–Example

declare @name varchar(100)
set @name =’marcus ford’
set @name = dbo.propercase(@name)
print @name

Result — Marcus Ford