Stored procedure - 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 2005 Forums

Transact-SQL (2005)

Stored procedure

Author

Topic

tkotey
Yak Posting Veteran

75 Posts

Posted - 2008-07-14 : 08:18:03

I would like to create a stored procedure and insert the following code below into it. Where 'BillingTransactions2' will be a parameter. The name of the SP should be sp_invoice

DELETE BillingTransactions2

SET IDENTITY_INSERT BillingTransactions2 ON
Insert into BillingTransactions2(BillingTransactionIndex, CreateDate, UserType, UserIndex, TransType, Reference, Total)
Select BillingTransactionIndex, CreateDate, UserType, UserIndex, TransType, Reference, Total from BillingTransactions

SET IDENTITY_INSERT BillingTransactions2 OFF

UPDATE b
SET b.Balance=b.Total + ISNULL(c.PrevBalance,0)
FROM BillingTransactions2 b
OUTER APPLY (SELECT SUM(Total) AS PrevBalance
             FROM BillingTransactions2
             WHERE UserIndex =b.UserIndex
             AND BillingTransactionIndex<b.BillingTransactionIndex) c

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-14 : 08:22:10

quote:

Originally posted by tkotey

I would like to create a stored procedure and insert the following code below into it. Where 'BillingTransactions2' will be a parameter. The name of the SP should be sp_invoice

DELETE BillingTransactions2

SET IDENTITY_INSERT BillingTransactions2 ON
Insert into BillingTransactions2(BillingTransactionIndex, CreateDate, UserType, UserIndex, TransType, Reference, Total)
Select BillingTransactionIndex, CreateDate, UserType, UserIndex, TransType, Reference, Total from BillingTransactions

SET IDENTITY_INSERT BillingTransactions2 OFF

UPDATE b
SET b.Balance=b.Total + ISNULL(c.PrevBalance,0)
FROM BillingTransactions2 b
OUTER APPLY (SELECT SUM(Total) AS PrevBalance
             FROM BillingTransactions2
             WHERE UserIndex =b.UserIndex
             AND BillingTransactionIndex<b.BillingTransactionIndex) c

Thanks

Do you mean you want to make table name a parameter? you have to use dynamic sql if you want to pass object name as a parameter. This is not at all a good approach. can i ask why you want this to be done?

tkotey
Yak Posting Veteran

75 Posts

Posted - 2008-07-15 : 02:09:27

quote:
Originally posted by visakh16

quote:
Originally posted by tkotey

I would like to create a stored procedure and insert the following code below into it. Where 'BillingTransactions2' will be a parameter. The name of the SP should be sp_invoice

DELETE BillingTransactions2

SET IDENTITY_INSERT BillingTransactions2 ON
Insert into BillingTransactions2(BillingTransactionIndex, CreateDate, UserType, UserIndex, TransType, Reference, Total)
Select BillingTransactionIndex, CreateDate, UserType, UserIndex, TransType, Reference, Total from BillingTransactions

SET IDENTITY_INSERT BillingTransactions2 OFF

UPDATE b
SET b.Balance=b.Total + ISNULL(c.PrevBalance,0)
FROM BillingTransactions2 b
OUTER APPLY (SELECT SUM(Total) AS PrevBalance
FROM BillingTransactions2
WHERE UserIndex =b.UserIndex
AND BillingTransactionIndex<b.BillingTransactionIndex) c

Thanks

Do you mean you want to make table name a parameter? you have to use dynamic sql if you want to pass object name as a parameter. This is not at all a good approach. can i ask why you want this to be done?

I want to create a cron job to run SQL statement but I want to input a table name before it runs. Also I am trying to learn how to write stored procedures

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-15 : 03:18:33

quote:
Originally posted by tkotey

quote:
Originally posted by visakh16

quote:
Originally posted by tkotey

I would like to create a stored procedure and insert the following code below into it. Where 'BillingTransactions2' will be a parameter. The name of the SP should be sp_invoice

DELETE BillingTransactions2

SET IDENTITY_INSERT BillingTransactions2 ON
Insert into BillingTransactions2(BillingTransactionIndex, CreateDate, UserType, UserIndex, TransType, Reference, Total)
Select BillingTransactionIndex, CreateDate, UserType, UserIndex, TransType, Reference, Total from BillingTransactions

SET IDENTITY_INSERT BillingTransactions2 OFF

UPDATE b
SET b.Balance=b.Total + ISNULL(c.PrevBalance,0)
FROM BillingTransactions2 b
OUTER APPLY (SELECT SUM(Total) AS PrevBalance
FROM BillingTransactions2
WHERE UserIndex =b.UserIndex
AND BillingTransactionIndex<b.BillingTransactionIndex) c

Thanks

Do you mean you want to make table name a parameter? you have to use dynamic sql if you want to pass object name as a parameter. This is not at all a good approach. can i ask why you want this to be done?

I want to create a cron job to run SQL statement but I want to input a table name before it runs. Also I am trying to learn how to write stored procedures

You need to use dynamic sql if you want to pass object name like this

something like

SET @Sql='DELETE ' + @Objectname +
        'SET IDENTITY_INSERT '+@Objectname +' ON
Insert into '+@Objectname +' (BillingTransactionIndex, CreateDate, UserType, UserIndex, TransType, Reference, Total)
Select BillingTransactionIndex, CreateDate, UserType, UserIndex, TransType, Reference, Total from BillingTransactions

SET IDENTITY_INSERT '+@Objectname +' OFF

UPDATE b
SET b.Balance=b.Total + ISNULL(c.PrevBalance,0)
FROM '+@Objectname +' b
OUTER APPLY (SELECT SUM(Total) AS PrevBalance
             FROM '+@Objectname + '
             WHERE UserIndex =b.UserIndex
             AND BillingTransactionIndex<b.BillingTransactionIndex) c'

and use EXEC to execute it

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources