Accelerate your database and program development of stored procedures

program | stored Procedure | data | Database If you are a database worker, or a code writer, are you bothered to fill out those fields? Less fortunately, if you reach dozens of, you will be confused and dizzy brain, and then lost the happiness of writing code.

Well, use the following methods to make you omit the trouble of filling in the fields, and then you can reach the high tide of writing code! It is home travel, the necessary medicine, better than Viagra!

The first step is to create a view!
Create VIEW Col as
Select
B.name ColName,
B.colid,
C.name Xcoltype,
(select Name from systypes where Xusertype = C.xtype and XType = Xusertype) Coltype,/*convert user define type to system type*/
B.length sizes,
B.prec Prec,
B.xscale Scale,
Convert (bit,b.status&8) Nulls,
A.name ObjectName,
A.type ObjectType
From sysobjects a,syscolumns b,systypes C
where A.type in (' U ', ' V ', ' P ') and A.id=b.id and B.xusertype=c.xusertype

The first step is to create a stored procedure!
CREATE PROCEDURE Sysgetcol
@objectname Char (80)
As
Declare
@objecttype Char (10)
Select
@objecttype = ObjectType
From COL
where objectname = @objectname

If @ @ROWCOUNT = 0
Begin
Print ' Internal Error (001): '
Print ' not Found object: ' + Rtrim (@objectname) + '! '
Return-1
End


Select
ColName,
Coltype types,
Xcoltype,
Sizes,
Prec
Scale
Colid
Nulls
Into #temp
From COL
where objectname = @objectname
ORDER BY Colid
--patindex ('%pattern% ', expression)

--script Object Structure
If @objecttype = ' U '
Begin
Select ' Create Table ' + Rtrim (@objectname) + ' ('
UNION ALL
Select ' + Rtrim (colname) + ' + Rtrim (xcoltype) +
Case Xcoltype when ' char ' then ' (' +rtrim (Convert (Char (3), sizes) + ') '
When ' Numeric ' then ' (' + RTrim (CONVERT (char (3), Prec)) + ', ' + RTrim (CONVERT (char (3), Scale)) '
When ' Varchar ' Then ' (' +rtrim (Convert (Char (3), sizes) + ') '
When ' nchar ' then ' (' + RTrim (Convert (Char (3), sizes) + ') '
When ' nvarchar ' then ' (' + RTrim (Convert (Char (3), sizes) + ') '
Else ' "
End +
Case Nulls when 0 then ' not Null ' else ' "End + ', '
From #temp
UNION ALL
Select ') '
End

/*building Select statement*/

Select ' CREATE VIEW view_ ' + RTrim (@objectname) + ' as ' + Char (+ ' select ')
UNION ALL
Select ' +rtrim (colname) + ', ' from #temp--order by colid
UNION ALL
Select ' From ' + RTrim (@objectname)

/******update #temp set sizes=null where types<> ' Char ' ******/
--bulid procedure Parameter
Select ' CREATE PROCEDURE ' + RTrim (@objectname) + ' _update '
UNION All
Select
' @ ' + RTrim (colname) + ' + RTrim (xcoltype) +
Case Xcoltype when ' Char ' Then ' (' +rtrim (Convert (Char (3), sizes) + '), '
When ' Numeric ' then ' (' + RTrim (CONVERT (char (3), Prec)) + ', ' + RTrim (CONVERT (char (3), Scale)), '
When ' Varchar ' Then ' (' +rtrim (Convert (Char (3), sizes) + '), '
When ' nchar ' then ' (' + RTrim (Convert (Char (3), sizes) + ') '
When ' nvarchar ' then ' (' + RTrim (Convert (Char (3), sizes) + ') '
Else ', '
End
From #temp
--order by Colid
UNION All
Select ' As '
/*building Update part*/
UNION All
Select ' Update ' + RTrim (@objectname) + ' set '
UNION All
Select ' +rtrim (colname) + ' = @ ' +rtrim (colname) + ', ' #temp--order by Colid
Union All
Select ' WHERE '
Union All
Select ' +rtrim (colname) + ' = @ ' +rtrim (colname) + ' and ' from #temp--order by Colid
UNION All
/*update #temp Set sizes=null*/

/*building Insert statement*/
Select ' If @ @ROWCOUNT = 0 '
UNION All
Select ' INSERT INTO ' + Rtrim (@objectname) + ' ('
UNION All
Select ' +rtrim (colname) + ', ' from #temp-order by Colid
UNION All
Select ') '
UNION All
Select ' Values ' ('
UNION All
Select ' @ ' +rtrim (colname) + ', ' from #temp--order by colid
UNION All
Select ') '

Select ' +rtrim (colname) + ' = Trim (Request ("' +rtrim (colname) +")) ' from #temp

Select ' +rtrim (colname) + ' = Trim (rs ("' +rtrim (colname) + ')) ' from #temp--order by colid

Select '. Parameters (' +rtrim (colid) + ') = ' + colname from #temp--order by colid
Go


The third step is to use the stored procedure!
Let's say you have a table called Nta_base_member in your database.
Create Table Nta_base_member (
m_id bigint not Null,
M_type smallint,
M_state smallint,
MemberID bigint,
travelco_id bigint
)

Open your Query Analyzer and type
Sysgetcol Nta_base_member

Then press ctrl+t, then press F5 to see what the Query Analyzer appears?

The number of rows affected is 5 rows)


--------------------------------------------------------------------------------------------------------------- -------------------------------