T-SQL : Auto generating data retrieval SP
Auto generating data retrieval SP will be done by just a second...
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
CREATE procedure [dbo].[SelectSP]
( @tablename varchar(100))
as
declare @cname varchar(100),
@strAllColumns varchar(5000)
declare crsColumn cursor for
select name from syscolumns where id=object_id(@tablename)
----------------------------------------------------------------------
begin
----------------------------------------------------------------------
---------------------------------------- Header Information ----------
print '/*
Procedure Name : usp' + replace(@tablename,'tbl','') + 'InsertUpdate
Version : 1.0
Dated : ' + convert(varchar(12),getdate(),107) + '
Purpose : For retrieving data from ' + @tablename +'
Tables Accessed : ' + @tablename + '
*/'
print 'CREATE PROCEDURE uspGet' + replace(@tablename,'tbl','')
OPEN crsColumn
FETCH NEXT FROM crsColumn INTO @cname
select @strAllColumns = ''
WHILE @@FETCH_STATUS = 0
BEGIN
select @strAllColumns = @strAllColumns + '[' + @cname + '],'
FETCH NEXT FROM crsColumn INTO @cname
END
CLOSE crsColumn
DEALLOCATE crsColumn
----------------------------------------------------------------------------
print 'AS
BEGIN
BEGIN'
print ' SELECT ' +left(@strAllColumns ,len(@strAllColumns)-1)+ '
FROM [' + @tablename +']'
print ' END
IF @@ERROR <> 0
BEGIN
RETURN -1
END
ELSE
BEGIN
RETURN 0
END
END'
end
To test by executing the below statement in NorthWind database data retrieval SP will be ready.
SelectSP Customers
/*
Procedure Name : uspCustomersInsertUpdate
Version : 1.0
Dated : Aug 27, 2008
Purpose : For retrieving data from Customers
Tables Accessed : Customers
*/
CREATE PROCEDURE uspGetCustomers
AS
BEGIN
BEGIN
SELECT [CustomerID],[CompanyName],[ContactName],[ContactTitle],[Address],[City],[Region],[PostalCode],[Country],[Phone],[Fax]
FROM [Customers]
END
IF @@ERROR <> 0
BEGIN
RETURN -1
END
ELSE
BEGIN
RETURN 0
END
END