dynamic sp build

From: Ollie2308 (moliver_at_cima-inc.com)
Date: 03/31/04 

Date: Wed, 31 Mar 2004 11:06:12 -0800

I want to build a "GetbyPK" stored procedure by passing in a table_name. I use the system tables to get the columns that make up the table for the select part of the query... and the columns that make up the PK that I use for the where clause.

I currently build the script in a temp table that hold the statements for the new stored procedure. Then I just select all the rows, copy the result set and paste that into a new query window and my sp is created.

Here is a sample

***************************

CREATE PROCEDURE dbo.x_Build_GetByPK @intablename varchar(50) AS

truncate table sql_text;

insert into sql_text(Line_text)
values ('IF EXISTS (SELECT name ');

insert into sql_text(Line_text)
values ('FROM sysobjects ');

insert into sql_text(Line_text)
select 'WHERE name = ''sp_'+name+'_GetbyPK'''
from sysobjects
where name = @intablename
and type = 'U';

insert into sql_text(Line_text)
values ('AND type = ''P'')');

insert into sql_text(Line_text)
values ('DROP PROCEDURE ')

insert into sql_text(Line_text)
select 'sp_'+name+'_GetbyPK'
from sysobjects
where name = @intablename
and type = 'U';

insert into sql_text(Line_text)
values ('GO');

insert into sql_text(Line_text)
values ('Create procedure ');

-- Get table_name for SP title

insert into sql_text(Line_text)
select 'sp_'+name+'_GetbyPK'
from sysobjects
where name = @intablename
and type = 'U';

-- get the PK columns for input parameters

insert into sql_text(Line_text)
select '@'+c.name+' '+t.name+'('+ltrim(str(c.length))+'),'
from sysobjects t0,
     sysobjects i0,
     syscolumns c,
     systypes t,
     sysindexes i,
     sysindexkeys k
where t0.id =i0.parent_obj
and i0.name = i.name
and i.indid = k.indid
and t0.id = k.id
and t0.id = c.id
and k.colid = c.colid
and c.xtype = t.xtype
and i0.xtype = 'PK'
and t0.name = @intablename;

-- Trim the last ,
update sql_text
set line_text = replace(line_text,',','')
where line_no = (select max(line_no) from sql_text);

Insert into sql_text (line_text)
values ('AS');

Insert into sql_text (line_text)
values ('Select');

-- get column names

Insert into sql_text (line_text)
select
c.name+','
 from sysobjects t, syscolumns c
where t.id = c.id
and t.type = 'U'
and t.name = @intablename
order by c.colorder;

-- Trim the last ,
update sql_text
set line_text = replace(line_text,',','')
where line_no = (select max(line_no) from sql_text);

Insert into sql_text (line_text)
values ('From');

-- Get table_name for SP title

insert into sql_text(Line_text)
select name
from sysobjects
where name = @intablename
and type = 'U';

Insert into sql_text (line_text)
values ('Where');

-- build the PK criteria

insert into sql_text(Line_text)
select c.name+' = @'+c.name+' and'
from sysobjects t0,
     sysobjects i0,
     syscolumns c,
     systypes t,
     sysindexes i,
     sysindexkeys k
where t0.id =i0.parent_obj
and i0.name = i.name
and i.indid = k.indid
and t0.id = k.id
and t0.id = c.id
and k.colid = c.colid
and c.xtype = t.xtype
and i0.xtype = 'PK'
and t0.name = @intablename;

-- Trim the last 'and'
update sql_text
set line_text = replace(line_text,'and','')
where line_no = (select max(line_no) from sql_text);

Insert into sql_text (line_text)
values ('');

Insert into sql_text (line_text)
values ('go');

*******************************************************************
Example .................................
Step 1 --- Build the create sp script in temp table
use pubs
exec x_Build_GetByPK Authors

Step 2 --- now select all the rows that are needed to create my new sp from my temp table sql_text

select line_text from sql_text order by line_no

Step 3 ---- cut and paste the results into a new query window and run ...to create my new sp ''sp_Authors_GetbyPK''

Now for my question... how do I fully automate this.....

I want to remove the manual steps Step 2 and Step 3 and still create the desired sp

thanks !!!

Relevant Pages

  • Re: Index Creation Date
    ... Mistake, mistake.. ... >>Join sysindexes and sysobjects and use the crdate in the ... Read details on SQL Server Books Online. ...
    (microsoft.public.sqlserver.security)
  • Re: Index Creation Date
    ... Join sysindexes and sysobjects and use the crdate in the ... Read details on SQL Server Books Online. ... sysusers u JOIN ...
    (microsoft.public.sqlserver.security)
  • DBCC
    ... DBCC results for 'sysobjects'. ... DBCC results for 'sysindexes'. ... There are 5613 rows in 91 pages for object 'syscolumns'. ...
    (microsoft.public.sqlserver.server)
  • Re: Replicating new table
    ... such a method would involve constantly polling the sysobjects and sysindexes ... "Jim" wrote in message ...
    (microsoft.public.sqlserver.replication)