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?

Passing a session parameter to a report call

Hi

I've created my report with a parameter and a dependent cascading parameter, this all work fine when I preview the .rdl

It also works fine when I access it through my asp.net application.

When accessed the report via my asp,net application the report prompts the user for both parameters, which, when I enter them, also works fine.

I would like to call the report while passing the first parameter to it so that the user only has to select the second parameter, which is dependent on the first.

The first parameter is stored as a session variable, but I cannot figure out how to pass this as a parameter to the report.

I have embedded a report view as:

rsweb:reportviewer id="ReportViewer1" runat="server" processingmode="Remote" width="795px" Font-Names="Verdana" Font-Size="8pt" Height="764px" ShowExportControls="False" ShowFindControls="False" ShowRefreshButton="False" ShowParameterPrompts="true">
<ServerReport ReportPath="/SubSrvs Reports/SubscribedServices" DisplayName="Confirmation Invoices" />
</rsweb:reportviewer>

Is there a way to pass my session value as a parameter? If so where do I do this pls.

I am not using any code behind at the moment.

I have tried passing it in the url of the page I call e.g. Invoices.aspx?ClientId=?? and in thehttp://localhost/reportserver?ClientId=??? but no luck, oh yes I have also tried surpressing the ShowParameterPrompts of the report view, but again no luck

many thanks

string sessParam = Convert.ToString(Session["myparam"]);

RptParameters[0] =
new Microsoft.Reporting.WebForms.ReportParameter("ClientId", sessParam );

this.ReportViewer1.ServerReport.SetParameters(RptParameters);
this.ReportViewer1.ServerReport.Refresh();

Check a basic tutorial over here:

http://www.codeproject.com/sqlrs/ReportViewer2005.asp

HTH,
Suprotim Agarwal

--
http://www.dotnetcurry.com
--

|||

muchas gracias! that worked a treat, used the C# equivalent and placed in the page load event with a !Page.IsPostBack.

also a very good articlehttp://www.codeproject.com/sqlrs/ReportViewer2005.asp

thanks again