Table name as parameter - SQL Server Forums

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

Transact-SQL (2000)

Table name as parameter

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2007-02-26 : 10:14:56

Baji Prasad.B writes "Hi,

Im trying to write a stored proc which will take table name and column names as parameters.

aim of my procedure is to insert or update into a table by the result set of a another table.

here every thing i will as parameters to my stored proc.

my question : is it possible to create a stored proc like this.

please go through the below procedure.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author: Baji Prasad.B
-- Create date: 16/02/2007
-- Description: This Procedure will replicate data from different DataSources to a Central DataSource
-- =============================================
ALTER PROCEDURE [dbo].[Sample_Replicate_Data]
--parameters for Source and Destination Sources
@SRCDbName varchar(20),
@SRCTabName varchar(20),
@SRCColList varchar(5000),
@DSTDbName varchar(20),
@DSTTabName varchar(20),
@DSTColList varchar(5000),
@ErrMessage varchar(50) output

AS
SET NOCOUNT ON;
BEGIN
Declare @tempSRCCon varchar(50),
@tempDSTCon varchar(50),
@outQ as nvarchar(200)
-- SET NOCOUNT ON added to prevent extra result sets from
--Fetching Records from Source Table and Making Cursor on it

set @tempSRCCon = @SRCDbName +'.[dbo].'+@SRCTabName
print @tempSRCCon
set @outQ = 'select'+' '+ @SRCColList +' '+'from'+' '+ @tempSRCCon
print @outQ
exec(@outQ)

declare @prCustomerID as int,
@prCustomerName as varchar,
@prOrderID as int,
@prCustomerAddress as varchar,
@FETCH_STATUS as int

declare Data_Mapping_Cursor CURSOR for
select customerid,customername,orderid,customeraddress from salesplantone.[dbo].customerorder
OPEN Data_Mapping_Cursor

FETCH NEXT FROM Data_Mapping_Cursor into @prCustomerID,@prCustomerName,@prOrderID,@prCustomerAddress
--SELECT CUSTOMERID FROM Data_Mapping_Cursor
set @FETCH_STATUS = @@FETCH_STATUS

WHILE @FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM Data_Mapping_Cursor into @prCustomerID,@prCustomerName,@prOrderID,@prCustomerAddress
set @FETCH_STATUS =@@FETCH_STATUS
declare @resQuery as nvarchar(100)
set @resQuery = 'insert into salesplantone.[dbo].customersample(customerid,customername,orderid,customeraddress) values(@prCustomerID,@prCustomerName,@prOrderID,@prCustomerAddress) '
-- 'insert into'+' '+@prTabName+'(customerid,customername,orderid,customeraddress)'+' values('
--+@prCustomerID+','+@prCustomerName+','+@prOrderID+','+@prCustomerAddress+')'
print @resQuery
exec(@resQuery)
print '1'
--select CUSTOMERID from customerorder
END

CLOSE Data_Mapping_Cursor
DEALLOCATE Data_Mapping_Cursor

END"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-02-26 : 10:18:51

That's the scariest SP I can see in SQL:

1. Bad DB design
2. Dynamic SQL (inevitable)
3. Cursor on top of it

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-26 : 10:22:51

Three strikes and you're out?

Peter Larsson
Helsingborg, Sweden

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-02-26 : 10:25:01

If you are trying to do replication, why not use the replication features that sql server already has?

- Jeff
http://weblogs.sqlteam.com/JeffS

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-26 : 10:28:06

quote:
Originally posted by Baji Prasad.B

declare @prCustomerID as int,
@prCustomerName as varchar,
@prOrderID as int,
@prCustomerAddress as varchar,
@FETCH_STATUS as int

declare Data_Mapping_Cursor CURSOR for
select customerid,customername,orderid,customeraddress from salesplantone.[dbo].customerorder
OPEN Data_Mapping_Cursor

FETCH NEXT FROM Data_Mapping_Cursor into @prCustomerID,@prCustomerName,@prOrderID,@prCustomerAddress
--SELECT CUSTOMERID FROM Data_Mapping_Cursor
set @FETCH_STATUS = @@FETCH_STATUS

WHILE @FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM Data_Mapping_Cursor into @prCustomerID,@prCustomerName,@prOrderID,@prCustomerAddress
set @FETCH_STATUS =@@FETCH_STATUS
declare @resQuery as nvarchar(100)
set @resQuery = 'insert into salesplantone.[dbo].customersample(customerid,customername,orderid,customeraddress) values(@prCustomerID,@prCustomerName,@prOrderID,@prCustomerAddress) '
-- 'insert into'+' '+@prTabName+'(customerid,customername,orderid,customeraddress)'+' values('
--+@prCustomerID+','+@prCustomerName+','+@prOrderID+','+@prCustomerAddress+')'
print @resQuery
exec(@resQuery)
print '1'
--select CUSTOMERID from customerorder
END

CLOSE Data_Mapping_Cursor
DEALLOCATE Data_Mapping_Cursor

END

All this code can be replaced with

INSERT	SalesplantOne.[dbo].CustomerSample
	(
		CustomerID,
		CustomerName,
		OrderID,
		CustomerAddress
	)
SELECT	CustomerID,
	CustomerName,
	OrderID,
	CustomerAddress
FROM	SalesplantOne.[dbo].CustomerOrder

Peter Larsson
Helsingborg, Sweden

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-02-26 : 12:52:22

I think, this is really a problem with the Freshers they consider that front end programming is similar to the back end progamming..

since in front end we develop most of the functions which are parametrized and work dynamically.. and freshers try to implement the same logic on the back end..

Around a week ago.. a new programmer assisting me wrote something similar .. where he was passing tablename, column name and conditions as a parameters to procedure and using dynamic query..was getting records..

Chirag

http://chirikworld.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources