Stored Procedure Problem! - 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

General SQL Server Forums

New to SQL Server Programming

Stored Procedure Problem!

Author

Topic

TheWomanInIT
Starting Member

3 Posts

Posted - 2011-06-06 : 09:36:55

Hi,

I have created a c# asp.net page in visual studio and created a stored procedure so when the user has entered details and clicked submit all that data is sent to a database table in SQL.

I am trying to (inside that stored procedure) write a process to create a number of rows based on a field in a different table.

IE - Stored Procedure looks like this:

Create PROCEDURE [dbo].[sp_InsNewBudget_Splits]
(
@Budget_Code varchar(50),
@Budget_Type varchar(255),
@Total_Budget varchar(50),
@Number_Of_Splits varchar(50),
@Budget_Year_Start varchar(50),
@Budget_Year_End varchar(50)
)

AS INSERT INTO tbl_Budget_Code
VALUES(@Budget_Code, @Budget_Type, @Total_Budget, @Number_Of_Splits,@Budget_Year_Start);

I now need to add some more to retrieve the Number of Splits and create rows in a new table (tbl_Budget_Splits) based on that number.

I hope i have been clear! I will post below a solution i was given but dont really understand so cannot edit it / see if it will work:

DECLARE @count INT

SET @count = 0

WHILE (@count < 4)

BEGIN

INSERT INTO some_table ([splitid], [value]) VALUES (@count, 'val2')

SET @count = (@count + 1)

END

Thanks in advance

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-06 : 09:44:22

To insert four rows (or n rows, especially if n is large), a more efficient way would be this:


INSERT INTO some_table ([splitid],[value]) 
SELECT number,'val2' FROM MASTER..spt_values WHERE type = 'p' and number BETWEEN 1 and 4

But, that will insert 'val2' in value column always. Is that what you need? To see what the query is doing you can just run the second line (that starts with SELECT by itself). The output of that query will be inserted into some_table.

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-06-06 : 23:13:20

>> I have created a c# asp.net page in visual studio and created a stored procedure so when the user has entered details and clicked submit all that data is sent to a database table in SQL. <<

We don't care. We are the database guys. We throw data over the wall to the front and they throw stuff over the wall to use.

>> I am trying to (inside that stored procedure) write a process to create a number of rows based on a field [sic: columns are not field] in a different table. <<

1. We do not put sp_ on procedure names – it has special meaning in T-SQL from the Sybase days
2. we do not put “tbl-” on table names. It violates ISO-11179 and it is stupid.
3. We plan the size of our data. Give me an example of a fifty character budget code. Why are you passing numeric as fifty character? Dates as string? Both a budget type and a budget code?

Let me try to clean up the procedure header.

CREATE PROCEDURE Insert_Budget_Splits]
(@in_budget_code CHAR(10),
@in_budget_type CHAR(5),
@in_budget_tot DECIMAL (12,2),
@in_budget_year_start_date DATE,
@in_budget_year_end_date DATE)
AS INSERT INTO Budget_Codes
VALUES(@in_budget_code,
@in_budget_type,
@in_budget_tot,
@in_budget_year_start_date,
@in_budget_year_end_date);

>> I now need to add some more to retrieve the Number of Splits and create rows in a new table (Budget_Splits) based on that number. <<

Why not use these parameters and do the splits in one step? You are still thinking in terms of magnetic tape files and punch cards, not SQL. WHILE loop? We are set-oriented and do not write loops. It is obvious that you have never done declarative programing or SQL before.

A procedure to distribute would look something like this:

CREATE PROCEDURE Splits
(@in_amt_tot DECIMAL(12,2) NOT NULL,
@in_account_nbr_01 CHAR (10),
@in_account_nbr_01 CHAR (10),
..
@in_account_nbr_xx CHAR (10))
AS ..

The @in_account_nbr_xx parameters will use the long parameter idiom to build a set of target accounts, count the size of the set (n) and assign (1/n) of the total amount to each with a rounding rule of some kind. Use NULL for accounts that are left out of the distribution.

Would you like to see a skeleton or does this get you started?

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources