header image

T-SQL : Auto generating data retrieval SP

Posted by: | August 27, 2008 | No Comment |

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

under: 9548