Passing DateAdd Parameters Via Sproc

Hello
I am kinda stumped with this one and would appreciate any suggestions.
I want to pass two parameters (@.period_unit and @.period_type) so that the
sproc
can calculate the exiry date which is inserted into the table. Below is some
psedo code
for the sproc in question. I envisaged calling the sproc as such ...
EXEC MYPROC 20, 100, 200, 'year', 1, 1
or
EXEC MYPROC 20, 100, 200, 'month', 6, 1
However, when I try and save the sproc I get the error
"invalid parameter 1 specified for dateadd". If I "hard code" the
@.date_expires_utc variable to year, it works fine meaning that the
dateadd function accepts the value @.period_unit as it's second
parameter but doesn't accept @.period_type as it's first parameter.
Go figure! Anyway, any and all suggestions most welcome and encouraged.
PS: I realize that I could pass the creation and expiry date from the
calling code
but I would prefer the sproc to calculate these dates.
Thanks Peter
---
CREATE TABLE MYTABLE (
coupon_id integer identity(1, 1) not null,
member_id integer not null,
transaction_id integer not null,
status char(1) not null,
date_created_utc smalldatetime not null,
date_expires_utc smalldatetime not null
)
CREATE PROCEDURE MYPROC
@.count integer,
@.member_id integer,
@.transaction_id integer,
@.period_unit integer,
@.period_type varchar(20),
@.status char(1)
AS
SET NOCOUNT ON
DECLARE @.date_created_utc smalldatetime
DECLARE @.date_expires_utc smalldatetime
DECLARE @.counter integer
SET @.date_created_utc = GetUtcDate()
SET @.date_expires_utc = DateAdd(@.period_unit, @.period_unit,
@.date_created_utc)
SET @.counter = 1
WHILE (@.counter <= @.count)
BEGIN
INSERT INTO MYTABLE (
[member_id],
[transaction_id],
[status],
[date_created_utc],
[date_expires_utc] )
VALUES (
@.member_id,
@.transaction_id,
@.status,
@.date_created_utc,
@.date_expires_utc )
SET @.counter = @.counter + 1
END
SELECT coupon_id
FROM MYTABLE
WHERE transaction_id = @.transaction_id
ORDER BY coupon_id
---
--== Posted via mcse.ms - Unlimited-Unrestricted-Secure Usenet News=
=--
http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+ New
sgroups
--= East and West-Coast Server Farms - Total Privacy via Encryption =--Hi, Peter
Instead of:
SET @.date_expires_utc = DateAdd(@.period_unit, @.period_unit,
@.date_created_utc)
You can use:
IF @.period_unit='year'
SET @.date_expires_utc = DateAdd(year, @.period_unit,
@.date_created_utc)
IF @.period_unit='month'
SET @.date_expires_utc = DateAdd(month, @.period_unit,
@.date_created_utc)
[...]
Razvan|||Razvan
Thanks for that. It is, in fact, exactly what I ended up doing although it
seems a little messy.
I still find it a little strange that the dateadd function accepts an sproc
parameter as it's own second
parameter but not as its first parameter.
Regards
Peter
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1139386825.679863.20140@.f14g2000cwb.googlegroups.com...
> Hi, Peter
> Instead of:
> SET @.date_expires_utc = DateAdd(@.period_unit, @.period_unit,
> @.date_created_utc)
> You can use:
> IF @.period_unit='year'
> SET @.date_expires_utc = DateAdd(year, @.period_unit,
> @.date_created_utc)
> IF @.period_unit='month'
> SET @.date_expires_utc = DateAdd(month, @.period_unit,
> @.date_created_utc)
> [...]
> Razvan
>
--== Posted via mcse.ms - Unlimited-Unrestricted-Secure Usenet News=
=--
http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+ New
sgroups
--= East and West-Coast Server Farms - Total Privacy via Encryption =--

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 an IN (a, b, c) list to a sproc as a string -- best method?

I want to do something like this in a stored proc:

--

Create Procedure dbo.GetPatients
@.PatientIdList varchar(200) -- comma separated list of PatientIDs
As

Select *
From Patients
Where PatientId In (@.PatientIdList)

--

I know the above won't work, but of course what I want is if
@.PatientIdList = '1,2,3' then I want Patient records with PatientIds
1, 2, and 3 returned.

It looks like the only way to do this is to build the SQL statement as
a string within the stored procedure ... which pretty much defeats the
usefulness of using precompiled sprocs as I understand it (better off
building a dynamic query against a View in that case).

Thoughts?

Joel Thornton ~ <groups@.joelpt.eml.cc>Joel,

Erland has a decent writing on this topic.

http://www.sommarskog.se/arrays-in-sql.html

--
-oj
http://www.rac4sql.net

"Joel Thornton" <joelpt@.eml.cc> wrote in message
news:c190a45a.0401072012.5c38ba06@.posting.google.c om...
> I want to do something like this in a stored proc:
> --
> Create Procedure dbo.GetPatients
> @.PatientIdList varchar(200) -- comma separated list of PatientIDs
> As
> Select *
> From Patients
> Where PatientId In (@.PatientIdList)
> --
> I know the above won't work, but of course what I want is if
> @.PatientIdList = '1,2,3' then I want Patient records with PatientIds
> 1, 2, and 3 returned.
> It looks like the only way to do this is to build the SQL statement as
> a string within the stored procedure ... which pretty much defeats the
> usefulness of using precompiled sprocs as I understand it (better off
> building a dynamic query against a View in that case).
>
> Thoughts?
> Joel Thornton ~ <groups@.joelpt.eml.ccsql

Passing an Array and/or Variable Field Name to an SProc

I have 2 questions.

I am trying to write a stored procedure to update a table. I am trying
to pass a variable that represents the name of the column/field and
another for the value that I am changing.

For example:
@.FieldName VARCHAR(100)
@.FieldValue VARCHAR(100)
AS
UPDATE tblTHETABLE
SET @.FieldName = @.FieldValue

First is it possible to use a variable as the column/field name? If
so, how do I go about it?

Also, it would be nice if I could have the @.FieldName and @.FieldValue
variables as arrays. Is that possible?

Thank-you for any assistance
Bill"~TheIcemanCometh~" <bhazelwood@.delta-elevator.com> wrote in message
news:8d372e43.0402171320.5d263673@.posting.google.c om...
> I have 2 questions.
> I am trying to write a stored procedure to update a table. I am trying
> to pass a variable that represents the name of the column/field and
> another for the value that I am changing.
> For example:
> @.FieldName VARCHAR(100)
> @.FieldValue VARCHAR(100)
> AS
> UPDATE tblTHETABLE
> SET @.FieldName = @.FieldValue
> First is it possible to use a variable as the column/field name? If
> so, how do I go about it?
> Also, it would be nice if I could have the @.FieldName and @.FieldValue
> variables as arrays. Is that possible?
> Thank-you for any assistance
> Bill

The short answer is that it's possible, but probably not advisable. The
first link should help explain why; the second covers arrays:

http://www.sommarskog.se/dynamic_sql.html
http://www.sommarskog.se/arrays-in-sql.html

Simon|||[posted and mailed, please reply in news]

~TheIcemanCometh~ (bhazelwood@.delta-elevator.com) writes:
> I am trying to write a stored procedure to update a table. I am trying
> to pass a variable that represents the name of the column/field and
> another for the value that I am changing.
> For example:
> @.FieldName VARCHAR(100)
> @.FieldValue VARCHAR(100)
> AS
> UPDATE tblTHETABLE
> SET @.FieldName = @.FieldValue
> First is it possible to use a variable as the column/field name? If
> so, how do I go about it?
> Also, it would be nice if I could have the @.FieldName and @.FieldValue
> variables as arrays. Is that possible?

Anything is possible, but what's the point? Why not construct the
SQL statements in client code instead?

If you really want to know how to do it, I have an article on my web
site. There you also learn why you should not do it.
http://www.sommarskog.se/dynamic_sql.html.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Pass concatenated string to SPROC

Hello,

We are creating an app to search through products. On the presentation layer, we allow a user to 'select' categories (up to 10 check boxes). When we get the selected check boxes, we create a concatenated string with the values.

My question is: when I pass the concatenated string to the SPROC, how would I write a select statement that would search through the category field, and find the values in the concatenated string?

Will I have to create Dynamic SQL to do this?...or... can I do something like this...

@.ConcatenatedString --eg. 1,2,3,4,5,6,7
SELECT col1, col2, col3 FROM TABLE WHERE CategoryId LIKE @.ConcatenatedString

Thanks for your help.you need to use IN.

hth|||You can to dynamically build an SQL string using the IN keyword and then execute it. Or, you can build a function to parse the list and return a table of category ids. Then use you IN clause with a select statement from the table or you could join to the table that's returned. I'd just do the dynamic SQL, but you could try both to see what's faster in your situation.

Pass a collection to a SPROC

I am collecting companyID's form a data grid, I want to pass the selected values to a sproc via a variable. Any idea on the syntax?

this works using a query string within my code

WHERE (dbo.Promotions.ExpirationDate > GETDATE()) AND (dbo.Promotions.CompanyID IN (" + selectedCompanies + "))

this doesn't within my sproc

WHERE (dbo.Promotions_ByLink.ExpirationDate > GETDATE()) AND (dbo.Promotions_ByLink.CompanyID IN (@.SelectedCompanies))

I also tried

WHERE (dbo.Promotions_ByLink.ExpirationDate > GETDATE()) AND (dbo.Promotions_ByLink.CompanyID IN (SELECT @.SelectedCompanies))

and

WHERE (dbo.Promotions_ByLink.ExpirationDate > GETDATE()) AND (dbo.Promotions_ByLink.CompanyID IN (' + @.SelectedCompanies + '))

ThanksI know it's a little dirty, but I just created an user defined function to parse out a comma delimited string, and return a recordset of one column. Select [code] FROM dbo.MyFunctionName(@.myCommaDelimitedString) can be used in your where clause.|||Thanks,
I will give that a try|||We've had this discussion several times in this forum -- there are various solutions and various advocates of the different solutions (e.g.view post 306752). I happen to use the one KraGiE suggests. Here's an example of the parsing function:Treat Yourself to Fn_Split().

Terri|||Thanks a TON..I search the other post, but I didn't know what to search for.
Thanks again