What exactly is a stored procedure? (for the laymen)

Ah… the magical stored procedures!

I have heard people talk about stored procedures for Vision, heard people ask about them, heard people say they can write them.  But what exactly IS a stored procedure?

A stored procedure is a batch of programming code written in T-SQL (short for transact SQL) that can be called upon from within SQL Server.  It is a set of instructions with a beginning, an end, a name, and some parameters (variables) that can be passed to it.

Stored procedures are constructed like this:

create procedure z_custom_my_fabulous_magical_code(@variable1 varchar(50), @variable2 int)
as
BEGIN

…. code goes here…

END

That’s the basic idea. What happens where “…code goes here…” is entirely up to the creator of that stored procedure… the programmer.

Because of the nature of the SQL programming language, and because of the power of it, it is important to have a test database upon which someone can test their programming talents or essays so that critical data is not deleted, altered or otherwise destroyed.

Because SQL is a programming language, then really only programmers (aka coders) have any business mucking around in there.

Thus, if someone tells you they can write/craft or otherwise produce those magical “stored procedures” to move data around or otherwise manipulate Deltek Vision for you. Be cautious.  Make sure that person can actually do what they claim.. that they have lots of programming experience, that they know the Deltek Vision database schema well, that in effect they know what the hell they are doing!

So how many stored procedures have I written?  Hundreds.  As well as hundreds of inline functions, scalar functions, views and crafted entire databases and applications from merely a clean slate and my brain.  That’s because I’m a programmer!

Don’t have one of those?  Go get one… or call me.

Cheers!

p.s. here’s an interesting one I wrote for analyzing Vision data… just for kicks.

ALTER procedure [dbo].[z_pr_pgc_dependent_table_assessor](@databasename varchar(255), @findstring varchar(255), @andclause varchar(8000), @optionPrint varchar(1)=’Y’)

as
BEGIN

— SELECT rows FROM sysindexes WHERE id = OBJECT_ID(‘tbTest’) AND indid < 2

select ‘TABLE COLUMNS ANALYSIS’ AS DESCRIPTION
, @DATABASENAME AS DATABASENAME
, @FINDSTRING AS FINDSTRING
, @ANDCLAUSE AS ANDCLAUSE

create table #mytables(objectid varchar(255), tablename varchar(255), columnname varchar(255), relevantrowcount bigint)

declare @runsql varchar(8000)

— get relevant table list
set @runsql = ‘
insert #mytables(objectid, tablename, columnname, relevantrowcount)
select t.object_id, t.name as ”table_name”, c.name as ”column_name”, -1 as ”rowcount”
from [‘[email protected]+’].sys.tables t
inner join [‘[email protected]+’].sys.all_columns c
on c.object_id = t.object_id

where c.name like ”%’[email protected]+’%” ‘
+ isnull(‘ and ‘[email protected],”) + ‘
group by t.object_id, t.name, c.name
order by t.name, c.name ‘

if @optionprint = ‘y’ print @runsql
exec(@runsql)

declare @objectid varchar(255), @tablename varchar(255), @thiscolumn varchar(255), @rowcount bigint

select top 1 @objectid = objectid, @tablename = tablename, @thiscolumn = columnname
from #mytables where relevantrowcount = -1

— loop through relevant tables getting relevant row counts
while isnull(@objectid,”) <> ”
begin

set @runsql =
‘update #mytables
set relevantrowcount = (select count(*) from [‘[email protected]+’].dbo.[‘ + @tablename + ‘] where isnull(‘ + @thiscolumn + ‘,””)<> ”” )
where tablename = ”’ + @tablename + ”’ and columnname = ”’ + @thiscolumn + ”’ and relevantrowcount = -1 ‘
if @optionprint = ‘y’ print @runsql
exec(@runsql)

set @objectid = ”

select top 1 @objectid = objectid, @tablename = tablename, @thiscolumn = columnname
from #mytables where relevantrowcount = -1
end

— spit out table stats
select *
from #mytables
order by tablename, columnname

declare @oldtable varchar(255)=”
— now sample data from those tables where it looks relevant
set @tablename = ”

select top 1 @tablename = tablename
, @thiscolumn = columnname
, @rowcount = relevantrowcount
, @runsql = case when relevantrowcount > 0 then
(case when relevantrowcount < 10
then ‘select * from [‘[email protected]+’].dbo.’ + tablename
else ‘select top 3 * from [‘[email protected]+’].dbo.’ + tablename
end )
else ‘print ””’
end
from #mytables
where tablename != @oldtable
and tablename > @tablename

while @tablename <> ‘**all done **’ and @tablename != @oldtable
begin
select @tablename as tablename, @thiscolumn as thiscolumn, @rowcount as [rowcount]

if @optionPrint=’Y’
begin
print @runsql
end
ELSE
begin
EXEC(@runsql)
end

set @oldtable = @tablename

select top 1
@tablename = isnull(tablename,’**all done **’)
, @thiscolumn = columnname
, @rowcount = relevantrowcount
, @runsql = case when relevantrowcount > 0 then
(case when relevantrowcount < 10
then ‘select * from [‘[email protected]+’].dbo.’ + tablename
else ‘select top 3 * from [‘[email protected]+’].dbo.’ + tablename
end )
else ‘print ””’
end
from #mytables
where tablename != @oldtable
and tablename > @tablename

end

drop table #mytables

END