Passing OLAP Parameters Within a URL

Hi there!

My report uses an OLAP query with a (multi-valued) parameter, the MDX generated query is something like "...ON ROWS FROM ( SELECT ( STRTOSET(@.Region, CONSTRAINED) ) ON COLUMNS FROM [cube]) ...".

The report works fine using the Report Manager frontend. Now I want to pass this parameter directly within the query string:

https://servername/ReportServer?/PathTo/MyReport&rs:Command=Render&rs:Format=HTML4.0&Region=[foo].[bar 123].&[baz]

But I cannot get Reporting Services to accept the parameter instead of complaining about a missing parameter value. I've already tried quoting the braces, spaces, and the ampersand, putting the dimension in curly braces and/or quotes... No success.

How do I quote this parameter correctly?

I do this befor but so it was as follow

Report server URL(http://Machinename/reportserver/) + Reports Folder (MyReports/)+Reportname (My report) +"&Firstparametername"+Value+"&secondparametername"+Value

it will be like this

http://Machinename/reportserver/(MyReports/My repor&Firstparametername=Value1&secondparametername=Value

|||

Thanks for your reply.

Normal query parameters work fine, I just can't pass OLAP parameters (dimensions like "[foo].[bar 123].&[baz]") this way. ReportServer won't accept them, I think it doesn't like the way I'm trying to quote them...

Thanks and best regards,
Thomas

|||

Hi Tamer,

I was reading your question and i have the same problem.

Please, tell me, Have you resolved this issue ?

email me to :megch00@.hotmail.com ormanuelgo@.cr-dss.com

Thanks a lot !!

|||

Hi there!

It was indeed quoting related, I must have confused hex with decimal notation in my earlier attempts.

All you have to do is urlencode the parameter names and values. Here is how to quote correctly:

InputEncoded[%5B]%5D&%26spaces+ or%20

Or you let ASP.Net do the job:

1string encodedParam = HttpContext.Current.Server.UrlEncode(myParam);

So ...&Region=[foo].[bar 123].&[baz]... becomes...&Region=%5Bfoo%5D.%5Bbar+123%5D.%26%5Bbaz%5D...

If you want this parameter to have multiple values, just use it multiple times in your URL: ...&Region=%5Bfoo%5D.%5Bbar+123%5D.%26%5Bbaz%5D&Region=%5Bfoo%5D.%5Bbar+123%5D.%26%5Bheureka%5D...

HTH and best regards,
Thomas

sql

Passing Multiple values in Drill Through Report

I have a report called ReportA which has a group header that is summing
detail rows. In the textbox that I am using to some I have selected the
action property and set it to "Jump to" ReportB. ReportB has a parameter of
StudentID. Based on Summed values from the detail section of ReportA I would
like to pass the StudentID's to ReportB. Basically what I'm saying is I
would like for ReportA to pass ReportB a parameter that has multiple values.
Either this way or any way that based on the action property of a textbox I
could pass the multiple values to another report. Any help or ideas would be
appreciated.Should work similar to other multiple value parameter reports. Search this
newsgroup for "multi-value parameters" and "multiple parameters" for
postings from folks who have asked similar question.
--
-- "This posting is provided 'AS IS' with no warranties, and confers no
rights."
jhmiller@.online.microsoft.com
"P" <P@.discussions.microsoft.com> wrote in message
news:BCD0DCB4-C644-491D-91FC-79349F89085E@.microsoft.com...
>I have a report called ReportA which has a group header that is summing
> detail rows. In the textbox that I am using to some I have selected the
> action property and set it to "Jump to" ReportB. ReportB has a parameter
> of
> StudentID. Based on Summed values from the detail section of ReportA I
> would
> like to pass the StudentID's to ReportB. Basically what I'm saying is I
> would like for ReportA to pass ReportB a parameter that has multiple
> values.
> Either this way or any way that based on the action property of a textbox
> I
> could pass the multiple values to another report. Any help or ideas would
> be
> appreciated.|||One problem is that I have a cell on a table that is the sum of other values.
I need to set the action property for this cell that is summing to be able
to pass as a parameter StudentID's of the related records that make up the
summed values. If I can get pass this I can accomplish what I need.
Thanks!
"John H. Miller" wrote:
> Should work similar to other multiple value parameter reports. Search this
> newsgroup for "multi-value parameters" and "multiple parameters" for
> postings from folks who have asked similar question.
> --
> -- "This posting is provided 'AS IS' with no warranties, and confers no
> rights."
> jhmiller@.online.microsoft.com
> "P" <P@.discussions.microsoft.com> wrote in message
> news:BCD0DCB4-C644-491D-91FC-79349F89085E@.microsoft.com...
> >I have a report called ReportA which has a group header that is summing
> > detail rows. In the textbox that I am using to some I have selected the
> > action property and set it to "Jump to" ReportB. ReportB has a parameter
> > of
> > StudentID. Based on Summed values from the detail section of ReportA I
> > would
> > like to pass the StudentID's to ReportB. Basically what I'm saying is I
> > would like for ReportA to pass ReportB a parameter that has multiple
> > values.
> > Either this way or any way that based on the action property of a textbox
> > I
> > could pass the multiple values to another report. Any help or ideas would
> > be
> > appreciated.
>
>

Passing multiple rows of data to a code function

Is there a way to pass multiple rows to a function on the report? Here's
what I'm trying to do:
I have field in my detail section, "customer", that is shows each customer:
[Customer1]
[Customer2]
[Customer3]
[etc.]
I'd like to wrap these into a single field at the parent group so I get the
following in a single field:
[Customer1, Customer2, Customer3, etc.]
Does that make sense?
I was thinking I might be able to write a VB function to take in a group of
records, itereate through them, and return the reformated string.
Of course if there's another way to accomplish this, I'm completely open to
other ideas.hi,i think it's better 2 do it on the sql side,so u get it in the ds as one
field.
"Greg S" wrote:
> Is there a way to pass multiple rows to a function on the report? Here's
> what I'm trying to do:
> I have field in my detail section, "customer", that is shows each customer:
> [Customer1]
> [Customer2]
> [Customer3]
> [etc.]
> I'd like to wrap these into a single field at the parent group so I get the
> following in a single field:
> [Customer1, Customer2, Customer3, etc.]
> Does that make sense?
> I was thinking I might be able to write a VB function to take in a group of
> records, itereate through them, and return the reformated string.
> Of course if there's another way to accomplish this, I'm completely open to
> other ideas.
>
>|||Take a look at the matrix control and see if that will work for you.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Greg S" <gregslistacct@.hotmail.com> wrote in message
news:e2lvbHHIGHA.532@.TK2MSFTNGP15.phx.gbl...
> Is there a way to pass multiple rows to a function on the report? Here's
> what I'm trying to do:
> I have field in my detail section, "customer", that is shows each
> customer:
> [Customer1]
> [Customer2]
> [Customer3]
> [etc.]
> I'd like to wrap these into a single field at the parent group so I get
> the following in a single field:
> [Customer1, Customer2, Customer3, etc.]
> Does that make sense?
> I was thinking I might be able to write a VB function to take in a group
> of records, itereate through them, and return the reformated string.
> Of course if there's another way to accomplish this, I'm completely open
> to other ideas.
>|||Follow up solution to my own thread:
Well, I did find some way to do this
concatenation/aggregation/rows-to-a-column on the SQL side. Here's a good
example using CROSS APPLY and leveraging FOR XML in sql 2005
http://www.aspfaq.com/show.asp?id=2529
I found a number of other examples as well - some using UDF functions,
orthers using customer CRL assemblies. Most threads had someone commenting
to the effect of "... this is usually needed for some kind of reporting and
should be handled in the presentation layer... doing it via SQL is breaking
the idea of pure relational databases..." Just thought this was funny as
my presentation layer (reporting services) can't do it. :^)
"'" <@.discussions.microsoft.com> wrote in message
news:4D2617C3-804E-4A10-AE87-53C8CB077DD4@.microsoft.com...
> hi,i think it's better 2 do it on the sql side,so u get it in the ds as
> one
> field.
> "Greg S" wrote:
>> Is there a way to pass multiple rows to a function on the report? Here's
>> what I'm trying to do:
>> I have field in my detail section, "customer", that is shows each
>> customer:
>> [Customer1]
>> [Customer2]
>> [Customer3]
>> [etc.]
>> I'd like to wrap these into a single field at the parent group so I get
>> the
>> following in a single field:
>> [Customer1, Customer2, Customer3, etc.]
>> Does that make sense?
>> I was thinking I might be able to write a VB function to take in a group
>> of
>> records, itereate through them, and return the reformated string.
>> Of course if there's another way to accomplish this, I'm completely open
>> to
>> other ideas.
>>

Passing Integer list to SQL using TableAdapter?

I want to select rows that have an integer value IN a parameter list value

I want to pass an integer list to my tableadapter's select

as in Select * from sometable

Where myIntValue IN (@.intParamList )

I cannot figure out how to get my list into @.intParamList

It works fine as a single value - eg 3, but how do I set it to 1,2,3 ?

Thanks

Bill

Hi,

I don't think you can't pass 1,2,3 as integer parameter according to my knowledge. Well you might have to change the parameter to varchar and it will work for you.

Thanks and best regards,

|||

Thanks I got it.

The answer is to use a varchar list AND a Numbers table which allows me to Select the values without using IN

Googling SQL Numbers table gives several examples

Bill

Passing IN() values as parameter

Hi...

I'm creating a procedure to fetch rows from table. One field will come come as IN(). Its the condition. That field is numeric field (note down), i would like to pass the In values as parameter.

eg: procedurename @.fieldvalue varchar(100)
as
begin
...

WHERE fieldname IN(@.fieldvalue)

while executing the procedure how to pass the value... or procedure itself has problem...?
Help me...

Tx in Advance...Once again dynamic SQL seems to be the deal here, unless a more complete description of the problem is available.
The thing here is that you cannot do a procedure that works in the way you describe without using dynamic SQL, because of the datatype clash.

Your IN clause wants to do comparisons with integers, and therefore your argument to the procedure will never be able to work unless you convert your values in the fieldvalue variable to a string which you add to the the last part of your existing query, and execute it with dynamic SQL. I cannot judge whether this is the right decision to do in your particular case without more information about the problem. Hope I got your thinking going atleast ...|||In your procedure, parse the parameter into a temp table. Join your temp table to the production table to limit the result set. Works every time, and often runs much faster than the dynamic SQL solution.

-PatP

Passing data to SQL Server

How to pass big set of data from ASP to SQL Server stored procedure (for example 30 rows and 5 columns - content of html table) ?Stored procedures cannot accept recordsets as parameters, so you will need to pass the data to the procedure one record at a time.

Another alternative is to insert your data into a staging table. Then your application either calls the stored procedure or you create a scheduled job that calls the stored procedure. The procedure checks the staging table for records and processes them as a set.

blindman|||What about XML?sql

Passing Column Name as Parameter (SPROC)

Hello, I don't know a lot of T-SQL. I have a table with 49 columns and 48 rows (US States). The fist column is a list of "from" states and the other columns are named for each state. The point is to look up a variable column for a variable row to obtain a state to state transit rate. I tried everything I could find and no luck. If someone can help I would appreciate it, and if not thanks anyway. Gregg

You could use dynamic SQL from this problem:

Code Snippet

create table rates

(

from_state char(2),

WA decimal,

NY decimal,

CA decimal

)

insert into rates values('WA',0,1,10)

insert into rates values('NY',2,0,20)

insert into rates values('CA',3,30,0)

create procedure GetRate

@.from_state char(2),

@.to_state char(2)

AS

BEGIN

declare @.query varchar(100)

set @.query = 'select '+@.to_state+' from rates where from_state='''+@.from_state+''''

execute ( @.query)

END

But this method has SQL Injections.

Partly you could solve this problem by following code:

Code Snippet

create procedure GetRate2

@.from_state char(2),

@.to_state char(2)

AS

BEGIN

declare @.query nvarchar(100)

set @.query = 'select '+@.to_state+' from rates where from_state=@.from_state'

EXEC sp_executesql @.query, N'@.from_state char(2)', @.from_state=@.from_state

END

But best solution is convert you table for following format:

Code Snippet

create table rates2

(

from_state char(2),

to_state char(2),

rate decimal

)

|||

Here the sample ..

Code Snippet

--Sample table (first 10 States)

Create Table #statesfare (

[Starting From] Varchar(100) ,

[Alabama] Varchar(100) ,

[Alaska] Varchar(100) ,

[Arizona] Varchar(100) ,

[Arkansas] Varchar(100) ,

[California] Varchar(100) ,

[Colorado] Varchar(100) ,

[Connecticut] Varchar(100) ,

[Delaware] Varchar(100) ,

[Florida] Varchar(100) ,

[Georgia] Varchar(100)

);

--Random Generated data (Not For Scale, But City From & To same then ZERO)

Insert Into #statesfare Values('Alabama','0','21','29','90','82','65','72','84','4','51');

Insert Into #statesfare Values('Alaska','33','0','17','80','37','92','90','21','12','20');

Insert Into #statesfare Values('Arizona','62','19','0','97','23','66','22','43','94','60');

Insert Into #statesfare Values('Arkansas','61','38','12','0','98','42','68','70','81','87');

Insert Into #statesfare Values('California','92','27','82','72','0','43','84','39','24','80');

Insert Into #statesfare Values('Colorado','72','34','97','52','52','0','10','38','64','40');

Insert Into #statesfare Values('Connecticut','100','78','27','18','74','3','0','67','26','48');

Insert Into #statesfare Values('Delaware','93','49','20','88','45','3','60','0','57','77');

Insert Into #statesfare Values('Florida','79','80','37','12','90','30','24','48','0','50');

Insert Into #statesfare Values('Georgia','33','46','16','30','46','72','42','85','18','0');

Declare @.StartFrom as varchar(100);

Declare @.EndAt as varchar(100);

--Sample Input

Set @.StartFrom = 'Alabama'

Set @.EndAt = 'Georgia'

--Using Dynamic SQL (Supports Both SQL Server 2000 & 2005)

Exec ('Select ' + @.EndAt + ' Fare From #statesfare Where [Starting From] =''' +@.StartFrom + '''');

--Using UNPIVOT operator Only on SQL Server 2005

Select

[Starting From]

,[Target States]

,Fares

From

#statesfare P

UNPIVOT

(

Fares FOR [Target States] IN

(

[Alabama],

[Alaska],

[Arizona],

[Arkansas],

[California],

[Colorado],

[Connecticut],

[Delaware],

[Florida],

[Georgia]

)

) as UPVT

Where

[Starting From] = @.StartFrom

And [Target States] = @.EndAt

|||

I recommend to use quotename() function to avoid sql injection.

Code Snippet

set @.qeury = 'select ' + quotename(@.to_sate) + ' from ....';

quotename('abc') returns '[abc]';

Regards,

|||This is my table

FROM AL AR AZ CA AL 1.75 1.95 1.10 1.75 AR 1.50 1.50 1.50 1.75 AZ 1.50 1.50 1.50 1.75 CA 1.50 1.50 1.50 1.75

I Need to use a stored procedure to select one of these decimal rates based on two input parameters (@.fromstate AND @.tostate) and return the rate as an out put parameter(@.rate). I tried this even though I knew it was too simple to worrk.

ALTER PROCEDURE RC_Get_Rate

(

@.origstate nvarchar(255),

@.deststate nvarchar(255),

@.rate nvarchar(255) OUTPUT

)

AS

SET NOCOUNT ON

BEGIN

SELECT @.rate = @.deststate

FROM Rates48

WHERE Origin_State = @.origstate

END

RETURN

I'm sorry if you're previous posts already answered this, I thought I might not have been clear enougn as to my situation and my goal. I really did search a lot on this topic but got nowhere. Again if you can help (or already did) thanks, and if you can't thanks anyway, Gregg|||

here you go...

Code Snippet

Create Table rates48 (

[Origin_State] Varchar(100) ,

[AL] float ,

[AR] float ,

[AZ] float ,

[CA] float

);

Go

Insert Into rates48 Values('AL','1.75','1.95','1.10','1.75');

Insert Into rates48 Values('AR','1.50','1.50','1.50','1.75');

Insert Into rates48 Values('AZ','1.50','1.50','1.50','1.75');

Insert Into rates48 Values('CA','1.50','1.50','1.50','1.75');

Go

Create PROCEDURE RC_Get_Rate

(

@.origstate nvarchar(255),

@.deststate nvarchar(255),

@.rate nvarchar(255) OUTPUT

)

AS

SET NOCOUNT ON

BEGIN

Declare @.Query as NVarchar(1000);

Set @.Query = N'SELECT @.rateout = ' + quotename(@.deststate) + '

FROM Rates48

WHERE Origin_State = @.origstatein';

Exec sp_executesql @.Query, N'@.origstatein varchar(255), @.rateout nvarchar(255) output',@.origstatein = @.origstate, @.rateout=@.rate OUTPUT;

Return;

END

Go

Declare @.rate nvarchar(255)

Exec RC_Get_Rate 'AL', 'AZ', @.rate OUTPUT

Select @.rate

|||Thank You very much Manivannan.D.Sekaran. That works very well. I must admit though I don't know how. Could you maybe recommend a good book I could pick up to further myself on T-SQL. I would like to learn SPROC's, Functions, and some more advanced SQL. Well, again, thank you very much Manivannan.D.Sekaran, and also thank you to everyone who took the time to read my post and help me out. I really appreciate it. Thanks, Gregg

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?