Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Stored Procedure

Author  Topic 

rameshkg
Starting Member

22 Posts

Posted - 2004-06-19 : 02:27:53
Hi,

First of all i am new to SQL Server and also to stored procedure (for that matter, new to Stored procedures in any database).

My requirement is as follows :
1. I need to do a SELECT on a table, using the primary key (supplied
as an input parameter)
2. If SELECT returns a row(it should return only one row),i need to
UPDATE the same table columns(including the prim key with the
input parameter value, used above) with the column values and the
WHERE clause being supplied as input parameters
3. If SELECT returns ZERO rows , then i need to INSERT the record to
the table with all column values being supplied as input
parameters
4. The SP should come out of the SP if any of the SQL statement FAILS,
by returning the SQL Error message and SQLError Code
5. The output parameters from the Stored procedure should be THREE.
a) A status (boolean or char type) to indicate whether the SP
was executed successfully or not
b) A message to indicate whether UPDATE was done or INSERT. This
is required to do some processing based on whether it is INSERT
or UPDATE, outside the SP.
c) An error message, with SQL Error code if the SP fails.

May be i have explained in length, eventhough the reqmt is simple. But
i am very new to SP.

Your early response is highly appreciated.

Regards
Ramesh

Kristen
Test

22859 Posts

Posted - 2004-06-19 : 03:59:10
Given this table:

CREATE TABLE xxx_SYS_CC_CountryCode
(
sys_cc_Code varchar(2), -- Country Code, e.g. 'GB' or 'US'
sys_cc_Description varchar(30) -- Description
)
GO

Here is the SAVE procedure we use (slightly adapted)

PRINT 'Create procedure xxx_SP_SYS_CC_Save'
GO
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[xxx_SP_SYS_CC_Save]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.xxx_SP_SYS_CC_Save
GO

CREATE PROCEDURE dbo.xxx_SP_SYS_CC_Save

@sys_cc_Code varchar(2), -- Country code [PK]
@sys_cc_Description varchar(30), -- Description

@intMode int=0 OUTPUT, -- Mode 0=Insert/Update, 1=Forced ADD, 2=Forced UPDATE
@intRetVal int=NULL OUTPUT, -- Return value, 0=No error
@strErrMsg varchar(255)=NULL OUTPUT, -- Error messages

@intDebug int=0 -- 0=No debugging data, 1+ = debugging at increasing levels of details

/* WITH ENCRYPTION */
AS
/*
* xxx_SP_SYS_CC_Save Save a record to xxx_SYS_CC_CountryCode
*
* Returns:
*
* Result code (0=No error)
*
* ERRORS:
*
* -1 RowCount <> 1
* -2 INSERT failed, record exists
* -3 UPDATE failed, record does not exist
* -4 Error INSERTING
* -5 Error UPDATING
*
* HISTORY:
*
* 18-Jun-2004 KBM Started
*/

SET NOCOUNT ON
SET XACT_ABORT ON
-- System variables
DECLARE @intErrNo int,
@intRowCount int

-- Local variables
--TODO Add local variables here
-- DECLARE @strMyVar1 varchar(255),
-- @intMyVar2 int


SELECT @intErrNo = 0, -- Assume no error
@strErrMsg = '',
@intRetVal = 0 -- Return value (Assume no error)

IF EXISTS (SELECT * FROM dbo.xxx_SYS_CC_CountryCode WHERE @sys_cc_Code = sys_cc_Code)
BEGIN
-- Record exists
IF @intMode = 1 -- Only INSERT permitted
BEGIN
SELECT @intMode = -2, -- Show failure
@intRetVal = -2,
@strErrMsg = @strErrMsg + 'INSERT failed, record exists. '
END
ELSE
BEGIN
SELECT @intMode = 2 -- Use UPDATE
END
END
ELSE
BEGIN
-- Record does not exists
IF @intMode = 2 -- Only INSERT permitted
BEGIN
SELECT @intMode = -3, -- Show failure
@intRetVal = -3,
@strErrMsg = @strErrMsg + 'UPDATE failed, record does not exist. '
END
ELSE
BEGIN
SELECT @intMode = 1 -- Use INSERT
END
END
IF @intRetVal <> 0 GOTO xxx_SP_SYS_CC_Save_EXIT -- Daily out


-- Validation Section
--TODO Add validation code here, set @intRetVal to NON-ZERO if error
--TODO Can use GOTO xxx_SP_SYS_CC_Save_EXIT at this point, if necessary

IF @intRetVal = 0 -- No validation errors
BEGIN
-- Save Section
BEGIN TRANSACTION xxx_SP_SYS_CC_Save_01
SAVE TRANSACTION xxx_SP_SYS_CC_Save_02
IF @intMode = 1
BEGIN
-- Insert
IF @intDebug >= 1 SELECT [xxx_SP_SYS_CC_Save DEBUG(1)]='INSERT new record'
INSERT INTO dbo.xxx_SYS_CC_CountryCode
(
sys_cc_Code,
sys_cc_Description
) VALUES
(
@sys_cc_Code,
@sys_cc_Description
)
SELECT @intErrNo = @@ERROR, @intRowCount = @@ROWCOUNT -- , @MyID = scope_identity()
if @intErrNo <> 0
BEGIN
SELECT @intRetVal = -4,
@strErrMsg = @strErrMsg + 'Error INSERTING xxx_SYS_CC_CountryCode. '
GOTO xxx_SP_SYS_CC_Save_ABORT
END
END
ELSE
IF @intMode = 2
BEGIN
-- Update
IF @intDebug >= 1 SELECT [xxx_SP_SYS_CC_Save DEBUG(2)]='UPDATE existing record'
UPDATE U
SET
-- sys_cc_Code = @sys_cc_Code, -- Primary Key field, no update needed
sys_cc_Description = @sys_cc_Description
FROM dbo.xxx_SYS_CC_CountryCode U
WHERE sys_cc_Code = @sys_cc_Code
SELECT @intErrNo = @@ERROR, @intRowCount = @@ROWCOUNT
if @intErrNo <> 0
BEGIN
SELECT @intRetVal = -5,
@strErrMsg = @strErrMsg + 'Error UPDATING xxx_SYS_CC_CountryCode. '
GOTO xxx_SP_SYS_CC_Save_ABORT
END
END
ELSE
BEGIN
SELECT @intRetVal = -6,
@strErrMsg = @strErrMsg + 'Incorrect @intMode [' + CONVERT(varchar(20), @intMode) + '] '
GOTO xxx_SP_SYS_CC_Save_ABORT
END

IF @intRetVal = 0 AND @intRowCount <> 1 -- No error but rowcount wrong
BEGIN
SELECT @intRetVal = -1, -- Incorrect row count (should be 1).
@strErrMsg = @strErrMsg + 'Row count error [' + CONVERT(varchar(20), @intRowCount) + '] '
GOTO xxx_SP_SYS_CC_Save_ABORT
END

xxx_SP_SYS_CC_Save_ABORT:
IF @intRetVal = 0
BEGIN
COMMIT TRANSACTION xxx_SP_SYS_CC_Save_01
END
ELSE
BEGIN
ROLLBACK TRANSACTION xxx_SP_SYS_CC_Save_02
COMMIT TRANSACTION xxx_SP_SYS_CC_Save_01
IF @intDebug >= 1 SELECT [xxx_SP_SYS_CC_Save DEBUG(9)]='ROLLBACK', [@intRetVal]=@intRetVal
END
END

xxx_SP_SYS_CC_Save_EXIT:

SET NOCOUNT OFF

RETURN @intRetVal -- Return error number, 0=No error
--================== xxx_SP_SYS_CC_Save ==================--
GO

IF exists (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[xxx_SP_SYS_CC_Save]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
GRANT EXECUTE ON dbo.xxx_SP_SYS_CC_Save TO MyRole
GO
PRINT 'Create procedure xxx_SP_SYS_CC_Save DONE'
GO

and some test code:

DELETE FROM xxx_SYS_CC_CountryCode -- Empty the table
DECLARE @intErrNo int, @intRetVal int, @intMode int, @strErrMsg varchar(8000)
SELECT @intMode = 0 -- Insert/Update
EXEC @intErrNo = dbo.xxx_SP_SYS_CC_Save
@sys_cc_Code = 'GB',
@sys_cc_Description = 'United Kingdom',
@intMode = @intMode OUTPUT, -- Insert/Update
@intRetVal = @intRetVal OUTPUT,
@strErrMsg = @strErrMsg OUTPUT,
@intDebug = 0 -- 0=No debugging data, 1+ = debugging at increasing levels of details

SELECT [Insert]=@intErrNo, [@intRetVal]=@intRetVal, [@intMode]=@intMode, [@strErrMsg]=@strErrMsg
SELECT * FROM xxx_SYS_CC_CountryCode

SELECT @intMode = 0 -- Insert/Update
EXEC @intErrNo = dbo.xxx_SP_SYS_CC_Save
@sys_cc_Code = 'GB',
@sys_cc_Description = 'England',
@intMode = @intMode OUTPUT, -- Insert/Update
@intRetVal = @intRetVal OUTPUT,
@strErrMsg = @strErrMsg OUTPUT,
@intDebug = 0 -- 0=No debugging data, 1+ = debugging at increasing levels of details

SELECT [Insert]=@intErrNo, [@intRetVal]=@intRetVal, [@intMode]=@intMode, [@strErrMsg]=@strErrMsg
SELECT * FROM xxx_SYS_CC_CountryCode


Kristen
Go to Top of Page

rameshkg
Starting Member

22 Posts

Posted - 2004-06-19 : 04:11:26
Ohh.... So quick...

First of all, thanks a billion...for posting this code... but i have to DIGEST this...i will go through this and modify for my table.

Again, thanks for your patience in sending this and please WISH ME GOODLUCK in understanding this code and implement the same...

Regards
Ramesh
Go to Top of Page

rameshkg
Starting Member

22 Posts

Posted - 2004-06-19 : 05:05:59
Kristen,

I went through the code. I need more information on the usage of below items in the code.

1. The handling of @intDebug and the purpose of the same.
2. Use of BEGIN TRANSACTION and SAVE TRANSACTION. Eventhough i understood their purpose in the
code, the commit and the rollback done in the ABORT section is not understood. Please explain
the same.

Thanks again, for the code Kristen.

Regards
Ramesh
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2004-06-19 : 05:38:44
quote:
Originally posted by rameshkg

but i have to DIGEST this...

Yeah, its the full 9 yards I'm afraid. Probably a bit heavy if you are just starting out, but on the other hand this is how our heavy-weght production code looks, and although I'm not up to the standard of the big-hitters on here, I'm proud-enough of our code, so if you feel comfortable to aim for this that should be no bad thing.

Your specification indicated that's where you are heading ...

Kristen
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2004-06-19 : 06:04:56
>>1. The handling of @intDebug and the purpose of the same.

Sorry, left that in the parameters and then removed all the code that used it!

We pepper the code with
IF @intDebug >= 1 SELECT [MyProcName]='DEBUG(1)', [@MyVariable]=@MyVariable ...
where the "DEBUG(1)" bit tells us where abouts in the code we are, and the [MyProcName] column label tells us which SProc we are in - e.g. when one SProcs calls another we get a clue which one is displaying debug data!.

So say that our application does
EXEC xxx_SP_MyProc @Param1='foo, @Param2='bar'
and we are getting the wrong result, we then just put the call into Query Analyser as
EXEC xxx_SP_MyProc @Param1='foo, @Param2='bar', @intDebug=1
and check through the debug info. Of course if you've got the server-side SQL debugger installed you can just step through the code instead ...

>>2. Use of BEGIN TRANSACTION and SAVE TRANSACTION.

You can just do:
BEGIN TRANSACTION
... some stuff ...
IF @MyErrorCode=0
COMMIT
ELSE
ROLLBACK

HOWEVER!! if you call an SProc that does this from another SProc then the transaction level (@@TRANSCOUNT I think) that is in place when you return to the calling SProc will be checked in some way by SQL (or will be at the wrong level, I forget the technicalities exactly) such that SQL will issue a warning message; if you are using ADO to access the resultsets when this happens life will become miserable! so this SAVE TRANSACTION thingie gets around the problem.

SAVE TRANSACTION is intended to allow a bit of code to be wrapped as a sub-transaction that can be rolled back independently of the "outer" transaction(s), but in this usage there isn't any data processing in the outer layer, as such, its just a wrapper.

So ... if @intRetVal = 0 we have a successful outcome, in which case we COMMIT all the work we have done - i.e. everything to the savepoint "02" and the outer wrapper "01".

Note that COMMIT commits everything to the previous BEGIN TRANSACTION, the label "xxx_SP_SYS_CC_Save_01" is ignored by SQL server (maybe Oracle etc. honour this, dunno!) - I put it in becuase it helps me remember what it refers to.

Note also that ROLLBACK TRANSACTION xxx rolls back just as far as the named xxx savepoint. Our "House Style" uses the SProc name + Tie-Break_number for the savepoint names

On the other hand, if @intRetVal <> 0 we have an error, and we need to rollback. So we rollback to our savepoint "02" but then we COMMIT the outer wrapper "01". This stops any SQL warning errors - because as far as it is concerned the outcome from this SProc was "success" and the @@TRANSCOUNT level et al. are all just fine.

Probably won't matter a jot if you call the SProc direct from ADO, its when it gets called from another SProc that the warning message problem arises IIRC.

Good luck!

Kristen
Go to Top of Page

rameshkg
Starting Member

22 Posts

Posted - 2004-06-19 : 06:17:15
Thanks a lot Kristen !!!

Really i owe u...but u r in UK....

Thanks for this sumptious food.... Have a nice weekend...
Go to Top of Page

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-09-04 : 12:57:39
Kristen, do you have an update-insert procedure generator for tables?

--Ken
I want to die in my sleep like my grandfather, not screaming in terror like his passengers.
Go to Top of Page

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-09-04 : 14:11:24
One technique I usually implement when generating INSERT / UPDATE or "UPSERT" procedures is to reselect the tuple(row) from the table.
This way the client can access values that SQL Server has assigned for columns with IDENTITY, DEFAULT values, COMPUTED columns, and perchance TRIGGERS that affect the values of the "UPSERTED" tuple.

The following two cases might also require special logic in the procedures:
1. Update of PK ( if allowed )
2. Identity columns

Now in my very personal opinion, I prefer to have 2 separate sp's for INSERT & UPDATE,
the reason is that I get 2 simpler procs rather than 1 more complicated.

Here follows sample inser/update procs.

CREATE TABLE [dbo].[DD_CATALOGS] (
[id] [int] NOT NULL ,
[dd_system_id] [int] NOT NULL ,
[dd_server_id] [int] NOT NULL ,
[catalog_code] [char] (8) NOT NULL ,
[name] [nvarchar] (128) NOT NULL ,
[description] [varchar] (2000) NOT NULL CONSTRAINT [DF_DD_CATALOGS_description] DEFAULT ('Description unknown'),
[regdate] [datetime] NOT NULL CONSTRAINT [DF_DD_CATALOGS_regdate] DEFAULT (getdate()),
[changedate] [datetime] NULL ,
[systemuser] [varchar] (30) NOT NULL CONSTRAINT [DF_DD_CATALOGS_systemuser] DEFAULT (suser_sname()),
[rowtimestamp] [timestamp] NOT NULL ,
CONSTRAINT [PK_DD_CATALOGS] PRIMARY KEY CLUSTERED( [id] ) )
GO

CREATE PROCEDURE dbo.DD_CATALOGS_Insert
@id int
,@dd_system_id int
,@dd_server_id int
,@catalog_code char(8)
,@name nvarchar(128)
,@description varchar(2000) = null
,@regdate datetime = null
,@changedate datetime = null
,@systemuser varchar(30) = null
AS

SET NOCOUNT ON

-- Insert the row
INSERT [dbo].[DD_CATALOGS](
[id]
,[dd_system_id]
,[dd_server_id]
,[catalog_code]
,[name]
,[description]
,[regdate]
,[changedate]
,[systemuser] )
VALUES(
@id
,@dd_system_id
,@dd_server_id
,@catalog_code
,@name
,COALESCE( @description, ('Description unknown') )
,COALESCE( @regdate, (getdate()) )
,@changedate
,COALESCE( @systemuser, (suser_sname()) ) )


-- Reselect the row so that client can access it
SELECT
[id]
,[dd_system_id]
,[dd_server_id]
,[catalog_code]
,[name]
,[description]
,[regdate]
,[changedate]
,[systemuser]
,[rowtimestamp]
FROM
[dbo].[DD_CATALOGS]
WHERE
[id] = @id


RETURN 0 -- End dbo.DD_CATALOGS_Insert
GO

CREATE PROCEDURE dbo.DD_CATALOGS_Update
@id int
,@id_NewKey int = null
,@dd_system_id int
,@dd_server_id int
,@catalog_code char(8)
,@name nvarchar(128)
,@description varchar(2000) = null
,@regdate datetime = null
,@changedate datetime = null
,@systemuser varchar(30) = null
,@rowtimestamp timestamp
AS

SET NOCOUNT ON

-- Check Concurrency
IF EXISTS( SELECT * FROM [dbo].[DD_CATALOGS] WHERE
[id] = @id
AND COALESCE( @rowtimestamp, [rowtimestamp] ) != [rowtimestamp] )
BEGIN
-- WARNING: post has been modified
RAISERROR( 'Concurrency error, post has been modified by other user', 16, 1 )
RETURN 50000
END


-- Update the row
UPDATE [dbo].[DD_CATALOGS] SET
[id] = COALESCE( @id_NewKey, @id )
,[dd_system_id] = @dd_system_id
,[dd_server_id] = @dd_server_id
,[catalog_code] = @catalog_code
,[name] = @name
,[description] = COALESCE( @description, ('Description unknown') )
,[regdate] = COALESCE( @regdate, (getdate()) )
,[changedate] = @changedate
,[systemuser] = COALESCE( @systemuser, (suser_sname()) )
WHERE
[id] = @id


-- Reselect the row so that client can access it
SELECT
[id]
,[dd_system_id]
,[dd_server_id]
,[catalog_code]
,[name]
,[description]
,[regdate]
,[changedate]
,[systemuser]
,[rowtimestamp]
FROM
[dbo].[DD_CATALOGS]
WHERE
[id] = COALESCE( @id_NewKey, @id )


RETURN 0 -- End dbo.DD_CATALOGS_Update


rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */
Go to Top of Page

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-09-04 : 14:13:23
By the way Ramesh,
My last post did not help your requirement as such very much ,
but it might be interesting to see variuos samples.


rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */
Go to Top of Page

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-09-04 : 15:23:35
I was looking for more of a generic procedure builder script that would generate column names, variables, PK checks etc.

--Ken
I want to die in my sleep like my grandfather, not screaming in terror like his passengers.
Go to Top of Page

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-09-04 : 16:19:49
Kselvia,

for generators see: www.codegeneration.net, codesmith is good.

Also for basic SELECT/INSERT/UPDATE/DELETE procedures ( see sample above )
Mail me the output of this, and I could generate those for You.

/* START OF METADATASCRIPT, mvscriptor v.0.9 */
/* T - Table, V - View, D - Default, K - Primary Key, F - Foreign Key, P - Procedure */
set nocount on
declare @start datetime
set @start=getdate()

/* OPTIMIZATION: PREPARE TABLE VARIABLES TO SPEED UP THINGS */
declare @pkcols table( table_schema varchar(128) collate database_default not null,
table_name varchar(128) collate database_default not null,
column_name varchar(128) collate database_default not null,
ordinal_position int,
primary key(table_schema,table_name,ordinal_position) )
insert @pkcols(table_schema,table_name,column_name,ordinal_position)
select
convert(varchar(128),tc.table_schema collate database_default),
convert(varchar(128),tc.table_name collate database_default),
convert(varchar(128),kcu.column_name collate database_default),
kcu.ordinal_position
from
information_schema.table_constraints tc
inner join information_schema.key_column_usage kcu
on tc.constraint_catalog=kcu.constraint_catalog
and tc.constraint_schema=kcu.constraint_schema
and tc.constraint_name=kcu.constraint_name
where tc.constraint_type='PRIMARY KEY'
/* END OPTIMIZATION */

/* GET METADATA */
declare @metadata table(metadata varchar(8000) collate database_default not null)
declare @schema varchar(128), @name varchar(128), @s varchar(8000), @d varchar(8000), @pk varchar(8000)

-- this is for relations(tables,views), pk, and column defaults
declare csr_tables cursor static for
select convert(varchar(128),table_schema) as table_schema, convert(varchar(128),table_name) as table_name
from information_schema.tables
where objectproperty(object_id(table_schema+'.'+table_name),'IsMsShipped')=0

open csr_tables
fetch next from csr_tables into @schema,@name
while @@fetch_status = 0
begin
set @s=null
set @d=null
set @pk=null
-- column metadata and defaults
select
@s = isnull(@s+'{','{')+
convert(varchar(128),column_name collate database_default)+
','+convert(varchar(128),data_type collate database_default)+
','+isnull(convert(varchar,character_maximum_length),'')+
','+isnull(convert(varchar,numeric_precision),'')+
','+isnull(convert(varchar,numeric_scale),'')+
','+case is_nullable when 'NO' then '0' else '1' end+
','+isnull(convert(varchar,columnproperty(object_id(table_schema+'.'+table_name),column_name,'IsComputed')),'0')+
','+isnull(convert(varchar,columnproperty(object_id(table_schema+'.'+table_name),column_name,'IsIdentity')),'0')+'}',
@d = case when column_default is null then @d else
isnull(@d+'{','{')+
convert(varchar(128),column_name collate database_default)+
','+convert(varchar(4000),column_default collate database_default)+'}' end
from information_schema.columns
where table_name = @name and table_schema = @schema
order by ordinal_position

if(objectproperty(object_id(@schema+'.'+@name),'IsTable'))=1
begin
-- primary keys ( we dont retrieve unique constraints that are not pk )
select @pk = isnull( @pk + '{', '{' ) + column_name + '}'
from @pkcols where table_name = @name and table_schema = @schema order by ordinal_position

insert @metadata(metadata) select 'T('+@schema+'.'+@name+'{'+@s+'})'
if @d is not null
insert @metadata(metadata) select 'D('+@schema+'.'+@name+'{'+isnull(@d,'')+'})'
if @pk is not null
insert @metadata(metadata) select 'K('+@schema+'.'+@name+'{'+isnull(@pk,'')+'})'
end
else
begin
insert @metadata(metadata) select 'V('+@schema+'.'+@name+'{'+@s+'})'
end
fetch next from csr_tables into @schema, @name
end
close csr_tables
deallocate csr_tables

/* END GET METADATA */

insert @metadata(metadata)
select 'MV({mvscriptor,0.9,'+convert(varchar(257),@@servername+','+db_name())+','+
convert(varchar(23),getdate(),121)+','+ltrim(datediff(ms,@start,getdate()))+','+ltrim(count(*)+1)+'})'
from @metadata

/* OUTPUT METADATA */
select * from @metadata
/* END OF SCRIPT */


rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */
Go to Top of Page

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-09-04 : 16:59:31
Thanks rockmoose. My intention was mostly to point Nilesh (http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=5389) to a good starting point for his question. I have seen some of Kristen's code fragements similar to this thread, and another one here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=31805



--Ken
I want to die in my sleep like my grandfather, not screaming in terror like his passengers.
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2004-09-07 : 14:08:28
quote:
Originally posted by kselvia

Kristen, do you have an update-insert procedure generator for tables?


Yes, also DELETE and some basic FIND SProcs, and Triggers.

(Sorry for late reply, been away+busy)

Kristen
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2004-09-07 : 14:14:24
quote:
Originally posted by kselvia

I was looking for more of a generic procedure builder script that would generate column names, variables, PK checks etc.


I have an SProc called "KLC" - deliberately short (Kristen's List Columns) that displays the columns for a table as a comma delimited list, one-per line (for a SELECT) in two styles:

SELECT MyCol1, -- Descriptive name
and
SELECT [Descriptive name] = MyCol1,

plus a set of DECLARE @MyCOl1 varchar(40), ... and a set of JOINs for the various FKs on the table, and tables "pointing-to" the current table.

We use this all the time, in QA, to output a set of cut&paste data for query building, but I'm sure we ought to ahve a web page that did the same, but allowed selection of columns etc. to build more ultimate queries.

But it all relies on loads of tables that describe the database, so not much I can show here as an example I'm afraid.

One day maybe ...

Kristen
Go to Top of Page
   

- Advertisement -