Passing long text strings to a stored procedure

Hello!

I am attempting to insert a group of records into a SQL Server 2000 database table. The data for each of these records is the same, with the exception of a foreign key (hereafter known as the 'RepKey') and the generated primary key. To improve performance and cut down on the network traffic, I pack the RepKeys in a comma-delimited string and send it as a single parameter, with the intention of parsing it in the stored proicedure to obtain each individual RepKey, or to use it as a list in an 'WHERE RepKey IN (' + @.RepKeyString + ')' type of query.

My problem is that there may be 1000's of items in this string. Using a varchar(8000) as the parameter type is too short, while using the 'text' data type does not allow me to perform any string operations on it. Any ideas on how to make one network call and insert multiple records that breaks the 8000 character barrier?

One thing that I cannot do is add a table so that the record is stored once, then mapped to each individual rep key. The database structure cannot change. Other solutions that I may not have considered are welcome. Thanks!Can you add a global temp table?
You could write all the data to a disk file then bcp it in to the global table.
Could also create a disconnected recordset on the golobal temp table, diconnect it, populate it then connect it to commit the records then use that.

You could use a text datatype then use substring to parse it in chunks and use char functions on it.|||Nigelrivett idea of a text file sounds interesting. What if the parameter used in your stored procedure was the path to a file containing the list of RepKeys? Once in your procedure you use BULK INSERT into a temporay table (Globle table if needed like nigelrivett suggested) then perform the same looping as you would have done before.

sp_MyProc (RepKeyFile AS varchar(50), ....)

CREATE TABLE #temptable ...
BULK INSERT #temptable FROM @.RepKeyFile
.
.
.
CREATE CURSOR on #temptable
loop through

The only problem is your point on:
or to use it as a list in an 'WHERE RepKey IN (' + @.RepKeyString + ')' type of query.

I thought that you could create a local text variable and while looping append the RepKey to the local text field, SET @.txt = @.txt + ',' + @.RepKey. However I got an error when trying to create a local variable as type text.

Msg 2739, Level 16, State 1, Server ATLAS, Line 1
The text, ntext, and image data types are invalid for local variables.|||Thanks guys - I ended up using the substring procedure to break off chunks, then used an INSERT..SELECT statement that looks like the following:

SET @.query = "INSERT INTO RepContact([fields])"
SET @.query = @.query + "SELECT RepKey, [@.vars] FROM Rep WHERE RepKey IN (" + @.currentString + ")"
exec(@.query)

@.CurrentString is the current list of keys. Each time through the loop, as long as there are still items, the query is run.

Thanks again - if anyone has any ideas on speeding this up, it would be greatly appreciated. (The insert runs a bit slower than I would have hoped).

Everett|||I was thinking that the text file would hold the keys delimitted by crlf so that the bcp would insert them into separate rows and you wouldn't have to do any further processing.|||I think that I would prefer to leave it as it is and avoid writing to and reading from disk. Wouldn't this make it slower, not faster? Anyways, thanks again.|||>> Wouldn't this make it slower, not faster?

Depends on the data and environment.
the bcp will be non-logged so the inser will be faster. It will reduce the handshaking across the network and reduce the amount of manipulation needed before the insert into the production tables.
It would probably end up slower but maybe not. It does give an automatic record of the dta inserted from the text files and makes it easy to make the insert asynchronous if you need to.|||I'll try it during the week and advise you of the outcome.

Thanks again for all of your help.

Passing Index Value

I am attempting to string together insert statements in a stored procedure...

In tblCatalogue there is all the information on catalogues.

In tblArticles there is all the information on articles.

There is a key between the articles table and the catalogues, that reflect which catalogue a specific article belongs in.

I am trying to add a new article and column at the same time in a stored procedure. If a new record is created in the Catalogue table can that KeyCol value then be passed into the Insert statement for the Article table? Would this be done with a "SELECT KeyCol FROM INSERTED" trigger on the Catalogue table? If so how do you trap that output in a variable to be passed to the article insert?

All help and comments are appreciated.I'm not clear on the relationship between articles and catalogs. Must an article be in a catalog? Can an article be in more than one catalog?

It would also help to know what database engine you are targeting (Db2, Oracle, MS-SQL, etc). This can influence many design choices.

-PatP|||Apologies. I am using MSSQL 2000. An article can only appear in one catalog and must appear in a catalog.

The key here is that I am calling a SP, usp_ins_Article. Passed into the SP are two values, ArticleName, and CatalogName. The stored procedure checks to see if the CatalogName exists and if not it calls usp_ins_Catalog, passing in the catalog name. Once that is complete I need to have the value of the KeyCol for the new record. This is then inserted into the table (tblArticles) as a foreign key along with the ArticleName.

What I am hoping for is to have the Catalog SP return the KeyCol value into a variable in the Articles SP.

I hope this makes sense, and thanks for your quick reply.|||If your insert stored procedure concocts the KeyCol value using code, just keep a copy. If it uses an IDENTITY column, recover the value using @.@.IDENTITY.

In both your CREATE PROCEDURE paramenter list and your EXECUTE parameter list for the insert procedure, include the OUTPUT modifier for a parameter to return the value to the caller.

That should get you on your way!

-PatP

passing boolean to stored proc as SQLDBtype.bit not working

Hi I was hoping that someone might be able to help me with this.

I'm trying to figure out why my VB.net code below generates 0 or 1 but doesn't insert it when I can execute my stored procedure with: exec sp 0

myParm = myCommand.Parameters.Add("@.bolProMembCSNM", SqlDbType.Bit)
myParm.Value = IIf(CBool(rblProMembCSNM.SelectedItem.Value) = True, 1, 0)

I've tried everything I used to use with Classic ASP and am stumped now.
Any ideas? I will have to do this for numerous controls on my pages.

Thanks in advance for any advice.If you can execute the stored procedure with bit value in database, that means the value passed from application is not right. You may check the input value by inserting into a temp. table or as a return value to the calling app.|||thank you, that is what I thought.

I have printed out the value of the param in the trace and it is 0 which I find odd as that should be accepted as a valid bit, unless I'm totally missing something.

Is there not a way to pass 'true' or 'false' to the SQLDBtype.bit and have SQL Server convert it to 1 or 0?

still working on it here.
thanks again for your input|||What kind of control is "rblProMembCSNM"?|||thank you,

I took your advice and created a temp table and insert all of my values, after all that it was a different parameter I was passing and I was not escaping the ' ...... very frustrating to find that out after everything but I did learn quite a bit in my research.

Passing ANY value to the <InsertParameters> section of a SQLDataSource

Ok-

I'm new at this, but just found out that to get data off a form and insert into SQL I can scrape it off the form and insert it in the <insertParameter> section by using

<asp:ControlParameterName="text2"Type="String"ControlID="TextBox2"PropertyName="Text"> (Thanks CSharpSean)

Now I ALSO need to set the user name in the same insert statement. I put in a UserName control that is populated when a signed in user shows up on the page. But in my code I've tried:

<asp:ControlParameter Name="UserName" Type="String" ControlID="LoginName1" DefaultValue="Daniel" PropertyName="Text"/>

and I get teh error

DataBinding: 'System.Web.UI.WebControls.LoginName' does not contain a property with the name 'Text'.

So I take PropertyName="Text" out, and get the error:

PropertyName must be set to a valid property name of the control named 'LoginName1' in ControlParameter 'UserName'.

what is the proper property value?

SO....BIG question...

Is there a clean way to pass UserName to the insert parameters? Or ANY value for that matter? Id like to know how to write somehting like

String s_test = "test string";

then in the updateparameter part of the sqldatasource pass SOMEHITNG like (in bold) <asp:Parameter Name="UserName" Type="String"Value=s_test/>

Thanks in advance...again!

Dan

Create the parameter, with a name attribute and a type. Then in SqlDataSource_Inserting event, just set the value of the parameter to whatever you want it to be. I believe the syntax (VB.NET) is either

e.Command.Parameters("@.UserName").Value=s_test

or

e.InsertCommand.Parameters("@.UserName").Value=s_test

|||

Sounds easy.

Im writing in c#

But Im not sure where you are suggesting dropping in the code?

Is it in the '<InsertParameters> of the sql data source?

What is the 'E' in the e.insert...

Sorry for being so dense, but can you give me a psudo-code example?

Thanks for the help!
Dan

|||

Step by step:

"Create the parameter, with a name attribute and a type." This is what you did before, just add:

<asp:Parameter name="UserName" type="String" /> in the <InsertParameters> section of the sqldatasource control.

"Then in SqlDataSource_Inserting event": double click the sqldatasource control while in design mode.

That should take you to your code behind and create a dummy event called "SqlDatasource1_Selecting".

Now at the top of the window, there are two dropdown list boxes. The right one should say "Selecting", change it to "Inserting". Now you should have a dummy event sub called "SqlDataSource1_Inserting". The code I gave goes in there. "e" is the second parameter of the two that gets passed in whenever the sqldatasource control is about to do an insert.

If you were doing this in VB.Net, your code behind page (Mypage.aspx.vb) would have these two new subs:

ProtectedSub SqlDataSource1_Inserting(ByVal senderAsObject,ByVal eAs System.Web.UI.WebControls.SqlDataSourceCommandEventArgs)Handles SqlDataSource1.Inserting

e.Command.Parameters("@.UserName").Value=s_test

EndSub

ProtectedSub SqlDataSource1_Selecting(ByVal senderAsObject,ByVal eAs System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs)Handles SqlDataSource1.Selecting

EndSub

You can now delete the "SqlDataSource1_Selecting" sub if you want, since you aren't really using it.

|||

Thanks a BUNCH!
(Will try it after i make MORE coffee...)

|||

C# People can use the above discussion and use

protected void SqlDataSource1_Inserting(object sender, SqlDataSourceCommandEventArgs e)
{
String var1 = TextBox1.Text;
e.Command.Parameters["@.text1"].Value = var1;
}

in the inserting function

sql

Passing a variable to multiple rows in an INSERT

Through a stored procedure I want to insert twelve rows into a table with
each value dependent on a single date value passed to the procedure. I've
tried various combinations of SET and GO, but always lose the variable
definition. What would be the structure of the statements for inserting the
rows?Why don't you post the procedure and let us look at that? Otherwise,
my answer is 42.
Stu|||You can't have GO inside a stored procedure. GO ends the procedure. Can you
be more specific what
you want to achieve? Something like:
CREATE PROC p @.dt datetime
AS
INSERT INTO tbl (c1, c2) VALUES(1, @.dt)
INSERT INTO tbl (c1, c2) VALUES(2, @.dt)
INSERT INTO tbl (c1, c2) VALUES(3, @.dt)
INSERT INTO tbl (c1, c2) VALUES(4, @.dt)
INSERT INTO tbl (c1, c2) VALUES(5, @.dt)
INSERT INTO tbl (c1, c2) VALUES(6, @.dt)
INSERT INTO tbl (c1, c2) VALUES(7, @.dt)
INSERT INTO tbl (c1, c2) VALUES(8, @.dt)
INSERT INTO tbl (c1, c2) VALUES(9, @.dt)
GO
I know, probably not, but as you probably understand it s pretty hard to gue
ss from your desciption.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"TLD" <TLD@.discussions.microsoft.com> wrote in message
news:F2F3AFE8-9ECD-4E11-950F-5619BC1B9A47@.microsoft.com...
> Through a stored procedure I want to insert twelve rows into a table with
> each value dependent on a single date value passed to the procedure. I've
> tried various combinations of SET and GO, but always lose the variable
> definition. What would be the structure of the statements for inserting th
e
> rows?|||Thanks. This is what I've got:
CREATE TABLE tblMaturityDate
(Duration varchar(15),
MaturityDate smalldatetime)
INSERT INTO tblMaturityDate
VALUES ('3 mo', CASE WHEN DATEPART(dw,DATEADD(mm,3,GETDATE())) = 1
THEN DATEADD(mm,3,GETDATE()) + 1
WHEN DATEPART(dw,DATEADD(mm,3,GETDATE())) = 7
THEN DATEADD(mm,3,GETDATE()) + 2
ELSE DATEADD(mm,3,GETDATE())
END)
INSERT INTO tblMaturityDate
VALUES ('6 mo', CASE WHEN DATEPART(dw,DATEADD(mm,6,GETDATE())) = 1
THEN DATEADD(mm,6,GETDATE()) + 1
WHEN DATEPART(dw,DATEADD(mm,6,GETDATE())) = 7
THEN DATEADD(mm,6,GETDATE()) + 2
ELSE DATEADD(mm,6,GETDATE())
END)
INSERT INTO tblMaturityDate
VALUES ('9 mo', CASE WHEN DATEPART(dw,DATEADD(mm,9,GETDATE())) = 1
THEN DATEADD(mm,9,GETDATE()) + 1
WHEN DATEPART(dw,DATEADD(mm,9,GETDATE())) = 7
THEN DATEADD(mm,9,GETDATE()) + 2
ELSE DATEADD(mm,9,GETDATE())
END)
INSERT INTO tblMaturityDate
VALUES ('12 mo', CASE WHEN DATEPART(dw,DATEADD(mm,12,GETDATE())) = 1
THEN DATEADD(mm,12,GETDATE()) + 1
WHEN DATEPART(dw,DATEADD(mm,12,GETDATE())) = 7
THEN DATEADD(mm,12,GETDATE()) + 2
ELSE DATEADD(mm,12,GETDATE())
END)
INSERT INTO tblMaturityDate
VALUES ('15 mo', CASE WHEN DATEPART(dw,DATEADD(mm,15,GETDATE())) = 1
THEN DATEADD(mm,15,GETDATE()) + 1
WHEN DATEPART(dw,DATEADD(mm,15,GETDATE())) = 7
THEN DATEADD(mm,15,GETDATE()) + 2
ELSE DATEADD(mm,15,GETDATE())
END)
INSERT INTO tblMaturityDate
VALUES ('18 mo', CASE WHEN DATEPART(dw,DATEADD(mm,18,GETDATE())) = 1
THEN DATEADD(mm,18,GETDATE()) + 1
WHEN DATEPART(dw,DATEADD(mm,18,GETDATE())) = 7
THEN DATEADD(mm,18,GETDATE()) + 2
ELSE DATEADD(mm,18,GETDATE())
END)
INSERT INTO tblMaturityDate
VALUES ('2 yr', CASE WHEN DATEPART(dw,DATEADD(yy,2,GETDATE())) = 1
THEN DATEADD(yy,2,GETDATE()) + 1
WHEN DATEPART(dw,DATEADD(yy,2,GETDATE())) = 7
THEN DATEADD(yy,2,GETDATE()) + 2
ELSE DATEADD(yy,2,GETDATE())
END)
INSERT INTO tblMaturityDate
VALUES ('3 yr', CASE WHEN DATEPART(dw,DATEADD(yy,3,GETDATE())) = 1
THEN DATEADD(yy,2,GETDATE()) + 1
WHEN DATEPART(dw,DATEADD(yy,3,GETDATE())) = 7
THEN DATEADD(yy,2,GETDATE()) + 2
ELSE DATEADD(yy,2,GETDATE())
END)
INSERT INTO tblMaturityDate
VALUES ('5 yr', CASE WHEN DATEPART(dw,DATEADD(yy,5,GETDATE())) = 1
THEN DATEADD(yy,2,GETDATE()) + 1
WHEN DATEPART(dw,DATEADD(yy,5,GETDATE())) = 7
THEN DATEADD(yy,2,GETDATE()) + 2
ELSE DATEADD(yy,2,GETDATE())
END)
"Stu" wrote:

> Why don't you post the procedure and let us look at that? Otherwise,
> my answer is 42.
> Stu
>|||That looks very simple. I'll try it and let you know.
I've posted what I have so far.
Thank you.
"Tibor Karaszi" wrote:

> You can't have GO inside a stored procedure. GO ends the procedure. Can yo
u be more specific what
> you want to achieve? Something like:
> CREATE PROC p @.dt datetime
> AS
> INSERT INTO tbl (c1, c2) VALUES(1, @.dt)
> INSERT INTO tbl (c1, c2) VALUES(2, @.dt)
> INSERT INTO tbl (c1, c2) VALUES(3, @.dt)
> INSERT INTO tbl (c1, c2) VALUES(4, @.dt)
> INSERT INTO tbl (c1, c2) VALUES(5, @.dt)
> INSERT INTO tbl (c1, c2) VALUES(6, @.dt)
> INSERT INTO tbl (c1, c2) VALUES(7, @.dt)
> INSERT INTO tbl (c1, c2) VALUES(8, @.dt)
> INSERT INTO tbl (c1, c2) VALUES(9, @.dt)
> GO
> I know, probably not, but as you probably understand it s pretty hard to g
uess from your desciption.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "TLD" <TLD@.discussions.microsoft.com> wrote in message
> news:F2F3AFE8-9ECD-4E11-950F-5619BC1B9A47@.microsoft.com...
>|||That code executed without errors on my machine...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"TLD" <TLD@.discussions.microsoft.com> wrote in message
news:5E81EF19-7534-4E02-B96A-C59D8DA5EE1C@.microsoft.com...
> Thanks. This is what I've got:
> CREATE TABLE tblMaturityDate
> (Duration varchar(15),
> MaturityDate smalldatetime)
> INSERT INTO tblMaturityDate
> VALUES ('3 mo', CASE WHEN DATEPART(dw,DATEADD(mm,3,GETDATE())) = 1
> THEN DATEADD(mm,3,GETDATE()) + 1
> WHEN DATEPART(dw,DATEADD(mm,3,GETDATE())) = 7
> THEN DATEADD(mm,3,GETDATE()) + 2
> ELSE DATEADD(mm,3,GETDATE())
> END)
> INSERT INTO tblMaturityDate
> VALUES ('6 mo', CASE WHEN DATEPART(dw,DATEADD(mm,6,GETDATE())) = 1
> THEN DATEADD(mm,6,GETDATE()) + 1
> WHEN DATEPART(dw,DATEADD(mm,6,GETDATE())) = 7
> THEN DATEADD(mm,6,GETDATE()) + 2
> ELSE DATEADD(mm,6,GETDATE())
> END)
> INSERT INTO tblMaturityDate
> VALUES ('9 mo', CASE WHEN DATEPART(dw,DATEADD(mm,9,GETDATE())) = 1
> THEN DATEADD(mm,9,GETDATE()) + 1
> WHEN DATEPART(dw,DATEADD(mm,9,GETDATE())) = 7
> THEN DATEADD(mm,9,GETDATE()) + 2
> ELSE DATEADD(mm,9,GETDATE())
> END)
> INSERT INTO tblMaturityDate
> VALUES ('12 mo', CASE WHEN DATEPART(dw,DATEADD(mm,12,GETDATE())) = 1
> THEN DATEADD(mm,12,GETDATE()) + 1
> WHEN DATEPART(dw,DATEADD(mm,12,GETDATE())) = 7
> THEN DATEADD(mm,12,GETDATE()) + 2
> ELSE DATEADD(mm,12,GETDATE())
> END)
> INSERT INTO tblMaturityDate
> VALUES ('15 mo', CASE WHEN DATEPART(dw,DATEADD(mm,15,GETDATE())) = 1
> THEN DATEADD(mm,15,GETDATE()) + 1
> WHEN DATEPART(dw,DATEADD(mm,15,GETDATE())) = 7
> THEN DATEADD(mm,15,GETDATE()) + 2
> ELSE DATEADD(mm,15,GETDATE())
> END)
> INSERT INTO tblMaturityDate
> VALUES ('18 mo', CASE WHEN DATEPART(dw,DATEADD(mm,18,GETDATE())) = 1
> THEN DATEADD(mm,18,GETDATE()) + 1
> WHEN DATEPART(dw,DATEADD(mm,18,GETDATE())) = 7
> THEN DATEADD(mm,18,GETDATE()) + 2
> ELSE DATEADD(mm,18,GETDATE())
> END)
> INSERT INTO tblMaturityDate
> VALUES ('2 yr', CASE WHEN DATEPART(dw,DATEADD(yy,2,GETDATE())) = 1
> THEN DATEADD(yy,2,GETDATE()) + 1
> WHEN DATEPART(dw,DATEADD(yy,2,GETDATE())) = 7
> THEN DATEADD(yy,2,GETDATE()) + 2
> ELSE DATEADD(yy,2,GETDATE())
> END)
> INSERT INTO tblMaturityDate
> VALUES ('3 yr', CASE WHEN DATEPART(dw,DATEADD(yy,3,GETDATE())) = 1
> THEN DATEADD(yy,2,GETDATE()) + 1
> WHEN DATEPART(dw,DATEADD(yy,3,GETDATE())) = 7
> THEN DATEADD(yy,2,GETDATE()) + 2
> ELSE DATEADD(yy,2,GETDATE())
> END)
> INSERT INTO tblMaturityDate
> VALUES ('5 yr', CASE WHEN DATEPART(dw,DATEADD(yy,5,GETDATE())) = 1
> THEN DATEADD(yy,2,GETDATE()) + 1
> WHEN DATEPART(dw,DATEADD(yy,5,GETDATE())) = 7
> THEN DATEADD(yy,2,GETDATE()) + 2
> ELSE DATEADD(yy,2,GETDATE())
> END)
>
> "Stu" wrote:
>|||I'm sorry; I'm a bit . Where's the variable that you're trying
to declare, but always lose? Is it to replace the GETDATE() function?
Stu|||Yes, that's right. It finds the date from a field on a form and passes it to
the procedure.
"Stu" wrote:

> I'm sorry; I'm a bit . Where's the variable that you're trying
> to declare, but always lose? Is it to replace the GETDATE() function?
> Stu
>|||It worked in Query Analyzer, but it didn't work when created as a procedure.
I replaced the GETDATE() with a variable, but couldn't pass the variable dow
n
all of the rows.
"Tibor Karaszi" wrote:

> That code executed without errors on my machine...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "TLD" <TLD@.discussions.microsoft.com> wrote in message
> news:5E81EF19-7534-4E02-B96A-C59D8DA5EE1C@.microsoft.com...
>|||I've had some success, but not complete. The following, when applied to a
combo box, returns "The stored procedure completed successfully, but did not
return any records." Am I close?
CREATE PROCEDURE spp_MaturityDateComboBox
(@.DealDate smalldatetime)
AS
DELETE FROM tblMaturityDate
INSERT INTO tblMaturityDate
VALUES ('3 mo', CASE WHEN DATEPART(dw,DATEADD(mm,3,@.DealDate)) = 1
THEN DATEADD(mm,3,@.DealDate) + 1
WHEN DATEPART(dw,DATEADD(mm,3,@.DealDate)) = 7
THEN DATEADD(mm,3,@.DealDate) + 2
ELSE DATEADD(mm,3,@.DealDate)
END)
INSERT INTO tblMaturityDate
VALUES ('6 mo', CASE WHEN DATEPART(dw,DATEADD(mm,6,@.DealDate)) = 1
THEN DATEADD(mm,6,@.DealDate) + 1
WHEN DATEPART(dw,DATEADD(mm,6,@.DealDate)) = 7
THEN DATEADD(mm,6,@.DealDate) + 2
ELSE DATEADD(mm,6,@.DealDate)
END)
INSERT INTO tblMaturityDate
VALUES ('9 mo', CASE WHEN DATEPART(dw,DATEADD(mm,9,@.DealDate)) = 1
THEN DATEADD(mm,9,@.DealDate) + 1
WHEN DATEPART(dw,DATEADD(mm,9,@.DealDate)) = 7
THEN DATEADD(mm,9,@.DealDate) + 2
ELSE DATEADD(mm,9,@.DealDate)
END)
INSERT INTO tblMaturityDate
VALUES ('12 mo', CASE WHEN DATEPART(dw,DATEADD(mm,12,@.DealDate)) = 1
THEN DATEADD(mm,12,@.DealDate) + 1
WHEN DATEPART(dw,DATEADD(mm,12,@.DealDate)) = 7
THEN DATEADD(mm,12,@.DealDate) + 2
ELSE DATEADD(mm,12,@.DealDate)
END)
INSERT INTO tblMaturityDate
VALUES ('15 mo', CASE WHEN DATEPART(dw,DATEADD(mm,15,@.DealDate)) = 1
THEN DATEADD(mm,15,@.DealDate) + 1
WHEN DATEPART(dw,DATEADD(mm,15,@.DealDate)) = 7
THEN DATEADD(mm,15,@.DealDate) + 2
ELSE DATEADD(mm,15,@.DealDate)
END)
INSERT INTO tblMaturityDate
VALUES ('18 mo', CASE WHEN DATEPART(dw,DATEADD(mm,18,@.DealDate)) = 1
THEN DATEADD(mm,18,@.DealDate) + 1
WHEN DATEPART(dw,DATEADD(mm,18,@.DealDate)) = 7
THEN DATEADD(mm,18,@.DealDate) + 2
ELSE DATEADD(mm,18,@.DealDate)
END)
INSERT INTO tblMaturityDate
VALUES ('2 yr', CASE WHEN DATEPART(dw,DATEADD(yy,2,@.DealDate)) = 1
THEN DATEADD(yy,2,@.DealDate) + 1
WHEN DATEPART(dw,DATEADD(yy,2,@.DealDate)) = 7
THEN DATEADD(yy,2,@.DealDate) + 2
ELSE DATEADD(yy,2,@.DealDate)
END)
INSERT INTO tblMaturityDate
VALUES ('3 yr', CASE WHEN DATEPART(dw,DATEADD(yy,3,@.DealDate)) = 1
THEN DATEADD(yy,2,@.DealDate) + 1
WHEN DATEPART(dw,DATEADD(yy,3,@.DealDate)) = 7
THEN DATEADD(yy,2,@.DealDate) + 2
ELSE DATEADD(yy,2,@.DealDate)
END)
INSERT INTO tblMaturityDate
VALUES ('5 yr', CASE WHEN DATEPART(dw,DATEADD(yy,5,@.DealDate)) = 1
THEN DATEADD(yy,2,@.DealDate) + 1
WHEN DATEPART(dw,DATEADD(yy,5,@.DealDate)) = 7
THEN DATEADD(yy,2,@.DealDate) + 2
ELSE DATEADD(yy,2,@.DealDate)
END)
SELECT * FROM tblMaturityDate
GO
--End
"TLD" wrote:

> Through a stored procedure I want to insert twelve rows into a table with
> each value dependent on a single date value passed to the procedure. I've
> tried various combinations of SET and GO, but always lose the variable
> definition. What would be the structure of the statements for inserting th
e
> rows?

Pass XML Data to a Stored Procedure

Does anyone know how to pass a XML File to a MS SQL 2005 Stored Procedure (INSERT/UPDATE), and how to create the stored procedure so it will accept the XML values using VB 2005.

Here is an example of the XML File.

Code:
<MYROOT>
<TableName>
<Field1>String</Field1>
<Field2>String</Field2>
<Field3>String</Field3>
</TableName>
</MYROOT>

Thank you.
bty The following link provided by Microsoft does not work in VB 2005, I have tried that

http://support.microsoft.com/default.aspx?scid=kb;en-us;555266

You can do this using XQuery to strip the values and pass them to the stored proc. You can view a good article on using XQuery here:

http://www.15seconds.com/issue/050803.htm

***

Download this free script (WSP Snapshot 1.0) to take snapshot sample of your web server(s) from anywhere/anytime using a browser. View the stats (cpu and disk stress, available memory, requests queued, request wait time and more) on a cellphone or PDA also. http://www.ifusionsoft.com

|||

Also check out Sushil's weblog on this ->

http://blogs.msdn.com/sushilc/archive/2004/08/03/207162.aspx

This explains the basics of sending XML to SQL 2005 as a parameter.

Pass variables across forms, then insert into database

Hey Guys:

-- Not sure if this should be moved to webforms forum, or if it belongs here --

Alright, I have been dealing with this issue for a few days now, and have found a few solutions but they all seem to throw different errors so I figured I'd ask here.

What i am trying to do is have a webform where user enter data, and have the data passed across forms, then displayed and inserted into a database on another form. THe first for has an asp:rangevalidator control dymamicly built so I cannot simply take of the tags and use the old style.

Eventually the user will be directed to a paypal form, and upon successful completion be redirected to the page with the insert command within it, but for now, passing it to a second page for review, then inserting it will work.

I am not sure how to accomplish this, a tutorial or a code example would be great!! I have though about panels, creating public objects, etc, but all the solutions I have found have one issue or another when I attempt to create them.

I'm using asp.net 1.1, VB.net and SQL server.

Thanks,
Brian Sierakowskiyou can store the data in some temp tables ( actual tables but we call them temp coz they hold data temporarily) in your DB as the user goes through each page and eventually move them to the actual tables after your final page. this way when the user needs to go back you can always pull the data out of the table. so you would just need to get the autonumber id from the first page and keep it in memory and pass it over to the next page so the subsequent page can update the table using the id.

this is one of the ways you can do..

hth

pass the parameters with more then one insert into statement

We could do this
Insert into tablename (field1, field2) values ('apple','23'), ('organes',
34), etc.
How can we pass the parameters so I can do that in stored procedure?
Thanks.
Grant
Who gives a {censored} if I am wrong.Grant,
Create an xml document and pass it to the stored procedure. See function
"openxml" in BOL for more info.
AMB
"Grant" wrote:

> We could do this
> Insert into tablename (field1, field2) values ('apple','23'), ('organes',
> 34), etc.
>
> How can we pass the parameters so I can do that in stored procedure?
>
> Thanks.
>
> --
> Grant
> Who gives a {censored} if I am wrong.
>
>|||Grant wrote:
> We could do this
> Insert into tablename (field1, field2) values ('apple','23'), ('organes',
> 34), etc.
>
> How can we pass the parameters so I can do that in stored procedure?
>
> Thanks.
>
> --
> Grant
> Who gives a {censored} if I am wrong.
INSERT INTO tablename (col1, col2)
SELECT @.p1, @.p2 UNION ALL
SELECT @.p3, @.p4 UNION ALL
SELECT @.p5, @.p6 ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||but you cannot do that in SP, right? We will not know how many records will
be inserted.
Grant
Who gives a {censored} if I am wrong.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1144093592.580516.164870@.i40g2000cwc.googlegroups.com...
> Grant wrote:
> INSERT INTO tablename (col1, col2)
> SELECT @.p1, @.p2 UNION ALL
> SELECT @.p3, @.p4 UNION ALL
> SELECT @.p5, @.p6 ;
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||You could pass the data as a comma separated string, there's a good
article here:
http://www.sommarskog.se/arrays-in-sql.html
that will describe efficient ways to split it back out into a temporary
table, from which you could easily call a multirow insert.|||Grant wrote:
> but you cannot do that in SP, right? We will not know how many records wil
l
> be inserted.
> --
> Grant
You can do it like the following example. I'm assuming Col1 is the key
column here. Procs can have over 2000 optional parameters. If you need
as many as that then I'd consider the XML solution.
CREATE PROC dbo.usp_tablename_insert
(
@.p1 INTEGER = NULL,
@.p2 INTEGER = NULL,
@.p3 INTEGER = NULL,
@.p4 INTEGER = NULL,
@.p5 INTEGER = NULL,
@.p6 INTEGER = NULL
)
AS
BEGIN
INSERT INTO tablename (col1, col2)
SELECT col1, col2
FROM
(SELECT @.p1, @.p2 UNION ALL
SELECT @.p3, @.p4 UNION ALL
SELECT @.p5, @.p6)
AS T(col1, col2)
WHERE col1 IS NOT NULL ;
END ;
RETURN ;
GO
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx|||>> but you cannot do that in SP, right? We will not know how many records [sic] w
ill
be inserted. <<
teh syntax you guessed at in your first posting was Standard SQL, but
SQL Server does not support it yet.
You can have just over 1000 parameters in T-SQL, so you could use
David's suggestion with a really long union of pairs. This code will
port, avoid proprietary XML tricks and dynamic SQL. Do you often have
over 500 rows (NOT records) to insrt?|||One table has up to 20 records and another one up to 30 so I am using David
suggestion. I do have one table that could go over hundred so I may use
openxml method for that one but I may just send out 100 at a time because I
think opensml is not good for performance.
Grant
Who gives a {censored} if I am wrong.
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1144270087.388066.264100@.v46g2000cwv.googlegroups.com...
> be inserted. <<
> teh syntax you guessed at in your first posting was Standard SQL, but
> SQL Server does not support it yet.
> You can have just over 1000 parameters in T-SQL, so you could use
> David's suggestion with a really long union of pairs. This code will
> port, avoid proprietary XML tricks and dynamic SQL. Do you often have
> over 500 rows (NOT records) to insrt?
>

pass several records at once for insert ?

If I was to pass several xml elements into a stored procedure (as an xml parameter), how could I then insert all the items into a table ?

Are you wanting to do something similar to what is done in this post:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1501246&SiteID=1

Here is an example that uses NODES:

Code Snippet

declare @.myXml xml

set @.myXml = N'<marketData>
<date>19-Apr-2007 17:08:55</date>
<Rates>
<Rate code="USDCAD">
<values>
<value type="BID">1.1276</value>
<value type="ASK">1.1277</value>
<value type="MID">1.127649997783</value>
</values>
</Rate>
<Rate code="EURUSD">
<values>
<value type="BID">1.3607</value>
<value type="ASK">1.3608</value>
<value type="MID">1.36075</value>
</values>
</Rate>
</Rates>
</marketData>'

select r.value ('http://@.code', 'varchar(20)') as rateCode,
r.value ('./@.type', 'varchar(20)') as type,
r.value ('.', 'varchar(20)') as value
from @.myXml.nodes('/marketData/Rates/Rate/values/value') as x(r)