SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Stored Procedure
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rameshkg
Starting Member

22 Posts

Posted - 06/19/2004 :  02:27:53  Show Profile  Reply with Quote
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

United Kingdom
22415 Posts

Posted - 06/19/2004 :  03:59:10  Show Profile  Reply with Quote
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 - 06/19/2004 :  04:11:26  Show Profile  Reply with Quote
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 - 06/19/2004 :  05:05:59  Show Profile  Reply with Quote
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

United Kingdom
22415 Posts

Posted - 06/19/2004 :  05:38:44  Show Profile  Reply with Quote
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

United Kingdom
22415 Posts

Posted - 06/19/2004 :  06:04:56  Show Profile  Reply with Quote
>>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 - 06/19/2004 :  06:17:15  Show Profile  Reply with Quote
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 - 09/04/2004 :  12:57:39  Show Profile  Reply with Quote
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

Sweden
3279 Posts

Posted - 09/04/2004 :  14:11:24  Show Profile  Reply with Quote
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

Sweden
3279 Posts

Posted - 09/04/2004 :  14:13:23  Show Profile  Reply with Quote
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 - 09/04/2004 :  15:23:35  Show Profile  Reply with Quote
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

Sweden
3279 Posts

Posted - 09/04/2004 :  16:19:49  Show Profile  Reply with Quote
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 */

Edited by - rockmoose on 09/04/2004 16:21:12
Go to Top of Page

kselvia
Aged Yak Warrior

526 Posts

Posted - 09/04/2004 :  16:59:31  Show Profile  Reply with Quote
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

United Kingdom
22415 Posts

Posted - 09/07/2004 :  14:08:28  Show Profile  Reply with Quote
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

United Kingdom
22415 Posts

Posted - 09/07/2004 :  14:14:24  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000