November 20, 2001 at 3:31 pm
hi
can anyone help.
I am new to the SQL Server.
I need to input data into a column with the PK (Primary Column) From other 2 tables
I am using a stored procedure to store the data.
Table1
NameIDFirstName LastName
1SamSmallwood
Table2
IDNamesofCD
1CD1
2CD2
Table3
NameIDID
12
11
In Real time the Person Sam Smallwood will have more than 2 CD.
In order to create the stored procedure i dont know how many variables to declare in the stored procedure.
I like to know where there is a array datatype in SQL or tell me how to a create a stored procedure with infinite variables
November 20, 2001 at 3:54 pm
There is no array data type but there are ways of simulating it.
Create a character parameter and pass the array in as a csv and parse it in the SP. You would probably want to do this in another SP which populates a temp table so thatit is re-usable. This will be limitted by the size of the variable.
Create a temp table in the client, populate it and the SP acts on the temp table (this means that the SP would be recompiled on every run).
Insert row by row into a table containing the spid, logon time and data. When this is complete call the update SP which then gets the data from the table.
Cursors never.
DTS - only when needed and never to control.
November 20, 2001 at 7:18 pm
Not clear what you mean (JAw1977)? You want to write a stored procedure that will insert what? The data from Tables 2 and 3? You would do a join from these two tables to do the insert. If you want to specify "what" rows to insert, then nigelrivet's answer will work. Alternatively, you could specify a range for the stored procedure or use some other qualifier. If you can give an example of the changes the stored procedure needs to make, we can be more helpful.
Steve Jones
November 21, 2001 at 2:53 am
Thanks for the Idea nigelrivet. (what is csv?)
You are telling to create a temp table which contains the data to be added to the table(3) and by executing a SP (which will add the data from temp table to the table(3)). I think it is correct.
If i have a array of 20 Values. So i need to execute 20 sp's to insert into the temp table and at last update sp (so Totally 21 Sp i should need to execute).
But i have some idea.
I combine the 20 values into a single value with some kind of mark (like ':', '#' Because the Primary key has a datatype NUMERIC(18,0)). Then pass the values into the sp as a single character variable.
Inside the SP i seperate single character value into 20 values and add into the table.
By using this i can use only one sp to update the table - 3. But i dont know it is a good kind of SP or it will work nicely.
What is your idea or comments on this?
November 21, 2001 at 10:32 am
csv is comma separated values. What you proposed is the same as nigelrivet, except you chose a # instead of a ,. Both will work.
Steve Jones
November 21, 2001 at 2:57 pm
In the SP to populate the table you can have a finite number of entries passed, say 10, so your 20 values would only take 2 calls.
It is usual to use a pipe character | to delimit but anything that isn't going to appear in the strings will do.
A csv string is still limitted by the size of the string.
Another way would be to write everything to a text file and then bulk insert it to a staging table in the SP.
Cursors never.
DTS - only when needed and never to control.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply