sql database integrity: function

Showing posts with label function. Show all posts

Thursday, March 29, 2012

Creditcard Validation script

Does anyone have a T-SQL script or function that validates US creditcard
numbers?
I am not talking about whether the account is current, just whether the
numeric value of the card is valid. Something similar to what one might use
for zip codes or phone numbers.
Right now I have something that looks for 15 or 16 characters in length and
then checks the first digit for the type of card (e.g., 3 = Amex).
However I was hoping that someone who has worked with this issue might give
me some insights into what conventions or standards a credit card number mus
t
comply with in order to be valid.
ThanksOn Sun, 19 Feb 2006 11:12:26 -0800, "Dave" <Dave@.discussions.microsoft.com>
wrote:
in <331D1E4C-2EB2-4ABF-ACC5-08F5D3E5FC2A@.microsoft.com>

>Does anyone have a T-SQL script or function that validates US creditcard
>numbers?
>I am not talking about whether the account is current, just whether the
>numeric value of the card is valid. Something similar to what one might use
>for zip codes or phone numbers.
>Right now I have something that looks for 15 or 16 characters in length and
>then checks the first digit for the type of card (e.g., 3 = Amex).
>However I was hoping that someone who has worked with this issue might give
>me some insights into what conventions or standards a credit card number mu
st
>comply with in order to be valid.
>Thanks
Here's a URL that I took to heart and converted to VBScript for use in my
classic ASP environment:
http://www.sitepoint.com/print/card...ation-class-php
If you want to see the VBScript also, post back and I'll put it up. I felt
that
this covered it well enough that ashamedly I didn't make any efforts to furt
her
validate the facts that the author presented.
Stefan Berglund

Sunday, March 25, 2012

creating user defined parameters with input prompts

Hi,
i am trying to create a user defined function that return a table and it
prompts for two inputs
i have created the functions but how do i get the system to prompt me for
the inputs via a view ?
appreciate any advise
tks & rdgsSQL Server cannot prompt a user for anything, as it runs on the server machines, as a service. You
need to have a client application do that.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:8AB52501-7876-4870-ADA1-6DC8BB93785E@.microsoft.com...
> Hi,
> i am trying to create a user defined function that return a table and it
> prompts for two inputs
> i have created the functions but how do i get the system to prompt me for
> the inputs via a view ?
> appreciate any advise
> tks & rdgs|||maxzsim,
Something like this?
CREATE FUNCTION myfunc
@.param1 <datatype>,
@.param2 <datatype>
RETURNS TABLE
AS
RETURN
(<your select statement>
where col1 = @.param1
and col2 = @.param2)
GO
Mark.
"maxzsim" wrote:
> Hi,
> i am trying to create a user defined function that return a table and it
> prompts for two inputs
> i have created the functions but how do i get the system to prompt me for
> the inputs via a view ?
> appreciate any advise
> tks & rdgs|||Hi ,
does a view able to prompt the user for the parameters then , i can only
open a view as a query and where it allowed me to create parameters but it
seems like there's no way for me to save those changes at all
is there other way thru using a view ?
tks & rdgs
"Tibor Karaszi" wrote:
> SQL Server cannot prompt a user for anything, as it runs on the server machines, as a service. You
> need to have a client application do that.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> news:8AB52501-7876-4870-ADA1-6DC8BB93785E@.microsoft.com...
> > Hi,
> >
> > i am trying to create a user defined function that return a table and it
> > prompts for two inputs
> >
> > i have created the functions but how do i get the system to prompt me for
> > the inputs via a view ?
> >
> > appreciate any advise
> >
> > tks & rdgs
>
>|||Hi Mark,
in this case it does not run unless i input some values directly however,
i wanted to be prompted to enter the 2 parameters
will a view/stored procedure be able to do that ?
rdgs
"Mark Allison" wrote:
> maxzsim,
> Something like this?
> CREATE FUNCTION myfunc
> @.param1 <datatype>,
> @.param2 <datatype>
> RETURNS TABLE
> AS
> RETURN
> (<your select statement>
> where col1 = @.param1
> and col2 = @.param2)
> GO
> Mark.
> "maxzsim" wrote:
> > Hi,
> >
> > i am trying to create a user defined function that return a table and it
> > prompts for two inputs
> >
> > i have created the functions but how do i get the system to prompt me for
> > the inputs via a view ?
> >
> > appreciate any advise
> >
> > tks & rdgs|||gain, prompting a user need to be done in your client applications. You cannot write the whole
application in SQL only, SQL is only a data retrieval and modification language. The interaction
with the user need to be done with a client application, written in VB. C#, C++ Delphi Access or
something like that.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:4B01C6CF-392B-499F-8D9F-3824DF3AB2A2@.microsoft.com...
> Hi ,
> does a view able to prompt the user for the parameters then , i can only
> open a view as a query and where it allowed me to create parameters but it
> seems like there's no way for me to save those changes at all
> is there other way thru using a view ?
> tks & rdgs
> "Tibor Karaszi" wrote:
>> SQL Server cannot prompt a user for anything, as it runs on the server machines, as a service.
>> You
>> need to have a client application do that.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
>> news:8AB52501-7876-4870-ADA1-6DC8BB93785E@.microsoft.com...
>> > Hi,
>> >
>> > i am trying to create a user defined function that return a table and it
>> > prompts for two inputs
>> >
>> > i have created the functions but how do i get the system to prompt me for
>> > the inputs via a view ?
>> >
>> > appreciate any advise
>> >
>> > tks & rdgs
>>|||tks Tibor for taking time out to explain to me
Cheers
"Tibor Karaszi" wrote:
> gain, prompting a user need to be done in your client applications. You cannot write the whole
> application in SQL only, SQL is only a data retrieval and modification language. The interaction
> with the user need to be done with a client application, written in VB. C#, C++ Delphi Access or
> something like that.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> news:4B01C6CF-392B-499F-8D9F-3824DF3AB2A2@.microsoft.com...
> > Hi ,
> >
> > does a view able to prompt the user for the parameters then , i can only
> > open a view as a query and where it allowed me to create parameters but it
> > seems like there's no way for me to save those changes at all
> >
> > is there other way thru using a view ?
> >
> > tks & rdgs
> >
> > "Tibor Karaszi" wrote:
> >
> >> SQL Server cannot prompt a user for anything, as it runs on the server machines, as a service.
> >> You
> >> need to have a client application do that.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> >> news:8AB52501-7876-4870-ADA1-6DC8BB93785E@.microsoft.com...
> >> > Hi,
> >> >
> >> > i am trying to create a user defined function that return a table and it
> >> > prompts for two inputs
> >> >
> >> > i have created the functions but how do i get the system to prompt me for
> >> > the inputs via a view ?
> >> >
> >> > appreciate any advise
> >> >
> >> > tks & rdgs
> >>
> >>
> >>
>
>

creating user defined parameters with input prompts

Creating User Defined Function for Rolling Calendar Year

Has anyone ever created a UDF for a rolling calendar year? I would like to
use that instead of writing this kind of stuff all the time:
if(datepart(month,@.CurrentDate) =12)
Begin
set @.PastDate = '1/1/'+convert(varchar, @.SelectedYear)
end
if(datepart(month,@.CurrentDate) !=12)
Begin
set @.PastDate = dateadd(month,1,@.PastDate)
End
set @.CurrentDate = dateadd(month,1,@.Currentdate)
if(datepart(day, @.CurrentDate) <= 30)
Begin
set @.CurrentDateStep = dateadd(day,1,@.CurrentDate)
if datepart(month,@.CurrentDateStep) = datepart(month,@.CurrentDate)
Begin
set @.currentDate = @.CurrentDateStep
end
if datepart(month, @.CurrentDate) = 3
Begin
set @.CurrentDate = dateadd(day,2,@.CurrentDate)
end
end
Thanks in advance for any assistance.
Thanks,
SPHow about a calendar table?
http://www.aspfaq.com/2519
"SP" <SP@.discussions.microsoft.com> wrote in message
news:8E1056B9-3011-4523-894A-1087FE936B1A@.microsoft.com...
> Has anyone ever created a UDF for a rolling calendar year? I would like to
> use that instead of writing this kind of stuff all the time:
> if(datepart(month,@.CurrentDate) =12)
> Begin
> set @.PastDate = '1/1/'+convert(varchar, @.SelectedYear)
> end
> if(datepart(month,@.CurrentDate) !=12)
> Begin
> set @.PastDate = dateadd(month,1,@.PastDate)
> End
> set @.CurrentDate = dateadd(month,1,@.Currentdate)
> if(datepart(day, @.CurrentDate) <= 30)
> Begin
> set @.CurrentDateStep = dateadd(day,1,@.CurrentDate)
> if datepart(month,@.CurrentDateStep) = datepart(month,@.CurrentDate)
> Begin
> set @.currentDate = @.CurrentDateStep
> end
> if datepart(month, @.CurrentDate) = 3
> Begin
> set @.CurrentDate = dateadd(day,2,@.CurrentDate)
> end
> end
> Thanks in advance for any assistance.
> Thanks,
> SP
>|||Hi,
Pardon me for my ignorance, can you please elucidate with an example what
you are trying to achieve?
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||Sure. Sorry for the sparse information.
At the company that I work for, they are always interested in a rolling
calendar year. When I write reports with Reporting Services, they may run
them for say 2006. The end date would always change depending on what month
the report is run. For example, if the report is run today, they would want
the results to show 6/1/2005 to 5/31/2006. If it gets run next month,
7/1/2005 to 6/30/2006, etc.
I always have to wrote some funky code to consider February, etc.
I just thought if someone had written a UDF, that might help. Or send me in
the right direction.
I hope this clarifies things a little and I will definitely take a look at
the Date table. I have done similiar things with an Access database, but
didn't think about it for work.
Thanks to all for looking.
SP
"Omnibuzz" wrote:

> Hi,
> Pardon me for my ignorance, can you please elucidate with an example wha
t
> you are trying to achieve?
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>|||Maybe, this should work.. If I understood it wrong.. correct me..
declare @.a datetime
set @.a = getdate() -- Date on which you are running the report
select
dateadd(mm,datediff(mm,0,dateadd(yy,-1,@.a)),0) as Begin_dt,
dateadd(mm,datediff(mm,0,@.a),0)-1 as end_dt
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||As written, the End_dt is midnight on the last day of the month, effectively
leaving out all entries for that day.
I would add one more dateadd step to include ALL times on the last day of th
e month (up to 23:59:59.997)
DECLARE @.Today datetime
SET @.Today = getdate() -- Date on which you are running the report
SELECT
dateadd( mm, datediff( mm, 0, dateadd( yy, -1, @.Today )), 0 ) AS 'Begin_Dt'
, dateadd( ms, -2, dateadd(mm, datediff( mm, 0, @.Today ), 0 ) ) AS 'End_Dt'
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message news:30BC1C01-180E-442C-8D
BE-15E9B60B0C08@.microsoft.com...
> Maybe, this should work.. If I understood it wrong.. correct me..
>
> declare @.a datetime
> set @.a = getdate() -- Date on which you are running the report
> select
> dateadd(mm,datediff(mm,0,dateadd(yy,-1,@.a)),0) as Begin_dt,
> dateadd(mm,datediff(mm,0,@.a),0)-1 as end_dt
>
> --
> -Omnibuzz (The SQL GC)
>
> http://omnibuzz-sql.blogspot.com/
>
>|||I think it would be cleaner to use the next month's start instead of 3 ms
earlier, and use < on the end_dt instead of <= (and obviously, don't use
between).
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:%23KeXiJIlGHA.4660@.TK2MSFTNGP05.phx.gbl...
As written, the End_dt is midnight on the last day of the month, effectively
leaving out all entries for that day.
I would add one more dateadd step to include ALL times on the last day of
the month (up to 23:59:59.997)
DECLARE @.Today datetime
SET @.Today = getdate() -- Date on which you are running the report
SELECT
dateadd( mm, datediff( mm, 0, dateadd( yy, -1, @.Today )), 0 ) AS
'Begin_Dt'
, dateadd( ms, -2, dateadd(mm, datediff( mm, 0, @.Today ), 0 ) ) AS
'End_Dt'
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:30BC1C01-180E-442C-8DBE-15E9B60B0C08@.microsoft.com...
> Maybe, this should work.. If I understood it wrong.. correct me..
> declare @.a datetime
> set @.a = getdate() -- Date on which you are running the report
> select
> dateadd(mm,datediff(mm,0,dateadd(yy,-1,@.a)),0) as Begin_dt,
> dateadd(mm,datediff(mm,0,@.a),0)-1 as end_dt
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>|||Agreed.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OhBC5SIlGHA.984@.TK2MSFTNGP05.phx.gbl...
>I think it would be cleaner to use the next month's start instead of 3 ms
>earlier, and use < on the end_dt instead of <= (and obviously, don't use
>between).
>
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:%23KeXiJIlGHA.4660@.TK2MSFTNGP05.phx.gbl...
> As written, the End_dt is midnight on the last day of the month,
> effectively leaving out all entries for that day.
> I would add one more dateadd step to include ALL times on the last day of
> the month (up to 23:59:59.997)
> DECLARE @.Today datetime
> SET @.Today = getdate() -- Date on which you are running the report
> SELECT
> dateadd( mm, datediff( mm, 0, dateadd( yy, -1, @.Today )), 0 ) AS
> 'Begin_Dt'
> , dateadd( ms, -2, dateadd(mm, datediff( mm, 0, @.Today ), 0 ) ) AS
> 'End_Dt'
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
> news:30BC1C01-180E-442C-8DBE-15E9B60B0C08@.microsoft.com...
>sql

Creating UDF?

Creating UDF In SQL Server 2005?

I find PLENTY of examples of User Defined functions. However, I
struggle to see how/where to SAVE this function! I can go under the
database, Programmability, Functions, Scalar Functions, and create a
scalar function. However, when I click SAVE I get prompted to save the
.sql file to disk. If I close that (query) tab, then the function is
gone as well.
How do I persist the function in a database? I should note that this is
for SQL Server 2005.<mikes@.vmsmailingservices.com> wrote in message
news:1132627572.039473.90980@.g47g2000cwa.googlegroups.com...
> I find PLENTY of examples of User Defined functions. However, I
> struggle to see how/where to SAVE this function! I can go under
the
> database, Programmability, Functions, Scalar Functions, and create
a
> scalar function. However, when I click SAVE I get prompted to save
the
> .sql file to disk. If I close that (query) tab, then the function
is
> gone as well.
> How do I persist the function in a database? I should note that
this is
> for SQL Server 2005.
>
mikes,
The .sql files are just text files with another extension. Of
course you want to save them as your scripts, but if you want to
check out your stored procedures and functions in the database,
then:
SELECT R1.*
FROM INFORMATION_SCHEMA.ROUTINES AS R1
Sincerely,
Chris O.|||Hi Mike,
When you run the text that you have just created then teh SQL server creates
the object in the database. The text is no longer needed by the SQL Server.
You should however save this text to a file and that file can be stored
anywhere you like
kind regards
Greg O
--
Looking to use CLR in SQL 2005. Try some pre-build CLR Functions and SP
AGS SQL 2005 Utilities, over 20+ functions
http://www.ag-software.com/?tabid=38
<mikes@.vmsmailingservices.com> wrote in message
news:1132627572.039473.90980@.g47g2000cwa.googlegroups.com...
>I find PLENTY of examples of User Defined functions. However, I
> struggle to see how/where to SAVE this function! I can go under the
> database, Programmability, Functions, Scalar Functions, and create a
> scalar function. However, when I click SAVE I get prompted to save the
> .sql file to disk. If I close that (query) tab, then the function is
> gone as well.
> How do I persist the function in a database? I should note that this is
> for SQL Server 2005.
>|||Why save it when sql will give you the create statement any time you need
it?
William Stacey [MVP]
"GregO" <grego@.community.nospam> wrote in message
news:e8rKpwx7FHA.2816@.tk2msftngp13.phx.gbl...
> Hi Mike,
> When you run the text that you have just created then teh SQL server
> creates the object in the database. The text is no longer needed by the
> SQL Server. You should however save this text to a file and that file can
> be stored anywhere you like
> kind regards
> Greg O
> --
> Looking to use CLR in SQL 2005. Try some pre-build CLR Functions and SP
> AGS SQL 2005 Utilities, over 20+ functions
> http://www.ag-software.com/?tabid=38
> <mikes@.vmsmailingservices.com> wrote in message
> news:1132627572.039473.90980@.g47g2000cwa.googlegroups.com...
>|||Hello mikes@.vmsmailingservices.com,

> I find PLENTY of examples of User Defined functions. However, I
> struggle to see how/where to SAVE this function! I can go under the
> database, Programmability, Functions, Scalar Functions, and create a
> scalar function. However, when I click SAVE I get prompted to save the
> .sql file to disk. If I close that (query) tab, then the function is
> gone as well.
> How do I persist the function in a database? I should note that this
> is for SQL Server 2005.
Rephrasing some of the answers you've gotten thus far, as soon as you execut
e
the CREATE FUNCTION statement, a "complied" version of the function is commi
tted
to the database. There's no need to save the text version of it, since, when
you issued that same CREATE FUNCTION statement, SQL Server also saved that
to the database.
Make sense?
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Hi William
Seriously?
What happens when some deletes it or changes the structure. You need
something to compare it to. There so many reasons for keeping the command
syntax and next to none for not keeping it. When in doubt keep the command.
kind regards
Greg O
--
Looking to use CLR in SQL 2005. Try some pre-build CLR Functions and SP
AGS SQL 2005 Utilities, over 20+ functions
http://www.ag-software.com/?tabid=38
"William Stacey [MVP]" <william.stacey@.gmail.com> wrote in message
news:OVdiJWy7FHA.472@.TK2MSFTNGP15.phx.gbl...
> Why save it when sql will give you the create statement any time you need
> it?
> --
> William Stacey [MVP]
> "GregO" <grego@.community.nospam> wrote in message
> news:e8rKpwx7FHA.2816@.tk2msftngp13.phx.gbl...
>|||>
> Rephrasing some of the answers you've gotten thus far, as soon as you exec
ute
> the CREATE FUNCTION statement, a "complied" version of the function is com
mitted
> to the database. There's no need to save the text version of it, since, wh
en
> you issued that same CREATE FUNCTION statement, SQL Server also saved that
> to the database.
> Make sense?
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
This helps a lot, thanks everyone!
a couple more related questions, based on your responses...
How does the DB engine manage a new function of the same name? Prompted
to override?
How can one determine a list of the functions in a database?
How is (function) debugging done, if the function is internal to the DB
Engine?|||Hello mikes@.vmsmailingservices.com,

> How does the DB engine manage a new function of the same name?
> Prompted to override?
No, unlike other development tools, most of the SQL Server tools *trusts*
the operator to know what he or she is doing. Most shops have a CVS (or simi
lar)
set up for storing scripts for objects. MS is offering one with team system
too. Native integration of that into SQL Server is probably a couple of vers
ions
away I suspect.

> How can one determine a list of the functions in a database?
select * from sys.objects where type_desc like '%_FUNCTION'

> How is (function) debugging done, if the function is internal to the
> DB Engine?
Like any other environment, the execution engine know that it has breakpoint
s
that it yields control to another process. It just so happens that SQL Serve
r's
engine uses a common Visual Studio API for debugging. No magic.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/

Thursday, March 29, 2012

Creditcard Validation script

Sunday, March 25, 2012

creating user defined parameters with input prompts

creating user defined parameters with input prompts

creating user defined parameters with input prompts

Creating User Defined Function for Rolling Calendar Year

Creating UDF?

Creating UDF In SQL Server 2005?

sql database integrity

Blog Archive

About Me