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?