Creating tables - 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)

Creating tables

Author

Topic

kloepper
Yak Posting Veteran

77 Posts

Posted - 2004-02-23 : 16:05:59

(I'm new to this) I don't understand how I can take the UserID, a Portfolio Name and a string of symbols from my web page and 2 web form textboxes (3 items), pass them as variables into the sql statement below and have these items, plus one(UserID,Portfolio_Name, Symbols and PortfolioID) written to the two tables (#2 and #3) correctly.

How is the PortfolioID added during the procedure? Is it autoincremented in the a_Users_Portfolios table as I add UserID and Portfolio_Name to this table with another procedure. Obviously, I don't know how to get these items into the correct tables.

(In the code supplied, should @PortfolioID be varchar or int?)
===================================================================
Table1: Users (UserID, Name, ...)
(11, 'MyName', ...)

Table2: a_Users_Portfolios (PortfolioID, UserID, Portfolio_Name)
(1, 11, 'My Portfolio')

Table3: a_Users_Portfolios_Symbols (PortfolioID, Symbol)
(1, 'A')
(1, 'B')
(1, 'C')

Table4: Name_Symbol (Symbol, Name)
('A', 'Agilent Tech')
('B', 'Bob the Builder')
('C', 'imsmart.info')

@Portfolio_Symbols is the user's text from a textbox in the form 'A B C D E ...' and
@PortfolioID is the ID of the new portfolio

declare @Query varchar(8000)

declare @PortfolioID int or varchar?
declare @Portfolio_Symbols varchar(1500)

set @Query = 'insert into a_Users_Portfolios_Symbols (PortfolioID, Symbol) select ' + cast(@PortfolioID as varchar) + ', Symbol from a_Users_Portfolios_Symbols where Symbol in (''' + replace(ltrim(rtrim(@Portfolio_Symbols)), ' ', ''', ''') + ''')'

exec @Query

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-02-23 : 16:09:28

Is PortfolioID an identity column? If so, then SQL Server will auto increment the value for you. Don't put a value for it in the insert statement:

INSERT INTO Portfolio (Column2, Column3)
SELECT Column2, Column3
FROM TableA

In my above example, pretend Column1 is the identity column. So I want SQL Server to fill the column for me, so I don't specify it in the query.

Yes PortfolioID should be an int column.

Why do you need dynamic sql for this? The query that you have doesn't appear to need it if you make @Portfoio_Symbols a CSV list. There is a great article how to parse a CSV list without the use of dynamic sql. Go to the article search on the first page of sqlteam and type in csv to see the solution.

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-02-23 : 16:21:41

Oh and I should have mentioned SCOPE_IDENTITY(). To know what value SQL Server inserted into your table, use SCOPE_IDENTITY():

DECLARE @IdentValue INT

INSERT INTO Table1 (Column2, Column3)
SELECT 'Tara', 'Duggan'

SELECT @IdentValue = SCOPE_IDENTITY()

INSERT INTO Table2 (Column1, Column2)
SELECT 'New', @IdentValue

Tara

kloepper
Yak Posting Veteran

77 Posts

Posted - 2004-02-23 : 16:45:30

Like I said, I don't have any training in sql, so I'm clueless as to how sql will write data to one row in table 2(one portfolio name added) and with the same statement, write many rows with the same Portfolio_ID (a single symbol to one row of table 3 with a corresponding Portfolio_ID).

This table structure and procedure were recommended to me by someone else because the way I had set it up originally was not normalized. I'm still waiting back for a response from that person on these questions.

As for the csv article, I'll read it.

Paul

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-02-23 : 16:47:19

So is PortfolioID an identity column in the parent table?

Tara

kloepper
Yak Posting Veteran

77 Posts

Posted - 2004-02-23 : 16:59:04

yes

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-02-23 : 17:01:58

Then SQL Server will handle the auto increment feature. Here is an example:

Column1 in Table1 is an IDENTITY column. I'm inserting values into two other columns. I then use SCOPE_IDENTITY() to get the value that SQL Server used for the row that I inserted. I then use that value for my Table2 insert.

DECLARE @IdentValue INT

INSERT INTO Table1 (Column2, Column3)
SELECT 'Tara', 'Duggan'

SELECT @IdentValue = SCOPE_IDENTITY()

INSERT INTO Table2 (Column1, Column2)
SELECT 'New', @IdentValue

Do you understand this part? If so, what else do you need some help/clarification on?

Tara

kloepper
Yak Posting Veteran

77 Posts

Posted - 2004-02-23 : 17:12:16

The proof that I understand it will be in whether or not I get it to work.

Looks like lots of articles on csv. This other person who normalized my tables also didn't like that I was just 'jamming a string into a single field as an array' and then trying to read them out. That was the user input into a textbox that he was referring to, the string being something like 'A B C D E F' and then trying to convert that string into a useable form for an IN statement, like IN('A', 'B', 'C').

Do you agree, that that's a bad idea?

Thanks,

Paul

PS gonna take a break

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-02-23 : 17:15:11

I don't mind the fact that the information is being stored in a string as long as dynamic sql isn't being used, since there is a better way of doing it. The CSV article that I was referring to is:

http://www.sqlteam.com/item.asp?ItemID=11499

Tara

kloepper
Yak Posting Veteran

77 Posts

Posted - 2004-02-24 : 02:11:03

I'm trying to determine if this other fellow has led me down the wrong path (he irritates me too because he seems quite arrogant, but what do I know about sql?, so I deferred to him).

I thought that my original approach was rather simple and straight-forward: In order to create a User portfolio, let the user enter their stock symbols in a textbox, store the symbols as a string in a single table, side-by-side with the portfolio name that they belong to and the UserID--This is where the other guy took exception to what I was doing because he didn't like the fact that this is a non-normalized table (or is it?)

Table: a_Users_Portfolios (PortfolioID, UserID, Portfolio_Name, Portfolio_Symbols)
(1, 11, 'My Portfolio', 'A B C D')

Then, when it came to querying the symbols, I could somehow convert the string into a form that could be used to construct the IN('A','B','C','D') statement.

Something like this:

SELECT UserID, Portfolio_Name, Portfolio_Symbols
FROM a_Users_Portfolios_02
WHERE (UserID = @UserID) AND (Portfolio_Name = @Portfolio_Name) AND (Portfolio_Symbols IN (REPLACE(LTRIM(RTRIM(@Portfolio_Symbols)), ' ', ''', ''')))

.......this isn't correct yet.

Also, I'm using VisualStudio.NET to make ASP.NET web pages and the DataSets that are constructed allow for easy editing of this field's contents, so it's a really slick way for the user to retrieve the contents of the portfolio when I just read the string back into a textbox and let the user do their editing directly on the string; however, when I split the data into two tables, VisualStudio won't construct an editable dataset.

So if the issues this other fellow says:

"It's bad because

- You cannot enforce referencial integrity rules
- There's a limit on the length of the string and thus on the number of codes in the list
- It makes your queries really hard - like in the example above, and in your REPLACE question
- It's hard to maintain
etc."

are not really much of a concern for me if I do it the way I had originally planned, then I'd like to simply find a way to create the IN('A','B') statement, because that's all I had to do before I started down this trail that he led me onto.

Sorry this is so long-winded, but it makes more sense to me when I explain it this way.

kloepper
Yak Posting Veteran

77 Posts

Posted - 2004-02-24 : 11:10:38

Well, I'm not getting this to work....see any obvious mistakes? I'm trying to insert a Autoincrememnted PortfolioID, a UserID and a Portfolio_Name into table 'a_Users_Portfolios' and then insert the individual symbols from a user submitted text string into a second table called 'a_Users_Portfolios_Symbol', by breaking up the text string and then adding one symbol per row.

I'm using the PortfolioID to link the two tables.

CREATE PROCEDURE _premium_Create_New_Portfolio_04 (@UserID as int, @Portfolio_Name as varchar (50)) AS

DECLARE @PortfolioID INT --Auto-incremented integer
DECLARE @Portfolio_Symbols varchar(1500) --User submitted, Raw string of Symbols from the TextBox

INSERT INTO a_Users_Portfolios (UserID, Portfolio_Name)
VALUES (@UserID, @Portfolio_Name)

SELECT @PortfolioID = SCOPE_IDENTITY()

INSERT INTO a_Users_Portfolios_Symbol (PortfolioID, Portfolio_Symbol) SELECT cast(@PortfolioID as int), Portfolio_Symbol FROM a_Users_Portfolios_Symbol WHERE Portfolio_Symbol IN ( replace(ltrim(rtrim(@Portfolio_Symbols)), ' ', ''', '''))

GO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-02-24 : 12:22:19

quote:
Originally posted by kloepper

CREATE PROCEDURE _premium_Create_New_Portfolio_04 (@UserID as int, @Portfolio_Name as varchar (50)) AS

DECLARE @PortfolioID INT --Auto-incremented integer
DECLARE @Portfolio_Symbols varchar(1500) --User submitted, Raw string of Symbols from the TextBox

INSERT INTO a_Users_Portfolios (UserID, Portfolio_Name)
VALUES (@UserID, @Portfolio_Name)

SELECT @PortfolioID = SCOPE_IDENTITY()

INSERT INTO a_Users_Portfolios_Symbol (PortfolioID, Portfolio_Symbol) SELECT cast(@PortfolioID as int), Portfolio_Symbol FROM a_Users_Portfolios_Symbol WHERE Portfolio_Symbol IN ( replace(ltrim(rtrim(@Portfolio_Symbols)), ' ', ''', '''))

GO

What part isn't working? I don't see a value being put into @Portfolio_Symbols. It isn't an input parameter either, so where is it being handled?

Tara

kloepper
Yak Posting Veteran

77 Posts

Posted - 2004-02-24 : 13:14:18

I changed the procedure to the code below and it auto increments the @PortfolioID and writes the @UserID and @Portfolio_Name to table 'a_Users_Portfolios', but nothing (neither @PortfolioID nor any of the @Portfolio_Symbols) is written to the other table, 'a_Users_Portfolios_Symbol'.

Am I passing the text string into the second INSERT INTO properly?

CREATE PROCEDURE _premium_Create_New_Portfolio_04 (@UserID int, @Portfolio_Name varchar (50), @Portfolio_Symbols varchar(1500)) AS

DECLARE @PortfolioID INT --Auto-incremented integer

INSERT INTO a_Users_Portfolios (UserID, Portfolio_Name)
VALUES (@UserID, @Portfolio_Name)

SELECT @PortfolioID = SCOPE_IDENTITY()

INSERT INTO a_Users_Portfolios_Symbol (PortfolioID, Portfolio_Symbol) SELECT cast(@PortfolioID as int), Portfolio_Symbol FROM a_Users_Portfolios_Symbol WHERE Portfolio_Symbol IN ( replace(ltrim(rtrim(@Portfolio_Symbols)), ' ', ''', '''))

GO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-02-24 : 13:57:01

Comment out the second insert. Put this in there:

PRINT @Portfolio_Symbols

What does the stored procedure show? It should show the value of @Portfolio_Symbols. Post the value here so that we can see if it is formatted correctly. I suspect that it isn't, so no rows are found due to the WHERE clause.

Tara

kloepper
Yak Posting Veteran

77 Posts

Posted - 2004-02-24 : 14:24:00

I'm not sure I did what you asked. This procedure gets variables passed to it when I run it from a web page, so I don't see any output of the PRINT @Portfolio_Symbols line. How am I supposed to see any output from that line?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-02-24 : 14:25:24

Run the stored procedure in Query Analyzer. You should always start with Query Analyzer. Once you have the stored procedure working there, you then try it out in the application.

Tara

kloepper
Yak Posting Veteran

77 Posts

Posted - 2004-02-24 : 14:28:00

How do I pass it variables in query analyzer?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-02-24 : 14:32:28

EXEC _premium_Create_New_Portfolio_04 @UserID = 'tduggan', @Portfolio_Name = 'Some Portfolio', @Portfolio_Symbols = 'Some String'

Tara

kloepper
Yak Posting Veteran

77 Posts

Posted - 2004-02-24 : 14:43:04

Is this the way it should be written?

CREATE PROCEDURE _premium_Create_New_Portfolio_04___ (@UserID as int, @Portfolio_Name as varchar (50), @Portfolio_Symbols varchar(1500)) AS

EXEC _premium_Create_New_Portfolio_04___ @UserID = 'tduggan', @Portfolio_Name = 'Some Portfolio', @Portfolio_Symbols = 'A C F D'

DECLARE @PortfolioID INT --Auto-incremented integer

INSERT INTO a_Users_Portfolios (UserID, Portfolio_Name)
VALUES (@UserID, @Portfolio_Name)

SELECT @PortfolioID = SCOPE_IDENTITY()

--INSERT INTO a_Users_Portfolios_Symbol (PortfolioID, Portfolio_Symbol) SELECT cast(@PortfolioID as int), Portfolio_Symbol FROM a_Users_Portfolios_Symbol WHERE Portfolio_Symbol IN ( replace(ltrim(rtrim(@Portfolio_Symbols)), ' ', ''', '''))
PRINT @Portfolio_Symbols

GO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-02-24 : 14:47:26

No. After your stored procedure has been created, you need to test it out. So you run it by running the command that I posted. So CREATE PROC has already been run, then run EXEC statement.

Looking at @Portfolio_Symbols, I see a probem. Run this in Query Analyzer:

declare @portfolio_symbols varchar(8000)

SET @portfolio_symbols = 'A C F D'

select ( replace(ltrim(rtrim(@Portfolio_Symbols)), ' ', ''', '''))

You'll see from the result set that you are missing a single quote at the beginning of A and a single quote after D. You need to fix that part of the code in order for your stored proc to work.

This might work:

('''' + replace(ltrim(rtrim(@Portfolio_Symbols)), ' ', ''', ''') + '''') instead of what you have after IN.

Tara

kloepper
Yak Posting Veteran

77 Posts

Posted - 2004-02-24 : 15:17:07

Tara:
Sorry I'm such a klutz at this. Thanks for your patience.

I ran what you posted (This is for the procedure with the OLD REPLACE):
EXEC _premium_Create_New_Portfolio_04 @UserID = 'tduggan', @Portfolio_Name = 'Some Portfolio', @Portfolio_Symbols = 'A B C D F G'

and it returned this:
Server: Msg 8114, Level 16, State 4, Procedure _premium_Create_New_Portfolio_04, Line 0
Error converting data type varchar to int.
===========================================================================================
I ran what you posted (This is for the procedure with the NEW REPLACE):
EXEC _premium_Create_New_Portfolio_04_ @UserID = 'tduggan', @Portfolio_Name = 'Some Portfolio', @Portfolio_Symbols = 'A B C D F G'

and it returned this:
Server: Msg 8144, Level 16, State 2, Procedure _premium_Create_New_Portfolio_04_, Line 0
Procedure or function _premium_Create_New_Portfolio_04_ has too many arguments specified.

Last night, I played around with that same REPLACE that you just sent and it worked fine by itself, but when I put it into the entire second INSERT INTO line that I'm using, the parenthesis did not match up and they WOULDN'T match up.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources